优雅的android数据库编程

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

需要详细了解的同学，可以搜索 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;
    }
}

后续的表格继承这个类。

第三步，定义个操作父类，把所有的数据库操作都封装在里面，然后后面继承它的子类就轻松很多。

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 + "]";
    }

}

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

}

以此类推。。。。。

写父类抽象的人很累，但是，一劳永逸。。