Board index » Android Tutorials with Source Code » Code Snippets for Android

Skip to content

Android SQLiteOpenHelper [Please Critique]

Quickly share your Android Code Snippets here...
Post a reply

Android SQLiteOpenHelper [Please Critique]

Postby Kittoes » Fri Apr 29, 2011 9:47 pm

So I'm completely new to Java and Android development but I'm extremely interested in creating some awesome stuff for the platform. I decided to start with something that I do know pretty well: databases. The first thing that I noticed in all of the tutorials and snippets that I could find is that most people utilize the SQLiteOpenHelper or create their own function that handles their database. I wanted to take a sort of hybrid approach because the idea behind the Open Helper is extremely solid but I really didn't like that it forces you to store everything on the phone.

My goal was to write a helper from scratch that retained pretty much all of the functionality of the original but added the ability to save the file onto the SD Card. Whether the database is external or not is defined simply with a boolean flag in the constructor. I made it a point not to hard-code the external location as I wanted Android to store the file dynamically based on the application. This also makes sure that the database is deleted upon application removal by default (I suppose I could add in the functionality to persist the database somehow but I believe that's something that should be done within the application itself).

Anyways, I'm pretty happy with what I came up with so far and I wanted to solicit feedback from people who actually know what their doing in the world of Java and Android. This is my first Java project outside of Hello World so I really want to know if I'm understanding the concepts and syntax. Fire away!

The OpenHelper class:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. import java.io.File;
  2.  
  3. import android.content.Context;
  4. import android.database.sqlite.SQLiteDatabase;
  5. import android.database.sqlite.SQLiteDatabase.CursorFactory;
  6. import android.database.sqlite.SQLiteException;
  7. import android.util.Log;
  8.  
  9. public abstract class KittDBHelper {
  10.         private static final String TAG = "KittDBHelper";
  11.        
  12.         private final Context mContext;
  13.         private final CursorFactory mCursorFactory;
  14.         private final String DB_NAME;
  15.         private final int DB_VERSION;
  16.        
  17.         private SQLiteDatabase mDB = null;
  18.         private boolean DB_EXTERNAL = false;
  19.         private String DB_PATH = "";
  20.        
  21.         public KittDBHelper(Context context, String name, CursorFactory factory, int version, boolean isExternal) {
  22.                 if(version < 1){
  23.                         throw new IllegalArgumentException("Version must be >= 1, version is: " + version + ".");
  24.                 }
  25.                
  26.                 mContext = context;
  27.                 mCursorFactory = factory;
  28.                 DB_NAME = name;
  29.                 DB_VERSION = version;
  30.                 DB_EXTERNAL = isExternal;
  31.         }
  32.        
  33.         //Return database with read only permission
  34.         public synchronized SQLiteDatabase getReadableDatabase() {
  35.                 if(mDB != null && mDB.isOpen()){
  36.                         Log.d(TAG, "Database: " + DB_NAME + " is already open.");
  37.                         return mDB;
  38.                 }
  39.                 else if (DB_NAME == null){
  40.                         throw new SQLiteException("Cannot open a temporary database as READ ONLY.");
  41.                 }
  42.                
  43.                 initializeDB();
  44.                 mDB.close();
  45.                 mDB = SQLiteDatabase.openDatabase(DB_PATH, mCursorFactory, SQLiteDatabase.OPEN_READONLY);
  46.                 Log.d(TAG, "Database: " + DB_NAME + " opened (READ ONLY).");
  47.                 return mDB;
  48.         }
  49.        
  50.         //Return database with write permission
  51.         public synchronized SQLiteDatabase getWritableDatabase() {
  52.                 if(mDB != null && mDB.isOpen() && !mDB.isReadOnly()){
  53.                         Log.d(TAG, "Database: " + DB_NAME + " is already open.");
  54.                         return mDB;
  55.                 }
  56.                
  57.                 initializeDB();
  58.                 Log.d(TAG, "Database: " + DB_NAME + " opened (READ/WRITE).");
  59.                 return mDB;
  60.         }
  61.        
  62.         //Perform necessary actions to create or update and/or open database
  63.         private void initializeDB() {
  64.                
  65.                 if(DB_NAME == null){
  66.                         mDB = SQLiteDatabase.create(null);
  67.                 }
  68.                 else{
  69.                         if(!DB_EXTERNAL){
  70.                                 mDB = mContext.openOrCreateDatabase(DB_NAME, 0, mCursorFactory);
  71.                                 DB_PATH = mContext.getDatabasePath(DB_NAME).getPath();
  72.                         }
  73.                         else{                  
  74.                                 File f = new File(mContext.getExternalFilesDir(null), DB_NAME);
  75.                                
  76.                                 mDB = SQLiteDatabase.openOrCreateDatabase(f, mCursorFactory);
  77.                                 DB_PATH = f.getPath();
  78.                         }
  79.                 }
  80.                
  81.                 int curVersion = mDB.getVersion();
  82.                 if(curVersion != DB_VERSION){
  83.                         mDB.beginTransaction();
  84.                         try{
  85.                                 if(curVersion == 0){
  86.                                         onCreate(mDB);
  87.                                 }
  88.                                 else{
  89.                                         if(curVersion < DB_VERSION){
  90.                                                 onUpgrade(mDB, curVersion, DB_VERSION);
  91.                                         }
  92.                                         else{
  93.                                                 onDowngrade(mDB, curVersion, DB_VERSION);
  94.                                         }
  95.                                 }
  96.                                 mDB.setVersion(DB_VERSION);
  97.                                 mDB.setTransactionSuccessful();
  98.                         }
  99.                         finally{
  100.                                 mDB.endTransaction();
  101.                         }
  102.                 }
  103.                
  104.                 onOpen(mDB);
  105.                 Log.d(TAG, "Database: " + DB_NAME + " initialized.");
  106.         }
  107.        
  108.         public void onOpen(SQLiteDatabase db)
  109.         {
  110.                 // Optional: override function to perform additional functions on open.
  111.         }
  112.                        
  113.         public synchronized void close() {
  114.                 if(mDB != null && mDB.isOpen()){
  115.                         mDB.close();
  116.                         mDB = null;
  117.                         Log.d(TAG, "Database: " + DB_NAME + " closed.");
  118.                 }
  119.         }
  120.        
  121.         public abstract void onCreate(SQLiteDatabase db);
  122.         public abstract void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion);
  123.         public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)
  124.         {
  125.                 throw new SQLiteException("Unable to downgrade database: " + DB_NAME + " from version " + mDB.getVersion() + " to version " + DB_VERSION + ". Override " + "onDowngrade method to provide this functionality.");
  126.         }
  127. }
