Base-Android快速開發框架(三)--數據存儲之SQLite，base-androidsqlite

Base-Android快速開發框架(三)--數據存儲之SQLite，base-androidsqlite

　　SQLite,是一款輕量級的關系型數據庫，Android原生集成的一個數據庫。具有輕量級、獨立性、隔離性、安全性等特點。是Android做數據存儲的必備知識之一。

　　在實際的項目中，我們常用於一些對象的存儲以及檢索。曾經做過一個餐飲點餐系統，就是需要把所有的菜譜、分類等基礎數據做本地緩存，這個時候如果你用上章介紹的SharedPreferences，簡直就瘋掉了。

數據需要做排序、篩選、檢索、分頁獲取等。這個時候就是Sqlite的長處了。跟上章一樣，不會有介紹基礎的api使用，直接介紹Base裡面應用的Sqlite Orm操作框架，如何高效、靈活的使用Sqlite。

先上一段在網上找的sqlite代碼片段。

似乎還不錯，操作student對象。也封裝了insert update等的操作。但是實際業務的app迭代開發，常由於業務需要，變化很大。假如此時student多了一個phone的屬性，怎麼破呢？

假設業務需求，又有老師的對象要加入，把代碼拷貝一份麼？好吧，大家自己想象。另外不建議在activity層自己出現原生的sql 語句操作，這樣耦合度太高了。接下來SQLite orm就派上用場了，同樣，先上一段代碼。

public void example(){
   UserDao userDao=new UserDao(mContext);
   //craete user
   User user=new User();
   userDao.insert(user);
   //update user
   userDao.update(user);
   //select user
   user=userDao.get("id");
   List<User> userList=userDao.find();
   //delete
   userDao.delete();
}


有人要說了你的UserDao是什麼，不會也是上面的InserData()方法集合吧？上下UserDao類。

public class UserDao extends TemplateDAO<User> {

   public UserDao(Context context) {
      super(new DBHelper(context));
   }
}

問題關鍵來了TemplateDAO，是一個泛型類，也是整個Orm框架的核心。使用了java裡面的標注 反射技術、泛型技術。下面先給大家看下User類。

@SimpleTable(name = "t_user")
public class User implements Serializable {
   private static final long serialVersionUID = 2365701157715369155L;

   public static Integer REMBER_PASSWORD = 1;
   public static Integer AUTO_LOGIN = 1;

   @SimpleId
   @SimpleColumn(name = "userId")
   private String userId;// 用戶id
   @SimpleColumn(name = "password")
   private String password;// 密碼
   @SimpleColumn(name = "createDate")
   private String createDate;// 創建時間
   @SimpleColumn(name = "headerPic")
   private String headerPic;// 頭像圖片路徑
   @SimpleColumn(name = "nickName")
   private String nickName;// 昵稱
   @SimpleColumn(name = "syllabusPic")
   private String syllabusPic;// 課程表圖片
   @SimpleColumn(name = "userName")
   private String userName;// 用戶名
   @SimpleColumn(name = "email")
   private String email;// 郵箱地址
   @SimpleColumn(name = "mobile")
   private String mobile;// 手機號
   @SimpleColumn(name = "remberPassword")
   private Integer remberPassword;// 記住密碼 1：記住，其它不記住
   @SimpleColumn(name = "autoLogin")
   private Integer autoLogin; // 自動登陸 1：自動登陸，其它不登陸
   @SimpleColumn(name = "type")
   private Integer type; // 1個人 2企業
   @SimpleColumn(name = "deptId")
   private Integer deptId;
   @SimpleColumn(name = "freeReadCount")
   private String freeReadCount;
   @SimpleColumn(name = "remark")
   private String remark;
   @SimpleColumn(name = "remainingSum")
   private String remainingSum;
   @SimpleColumn(name = "userToken")
   private String userToken;

}
這就是Java裡面的標注，@SimpleId標注了主鍵，@SimpleColumn標注了字段，TemplateDAO裡面會根據反射自動生成數據表，並提供基礎的增刪改查，hql查詢方法。上下關鍵的TemplateDAO類。

public class TemplateDAO<T> implements BaseDao<T> {
    private String TAG = "SimpleSqlite";
    private SQLiteOpenHelper dbHelper;
    private String tableName;
    private String idColumn;
    private Class<T> clazz;
    private List<Field> allFields;

