android sqlite orm

標籤：

要過年了，最近比較閑整理下以前的代碼。

14年寫的sqlite orm庫，當時為了儘快熟悉android資料庫操作，所以自己動手寫了這個庫。然後用這個庫開發了幾個項目發現用的還是比較順手的，於是就寫篇部落格記錄下吧。

我理解的orm的核心思想就是以對象為單位進行增刪改查。

app開發給欄位命名是很麻煩的一件事，特別是當頁面有很多控制項的時候，要兼顧介面開發人員命名風格和頁面快速辨認需求，如果orm再要求欄位名還要考慮到資料表欄位命名的問題就太麻煩了，於是我使用了反射來實現模型欄位和資料表欄位的對應。

public class SQLiteUtil {    private SQLiteDatabase db;    private Context context;    /**     * @param dbName     * @param context     */    public SQLiteUtil(String dbName, Context context) {        this.context = context;        //開啟或建立資料庫        db = this.context.openOrCreateDatabase(dbName, Context.MODE_PRIVATE, null);    }    /**     * 建表語句，如果表不存在則建表     *     * @param table 表結構，樣本 tableName(field1 text primary key, field2 integer)     */    public void createTable(String table) {        String cts = "create table if not exists " + table + "; ";//建表語句        db.execSQL(cts);    }    /**     * 刪除表     *     * @param table 表名     */    public void deleteTable(String table) {        String sql = "DROP TABLE IF EXISTS " + table;        db.execSQL(sql);    }    /**     * 執行sql命令     *     * @param sql     */    public void execSql(String sql) {        db.execSQL(sql);    }    /**     * 增加一條新的資料     */    public <T> void insert(String tableName, T data, Class<T> clazz) {        String sql = "insert into " + tableName + " ";        String fields = "( ";        String value = " VALUES( ";        for (Field f : clazz.getDeclaredFields()) {            f.setAccessible(true);            if (null != f.getAnnotation(SqlField.class)) {                SqlField ta = f.getAnnotation(SqlField.class);                fields += ta.field() + ",";                try {                    if (f.getType().getSimpleName().equals("int")) {                        value += f.getInt(data) + ",";                    } else if (f.getType().getSimpleName().equals("String")) {                        value += "‘" + f.get(data).toString() + "‘,";                    }                } catch (IllegalAccessException e) {                    e.printStackTrace();                }            }        }        fields = fields.substring(0, fields.length() - 1) + ") ";        value = value.substring(0, value.length() - 1) + ") ";        sql = sql + fields + value;        StaticMethod.debugEMSG(sql);        db.execSQL(sql);    }    /**     * 修改資料     */    public <T> void update(String tableName, String where, T data, Class<T> clazz) {        String sql = "UPDATE " + tableName + " SET ";        String set = "";        for (Field f : clazz.getDeclaredFields()) {            f.setAccessible(true);            if (null != f.getAnnotation(SqlField.class)) {                SqlField ta = f.getAnnotation(SqlField.class);                try {                    if (f.getType().getSimpleName().equals("int")) {                        set += " " + ta.field() + " = " + f.getInt(data) + ",";                    } else if (f.getType().getSimpleName().equals("String")) {                        if (f.get(data).toString() != null)                            set += " " + ta.field() + " = ‘" + f.get(data).toString() + "‘,";                    }                } catch (IllegalAccessException e) {                    e.printStackTrace();                }            }        }        set = set.substring(0, set.length() - 1);        sql = sql + set + where;        db.execSQL(sql);    }    /**     * 查詢方法，返回對應的數組     *     * @param sql     * @param <T> 泛型對象，必須提供一個空白建構函式     * @return     */    public <T> List<T> query(String sql, Class<T> clazz) {        List<T> result = new ArrayList<T>();        Cursor c = db.rawQuery(sql, null);        try {            while (c.moveToNext()) {                T temp = (T) Class.forName(clazz.getName()).newInstance();                for (Field f : clazz.getDeclaredFields()) {                    f.setAccessible(true);                    if (null != f.getAnnotation(SqlField.class)) {                        SqlField ta = f.getAnnotation(SqlField.class);                        if (f.getType().getSimpleName().equals("int")) {                            f.set(temp, c.getInt(c.getColumnIndex(ta.field())));                        } else if (f.getType().getSimpleName().equals("String")) {                            f.set(temp, c.getString(c.getColumnIndex(ta.field())));                        }                    }                }                result.add(temp);            }        } catch (Exception e) {            e.printStackTrace();        } finally {            c.close();        }        return result;    }    /**     * 查     *     * @param sql       查詢語句     * @param fieldName 要返回的欄位名稱     * @return 返回字串內容     */    public List<String> query(String sql, String fieldName) {        Cursor c = db.rawQuery(sql, null);        List<String> result = new ArrayList<String>();        while (c.moveToNext()) {            result.add(c.getString(c.getColumnIndex(fieldName)));        }        c.close();        return result;    }}

　　沒有刪除方法，實際開發中刪除操作都是業務比較複雜的，我都是在封裝好sql語句後直接調用execSql(sqlString)來實現刪除。

　　查詢有1個重載，是因為在開發時有比較多的業務情境只需要儲存和讀取一張表中的某個欄位，為了這個欄位實現一個model太麻煩，所以有了重載方法。

　　增查改中都使用了反射，sql語句也沒有最佳化。因為從實際開發角度來看，以app的資料庫裡的資料量級，做不做最佳化效果區別不大，所以可以放心食用。

　　貼一下Annotation的代碼

@Target({ElementType.FIELD})@Retention(RetentionPolicy.RUNTIME)public @interface SqlField {    String field();}

　　annotation使用例子