Parsed in 0.044 seconds, using GeSHi 1.0.8.4



The Database class:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. import android.content.Context;
  2. import android.database.sqlite.SQLiteDatabase;
  3. import android.util.Log;
  4.  
  5. public class Database extends KittDBHelper {
  6.         static final String TAG = "Kitt_DB";
  7.        
  8.         //General database settings
  9.         static final String DB_NAME = "Kitt.db";
  10.         static final int DB_VERSION = 6;
  11.         static final boolean DB_EXTERNAL = false;
  12.        
  13.         //Database table map
  14.         public static final int TABLE_ONE = 1;
  15.         public static final int TABLE_TWO = 2;
  16.         public static final int TABLE_THREE = 3;
  17.         public static final int arrDB_TABLES[] = { TABLE_ONE, TABLE_TWO, TABLE_THREE };
  18.        
  19.         Context context  = null;
  20.        
  21.         public Database(Context context) {
  22.                 super(context, DB_NAME, null, DB_VERSION, DB_EXTERNAL);
  23.                 this.context = context;
  24.         }
  25.        
  26.         @Override
  27.         public void onCreate(SQLiteDatabase db) {              
  28.                 final String SQL_USERS      = "create table tbl" + TABLE_ONE       + " (u_id int primary key, u_last_modified int, u_name text)";
  29.                 final String SQL_CHAMPIONS  = "create table tbl" + TABLE_TWO + " (c_id int primary key, c_last_modified int, c_name text)";
  30.                 final String SQL_ABILITIES  = "create table tbl" + TABLE_THREE + " (a_id int primary key, a_last_modified int, a_name text)";
  31.                 final String arrDB_SQL[]    = { SQL_USERS, SQL_CHAMPIONS, SQL_ABILITIES };
  32.                
  33.                 for (int i : arrDB_TABLES)
  34.                 {
  35.                         db.execSQL(arrDB_SQL[i-1]);
  36.                         Log.d(TAG, "Table: " + arrDB_TABLES[i-1] + " | SQL: " + arrDB_SQL[i-1] + ";");
  37.                 }
  38.         }
  39.        
  40.         @Override
  41.         public void onUpgrade(SQLiteDatabase db, int prevVersion, int newVersion) {
  42.                 //Delete all tables
  43.                 for (int i : arrDB_TABLES)
  44.                 {
  45.                         db.execSQL("drop table if exists tbl" + arrDB_TABLES[i-1] + ";");
  46.                         Log.d(TAG, "Table: " + arrDB_TABLES[i-1] + " was deleted.");
  47.                 }              
  48.                 //Recreate database
  49.                 onCreate(db);
  50.                 Log.d(TAG, "Database " + DB_NAME + " was upgraded.");
  51.         }
  52. }
Parsed in 0.039 seconds, using GeSHi 1.0.8.4


Here is the actual source exported from Eclipse (it's a lot prettier and easier to read IMO).

KittDBHelper.java
(3.73 KiB) Downloaded 81 times

Database.java
(1.83 KiB) Downloaded 71 times
Kittoes
Freshman
Freshman
 
Posts: 2
Joined: Wed Apr 27, 2011 9:14 pm
Location: Grand Prairie, TX
Top
Top

Re: Android SQLiteOpenHelper [Please Critique]

Postby reubenb87 » Wed Jun 22, 2011 1:51 pm

Looks pretty good, can I ask what is the reason for external database? So that many apps can read/write?
reubenb87
Freshman
Freshman
 
Posts: 2
Joined: Thu Jun 16, 2011 10:39 am
Top

Re: Android SQLiteOpenHelper [Please Critique]

Postby AfzalivE » Fri Jul 01, 2011 10:47 am

I am assuming the goal is to be able to use more external space for large databases.

@OP: Upload it to a repo in GitHub, post on more forums like StackOverflow and \r\Androiddev for further critique.

I might use this in the rewrite of my Android GTFS reader! I'm using my own implementation which is quite messy atm. You can have a look at it here:

https://github.com/AfzalivE/Android-GTF ... apter.java

Might have a thing or two that you missed.

Cheers
AfzalivE
Freshman
Freshman
 
Posts: 5
Joined: Sat Nov 13, 2010 10:53 am
Top
Top
Post a reply

Return to Code Snippets for Android

Who is online

Users browsing this forum: No registered users and 6 guests

Board index The team • Delete all board cookies • All times are UTC + 1 hour