    public TemplateDAO(SQLiteOpenHelper dbHelper) {
        this.dbHelper = dbHelper;
        this.clazz = (Class)((ParameterizedType)super.getClass().getGenericSuperclass()).getActualTypeArguments()[0];
        if(this.clazz.isAnnotationPresent(SimpleTable.class)) {
            SimpleTable field = (SimpleTable)this.clazz.getAnnotation(SimpleTable.class);
            this.tableName = field.name();
        }

        this.allFields = TableHelper.joinFields(this.clazz.getDeclaredFields(), this.clazz.getSuperclass().getDeclaredFields());
        Iterator var3 = this.allFields.iterator();

        while(var3.hasNext()) {
            Field field1 = (Field)var3.next();
            if(field1.isAnnotationPresent(SimpleId.class)) {
                SimpleColumn column = (SimpleColumn)field1.getAnnotation(SimpleColumn.class);
                this.idColumn = column.name();
                break;
            }
        }

        Log.d(this.TAG, "clazz:" + this.clazz + " tableName:" + this.tableName + " idColumn:" + this.idColumn);
    }

    public SQLiteOpenHelper getDbHelper() {
        return this.dbHelper;
    }

    public T get(int id) {
        String selection = this.idColumn + " = ?";
        String[] selectionArgs = new String[]{Integer.toString(id)};
        Log.d(this.TAG, "[get]: select * from " + this.tableName + " where " + this.idColumn + " = \'" + id + "\'");
        List list = this.find((String[])null, selection, selectionArgs, (String)null, (String)null, (String)null, (String)null);
        return list != null && list.size() > 0?list.get(0):null;
    }

    public T get(String id) {
        String selection = this.idColumn + " = ?";
        String[] selectionArgs = new String[]{id};
        Log.d(this.TAG, "[get]: select * from " + this.tableName + " where " + this.idColumn + " = \'" + id + "\'");
        List list = this.find((String[])null, selection, selectionArgs, (String)null, (String)null, (String)null, (String)null);
        return list != null && list.size() > 0?list.get(0):null;
    }

    public List<T> rawQuery(String sql, String[] selectionArgs) {
        Log.d(this.TAG, "[rawQuery]: " + sql);
        ArrayList list = new ArrayList();
        SQLiteDatabase db = null;
        Cursor cursor = null;

        try {
            db = this.dbHelper.getReadableDatabase();
            cursor = db.rawQuery(sql, selectionArgs);
            this.getListFromCursor(list, cursor);
        } catch (Exception var10) {
            Log.e(this.TAG, "[rawQuery] from DB Exception.");
            var10.printStackTrace();
        } finally {
            if(cursor != null) {
                cursor.close();
            }

            if(db != null) {
                db.close();
            }

        }

        return list;
    }

    public boolean isExist(String sql, String[] selectionArgs) {
        Log.d(this.TAG, "[isExist]: " + sql);
        SQLiteDatabase db = null;
        Cursor cursor = null;

        try {
            db = this.dbHelper.getReadableDatabase();
            cursor = db.rawQuery(sql, selectionArgs);
            if(cursor.getCount() <= 0) {
                return false;
            }
        } catch (Exception var9) {
            Log.e(this.TAG, "[isExist] from DB Exception.");
            var9.printStackTrace();
            return false;
        } finally {
            if(cursor != null) {
                cursor.close();
            }

            if(db != null) {
                db.close();
            }

        }

        return true;
    }

    public List<T> find() {
        return this.find((String[])null, (String)null, (String[])null, (String)null, (String)null, (String)null, (String)null);
    }

    public List<T> find(String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) {
        Log.d(this.TAG, "[find]");
        ArrayList list = new ArrayList();
        SQLiteDatabase db = null;
        Cursor cursor = null;

        try {
            db = this.dbHelper.getReadableDatabase();
            cursor = db.query(this.tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit);
            this.getListFromCursor(list, cursor);
        } catch (Exception var15) {
            Log.e(this.TAG, "[find] from DB Exception");
            var15.printStackTrace();
        } finally {
            if(cursor != null) {
                cursor.close();
            }

            if(db != null) {
                db.close();
            }

        }

        return list;
    }

