android 的数据库编程，说白了就是如何操控Sqlite，其实网上帖子一大把，大多都已经能够完成我们的数据开发任务。
今天我要讲的是，如何把这件事情做的优雅点。。
首先，就涉及到如何定义“优雅”。我想对数据库的操作，优雅，就是你去定义个表格，比如播放记录（见谅，我是做视频的，相信大家都喜欢看我司的片，4亿观众总有你）。
当然，出了播放记录还有一些偏好/下载信息等等等等，这些，都可以存储在Sqlite里面。这些表，我们特别希望，就是在android的代码里面定义这些表，跟在数据库表里面定义这个表几乎没有区别，你不再需要各种查询，删除，插入，等等这些繁琐的事情。加一个表，你只需要定义个表即可。。我想，这就是优雅。。
比如，我要加播放记录，PlayRecord,那么，我实际操作过程中，只需要定义个类 PlayRecord{ String mMovieId; String mMovieTitle....}等字段信息即可。
好，定义好了什么叫优雅，我们就来完成这项优雅的事情。。
大家要记住一点，优雅 的让你少干活，不代表没人干这个活，这个活，可能在父类里面干完了。以后的子类，基本就子传父业即可。所以，不要说，怎么这么难。。
第一步，我们需要定义个相对而言很多人不清楚的一个类，标签辅助类。
@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface DatabaseField {
public enum DataType {
INTEGER, REAL, TEXT, BLOB
};
String value() default "";
DataType type() default DataType.TEXT;
String name() default "";
boolean isNull() default true;
boolean isPrimaryKey() default false;
boolean isAutoIncrement() default false;
}
很困惑是正常的，我们看过interface，这里却冒出个@interface，前辈告诉我，这叫标签类，类似于@override，而我们自定义了一个标签类，它的作用我们后面会讲到。起到一个辅助的作用。。
需要详细了解的同学，可以搜索 anotation interface。
第二步，定义个所有表格类的父类。
public abstract class BaseInfo {
@DatabaseField(name = "_id", type = DataType.INTEGER, isPrimaryKey = true, isAutoIncrement = true)
public long id;
@DatabaseField(name = "created_at", type = DataType.INTEGER, isNull = false)
public long createdAt;
@DatabaseField(name = "updated_at", type = DataType.INTEGER, isNull = false)
public long updatedAt;
public static String getTableName(Class<?> clazz) {
String name = clazz.getSimpleName();
name = name.replaceAll("(?:(?<=[a-z])(?=[A-Z]))|(?:(?<=\\w)(?=[A-Z][a-z]))", "_");
return name.toLowerCase(Locale.US) + "s";
}
public BaseInfo() {
id = -1;
}
public boolean isNewRecord() {
return id == -1;
}
@Override
public boolean equals(Object o) {
BaseInfo info = (BaseInfo) o;
return info != null && info.id == id;
}
}
第一步的标签类初次出现在这里，这个父类定义了一些大家都需要定义的字段，例如id等。
后续的表格继承这个类。
第三步，定义个操作父类，把所有的数据库操作都封装在里面，然后后面继承它的子类就轻松很多。
public class BaseDao<T extends BaseInfo> {
private static final Logger LOG = Logger.getLogger(BaseDao.class);
private Class<T> mClazz;
private String mTableName;
private List<Field> mDbFields = new ArrayList<Field>();
private List<String> mColumnNames = new ArrayList<String>();
private int mMaxRowCount;
private DatabaseHelper mOpenHelper;
public BaseDao(Context context, Class<T> clazz) {
this(context, clazz, -1);
}
public BaseDao(Context context, Class<T> clazz, int maxRowCount) {
mClazz = clazz;
mTableName = BaseInfo.getTableName(clazz);
mMaxRowCount = maxRowCount;
mOpenHelper = DatabaseHelper.getInstance();
retrieveFieldInfos();
}
public void beginbeginTransaction() {
mOpenHelper.getWritableDatabase().beginTransaction();
}
public void setTransactionSuccessful() {
mOpenHelper.getWritableDatabase().setTransactionSuccessful();
}
public void endTransaction() {
mOpenHelper.getWritableDatabase().endTransaction();
}
public long insert(T data) {
return insert(data, null);
}
public long insert(T data, SQLiteDatabase database) {
long result = -1;
SQLiteDatabase db;
if (database == null) {
db = mOpenHelper.getWritableDatabase();
} else {
db = database;
}
// 超过条数限制则复用最后一个记录
if (mMaxRowCount > 0 && getRowCount(db) >= mMaxRowCount) {
data.id = last().id;
data.createdAt = getCurrentTimestamp();
update(data);
} else {
try {
ContentValues values = new ContentValues();
data.createdAt = getCurrentTimestamp();
data.updatedAt = data.createdAt;
for (Field field : mDbFields) {
DatabaseField dbFieldAnnotation = field.getAnnotation(DatabaseField.class);
if (dbFieldAnnotation != null) {
if (!(field.getName().equals("id"))) {
setFieldValue(data, values, field, dbFieldAnnotation);
}
}
}
result = db.insert(mTableName, null, values);
} catch (IllegalAccessException e) {
LOG.warn(e);
}
}
return result;
}
public T first() {
T result = null;
List<T> values = find(null, null, null, null, "`updated_at` DESC");
if (!(values.isEmpty())) {
result = values.get(0);
}
return result;
}
public T last() {
T result = null;
List<T> values = find(null, null, null, null, "`updated_at` ASC");
if (!(values.isEmpty())) {
result = values.get(0);
}
return result;
}
public int getRowCount() {
return getRowCount(null);
}
public boolean isMaxRowCount() {
return getRowCount() >= mMaxRowCount;
}
public int getRowCount(SQLiteDatabase database) {
SQLiteDatabase db = null;
if (database == null) {
db = mOpenHelper.getReadableDatabase();
} else {
db = database;
}
return db.query(mTableName, null, null, null, null, null, null).getCount();
}
public int delete(long id) {
return deleteBy("_id", Long.toString(id));
}
public int deleteBy(String columnName, String value) {
return mOpenHelper.getWritableDatabase()
.delete(mTableName, escapeColumnName(columnName) + " = ?", new String[] { value });
}
public int update(T data) {
int result = 0;
BaseInfo info = (BaseInfo) data;
try {
SQLiteDatabase db = mOpenHelper.getWritableDatabase();
ContentValues values = new ContentValues();
data.updatedAt = getCurrentTimestamp();
for (Field field : mDbFields) {
DatabaseField annotation = field.getAnnotation(DatabaseField.class);
if (annotation != null) {
setFieldValue(data, values, field, annotation);
}
}
result = db.update(mTableName, values, "`_id` = ?", new String[] { Long.toString(info.id) });
} catch (IllegalAccessException e) {
LOG.warn(e);
}
return result;
}
public T find(int id) {
T instance = null;
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
Cursor c = db.query(mTableName, null, "`_id` = ?", new String[] { Long.toString(id) }, null, null, null);
if (c != null && c.moveToNext()) {
instance = this.fillData(c);
}
if (c != null) {
c.close();
}
return instance;
}
public List<T> findAll() {
return find("", null, null, null, null);
}
public List<T> findAllOrderByUpdatedAt() {
return find("", null, null, null, "`updated_at` DESC");
}
public T find(String selection, String[] selectArgs, String sortOrder) {
List<T> dataList = find(selection, selectArgs, null, null, sortOrder);
return dataList.isEmpty() ? null : dataList.get(0);
}
public T findBy(String columnName, String value) {
List<T> dataList = find(escapeColumnName(columnName) + " = ?", new String[] { value }, null, null, null);
return dataList.isEmpty() ? null : dataList.get(0);
}
public List<T> find(String selection, String[] selectArgs, String groupBy, String having, String sortOrder) {
List<T> dataList = null;
SQLiteDatabase db = mOpenHelper.getReadableDatabase();
Cursor c = db.query(mTableName, null, selection, selectArgs, groupBy, having, sortOrder);
if (c != null) {
dataList = fillList(c);
c.close();
}
return dataList;
}
public Cursor query(String selection, String[] selectionArgs, String groupBy, String having, String orderBy) {
return mOpenHelper.getReadableDatabase()
.query(mTableName, null, selection, selectionArgs, groupBy, having, orderBy);
}
public boolean exist(String selection, String[] selectionArgs) {
boolean result = false;
Cursor cursor = mOpenHelper.getReadableDatabase()
.query(mTableName, null, selection, selectionArgs, null, null, null);
result = cursor.getCount() > 0;
cursor.close();
return result;
}
public void touch(T record) {
update(record);
}
public String getTableName() {
return mTableName;
}
public static String getColumnName(Field field, DatabaseField annoation) {
String name = annoation.name();
if (TextUtils.isEmpty(name)) {
name = field.getName();
}
return name;
}
@SuppressWarnings("unchecked")
public Class<T> getClassT() {
Type type = getClass().getGenericSuperclass();
ParameterizedType parameterizedType = (ParameterizedType) type;
return (Class<T>) parameterizedType.getActualTypeArguments()[0];
}
public List<T> fillList(Cursor cursor) {
List<T> dataList = new ArrayList<T>();
while (cursor.moveToNext()) {
T entry = fillData(cursor);
if (entry != null) {
dataList.add(entry);
}
}
return dataList;
}
public Object getFieldValue(Cursor cursor, DatabaseField annoation, String columnName) {
Object result = null;
final int columnIndex = cursor.getColumnIndex(columnName);
if (columnIndex != -1) {
switch (annoation.type()) {
case TEXT:
result = cursor.getString(columnIndex);
break;
case BLOB:
break;
case INTEGER:
result = cursor.getLong(columnIndex);
break;
case REAL:
result = cursor.getFloat(columnIndex);
break;
default:
break;
}
}
return result;
}
public T fillData(Cursor cursor) {
T instance = null;
try {
instance = mClazz.newInstance();
} catch (IllegalAccessException e) {
LOG.warn(e);
} catch (InstantiationException e) {
LOG.warn(e);
}
for (Field field : mDbFields) {
DatabaseField annoation = field.getAnnotation(DatabaseField.class);
if (annoation != null) {
String columnName = getColumnName(field, annoation);
try {
Object value = getFieldValue(cursor, annoation, columnName);
if (value != null) {
if (field.getType().equals(int.class)) {
field.set(instance, ((Long) value).intValue());
} else {
field.set(instance, value);
}
}
} catch (IllegalArgumentException e) {
LOG.warn(e);
} catch (IllegalAccessException e) {
LOG.warn(e);
}
}
}
return instance;
}
private void retrieveFieldInfos() {
Field[] fields = mClazz.getFields();
for (Field field : fields) {
DatabaseField annotation = field.getAnnotation(DatabaseField.class);
if (annotation != null) {
mDbFields.add(field);
mColumnNames.add(getColumnName(field, annotation));
}
}
}
private void setFieldValue(T data, ContentValues values, Field field, DatabaseField annotation)
throws IllegalAccessException {
String columnName = escapeColumnName(getColumnName(field, annotation));
switch (annotation.type()) {
case TEXT:
values.put(columnName, String.valueOf(field.get(data)));
break;
case BLOB:
break;
case INTEGER:
Object o = field.get(data);
if (o instanceof Integer) {
values.put(columnName, (Integer) o);
} else {
values.put(columnName, (Long) o);
}
break;
case REAL:
values.put(columnName, (Float) field.get(data));
break;
default:
break;
}
}
private String escapeColumnName(String columnName) {
return "`" + columnName + "`";
}
private long getCurrentTimestamp() {
return (new Date()).getTime();
}
}
对数据库的一些改变表的，例如删除，更新的操作放到一个单独 的类中。
public class DatabaseHelper extends SQLiteOpenHelper {
private static final Logger LOG = Logger.getLogger(DatabaseHelper.class);
private static final String DATABASE_NAME = "anime.db";
private static final int DATABASE_VERSION = 3;
private static List<Class<? extends BaseInfo>> sTables = new ArrayList<Class<? extends BaseInfo>>();
private static DatabaseHelper sInstance = null;
private SQLiteDatabase mDatabase = null;
public static void init(Context context) {
assert (sInstance == null);
LOG.info("init.");
registerDatabaseTable(LocalPlayRecord.class);
registerDatabaseTable(PlayRecord.class);
registerDatabaseTable(MovieUpdateInfo.class);
registerDatabaseTable(DownloadInfo.class);
registerDatabaseTable(SearchKeyword.class);
registerDatabaseTable(SearchResult.class);
registerDatabaseTable(SearchRecord.class);
registerDatabaseTable(Favorite.class);
registerDatabaseTable(TestTable.class);
sInstance = new DatabaseHelper(context);
}
public static void fini() {
LOG.info("fini.");
if (sTables.size() > 0) {
sTables.clear();
}
sInstance.close();
sInstance = null;
}
public static DatabaseHelper getInstance() {
return sInstance;
}
public void close() {
dropTable(mDatabase, SearchKeyword.class);
dropTable(mDatabase, SearchResult.class);
createTable(mDatabase, SearchKeyword.class);
createTable(mDatabase, SearchResult.class);
mDatabase.close();
}
@Override
public void onCreate(SQLiteDatabase db) {
LOG.debug("onCreate.");
createTables(db);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
LOG.info("onUpgrade. {} => {}", oldVersion, newVersion);
if (oldVersion < 3) {
upgrade(db, oldVersion);
} else {
createTables(db);
}
}
private void upgrade(SQLiteDatabase db, int oldVersion) {
try {
String tableName = null;
String tempTableName = null;
String sql;
db.beginTransaction();
if (oldVersion == 1) {
tableName = BaseInfo.getTableName(Favorite.class);
sql = "ALTER TABLE es RENAME TO " + tableName;
db.execSQL(sql);
tableName = BaseInfo.getTableName(MovieUpdateInfo.class);
sql = "ALTER TABLE gs RENAME TO " + tableName;
db.execSQL(sql);
tableName = BaseInfo.getTableName(SearchKeyword.class);
sql = "ALTER TABLE ks RENAME TO " + tableName;
db.execSQL(sql);
tableName = BaseInfo.getTableName(SearchRecord.class);
sql = "ALTER TABLE ms RENAME TO " + tableName;
db.execSQL(sql);
tableName = BaseInfo.getTableName(SearchResult.class);
sql = "ALTER TABLE os RENAME TO " + tableName;
db.execSQL(sql);
tableName = BaseInfo.getTableName(PlayRecord.class);
tempTableName = tableName + "_temp";
sql = "ALTER TABLE iis RENAME TO " + tempTableName;
db.execSQL(sql);
createTables(db);
StringBuilder columnsBuilder = new StringBuilder();
Field[] fields = PlayRecord.class.getFields();
for (Field field : fields) {
DatabaseField annotation = field.getAnnotation(DatabaseField.class);
if (annotation != null) {
String columnName = BaseDao.getColumnName(field, annotation);
if (!"_id".equals(columnName) && !"source".equals(columnName)) {
columnsBuilder.append(columnName).append(',');
}
}
}
columnsBuilder.deleteCharAt(columnsBuilder.length() - 1);
String columns = columnsBuilder.toString();
sql = "INSERT INTO " + tableName + "(" + columns + ")"
+ " SELECT " + columns + " FROM " + tempTableName;
db.execSQL(sql);
dropTable(db, tempTableName);
} else if (oldVersion == 2) {
createTables(db);
tableName = BaseInfo.getTableName(LocalPlayRecord.class);
StringBuffer sqlBuffer = new StringBuffer();
Field[] localFields = LocalPlayRecord.class.getFields();
for (Field field : localFields) {
DatabaseField annotation = field.getAnnotation(DatabaseField.class);
if (annotation != null) {
String fieldName = BaseDao.getColumnName(field, annotation);
if (fieldName.equals("total_episode")) {
DataType tableType = annotation.type();
sqlBuffer.append(" " + fieldName + " " + tableType.toString() + " ");
if (!(annotation.isNull())) {
sqlBuffer.append("NOT NULL ");
}
if (annotation.isPrimaryKey()) {
sqlBuffer.append("PRIMARY KEY ");
}
if (annotation.isAutoIncrement()) {
sqlBuffer.append("autoincrement");
}
break;
}
}
}
sql = "ALTER TABLE " + tableName + " ADD COLUMN " + sqlBuffer.toString();
db.execSQL(sql);
}
db.setTransactionSuccessful();
} catch (SQLException e) {
LOG.warn(e);
} catch (Exception e) {
LOG.warn(e);
} finally {
db.endTransaction();
}
}
private static void registerDatabaseTable(Class<? extends BaseInfo> tableClass) {
LOG.info("register {}.", tableClass.getName());
if (!(sTables.contains(tableClass))) {
sTables.add(tableClass);
}
}
private DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
mDatabase = getWritableDatabase();
LOG.debug("construction. database.version={}", DATABASE_VERSION);
}
private void dropTable(SQLiteDatabase db, Class<? extends BaseInfo> tableClass) {
dropTable(db, BaseInfo.getTableName(tableClass));
}
private void dropTable(SQLiteDatabase db, String tableName) {
db.execSQL("DROP TABLE IF EXISTS " + tableName);
}
private void createTable(SQLiteDatabase db, Class<? extends BaseInfo> tableClass) {
StringBuffer sqlBuffer = new StringBuffer();
sqlBuffer.append("CREATE TABLE IF NOT EXISTS " + BaseInfo.getTableName(tableClass) + " ( ");
Field[] fields = tableClass.getFields();
if (fields != null && fields.length > 0) {
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
if (field != null) {
DatabaseField annoation = field.getAnnotation(DatabaseField.class);
if (annoation != null) {
String fieldName = BaseDao.getColumnName(field, annoation);
DataType tableType = annoation.type();
sqlBuffer.append("`" + fieldName + "` " + tableType.toString() + " ");
if (!(annoation.isNull())) {
sqlBuffer.append("NOT NULL ");
}
if (annoation.isPrimaryKey()) {
sqlBuffer.append("PRIMARY KEY ");
}
if (annoation.isAutoIncrement()) {
sqlBuffer.append("autoincrement");
}
sqlBuffer.append(",");
}
}
}
}
// 删除最后一个逗号,
sqlBuffer.deleteCharAt(sqlBuffer.length() - 1);
sqlBuffer.append(" )");
String sql = sqlBuffer.toString();
LOG.info("create table. sql={}", sql);
db.execSQL(sql);
}
private void createTables(SQLiteDatabase db) {
for (Class<? extends BaseInfo> tableClass : sTables) {
createTable(db, tableClass);
}
}
}
后续，假如我们需要定义播放记录表。。则，变得非常便捷。。
1）表类
public class PlayRecord extends BaseInfo {
@DatabaseField(type = DataType.TEXT, isNull = false)
public String name;
@DatabaseField(type = DataType.INTEGER, isNull = false)
public int type;
@DatabaseField(name = "movie_id", type = DataType.INTEGER, isNull = false)
public int movieId;
@DatabaseField(type = DataType.TEXT)
public String source;
@DatabaseField(type = DataType.INTEGER, isNull = false)
public int episode;
@DatabaseField(name = "total_episode", type = DataType.INTEGER, isNull = false)
public int totalEpisode;
@DatabaseField(type = DataType.INTEGER, isNull = false)
public int position;
@DatabaseField(type = DataType.INTEGER, isNull = false)
public int duration;
public boolean isFinished() {
return duration > 0 && position == duration;
}
@Override
public String toString() {
return "[name=" + name + " movieId=" + movieId + " type=" + type + " source=" + source + " totalEpisode="
+ totalEpisode + " episode=" + episode + " position=" + position + " duration=" + duration + "]";
}
}
2）操作类
public class PlayRecordDao extends BaseDao<PlayRecord> {
@SuppressWarnings("unused")
private static final Logger LOG = Logger.getLogger(PlayRecordDao.class);
private static final int MAX_ROW_COUNT = 20;
public PlayRecordDao(Context context) {
super(context, PlayRecord.class, MAX_ROW_COUNT);
}
public boolean exist(int movieId) {
return findByMovieId(movieId) != null;
}
public PlayRecord getPlayRecord(int movieId) {
PlayRecord record = findByMovieId(movieId);
if (record == null) {
record = new PlayRecord();
record.movieId = movieId;
}
return record;
}
public PlayRecord getPlayRecord(int movieId, int episode) {
PlayRecord record = getPlayRecord(movieId);
if (record.isNewRecord()) {
record.episode = episode;
} else if (record.episode != episode) {
record.episode = episode;
record.position = 0;
}
return record;
}
public PlayRecord save(PlayRecord record) {
return save(record, null);
}
private PlayRecord save(PlayRecord record, SQLiteDatabase db) {
if (record.isNewRecord()) {
record.id = insert(record, db);
} else {
update(record);
}
return record;
}
public int deleteByMovieId(long movieId) {
return deleteBy("movie_id", String.valueOf(movieId));
}
public PlayRecord findByMovieId(int movieId) {
return findBy("movie_id", Integer.toString(movieId));
}
}
举一反三。
假如我们要定义用户偏好的一个表。也是如此进行。非常简单。
1）表格类定义
public class Favorite extends BaseInfo {
@DatabaseField(type = DataType.TEXT, isNull = false)
public String name;
@DatabaseField(name = "movie_id", type = DataType.INTEGER, isNull = false)
public int movieId;
@DatabaseField(name = "poster_url", type = DataType.TEXT, isNull = false)
public String posterUrl;
@DatabaseField(name = "release_date", type = DataType.TEXT, isNull = false)
public String releaseDate;
@DatabaseField(name = "tags", type = DataType.TEXT, isNull = false)
public String tags;
public Favorite() {
}
public Favorite(String name, int movieId, String posterUrl, long releaseDate, String tags) {
this.name = name;
this.movieId = movieId;
this.posterUrl = posterUrl;
this.releaseDate = String.valueOf(releaseDate);
this.tags = tags;
}
@Override
public String toString() {
return "[name=" + name + " movieId=" + movieId + " posterUrl=" + posterUrl + "]";
}
}
2）操作定义
public class FavoriteDao extends BaseDao<Favorite> {
public static final int MAX_COUNT = 100;
public FavoriteDao(Context context) {
super(context, Favorite.class, MAX_COUNT);
}
public long save(Favorite favorite) {
if (favorite.isNewRecord()) {
favorite.id = insert(favorite, null);
} else {
update(favorite);
}
return favorite.id;
}
public boolean exist(Favorite favorite) {
return findByMovieId(favorite.movieId) != null;
}
public int delete(Favorite favorite) {
return deleteBy("movie_id", String.valueOf(favorite.movieId));
}
public Favorite findByMovieId(int movieId) {
return findBy("movie_id", String.valueOf(movieId));
}
}
以此类推。。。。。
写父类抽象的人很累，但是，一劳永逸。。
