Monday, 26 September 2011

SQLite - Create multiple tables with different SQLiteOpenHelper in the same database

In order to have the code much more clear, when multiple tables are needed in the same database, several SQLiteOpenHelper could be used. This allow they have different methods depending on the table avoiding having a very long java file. Lately there could be a general class which offer public methods to access all the databases.

One way to do is using the SQLiteOpenHelper by overriding the method onOpen(SQLiteDatabase _db), which is executed when the database is opened.

1. Modify the SQL sentence
The SQL sentence for create the table should be modified. Instead of using

CREATE TABLE table_name
utilize:
CREATE TABLE IF NOT EXISTS table_name

2. Override the method onOpen
The method onOpen should be modified in order to create the table if the database is already opened.

@Override
public void onOpen(SQLiteDatabase _db) {
    _db.execSQL(CREATE_TABLE);
}

This database created will work exactly the same as if the java code are in the same file. This is because the manage of the database is a level below to the java code. No matter how the java code is, if the SQL sentences are the same, the database manager should act the same way.

10 comments:

  1. Thanks, that help me, i was having issues whit this using the onCreate method.

    ReplyDelete
  2. Great thanks! I was starting to think I had to use the same OpenHelper...

    ReplyDelete
    Replies
    1. The method onOpen is not so commonly used but it could be useful for many cases.

      Delete
  3. Thank you. I searched several articles on how to create multiple tables for each TableAdapter and all come with solutions like building a handler. This way is much more Clean and simple.

    ReplyDelete
    Replies
    1. Hi Bruno:
      You are welcome. For now I am too busy. When I have more time, I want to create an example of it and upload them to the gitHub. Then you can get better help.

      Delete
  4. How do you display info from two tables if the tables data are linked when the user enters the data eg. Two tables student details and their marks how do you match the right student with the correct marks?

    ReplyDelete
  5. From what I understand, I am using one Database helper for each one of the tables. Over them, there is the another class which instantiate the database helpers and use them.

    So, if you want to retrieve information from two tables, just instantiate two database helper.

    For the students, I guess they are related.
    1 student can have N marks and 1 mark belongs to 1 student.

    So, in the mark table, there should be the id of the table of student (It could be the primary key of the student).

    1. Retrive the student
    2. Get her id
    3. Get the marks using the student's id.

    Did I answered your question?

    ReplyDelete
  6. Thank you soooo much.... it was very helpful....

    ReplyDelete
  7. Very much appreciated Sir. Thank you for this solution, i am most already stock for 1 day thinking what is the problem of my code. Lol :) anyways Thank you

    ReplyDelete