    private void getListFromCursor(List<T> list, Cursor cursor) throws IllegalAccessException, InstantiationException {
        label77:
        while(cursor.moveToNext()) {
            Object entity = this.clazz.newInstance();
            Iterator var5 = this.allFields.iterator();

            while(true) {
                while(true) {
                    while(true) {
                        Field field;
                        Class fieldType;
                        int c;
                        do {
                            SimpleColumn column;
                            do {
                                if(!var5.hasNext()) {
                                    list.add(entity);
                                    continue label77;
                                }

                                field = (Field)var5.next();
                                column = null;
                            } while(!field.isAnnotationPresent(SimpleColumn.class));

                            column = (SimpleColumn)field.getAnnotation(SimpleColumn.class);
                            field.setAccessible(true);
                            fieldType = field.getType();
                            c = cursor.getColumnIndex(column.name());
                        } while(c < 0);

                        if(Integer.TYPE != fieldType && Integer.class != fieldType) {
                            if(String.class == fieldType) {
                                field.set(entity, cursor.getString(c));
                            } else if(Long.TYPE != fieldType && Long.class != fieldType) {
                                if(Float.TYPE != fieldType && Float.class != fieldType) {
                                    if(Short.TYPE != fieldType && Short.class != fieldType) {
                                        if(Double.TYPE != fieldType && Double.class != fieldType) {
                                            if(Blob.class == fieldType) {
                                                field.set(entity, cursor.getBlob(c));
                                            } else if(Character.TYPE == fieldType) {
                                                String fieldValue = cursor.getString(c);
                                                if(fieldValue != null && fieldValue.length() > 0) {     

 　　　　　　　　　　　　　　　　　　　　　　　　　　  field.set(entity, Character.valueOf(fieldValue.charAt(0)));
                                            }
                                        }
                                    } else {
                                        field.set(entity, Double.valueOf(cursor.getDouble(c)));
                                    }
                                } else {
                                    field.set(entity, Short.valueOf(cursor.getShort(c)));
                                }
                            } else {
                                field.set(entity, Float.valueOf(cursor.getFloat(c)));
                            }
                        } else {
                            field.set(entity, Long.valueOf(cursor.getLong(c)));
                        }
                    } else {
                        field.set(entity, Integer.valueOf(cursor.getInt(c)));
                    }
                }
            }
        }
    }

}

public long insert(T entity) {
    Log.d(this.TAG, "[insert]: inset into " + this.tableName + " " + entity.toString());
    SQLiteDatabase db = null;

    try {
        db = this.dbHelper.getWritableDatabase();
        ContentValues e = new ContentValues();
        this.setContentValues(entity, e, "create");
        long row = db.insert(this.tableName, (String)null, e);
        long var7 = row;
        return var7;
    } catch (Exception var11) {
        Log.d(this.TAG, "[insert] into DB Exception.");
        var11.printStackTrace();
    } finally {
        if(db != null) {
            db.close();
        }

    }

    return 0L;
}

public void delete(int id) {
    SQLiteDatabase db = this.dbHelper.getWritableDatabase();
    String where = this.idColumn + " = ?";
    String[] whereValue = new String[]{Integer.toString(id)};
    Log.d(this.TAG, "[delete]: delelte from " + this.tableName + " where " + where.replace("?", String.valueOf(id)));
    db.delete(this.tableName, where, whereValue);
    db.close();
}

public void delete(String id) {
    this.delete(Integer.parseInt(id));
}

public void delete(String where, String[] whereValue) {
    SQLiteDatabase db = this.dbHelper.getWritableDatabase();
    Log.d(this.TAG, "[delete]: delelte from " + this.tableName + " where " + where + "=" + whereValue);
    db.delete(this.tableName, where, whereValue);
    db.close();
}

public void delete() {
    SQLiteDatabase db = this.dbHelper.getWritableDatabase();
    db.delete(this.tableName, (String)null, (String[])null);
    db.close();
}

public void delete(Integer... ids) {
    if(ids.length > 0) {
        StringBuffer sb = new StringBuffer();

        for(int db = 0; db < ids.length; ++db) {
            sb.append('?').append(',');
        }

        sb.deleteCharAt(sb.length() - 1);
        SQLiteDatabase var5 = this.dbHelper.getWritableDatabase();
        String sql = "delete from " + this.tableName + " where " + this.idColumn + " in (" + sb + ")";
        Log.d(this.TAG, "[delete]: " + sql);
        var5.execSQL(sql, ids);
        var5.close();
    }

}

public void delete(String... ids) {
    if(ids.length > 0) {
        StringBuffer sb = new StringBuffer();

        for(int db = 0; db < ids.length; ++db) {
            sb.append('?').append(',');
        }

        sb.deleteCharAt(sb.length() - 1);
        SQLiteDatabase var5 = this.dbHelper.getWritableDatabase();
        String sql = "delete from " + this.tableName + " where " + this.idColumn + " in (" + sb + ")";
        Log.d(this.TAG, "[delete]: " + sql);
        var5.execSQL(sql, ids);
        var5.close();
    }

}

　public void update(T entity) {
    SQLiteDatabase db = null;

    try {
        db = this.dbHelper.getWritableDatabase();
        ContentValues e = new ContentValues();
        this.setContentValues(entity, e, "update");
        String where = this.idColumn + " = ?";
        String id = e.get(this.idColumn).toString().trim();
        e.remove(this.idColumn);
        Log.d(this.TAG, "[update]: update " + this.tableName + " where " + where.replace("?", id));
        String[] whereValue = new String[]{id};
        db.update(this.tableName, e, where, whereValue);
    } catch (Exception var10) {
        Log.d(this.TAG, "[update] DB Exception.");
        var10.printStackTrace();
    } finally {
        if(db != null) {
            db.close();
        }

    }

}

　　private void setContentValues(T entity, ContentValues cv, String type) throws IllegalAccessException {
        Iterator var5 = this.allFields.iterator();

        while(true) {
            Field field;
            SimpleColumn column;
            Object fieldValue;
            SimpleId id;
            do {
                do {
                    do {
                        if(!var5.hasNext()) {
                            return;
                        }

                        field = (Field)var5.next();
                    } while(!field.isAnnotationPresent(SimpleColumn.class));

                    column = (SimpleColumn)field.getAnnotation(SimpleColumn.class);
                    field.setAccessible(true);
                    fieldValue = field.get(entity);
                } while(fieldValue == null);

                id = (SimpleId)field.getAnnotation(SimpleId.class);
            } while("create".equals(type) && field.isAnnotationPresent(SimpleId.class) && id != null && id.auto());

            cv.put(column.name(), fieldValue.toString());
        }
    }

