Base-Android快速开发框架(三)--数据存储之SQLite
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类。
1 @SimpleTable(name = "t_user") 2 public class User implements Serializable { 3 private static final long serialVersionUID = 2365701157715369155L; 4 5 public static Integer REMBER_PASSWORD = 1; 6 public static Integer AUTO_LOGIN = 1; 7 8 @SimpleId 9 @SimpleColumn(name = "userId") 10 private String userId;// 用户id 11 @SimpleColumn(name = "password") 12 private String password;// 密码 13 @SimpleColumn(name = "createDate") 14 private String createDate;// 创建时间 15 @SimpleColumn(name = "headerPic") 16 private String headerPic;// 头像图片路径 17 @SimpleColumn(name = "nickName") 18 private String nickName;// 昵称 19 @SimpleColumn(name = "syllabusPic") 20 private String syllabusPic;// 课程表图片 21 @SimpleColumn(name = "userName") 22 private String userName;// 用户名 23 @SimpleColumn(name = "email") 24 private String email;// 邮箱地址 25 @SimpleColumn(name = "mobile") 26 private String mobile;// 手机号 27 @SimpleColumn(name = "remberPassword") 28 private Integer remberPassword;// 记住密码 1：记住，其它不记住 29 @SimpleColumn(name = "autoLogin") 30 private Integer autoLogin; // 自动登陆 1：自动登陆，其它不登陆 31 @SimpleColumn(name = "type") 32 private Integer type; // 1个人 2企业 33 @SimpleColumn(name = "deptId") 34 private Integer deptId; 35 @SimpleColumn(name = "freeReadCount") 36 private String freeReadCount; 37 @SimpleColumn(name = "remark") 38 private String remark; 39 @SimpleColumn(name = "remainingSum") 40 private String remainingSum; 41 @SimpleColumn(name = "userToken") 42 private String userToken; 43 44 }
这就是Java里面的标注，@SimpleId标注了主键，@SimpleColumn标注了字段，TemplateDAO里面会根据反射自动生成数据表，并提供基础的增删改查，hql查询方法。上下关键的TemplateDAO类。
1 public class TemplateDAO<T> implements BaseDao<T> { 2 private String TAG = "SimpleSqlite"; 3 private SQLiteOpenHelper dbHelper; 4 private String tableName; 5 private String idColumn; 6 private Class<T> clazz; 7 private List<Field> allFields; 8 9 public TemplateDAO(SQLiteOpenHelper dbHelper) { 10 this.dbHelper = dbHelper; 11 this.clazz = (Class)((ParameterizedType)super.getClass().getGenericSuperclass()).getActualTypeArguments()[0]; 12 if(this.clazz.isAnnotationPresent(SimpleTable.class)) { 13 SimpleTable field = (SimpleTable)this.clazz.getAnnotation(SimpleTable.class); 14 this.tableName = field.name(); 15 } 16 17 this.allFields = TableHelper.joinFields(this.clazz.getDeclaredFields(), this.clazz.getSuperclass().getDeclaredFields()); 18 Iterator var3 = this.allFields.iterator(); 19 20 while(var3.hasNext()) { 21 Field field1 = (Field)var3.next(); 22 if(field1.isAnnotationPresent(SimpleId.class)) { 23 SimpleColumn column = (SimpleColumn)field1.getAnnotation(SimpleColumn.class); 24 this.idColumn = column.name(); 25 break; 26 } 27 } 28 29 Log.d(this.TAG, "clazz:" + this.clazz + " tableName:" + this.tableName + " idColumn:" + this.idColumn); 30 } 31 32 public SQLiteOpenHelper getDbHelper() { 33 return this.dbHelper; 34 } 35 36 public T get(int id) { 37 String selection = this.idColumn + " = ?"; 38 String[] selectionArgs = new String[]{Integer.toString(id)}; 39 Log.d(this.TAG, "[get]: select * from " + this.tableName + " where " + this.idColumn + " = \'" + id + "\'"); 40 List list = this.find((String[])null, selection, selectionArgs, (String)null, (String)null, (String)null, (String)null); 41 return list != null && list.size() > 0?list.get(0):null; 42 } 43 44 public T get(String id) { 45 String selection = this.idColumn + " = ?"; 46 String[] selectionArgs = new String[]{id}; 47 Log.d(this.TAG, "[get]: select * from " + this.tableName + " where " + this.idColumn + " = \'" + id + "\'"); 48 List list = this.find((String[])null, selection, selectionArgs, (String)null, (String)null, (String)null, (String)null); 49 return list != null && list.size() > 0?list.get(0):null; 50 } 51 52 public List<T> rawQuery(String sql, String[] selectionArgs) { 53 Log.d(this.TAG, "[rawQuery]: " + sql); 54 ArrayList list = new ArrayList(); 55 SQLiteDatabase db = null; 56 Cursor cursor = null; 57 58 try { 59 db = this.dbHelper.getReadableDatabase(); 60 cursor = db.rawQuery(sql, selectionArgs); 61 this.getListFromCursor(list, cursor); 62 } catch (Exception var10) { 63 Log.e(this.TAG, "[rawQuery] from DB Exception."); 64 var10.printStackTrace(); 65 } finally { 66 if(cursor != null) { 67 cursor.close(); 68 } 69 70 if(db != null) { 71 db.close(); 72 } 73 74 } 75 76 return list; 77 } 78 79 public boolean isExist(String sql, String[] selectionArgs) { 80 Log.d(this.TAG, "[isExist]: " + sql); 81 SQLiteDatabase db = null; 82 Cursor cursor = null; 83 84 try { 85 db = this.dbHelper.getReadableDatabase(); 86 cursor = db.rawQuery(sql, selectionArgs); 87 if(cursor.getCount() <= 0) { 88 return false; 89 } 90 } catch (Exception var9) { 91 Log.e(this.TAG, "[isExist] from DB Exception."); 92 var9.printStackTrace(); 93 return false; 94 } finally { 95 if(cursor != null) { 96 cursor.close(); 97 } 98 99 if(db != null) { 100 db.close(); 101 } 102 103 } 104 105 return true; 106 } 107 108 public List<T> find() { 109 return this.find((String[])null, (String)null, (String[])null, (String)null, (String)null, (String)null, (String)null); 110 } 111 112 public List<T> find(String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit) { 113 Log.d(this.TAG, "[find]"); 114 ArrayList list = new ArrayList(); 115 SQLiteDatabase db = null; 116 Cursor cursor = null; 117 118 try { 119 db = this.dbHelper.getReadableDatabase(); 120 cursor = db.query(this.tableName, columns, selection, selectionArgs, groupBy, having, orderBy, limit); 121 this.getListFromCursor(list, cursor); 122 } catch (Exception var15) { 123 Log.e(this.TAG, "[find] from DB Exception"); 124 var15.printStackTrace(); 125 } finally { 126 if(cursor != null) { 127 cursor.close(); 128 } 129 130 if(db != null) { 131 db.close(); 132 } 133 134 } 135 136 return list; 137 } 138 139 private void getListFromCursor(List<T> list, Cursor cursor) throws IllegalAccessException, InstantiationException { 140 label77: 141 while(cursor.moveToNext()) { 142 Object entity = this.clazz.newInstance(); 143 Iterator var5 = this.allFields.iterator(); 144 145 while(true) { 146 while(true) { 147 while(true) { 148 Field field; 149 Class fieldType; 150 int c; 151 do { 152 SimpleColumn column; 153 do { 154 if(!var5.hasNext()) { 155 list.add(entity); 156 continue label77; 157 } 158 159 field = (Field)var5.next(); 160 column = null; 161 } while(!field.isAnnotationPresent(SimpleColumn.class)); 162 163 column = (SimpleColumn)field.getAnnotation(SimpleColumn.class); 164 field.setAccessible(true); 165 fieldType = field.getType(); 166 c = cursor.getColumnIndex(column.name()); 167 } while(c < 0); 168 169 if(Integer.TYPE != fieldType && Integer.class != fieldType) { 170 if(String.class == fieldType) { 171 field.set(entity, cursor.getString(c)); 172 } else if(Long.TYPE != fieldType && Long.class != fieldType) { 173 if(Float.TYPE != fieldType && Float.class != fieldType) { 174 if(Short.TYPE != fieldType && Short.class != fieldType) { 175 if(Double.TYPE != fieldType && Double.class != fieldType) { 176 if(Blob.class == fieldType) { 177 field.set(entity, cursor.getBlob(c)); 178 } else if(Character.TYPE == fieldType) { 179 String fieldValue = cursor.getString(c); 180 if(fieldValue != null && fieldValue.length() > 0) { 181 182 field.set(entity, Character.valueOf(fieldValue.charAt(0))); 183 } 184 } 185 } else { 186 field.set(entity, Double.valueOf(cursor.getDouble(c))); 187 } 188 } else { 189 field.set(entity, Short.valueOf(cursor.getShort(c))); 190 } 191 } else { 192 field.set(entity, Float.valueOf(cursor.getFloat(c))); 193 } 194 } else { 195 field.set(entity, Long.valueOf(cursor.getLong(c))); 196 } 197 } else { 198 field.set(entity, Integer.valueOf(cursor.getInt(c))); 199 } 200 } 201 } 202 } 203 } 204 205 } 206 207 public long insert(T entity) { 208 Log.d(this.TAG, "[insert]: inset into " + this.tableName + " " + entity.toString()); 209 SQLiteDatabase db = null; 210 211 try { 212 db = this.dbHelper.getWritableDatabase(); 213 ContentValues e = new ContentValues(); 214 this.setContentValues(entity, e, "create"); 215 long row = db.insert(this.tableName, (String)null, e); 216 long var7 = row; 217 return var7; 218 } catch (Exception var11) { 219 Log.d(this.TAG, "[insert] into DB Exception."); 220 var11.printStackTrace(); 221 } finally { 222 if(db != null) { 223 db.close(); 224 } 225 226 } 227 228 return 0L; 229 } 230 231 public void delete(int id) { 232 SQLiteDatabase db = this.dbHelper.getWritableDatabase(); 233 String where = this.idColumn + " = ?"; 234 String[] whereValue = new String[]{Integer.toString(id)}; 235 Log.d(this.TAG, "[delete]: delelte from " + this.tableName + " where " + where.replace("?", String.valueOf(id))); 236 db.delete(this.tableName, where, whereValue); 237 db.close(); 238 } 239 240 public void delete(String id) { 241 this.delete(Integer.parseInt(id)); 242 } 243 244 public void delete(String where, String[] whereValue) { 245 SQLiteDatabase db = this.dbHelper.getWritableDatabase(); 246 Log.d(this.TAG, "[delete]: delelte from " + this.tableName + " where " + where + "=" + whereValue); 247 db.delete(this.tableName, where, whereValue); 248 db.close(); 249 } 250 251 public void delete() { 252 SQLiteDatabase db = this.dbHelper.getWritableDatabase(); 253 db.delete(this.tableName, (String)null, (String[])null); 254 db.close(); 255 } 256 257 public void delete(Integer... ids) { 258 if(ids.length > 0) { 259 StringBuffer sb = new StringBuffer(); 260 261 for(int db = 0; db < ids.length; ++db) { 262 sb.append('?').append(','); 263 } 264 265 sb.deleteCharAt(sb.length() - 1); 266 SQLiteDatabase var5 = this.dbHelper.getWritableDatabase(); 267 String sql = "delete from " + this.tableName + " where " + this.idColumn + " in (" + sb + ")"; 268 Log.d(this.TAG, "[delete]: " + sql); 269 var5.execSQL(sql, ids); 270 var5.close(); 271 } 272 273 } 274 275 public void delete(String... ids) { 276 if(ids.length > 0) { 277 StringBuffer sb = new StringBuffer(); 278 279 for(int db = 0; db < ids.length; ++db) { 280 sb.append('?').append(','); 281 } 282 283 sb.deleteCharAt(sb.length() - 1); 284 SQLiteDatabase var5 = this.dbHelper.getWritableDatabase(); 285 String sql = "delete from " + this.tableName + " where " + this.idColumn + " in (" + sb + ")"; 286 Log.d(this.TAG, "[delete]: " + sql); 287 var5.execSQL(sql, ids); 288 var5.close(); 289 } 290 291 } 292 293 294 295 public void update(T entity) { 296 SQLiteDatabase db = null; 297 298 try { 299 db = this.dbHelper.getWritableDatabase(); 300 ContentValues e = new ContentValues(); 301 this.setContentValues(entity, e, "update"); 302 String where = this.idColumn + " = ?"; 303 String id = e.get(this.idColumn).toString().trim(); 304 e.remove(this.idColumn); 305 Log.d(this.TAG, "[update]: update " + this.tableName + " where " + where.replace("?", id)); 306 String[] whereValue = new String[]{id}; 307 db.update(this.tableName, e, where, whereValue); 308 } catch (Exception var10) { 309 Log.d(this.TAG, "[update] DB Exception."); 310 var10.printStackTrace(); 311 } finally { 312 if(db != null) { 313 db.close(); 314 } 315 316 } 317 318 } 319 320 private void setContentValues(T entity, ContentValues cv, String type) throws IllegalAccessException { 321 Iterator var5 = this.allFields.iterator(); 322 323 while(true) { 324 Field field; 325 SimpleColumn column; 326 Object fieldValue; 327 SimpleId id; 328 do { 329 do { 330 do { 331 if(!var5.hasNext()) { 332 return; 333 } 334 335 field = (Field)var5.next(); 336 } while(!field.isAnnotationPresent(SimpleColumn.class)); 337 338 column = (SimpleColumn)field.getAnnotation(SimpleColumn.class); 339 field.setAccessible(true); 340 fieldValue = field.get(entity); 341 } while(fieldValue == null); 342 343 id = (SimpleId)field.getAnnotation(SimpleId.class); 344 } while("create".equals(type) && field.isAnnotationPresent(SimpleId.class) && id != null && id.auto()); 345 346 cv.put(column.name(), fieldValue.toString()); 347 } 348 } 349 350 public List<Map<String, String>> query2MapList(String sql, String[] selectionArgs) { 351 Log.d(this.TAG, "[query2MapList]: " + sql); 352 SQLiteDatabase db = null; 353 Cursor cursor = null; 354 ArrayList retList = new ArrayList(); 355 356 try { 357 db = this.dbHelper.getReadableDatabase(); 358 cursor = db.rawQuery(sql, selectionArgs); 359 360 while(cursor.moveToNext()) { 361 HashMap e = new HashMap(); 362 String[] var10; 363 int var9 = (var10 = cursor.getColumnNames()).length; 364 365 for(int var8 = 0; var8 < var9; ++var8) { 366 String columnName = var10[var8]; 367 e.put(columnName.toLowerCase(), cursor.getString(cursor.getColumnIndex(columnName))); 368 } 369 370 retList.add(e); 371 } 372 } catch (Exception var14) { 373 Log.e(this.TAG, "[query2MapList] from DB exception"); 374 var14.printStackTrace(); 375 } finally { 376 if(cursor != null) { 377 cursor.close(); 378 } 379 380 if(db != null) { 381 db.close(); 382 } 383 384 } 385 386 return retList; 387 } 388 389 public void execSql(String sql, Object[] selectionArgs) { 390 SQLiteDatabase db = null; 391 Log.d(this.TAG, "[execSql]: " + sql); 392 393 try { 394 db = this.dbHelper.getWritableDatabase(); 395 if(selectionArgs == null) { 396 db.execSQL(sql); 397 } else { 398 db.execSQL(sql, selectionArgs); 399 } 400 } catch (Exception var8) { 401 Log.e(this.TAG, "[execSql] DB exception."); 402 var8.printStackTrace(); 403 } finally { 404 if(db != null) { 405 db.close(); 406 } 407 408 } 409 410 } 411 }
回到上面的问题，如果Students多了一个字段怎么办？那么实体模型加多一个phone字段和标注，多了个teacher对象怎么办。建一个teacher对象 和一个teacherDao继承TemplateDAO。似乎好像一切都很顺利，然而当需要对象连表查询、当数据库升级后兼容旧数据等怎么办呢？诚邀大神加入... ...
