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;
}
