Fetch data from existing sqlite database

Question

I am having an existing sqlite database. I am developing an android app and I want to connect it with this existing sqlite DataBase.

Problem 1: I have already included the sqlite database in my project via "DDMS push database function" as per my instructor's advise. Now I want to fetch the data from database, do I need to use SQLiteOpenHelper. If yes, how to use it and what will be coded in onCreate(SQLiteDatabase db) function and onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) function as we already have the Database with us, we don't really need to create it.

Problem 2: What should be done to simply fetch the required data from the existing database.

Being a newbie, I am quite confused, can someone please explain these concepts and guide me to overcome this issue. Any help would be appreciated.

I have seen a tutorial also for this purpose as sugggested by @TronicZomB, but according to this tutorial (http://www.reigndesign.com/blog/using-your-own-sqlite-database-in-android-applications/), I must be having all the tables with primary key field as _id.

I have 7 tables namely destinations, events, tour, tour_cat, tour_dest, android_metadata and sqlite_sequence. Out of all, only tour_dest is not fulfilling the conditions of having a primary key named as _id. How to figure out this one?

Following is the screenshot of table which is lacking the primary key field necessary for binding id fields of database tables.

score 5 · Accepted Answer · answered Jul 22 '13 at 18:21

5

The onCreate and onUpgrade methods will be empty since you already have the database. There is a great tutorial on how to achieve this here.

You could then access the database like such (example):

public ArrayList<String> getValues(String table) {
    ArrayList<String> values = new ArrayList<String>();

    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = db.rawQuery("SELECT value FROM " + table, null);

    if(cursor.moveToFirst()) {
        do {
            values.add(cursor.getString(cursor.getColumnIndex("value")));
        }while(cursor.moveToNext());
    }

    cursor.close();
    db.close();
    return values;
}

answered Jul 22 '13 at 18:21

TronicZomB

8,667
7
35
50

I have seen that tut, but i have a minor issue regarding that. It says that all your tables must have a field _id. But I have a table that doesnt possess primary key field. And according to the tutorial I need to have a primary key field named as _id. Now for that I have to recreate the table which will be a loss of data. what to do.. – divyang7 Jul 22 '13 at 18:42
hmm... I used that tutorial and have my id column in one table as "id" and none of my other tables have a primary key integer and it works fine for me... – TronicZomB Jul 22 '13 at 18:45
I have edited my question and have included an image too describing current structure of the table that is lacking the primary key _id. Rest all tables have a primary key _id and thus fulfill the condition of tutorial that you provided. – divyang7 Jul 22 '13 at 19:02

score 1 · Answer 2 · answered Jul 22 '13 at 18:22

1

Unless you are very comfortable with queries, databases, etc. I highly recommend you use http://satyan.github.io/sugar/ , it will also remove a lot of the boiler plate code required to do sqlite in Android

answered Jul 22 '13 at 18:22

Ryan S

4,549
2
21
33

Divers · Answer 3 · 2013-07-22T18:30:26.177

1. If DB already exists, onCreate will not invoke. onUpgrade will be invoked only if you will change DB version. onUpgrade you should to use if there some changes in your APP's database, and you have to make migration on new structure of data smoothly.

public class DbInit extends SQLiteOpenHelper {
    private static final String DATABASE_NAME = "name";
    private static final int DATABASE_VERSION = 3;
    private static final String DATABASE_CREATE = "create table  connections  . .. . ...

    public DbInit(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase database) {
        database.execSQL(DATABASE_CREATE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
         if (isChangeFromToVersion(1, 2, oldVersion, newVersion)) {
             //Execute UPDATE here
         }
    }

    private boolean isChangeFromToVersion(int from, int to, int oldVersion, int newVersion ) {
        return (from == oldVersion && to == newVersion);
  }
....

2. Simple example how to open connection to DB and get cursor object.

public class DAO {

private SQLiteDatabase database;
private DbInit dbHelper;

public ConnectionDAO(Context context) {
    dbHelper = new DbInit(context);
}

public void open() throws SQLException {
    database = dbHelper.getWritableDatabase();
}

public Connection getConnectionById(long id) {
    Cursor cursor = null;
    try {
        open();
        cursor = database.query(DbInit.TABLE_CONNECTIONS, allColumns, DbInit.COLUMN_ID + " = '" + id + "'", null, null, null, null);
        if (!cursor.moveToFirst())
            return null;
        return cursorToConnection(cursor);
    } finally {
        if (cursor != null)
            cursor.close();
        close();
    }
}

private Connection cursorToConnection(Cursor cursor) {
    Connection connection = new Connection();
    connection.setId(cursor.isNull(0) ? null : cursor.getInt(0));
    connection.setName(cursor.isNull(1) ? null : cursor.getString(1));
    .....
    .....
    return connection;
}

Fetch data from existing sqlite database

3 Answers3

Linked