Recommendations for working with multiple tables

Question

I am using a database with multiple tables in my application. I have an XML parser that needs to write data to two tables when parsing. I created two database adapters for both tables, but now I have a problem. When I work with one table, it is easy:

FirstDBAdapter firstTable = new FirstDBAdapter(mycontext); firstTable.open(); // open and close it every time I need to insert something // may be hundreds of times while parsing // it opens not a table but whole DB firstTable.insertItem(Item); firstTable.close(); 

Since this is a SAX parser, in my opinion (maybe I'm wrong), it will be even better:

 FirstDBAdapter firstTable = new FirstDBAdapter(mycontext); @Override public void startDocument() throws SAXException { firstTable.open(); // open and close only once } ... firstTable.insertItem(Item); ... @Override public void endDocument() throws SAXException { firstTable.close(); } 

But how do I do this if I need to insert data into a second table? For example, if I have a second adapter, which I think would be a bad idea:

 FirstDBAdapter firstTable = new FirstDBAdapter(mycontext); SecondDBAdapter secondTable = new SecondDBAdapter(mycontext); @Override public void startDocument() throws SAXException { firstTable.open(); secondTable.open(); } 

Any thoughts on how to achieve this?

Answer 1

My database adapter. The instance is always stored in MyApplication, which is inherited from the application. Think about the second table, where I defined the first ... currently it is just a short version, in fact this adapter processes 7 tables in the database.

 public class MyDbAdapter { private static final String LOG_TAG = MyDbAdapter.class.getSimpleName(); private SQLiteDatabase mDb; private static MyDatabaseManager mDbManager; public MyDbAdapter() { mDbManager = new MyDatabaseManager(MyApplication.getApplication()); mDb = mDbManager.getWritableDatabase(); } public static final class GameColumns implements BaseColumns { public static final String TABLE = "game"; public static final String IMEI = "imei"; public static final String LAST_UPDATE = "lastupdate"; public static final String NICKNAME = "nickname"; } public String getImei() { checkDbState(); String retValue = ""; Cursor c = mDb.rawQuery("SELECT imei FROM " + GameColumns.TABLE, null); if (c.moveToFirst()) { retValue = c.getString(c.getColumnIndex(GameColumns.IMEI)); } c.close(); return retValue; } public void setImei(String imei) { checkDbState(); ContentValues cv = new ContentValues(); cv.put(GameColumns.IMEI, imei); mDb.update(GameColumns.TABLE, cv, null, null); } public boolean isOpen() { return mDb != null && mDb.isOpen(); } public void open() { mDbManager = new MyDatabaseManager(MyApplication.getApplication()); if (!isOpen()) { mDb = mDbManager.getWritableDatabase(); } } public void close() { if (isOpen()) { mDb.close(); mDb = null; if (mDbManager != null) { mDbManager.close(); mDbManager = null; } } } private void checkDbState() { if (mDb == null || !mDb.isOpen()) { throw new IllegalStateException("The database has not been opened"); } } private static class MyDatabaseManager extends SQLiteOpenHelper { private static final String DATABASE_NAME = "dbname"; private static final int DATABASE_VERSION = 7; private MyDatabaseManager(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { createGameTable(db); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(LOG_TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + "!"); } private void dropDatabase(SQLiteDatabase db) { db.execSQL("DROP TABLE IF EXISTS " + GameColumns.TABLE); } private void createGameTable(SQLiteDatabase db) { db.execSQL("CREATE TABLE " + GameColumns.TABLE + " (" + GameColumns._ID + " INTEGER PRIMARY KEY," + GameColumns.IMEI + " TEXT," + GameColumns.LAST_UPDATE + " TEXT," + GameColumns.NICKNAME + " TEXT);"); ContentValues cv = new ContentValues(); cv.put(GameColumns.IMEI, "123456789012345"); cv.put(GameColumns.LAST_UPDATE, 0); cv.put(GameColumns.NICKNAME, (String) null); db.insert(GameColumns.TABLE, null, cv); } } } 

Answer 2

I had success with creating an abstract base class with a database name / create statement and other general information, and then expanding it for each table. That way, I can separate all my CRUD methods (which I prefer). The only drawback is that the DATABASE_CREATE statement must be in the parent class and must include all tables, because after that new tables cannot be added, but, in my opinion, there is a small price to pay for saving CRUD methods for each individual table.

It was pretty easy to do, but here are a few notes:

• The create statement in the parent class must be split for each table, since db.execSQL cannot execute more than one statement.
• I changed all private vars / methods to protected, just in case.
• If you add tables to an existing application (not sure if this is specific to the emulator), the application needs to be removed and then reinstalled.

Here is the code for my abstract parent class, which was based on the Notepad tutorial. Kids just extend this by calling a super constructor (feel free to use this):

 package com.pheide.trainose; import android.content.Context; import android.database.SQLException; import android.database.sqlite.SQLiteDatabase; import android.database.sqlite.SQLiteOpenHelper; import android.util.Log; public abstract class AbstractDbAdapter { protected static final String TAG = "TrainOseDbAdapter"; protected DatabaseHelper mDbHelper; protected SQLiteDatabase mDb; protected static final String TABLE_CREATE_ROUTES = "create table routes (_id integer primary key autoincrement, " + "source text not null, destination text not null);"; protected static final String TABLE_CREATE_TIMETABLES = "create table timetables (_id integer primary key autoincrement, " + "route_id integer, depart text not null, arrive text not null, " + "train text not null);"; protected static final String DATABASE_NAME = "data"; protected static final int DATABASE_VERSION = 2; protected final Context mCtx; protected static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(TABLE_CREATE_ROUTES); db.execSQL(TABLE_CREATE_TIMETABLES); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS routes"); onCreate(db); } } public AbstractDbAdapter(Context ctx) { this.mCtx = ctx; } public AbstractDbAdapter open() throws SQLException { mDbHelper = new DatabaseHelper(mCtx); mDb = mDbHelper.getWritableDatabase(); return this; } public void close() { mDbHelper.close(); } } 

A slightly more detailed explanation is available here: http://pheide.com/page/11/tab/24#post13

Answer 3

phoxicle solution is a great starting point, but in the notes on Android SQLite serialization regarding Android SQLite serialization, this implementation is not thread safe and will fail when several database connections (for example, from different threads) try to write a database:

If you try to write to the database from real individual connections at the same time, it will fail. He will not wait until the first is done, and then write. He just won't write your changes. Even worse, if you do not call the correct insert / update version in SQLiteDatabase, you will not get an exception. You just get the message in your LogCat, and that will be it.

So, multiple threads? Use one assistant.

---

Here's a modified implementation of the phoxicle database adapter, which uses a static instance of SQLiteOpenHelper and is thus limited to a single database connection:

 public class DBBaseAdapter { private static final String TAG = "DBBaseAdapter"; protected static final String DATABASE_NAME = "db.sqlite"; protected static final int DATABASE_VERSION = 1; protected Context mContext; protected static DatabaseHelper mDbHelper; private static final String TABLE_CREATE_FOO = "create table foo (_id integer primary key autoincrement, " + "bar text not null)"); public DBBaseAdapter(Context context) { mContext = context.getApplicationContext(); } public SQLiteDatabase openDb() { if (mDbHelper == null) { mDbHelper = new DatabaseHelper(mContext); } return mDbHelper.getWritableDatabase(); } public void closeDb() { mDbHelper.close(); } protected static class DatabaseHelper extends SQLiteOpenHelper { public DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(TABLE_CREATE_FOO); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data"); db.execSQL("DROP TABLE IF EXISTS routes"); onCreate(db); } } } 

Extend the DBBaseAdapter for each table to implement your CRUD methods:

 public class DBFooTable extends DBBaseAdapter { public DBFooTable(Context context) { super(context); } public void getBar() { SQLiteDatabase db = openDb(); // ... closeDb(); } 

Answer 4

I'm a little late, but I always open my database, not my table. So this does not change the meaning.

  firstTable.open(); secondTable.open(); 

Rather do it.

  dataBase.getWritableDatabase(); 

then if you want to update juste, select the table:

 public int updateTotal (int id, Jours jour){ ContentValues values = new ContentValues(); values.put(COL_TOTAL,Total ); //update the table you want return bdd.update(TABLE_NAME, values, COL_JOUR + " = " + id, null); } 

And it's all. Hope this helps other people.