Tuesday, May 26, 2009

GenericDAO for Android SQLite

GenericDAO.java
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class GenericDAO extends SQLiteOpenHelper {

private static final String TAG = "GenericDAO";
private static SQLiteDatabase db;
private static String dName;
private static String tName;
private static String sql;
public static final String KEY_ID = "_id";

private static GenericDAO instance;

private GenericDAO(Context ctx, String dbName, String sql, String tableName, int ver){
super(ctx, dbName, null, ver);
Log.i(TAG, "Creating or opening database [ " + dbName + " ].");
GenericDAO.sql = sql;
dName = dbName;
tName = tableName;
}

public static GenericDAO getInstance(Context ctx, String dbName, String sql, String tableName, int ver){
if(instance == null){
instance = new GenericDAO(ctx, dbName, sql, tableName, ver);
try{
Log.i(TAG, "Creating or opening the database [ " + dbName + " ].");
db = instance.getWritableDatabase();
}catch(SQLiteException se){
Log.e(TAG, "Cound not create and/or open the database [ " + dbName + " ] that will be used for reading and writing.", se);
}
}
return instance;
}

public void close(){
if(instance != null){
Log.i(TAG, "Closing the database [ " + dName + " ].");
db.close();
instance = null;
}
}

@Override
public void onCreate(SQLiteDatabase db){
Log.i(TAG, "Trying to create database table if it isn't existed [ " + sql + " ].");
try{
db.execSQL(sql);
}catch(SQLException se){
Log.e(TAG, "Cound not create the database table according to the SQL statement [ " + sql + " ].", se);
}
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion){
Log.i(TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + ", which will destroy all old data");
try{
db.execSQL("DROP TABLE IF EXISTS " + tName);
}catch(SQLException se){
Log.e(TAG, "Cound not drop the database table [ " + tName + " ].", se);
}
onCreate(db);
}

public long insert(String table, ContentValues values){
return db.insert(table, null, values);
}

public Cursor get(String table, String[] columns){
return db.query(table, columns, null, null, null, null, null);
}

public Cursor get(String table, String[] columns, long id){
Cursor cursor =db.query(true, table, columns, KEY_ID + "=" + id, null, null, null, null, null);
if (cursor != null) {
cursor.moveToFirst();
}
return cursor;
}

public int delete(String table) {
return db.delete(table, "1", null);
}

public int delete(String table, long id) {
return db.delete(table, KEY_ID + "=" + id, null);
}

public int update(String table, long id, ContentValues values) {
return db.update(table, values, KEY_ID + "=" + id, null);
}
}



How to use it?
Notes.java
public class Notes {

public static final String DATABASE_NAME = "data";
public static final String DATABASE_TABLE = "notes";
public static final int DATABASE_VERSION = 1;
public static final String TABLE_CREATE =
"create table notes (_id integer primary key autoincrement, "
+ "title text not null, body text not null);";

public static final String COL_TITLE = "title";
public static final String COL_BODY = "body";

private int id;
private String title;
private String body;

public int getId() {
return id;
}
public String getTitle() {
return title;
}
public String getBody() {
return body;
}
public void setId(int id) {
this.id = id;
}
public void setTitle(String title) {
this.title = title;
}
public void setBody(String body) {
this.body = body;
}


}



    private String testCRUD(){

String result="";
Cursor cursor = null;
String[] columns = new String[] {GenericDAO.KEY_ID, Notes.COL_TITLE, Notes.COL_BODY};

GenericDAO dao = GenericDAO.getInstance(this, Notes.DATABASE_NAME, Notes.TABLE_CREATE, Notes.DATABASE_TABLE, Notes.DATABASE_VERSION);

if(dao != null){

ContentValues values = new ContentValues();
values.put(Notes.COL_TITLE, "aaa");
values.put(Notes.COL_BODY, "bbb");
dao.insert(Notes.DATABASE_TABLE, values);

values = new ContentValues();
values.put(Notes.COL_TITLE, "ccc");
values.put(Notes.COL_BODY, "ddd");
dao.insert(Notes.DATABASE_TABLE, values);

cursor = dao.get(Notes.DATABASE_TABLE, columns);


int idColumn = cursor.getColumnIndex(GenericDAO.KEY_ID);
int titleColumn = cursor.getColumnIndex(Notes.COL_TITLE);
int bodyColumn = cursor.getColumnIndex(Notes.COL_BODY);

if(cursor != null){
if(cursor.moveToFirst()){

int count = cursor.getCount();
result = "there are " + count + " records.";
List<Notes> list = new ArrayList<Notes>();

for(int i=0; i<count; i++){

int id = cursor.getInt(idColumn);
String title = cursor.getString(titleColumn);
String body = cursor.getString(bodyColumn);

Notes notes = new Notes();
notes.setId(id);
notes.setTitle(title);
notes.setBody(body);

list.add(notes);

result += " " + i + ": " + "id=" + id + ", title=" + title + ", body=" + body + ";";

cursor.moveToNext();
}
}
}

result += " now update the second record.";

values = new ContentValues();
values.put(Notes.COL_TITLE, "eee");
values.put(Notes.COL_BODY, "fff");
dao.update(Notes.DATABASE_TABLE, 2, values);

cursor.requery();
cursor.close();

result += " now delete first record.";

dao.delete(Notes.DATABASE_TABLE, 1);

result += " now delete all records.";

dao.delete(Notes.DATABASE_TABLE);

dao.close();
}

return result;
}


7 comments:

  1. Thanks for posting this, neat code! I used it.

    ReplyDelete
  2. Super single table example:/ Waste of time...

    ReplyDelete
  3. Hi, When I am trying to delete the database the _id value is not resetting to 0. Hence when I am trying to access by the row in a list I am not able to do so as the values do not match. Can someone help me with this... I am just stuck...

    ReplyDelete
  4. Even this is a single table example, it is very useful. Thanks !

    ReplyDelete
  5. Great article, very useful. Thanks !

    ReplyDelete
  6. Passing the context to GeneralDAO.getInstance becomes a problem when I am using the method testCRUD() in a class that isn't Activity. How to pass the context in a plain class like public Logger {} ?

    ReplyDelete
  7. thanks from Colombia

    ReplyDelete