    public List<Map<String, String>> query2MapList(String sql, String[] selectionArgs) {
        Log.d(this.TAG, "[query2MapList]: " + sql);
        SQLiteDatabase db = null;
        Cursor cursor = null;
        ArrayList retList = new ArrayList();

        try {
            db = this.dbHelper.getReadableDatabase();
            cursor = db.rawQuery(sql, selectionArgs);

            while(cursor.moveToNext()) {
                HashMap e = new HashMap();
                String[] var10;
                int var9 = (var10 = cursor.getColumnNames()).length;

                for(int var8 = 0; var8 < var9; ++var8) {
                    String columnName = var10[var8];
                    e.put(columnName.toLowerCase(), cursor.getString(cursor.getColumnIndex(columnName)));
                }

                retList.add(e);
            }
        } catch (Exception var14) {
            Log.e(this.TAG, "[query2MapList] from DB exception");
            var14.printStackTrace();
        } finally {
            if(cursor != null) {
                cursor.close();
            }

            if(db != null) {
                db.close();
            }

        }

        return retList;
    }

    public void execSql(String sql, Object[] selectionArgs) {
        SQLiteDatabase db = null;
        Log.d(this.TAG, "[execSql]: " + sql);

        try {
            db = this.dbHelper.getWritableDatabase();
            if(selectionArgs == null) {
                db.execSQL(sql);
            } else {
                db.execSQL(sql, selectionArgs);
            }
        } catch (Exception var8) {
            Log.e(this.TAG, "[execSql] DB exception.");
            var8.printStackTrace();
        } finally {
            if(db != null) {
                db.close();
            }

        }

    }
}

回到上面的問題，如果Students多了一個字段怎麼辦？那麼實體模型加多一個phone字段和標注，多了個teacher對象怎麼辦。建一個teacher對象 和一個teacherDao繼承TemplateDAO。似乎好像一切都很順利，然而當需要對象連表查詢、當數據庫升級後兼容舊數據等怎麼辦呢？誠邀大神加入... ...