Here is a simple and most accepted way of creating a DBHelper class for SQLite database
Note : Each step I am introducing some instance variables. Don't forget to add these to your main class.
Step 1
Create a class named "DBHelper "
public class DBHelper {
}
Step 2
Add db name and db version information as constants
private static final String DATABASE_NAME = "testsqlitedb"; //DB Name
private static final String TABLE_DBVERSION = "dbversion"; //DB Version handling table
private static final int DATABASE_VERSION = 1; //Current DB Version info
private static boolean needsUpgrade = false; //DB upgrade is required or not
Step 3
Implement the constructor. This will create the database if it is not exist else it will just open it.
//Instance variables
Context myCtx;
private SQLiteDatabase db;
/**
* Constructor
* @param ctx
*/
public DBHelper(Context ctx) {
myCtx = ctx;
try {
db = myCtx.openOrCreateDatabase(DATABASE_NAME, 0, null);
// Check for the existence of the DBVERSION table
// If it doesn't exist than create the overall data,
// otherwise double check the version
Cursor c = db.query("sqlite_master", new String[] { "name" },
"type='table' and name='" + TABLE_DBVERSION + "'", null,
null, null, null);
int numRows = c.getCount();
if (numRows <>
CreateDatabase(db);
} else {
int version = 0;
Cursor vc = db.query(true, TABLE_DBVERSION,
new String[] { "version" }, null, null, null, null,
null, null);
if (vc.getCount() > 0) {
vc.moveToFirst();
version = vc.getInt(0);
}
vc.close();
if (version != DATABASE_VERSION) {
needsUpgrade = true;
}
}
c.close();
} catch (SQLException e) {
}
}
Step 4
Implement CreateDatabase function
//Instance variables
private static final String DBVERSION_CREATE = "create table "
+ TABLE_DBVERSION + " (" + "version integer not null);";
private static final String CATEGORIES_CREATE = "create table "
+ TABLE_CATEGORIES + " (id integer primary key autoincrement, column1 text);";
/**
* This method creates the database
* @param db
*/
private void CreateDatabase(SQLiteDatabase db) {
try {
db.execSQL(DBVERSION_CREATE);
ContentValues args = new ContentValues();
args.put("version", DATABASE_VERSION);
db.insert(TABLE_DBVERSION, null, args);
db.execSQL(CATEGORIES_CREATE);
} catch (SQLException e) {
//Handle Exception
}
}
Step 4
Implement Some generic functions
//Instance variables
private static final String CATEGORIES_DROP = "drop table " + TABLE_CATEGORIES + ";";
/**
* This method deletes the database
*/
public void deleteDatabase() {
try {
db.execSQL(EXPENSES_DROP);
db.execSQL(EXPENSES_CREATE);
} catch (SQLException e) {
//Handle Exception
}
}
/**
* Close database connection
*/
public void close() {
try {
if(db.isOpen())
db.close();
} catch (SQLException e) {
}
}
/**
* Fetch Database Version
* @return version
*/
public int fetchVersion() {
int version = 0;
try {
Cursor c = db.query(true, TABLE_DBVERSION,
new String[] { "version" }, null, null, null, null, null,
null);
if (c.getCount() > 0) {
c.moveToFirst();
version = c.getInt(0);
}
c.close();
} catch (SQLException e) {
}
return version;
}
/**
* Begin a transaction on an open database.
*
* @return true if successful
*/
public boolean beginTransaction() {
try {
db.execSQL("begin transaction;");
} catch (SQLException e) {
return false;
}
return true;
}
/**
* Commit all changes since the begin transaction on an open database.
*/
public void commit() {
try {
db.execSQL("commit;");
} catch (SQLException e) {
}
}
/**
* Rollback all changes since the begin transaction on an open database.
*/
public void rollback() {
try {
db.execSQL("rollback;");
} catch (SQLException e) {
}
}
Step 5
Add Application specific functions
/**
* This method adds a category entry in to database.
* @param entry
*/
public long addCategory(String name) {
ContentValues initialValues = new ContentValues();
initialValues.put("column1 ", name);
long ret = -1;
try {
ret = db.insert(TABLE_CATEGORIES, null, initialValues);
} catch (SQLException e) {
}
return ret;
}
/**
* This method deletes a category entry from database.
* @param Id
*/
public void deleteCategory(long Id) {
try {
db.delete(TABLE_CATEGORIES, "id=" + Id, null);
} catch (SQLException e) {
}
}
I think this will help you. Please feel free to write your comments
