优雅的android数据库编程
来源：互联网 发布：逆战神枪手源码 编辑：程序博客网 时间：2024/05/17 03:14
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)); }}以此类推。。。。。
写父类抽象的人很累，但是，一劳永逸。。
1 0
- 优雅的android数据库编程
- [编程题] 优雅的点
- [编程题] 优雅的点
- 优雅的AOP编程笔记
- [编程题] 优雅的点
- [编程题]优雅的点
- 如何配置风骚的android studio编辑器，让编程更优雅？
- iOS如何优雅的使用sqlite数据库
- [编程题] 优雅的点--网易
- 网络编程 - boost::asio优雅的退出
- Android 面向协议编程 体会优雅编程之旅
- 优雅的Android应用程序完全退出方案
- Android之BaseAdapter的优雅实现
- Android Studio美化之优雅的logcat
- Android如何优雅的缓存网络图片
- Android退出应用最优雅的方式
- Android退出应用最优雅的方式
- 如何优雅的退出android应用
- linux IO子系统和文件系统读写流程
- 动画的使用
- ubuntu12.04 mount NFS共享出现有坏的超级块解决方案
- FZU1911-Construct a Matrix(矩阵快速幂+规律)
- 委托事例（只适用于初学者）
- 优雅的android数据库编程
- myeclipse控制台日志显示宽度设置
- 将0强转为指针的一种用法
- SRAM Controller
- 年内最奇怪科技产品：三星耳机可变身为项链
- 黑马程序员_Java 高新技术（2）
- 定制餐饮美食简单点点app，提高餐饮店在市场的竞争力
- java堆栈 （转）
- 源码方式向openssl中添加新算法完整详细步骤（示例：摘要算法SM3）【非engine方式】