 　 @SqlField( field = "field1")    public String key;    @SqlField( field = "field2")    public String json;

　　例如，執行查詢的時候，orm會從資料表中取出field1的值，並賦值給key欄位。取出field2欄位的值並賦值給json欄位。

 

　　最後再來說一下我的使用經驗，一般我會根據業務需求對orm再次封裝。例如做緩衝時，記憶體緩衝,SharedPreferences緩衝和sqlite緩衝以及檔案快取，都要根據業務來配置，其中sqlite緩衝就可以通過二次封裝這個orm來實現。

     下面是我的sqlite緩衝實現代碼

public class SqliteCache {    public static void clean(String key) {        String sql = "delete from " + StaticConstant.SQLCACHE + " where  key=‘" + key + "‘";        StaticVariable.mSqLiteOper.execSql(sql);    }    public static void save(CacheModel cache) {        String sql = "select key from " + StaticConstant.SQLCACHE + " where key=‘" + cache.key + "‘";        if (StaticVariable.mSqLiteOper.query(sql, "key").size() > 0) {            StaticVariable.mSqLiteOper.update(StaticConstant.SQLCACHE,                    " where key=‘" + cache.key + "‘",                    cache,                    CacheModel.class);        } else {            StaticVariable.mSqLiteOper.insert(StaticConstant.SQLCACHE,                    cache,                    CacheModel.class);        }    }    public static CacheModel read(String key) {        String sql = "select * from " + StaticConstant.SQLCACHE + " where key=‘" + key + "‘";        List<CacheModel> temp = StaticVariable.mSqLiteOper.query(sql, CacheModel.class);        if (temp.size() > 0)            return temp.get(0);        else            return null;    }}

　　sqlite緩衝類都是根據業務來寫的，不同app有不同業務，業務也在隨時變化，所以這個類僅供參考。

　　既然都寫到緩衝了，順便就把我另一個例子也存檔一下吧。

　　推送是大部分app必備的功能，而一個體驗良好的app，推送到達app端後是持久存在的，直到被使用者消費掉，例如的紅點未讀提醒。

　　這就需要實現推送訊息的緩衝，而當推送訊息類型很多時，緩衝管理就十分麻煩，這時就必須使用資料庫來管理推送緩衝。

　　

public class PushMsgCache {    /**     * 首頁已讀(任務通知裡面)     */    public static void read1(String uid) {        String sql = "UPDATE " + StaticConstant.NOTICECACHE + " SET state = 2 WHERE state=1 and " +                " type!=‘" + StaticConstant.對話 + "‘ and " +                " type!=‘" + StaticConstant.系統通知 + "‘ and " +                " type!=‘" + StaticConstant.銷售新使用者 + "‘ and userid = ‘" + uid + "‘";        StaticVariable.mSqLiteOper.execSql(sql);    }    /**     * 特定通知1級已讀設定     */    public static void read1(String uid, String msgType) {        String sql = "UPDATE " + StaticConstant.NOTICECACHE +                " SET state = 2 WHERE state in(1,2) and userid = ‘" + uid + "‘ and type=‘" + msgType + "‘";        StaticVariable.mSqLiteOper.execSql(sql);    }    /**     * 2級通知頁面已讀     */    public static void read2(String uid, String msgType) {        String sql = "UPDATE " + StaticConstant.NOTICECACHE +                " SET state = 3 WHERE state in(1,2) and userid = ‘" + uid + "‘ and type=‘" + msgType + "‘";        StaticVariable.mSqLiteOper.execSql(sql);    }    /**     * 詳情已讀     */    public static void read3(String uid, String msgType) {        String sql = "UPDATE " + StaticConstant.NOTICECACHE +                " SET state = 4 WHERE state in(1,2,3) and userid = ‘" + uid + "‘ and type=‘" + msgType + "‘";        StaticVariable.mSqLiteOper.execSql(sql);    }    /**     * 列表已讀     */    public static void readwechatlist(String uid) {        String sql = "UPDATE " + StaticConstant.NOTICECACHE + " SET state = 2 WHERE state=1 and " +                " type=‘" + StaticConstant.對話 + "‘ and userid = ‘" + uid + "‘";        StaticVariable.mSqLiteOper.execSql(sql);    }    /**     * 訊息已讀     */    public static void readwechat(String uid, String cid) {        String sql = "UPDATE " + StaticConstant.NOTICECACHE +                " SET state = 3 WHERE state in(1,2) and userid = ‘" + uid + "‘ and targetid=‘" +                cid + "‘ and type=‘" + StaticConstant.對話 + "‘";        StaticVariable.mSqLiteOper.execSql(sql);    }    /**     * 跟進詳情回複已讀     */    public static void readgjxqhf2(String uid, String cid) {        String sql = "UPDATE " + StaticConstant.NOTICECACHE +                " SET state = 3 WHERE state in(1,2) and userid = ‘" + uid + "‘ and targetid=‘" +                cid + "‘ and type=‘" + StaticConstant.銷售新使用者 + "‘";        StaticVariable.mSqLiteOper.execSql(sql);    }    /**     * 推送訊息入庫     *     * @param json 推送過來的訊息對象的json     */    public static void save(String json) {        Gson gson = new Gson();        NoticeCacheModel model = new NoticeCacheModel();        PushReceiverModel push = gson.fromJson(json, PushReceiverModel.class);        model.setType(push.getMessageType());        model.setMsg(gson.toJson(push));        model.setTime(StaticMethod.getNowTimeStamp() + "");        model.setTargetid(push.getUid() + "");        if (push.getAdvid() != 0) {            model.setUserid(push.getAdvid() + "");        } else {            model.setUserid(push.getAdvisorid() + "");        }        model.setState(1);        StaticVariable.mSqLiteOper.insert(StaticConstant.NOTICECACHE, model, NoticeCacheModel.class);    }    /**     * 讀取推送訊息     *     * @param pageName 取資料的頁面名稱     */    public static List<PushReceiverModel> query(String pageName) {        String sql = "";        switch (pageName) {            case "xsgw_main"://尋找所有未讀的推送資訊                sql = "select * from " + StaticConstant.NOTICECACHE + " where userid=‘" +                        StaticVariable.uid + "‘ and state=1 and" +                        " type!=‘" + StaticConstant.對話 + "‘ and " +                        " type!=‘" + StaticConstant.搶客戶 + "‘ and " +                        " type!=‘" + StaticConstant.系統通知 + "‘ and " +                        "type!=‘" + StaticConstant.銷售新使用者 + "‘" +                        "order by time desc";                break;            case "xsgw_msgbox":                sql = "select * from " + StaticConstant.NOTICECACHE + " where userid=‘" +                        StaticVariable.uid + "‘ and state in(1,2) order by time desc";                break;            case "xsjl_main":                sql = "select * from " + StaticConstant.NOTICECACHE + " where userid=‘" +                        StaticVariable.uid + "‘ and state=1 and" +                        " type!=‘" + StaticConstant.對話 + "‘ and " +                        " type!=‘" + StaticConstant.搶客戶 + "‘ and " +                        " type!=‘" + StaticConstant.系統通知 + "‘ and " +                        "type!=‘" + StaticConstant.銷售新使用者 + "‘" +                        "order by time desc";                break;            case "xsjl_msgbox":                sql = "select * from " + StaticConstant.NOTICECACHE + " where userid=‘" +                        StaticVariable.uid + "‘ and state in(1,2)  order by time desc";                break;            case "wechat":                sql = "select * from " + StaticConstant.NOTICECACHE + " where userid=‘" +                        StaticVariable.uid + "‘ and state in(1,2) and type=‘" + StaticConstant.對話 + "‘ order by time desc";                break;            case "xsgw_main_activity":                sql = "select * from " + StaticConstant.NOTICECACHE + " where userid=‘" +                        StaticVariable.uid + "‘ and state=1 order by time desc";                break;            case "newnotice":                sql = "select * from " + StaticConstant.NOTICECACHE + " where userid=‘" +                        StaticVariable.uid + "‘ and state in(1,2,3) and type=‘" + StaticConstant.經理通知 + "‘";                break;            case "system"://未讀系統通知                sql = "select * from " + StaticConstant.NOTICECACHE + " where userid=‘" +                        StaticVariable.uid + "‘ and state in(1,2) and type=‘" + StaticConstant.系統通知 + "‘";                break;            default:                return null;        }        List<NoticeCacheModel> temp = StaticVariable.mSqLiteOper.query(sql, NoticeCacheModel.class);        if (temp.size() > 0) {            Gson gson = new Gson();            List<PushReceiverModel> result = new ArrayList<>();            for (NoticeCacheModel item : temp) {                result.add(gson.fromJson(item.getMsg(), PushReceiverModel.class));            }            return result;        } else            return null;    }}

　　對推送訊息的處理，我採用json來存放到資料庫中，使用gson來完成資料的序列化和還原序列化。

　　這個類裡面的核心方法是save(訊息入庫)和query(未讀訊息查詢)，readxxx系列方法則是根據不同業務產生的訊息被消費方法，作用是把訊息置為已消費。

　　因為我目前開發都是小團隊模式，1-5人之內，對代碼的抽象需求不大，所以寫類的時候我更多的是考慮代碼易讀和修改便捷，覺得水平太差的請輕噴.

android sqlite orm