幾年前剛剛涉及做Android開發(fā)時候畢竟頭大的是數(shù)據(jù)庫相關的增刪改查口渔，因為項目要對離線數(shù)據(jù)進行管理數(shù)據(jù)庫操作比較多帕翻，不斷通過查詢Cursor拼裝成對象，完了還要close cursor，重復工作太多太累筷狼，曾經(jīng)用過Hibernate只是Android平臺沒有罷了棍潘，那會兒也還沒有Android平臺的權威級的ORM恃鞋。
經(jīng)過項目實踐有意或無意做了一個如下操作DB的小lib，簡化了不少重復勞動.
想法起源：
最初的萌芽來自于Android Email App內(nèi)部一個叫EmailContent的class定義亦歉。每個Table類都手動實現(xiàn)toContentValues()和restore(Cursor cursor)恤浪，這樣可以避免重復通過Cursor拼裝表對象。隨后自己就想寫了一個Utils類似的工具類類來提供一系列增刪改查的API鳍徽，當然這些API的操作對象都是table對象资锰，隨著迭代慢慢衍化如今更加友好的light-dao了。
下面描述下如何使用此light-dao：
1. 得定義一個繼承BaseDBHelper的DBHelper阶祭，大家都懂的:
public class DBHelper extends BaseDBHelper {
private static final String DATABASE_NAME = "school.db";
private static final int VERSION = 1;
@SuppressLint("StaticFieldLeak")
private static DBHelper sSingleton;
private DBHelper(Context context) {
super(context, DATABASE_NAME, VERSION);
}
private static DBHelper getSingleton(Context context) {
if (sSingleton == null) {
synchronized (DBHelper.class) {
sSingleton = new DBHelper(context.getApplicationContext());
}
}
return sSingleton;
}
public static DBUtils with(Context context) {
return DBUtils.create(getSingleton(context));
}
/**
* all table classes should configured here
*
* @param tableClasses table classes
*/
@Override
protected void onClassLoad(List<Class<? extends Entity>> tableClasses) {
tableClasses.add(Student.class);
tableClasses.add(Teacher.class);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
super.onUpgrade(db, oldVersion, newVersion);
/* upgrade db version by version
if (oldVersion < VERSION) {
SQL sql = SQLBuilder.buildTableCreateSQL(Student.class);
db.execSQL(sql.getSql());
}
*/
}
}
2. 然后绷杜，定義你的各種表的類，如下為學生表和老師表濒募，且學生表里有老師表ID的外鍵：
@Table("teacher")
public class Teacher extends Entity {
@Column(name = "name", notnull = true)
public String name;
}
@Table("student")
public class Student extends Entity {
@Foreign(Teacher.class)
@Column(name = "teacher_id", notnull = true)
public long teacherId;
@Column(name = "name", notnull = true)
public String name;
@Column(name = "age", notnull = true)
public Integer age;
}
3. 最后就可以通過lightdao進行常見的數(shù)據(jù)庫增刪改查了：
3.1 單個保存
Teacher teacher = new Teacher();
teacher.name = "王老師";
long teacherId = DBHelper.with(mContext).save(teacher);
// 老師和學生關系是一對多鞭盟，因此學生表中有老師表的ID作為外鍵
Student student = new Student();
student.teacherId = teacherId;
student.name = "小學生";
student.age = 20;
long id = DBHelper.with(mContext).save(student);
assertTrue(id > 0);
3.2 批量保存
List<Student> students = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Student student = new Student();
student.name = "name " + i;
student.age = i;
student.teacherId = 1; // 假設都是ID為1的教師的學生
students.add(student);
}
int count = DBHelper.with(mContext).saveAll(students);
assertTrue(count == students.size());
3.3 數(shù)量查詢
int count = DBHelper.with(mContext)
.withTable(Student.class)
.withWhere("age > ?", 5)
.applyCount();
assertTrue(count > 0);
3.5 根據(jù)主鍵ID查找
Student student = DBHelper.with(mContext)
.withTable(Student.class)
.applySearchById(1);
assertTrue(student != null);
3.6 查詢所有并以list返回結(jié)果
List<Student> students = DBHelper.with(mContext)
.withTable(Student.class)
.applySearchAsList();
assertTrue(students.size() > 0);
3.7 帶有條件查詢并以list返回結(jié)果
// 類似的還有很多其他以“with”開頭的API，如：
// withColumns: 只查詢指定的column
// withGroupBy: 查詢分組
// withHaving: 分組后的條件篩選
// withOrderBy: 排序控制
// withLimit: 分頁控制
// withDistinct: 查詢?nèi)ブ?
List<Student> students = DBHelper.with(mContext)
.withTable(Student.class)
.withWhere("age>?", 5)
.applySearchAsList();
assertTrue(students.size() > 0);
3.8 更新部分字段
ContentValues values = new ContentValues();
values.put("name", "hello baby");
int count = DBHelper.with(mContext)
.withTable(Student.class)
.withWhere("age<?", 5)
.applyUpdate(values);
assertTrue(count > 0);
3.9 根據(jù)對象更新
DBUtils dbUtils = DBHelper.with(mContext);
Student student = dbUtils.withTable(Student.class).applySearchById(1);
assertTrue(student != null);
student.name = "testUpdateTable";
int count = dbUtils.withTable(Student.class).applyUpdate(student);
assertTrue(count > 0);
4.0 根據(jù)主鍵ID刪除
int count = DBHelper.with(mContext).withTable(Student.class).applyDeleteById(1);
assertTrue(count > 0);
4.1 刪除指定的對象
DBUtils dbUtils = DBHelper.with(mContext);
Student student = dbUtils.withTable(Student.class).applySearchById(2);
assertTrue(student != null);
int count = dbUtils.withTable(Student.class).applyDelete(student);
assertTrue(count > 0);
4.2 根據(jù)條件刪除
int count = DBHelper.with(mContext).withTable(Student.class).withWhere("age>=?", 9).applyDelete();
assertTrue(count > 0);
4.3 批處理（數(shù)據(jù)庫事務）
BatchJobs jobs = new BatchJobs();
Student student = new Student();
student.name = "insert from batch job";
student.age = 1;
jobs.addInsertJob(student);
// update with table object
student = DBHelper.with(mContext).withTable(Student.class).applySearchFirst();
student.name = "updated from batch job";
jobs.addUpdateJob(Student.class, student);
// update with id
jobs.addUpdateJob(Student.class, student.id, student.toContentValues());
// update with condition
jobs.addUpdateJob(Student.class, student.toContentValues(), "age=?", 6);
// delete with table object
jobs.addDeleteJob(student);
// delete with id
jobs.addDeleteJob(Student.class, 7);
// delete with condition
jobs.addDeleteJob(Student.class, "age<?", 3);
boolean success = DBHelper.with(mContext).applyBatchJobs(jobs);
assertTrue(success);
4.4 跨表查詢
// 因為跨表查詢的結(jié)果來自于多個表瑰剃，所以得重新定義返回結(jié)果的對象齿诉，并通過aliasName指定此字段來自于哪個表中的哪個字段
public class Relation extends Query {
@Column(name = "teacher_id", aliasName = "student._id as teacher_id")
public long teacherId;
@Column(name = "teacher_name", aliasName = "teacher.name as teacher_name")
public String teacherName;
@Column(name = "student_id", aliasName = "student._id as student_id")
public long studentId;
@Column(name = "student_name", aliasName = "student.name as student_name")
public String studentName;
@Column(name = "age")
public int studentAge;
}
List<Relation> list = DBHelper.with(mContext)
.withQuery(Relation.class)
.applySearchAsList();
System.out.println(list.size());
4.5 數(shù)據(jù)庫升級
數(shù)據(jù)庫升級其實啥也不用做，因為是自動的，因為重寫了onUpgrade():
@Override
public final void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
autoMigrate(db, mTableClasses);
}
private void autoMigrate(SQLiteDatabase db, List<Class<? extends Entity>> tableClasses) {
for (Class<? extends Entity> clazz : tableClasses) {
String tableName = ReflectTools.getTableName(clazz);
boolean exist = ReflectTools.isTableExist(db, tableName);
if (exist) {
Field[] fields = ReflectTools.getClassFields(clazz);
for (Field field : fields) {
Column column = field.getAnnotation(Column.class);
if (column == null) {
continue;
}
String columnName = !TextUtils.isEmpty(column.name()) ? column.name() : field.getName();
String dataType = ReflectTools.getDataTypeByField(field);
boolean columnExist = ReflectTools.isColumnExist(db, tableName, columnName);
if (!columnExist) {
db.execSQL("ALTER TABLE " + tableName + " ADD " + columnName + " " + dataType);
}
}
} else {
db.execSQL(SQLBuilder.buildCreateSQL(clazz).getSql());
}
}
}
static boolean isTableExist(SQLiteDatabase db, String tableName) {
Cursor cursor = null;
try {
cursor = db.rawQuery("SELECT count(*) FROM sqlite_master WHERE type='table' AND name=?", new String[]{tableName});
boolean hasNext = cursor.moveToNext();
return hasNext && cursor.getInt(0) > 0;
} finally {
if (cursor != null) {
cursor.close();
}
}
}
static boolean isColumnExist(SQLiteDatabase db, String tableName, String columnName) {
Cursor cursor = null;
try {
cursor = db.rawQuery("SELECT count(*) FROM sqlite_master WHERE tbl_name = ? AND (sql LIKE ? OR sql LIKE ?);",
new String[]{tableName, "%(" + columnName + "%", "%, " + columnName + " %"});
boolean hasNext = cursor.moveToNext();
return hasNext && cursor.getInt(0) > 0;
} finally {
if (cursor != null) {
cursor.close();
}
}
}
相關實現(xiàn)可查閱light-dao:
