Of the many http://stackoverflow.com/, blog and android SDK examples a vast majority only demonstrate a single database with a single table. I have seen examples a database helpers that are overly complicated. I used the Notepad SDK sample as my model. In the Notepad sample, there is a class that describes the database table. It includes a class that implements BaseColumns. Since I wanted to have two tables I also added the table name to the class instead of in the MainActvity.
import android.provider.BaseColumns;
public final class History {
public static final String TABLE_NAME = "history";
// This class cannot be instantiated
private History() {}
/**
* History table
*/
public static final class HistoryColumns implements BaseColumns {
// This class cannot be instantiated
private HistoryColumns() {}
/**
* The default sort order for this table
*/
public static final String DEFAULT_SORT_ORDER = "_id DESC";
/**
* Foreign Key Territory._id
* <P>Type: INTEGER NOT NULL</P>
*/
public static final String TID = "tid";
/**
* Name
* <P>Type: TEXT</P>
*/
public static final String NAME = "name";
/**
* The timestamp for when territory checked-out
* <P>Type: TEXT (date('now'))</P>
*/
public static final String CHKOUT_DATE = "chkout";
/**
* The timestamp for when territory checked-in
* <P>Type: TEXT (date('now'))</P>
*/
public static final String CHKIN_DATE = "chkin";
}
}
I created a similar class for my other table. Next I needed a database open helper. Some examples I have seen will check in the database exists and create. I found that all to be unnecessary. Here is my helper.
public class DatabaseHelper extends SQLiteOpenHelper {
private static final String TAG = "DatabaseHelper";
private static final String DATABASE_NAME = "ts";
private static final int DATABASE_VERSION = 1;
private SQLiteDatabase mDatabase;
public DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
mDatabase = db;
mDatabase.execSQL("CREATE TABLE " + Territory.TABLE_NAME + " ("
+ TerritoryColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ TerritoryColumns.TNUM + " TEXT COLLATE NOCASE UNIQUE ON CONFLICT ABORT,"
+ TerritoryColumns.DESC + " TEXT"
+ ");");
mDatabase.execSQL("CREATE TABLE " + History.TABLE_NAME + " ("
+ HistoryColumns._ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
+ HistoryColumns.TID + " INTEGER NOT NULL,"
+ HistoryColumns.NAME + " TEXT,"
+ HistoryColumns.CHKOUT_DATE + " TEXT,"
+ HistoryColumns.CHKIN_DATE + " TEXT,"
+ "FOREIGN KEY(" + HistoryColumns.TID + ") REFERENCES "
+ Territory.TABLE_NAME + "(" + TerritoryColumns._ID + "));");
}
@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 " + History.TABLE_NAME);
db.execSQL("DROP TABLE IF EXISTS " + Territory.TABLE_NAME);
onCreate(db);
}
}
You cannot execute multiple SQL statements with a single execSQL method. I tried, the method will not error, only the first statement is executed. Since i was using a foreign key I had to create one table then the other. When the tables are dropped I have to drop the foreign key before the primary key table.
I have only used this helper at the activity level but I believe it could also be used at the application level. I added two functions to my MainActvity that I use to assign a global helper and database object.
DatabaseHelper myDbHelper;
SQLiteDatabase myDatabase;
Cursor myCursor;
private SimpleCursorAdapter dataAdapter;
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
}
@Override
public void onResume() {
super.onResume();
go();
}
@Override
public void onPause() {
stop();
super.onPause();
}
@Override
public void onStop() {
stop();
super.onStop();
}
private void go() {
try {
myDbHelper = new DatabaseHelper(this);
myDatabase = myDbHelper.getReadableDatabase();
} catch (SQLException e) {
Log.w(TAG, e.toString());
}
displayListView();
}
private void stop() {
if (myCursor != null) myCursor.close();
if (myDatabase != null) myDatabase.close();
if (myDbHelper != null) myDbHelper.close();
if (myCursor == null && myDatabase == null && myDbHelper == null) Log.i(TAG, "Everything stopped");
}
I used onResume to call go(), instead of onCreate as most examples show, because it is always called in the activity life cycle. Calling getReadableDatabase is actually what creates the database the first time the application is launched. No need to have hard coded database paths or logic to check whether creation is needed. Google has taken care of that for us! Just start using the database with all the SQLiteDatabase methods like insert, update, or query.
To avoid getting a logcat full of database not closed errors use the onPause and onStop to close all connections. The cursor should be closed before the database which is before the helper. Then the super of each method can be called.
I have been using this model with each activity that needs database activity. The only difference is whether getReadableDatabase or getWritableDatabase is called. I know some will say that it slows the application to open and close the database with every activity. I am sure this is true but I have not seen lag yet. This could easily be added to a class that extends the application the only caveat being whether or not onTerminate will be called.
