Android之SQLite数据库操作

前些日子，搞了个Android项目，接触到了SQLite数据库

个人感觉有点麻烦：

1、数据库字段的修改，需要修改实体类，DAO类的字段的增加、修改、删除

2、传统的JDBC思想，体力劳动，十分浪费程序员的精力

3、程序耦合性太高，不够灵活

怎样才能将SQLite的DAO类使用的如同Hibernate一样简单呢？

Android的本身提供了SQLiteOpenHelper类、SQLiteDatabase类用作数据库方面的处理，很遗憾的是这两个类中没能提供如同Hibernate中Criteria类的操作

没办法，只能自己手工封装了，一切为了能有使用Hibernate的感觉

下面就给大家看代码了

首先定义一个BaseDAO的接口类，没太多的内容，就是实现类的方法汇总，这里呢就不提供了，让我们直接来看BaseDAO的实现类好了，尼玛，别说坑爹啊……

首先来看实现类的属性、构造函数、工具方法：

private DataBaseHelper helper;private JavaBeanReflect reflect = JavaBeanReflect.getInstance();private static Map<Class<? extends Object>, Class<? extends Object>> clsMap = new HashMap<Class<? extends Object>, Class<? extends Object>>();static {clsMap.put(int.class, Integer.class);clsMap.put(long.class, Long.class);clsMap.put(float.class, Float.class);clsMap.put(double.class, Double.class);clsMap.put(boolean.class, Boolean.class);clsMap.put(byte.class, Byte.class);clsMap.put(short.class, Short.class);}public SQLiteDAO(Context context) {helper = new DataBaseHelper(context);}

/** * 获得更改数据库操作权限 * @return */public SQLiteDatabase getWriteDataBase() {return helper.getWritableDatabase();}/** * 获得读取数据库操作权限 * @return */public SQLiteDatabase getReadDataBase() {return helper.getReadableDatabase();}/** * 关闭数据库连接操作，释放资源 * @param dbSQLiteDataBase对象 * @param cursorCursor对象 */public void freeResource(SQLiteDatabase db, Cursor cursor) {if (cursor != null) {cursor.close();}if (db != null) {db.close();}}/** * 将数据封装的ContentValues对象中 * 用于新增、更新数据操作 * @param t实体类对象 * @returnContentValues对象 */public <T> ContentValues setAttribute(T t) {ContentValues values = new ContentValues();Field[] fields = reflect.getDeclaredFields(t.getClass());for (Field field : fields) {if (!"id".equals(field.getName())) {values.put(field.getName(), String.valueOf(reflect.getFieldValue(t, field)));}}return values;}/** * 提取Cursor对象中的数据，并封装进实体类对象中 * @param cursorCursor对象 * @param entity实体类字节码 * @return实体类对象 * @throws Exception */public <T> T setAttribute(Cursor cursor, Class<T> entity) throws Exception {if (cursor != null) {Field[] fields = reflect.getDeclaredFields(entity);String[] columns = cursor.getColumnNames();T t = entity.newInstance();for (Field field : fields) {Class<? extends Object> cls = field.getType();for (String column : columns) {cls = getBasicClass(cls);boolean isBool = isBasicType(cls);if (isBool) {if (column.equalsIgnoreCase(field.getName())) {String str = cursor.getString(cursor.getColumnIndex(column));if (str == null) {break;}Constructor<? extends Object> con = cls.getConstructor(String.class);Object obj = con.newInstance(str);field.setAccessible(true);field.set(t, obj);}} else {Object obj = setAttribute(cursor, cls);field.set(t, obj);}}}return t;}return null;}/** * 获得包装类 * @param cls * @return */private Class<? extends Object> getBasicClass(Class<? extends Object> cls) {Class<? extends Object> _cls = clsMap.get(cls);if (_cls == null) {_cls = cls;}return _cls;}/** * 判断是否为基本类型 * @param cls * @return */private boolean isBasicType(Class<? extends Object> cls) {if (Integer.class.equals(cls) || Long.class.equals(cls)|| Float.class.equals(cls) || Double.class.equals(cls)|| Boolean.class.equals(cls) || Byte.class.equals(cls)|| Short.class.equals(cls) || String.class.equals(cls)) {return true;}return false;}/** * 将实体类属性字段封装到String数组中 * @param entity实体类Class文件 * @returnString[]数组 */public <T> String[] getColumns(Class<T> entity) {Field[] fields = reflect.getDeclaredFields(entity);String[] columns = new String[fields.length];for (int i = 0; i < fields.length; i++) {columns[i] = fields[i].getName();}return columns;}/** * 封装SQL查询中的where子句 * @param paramNameswhere子句条件参数 * @return */public String getSelection(String[] paramNames) {if (paramNames.length > 0) {StringBuilder builder = new StringBuilder();for (String str : paramNames) {builder.append(str + " = ? and ");}if (builder.length() > 0) {builder.delete(builder.lastIndexOf("?") + 1, builder.length());}return builder.toString();}return null;}/** * 封装SQL模糊查询中的where子句 * @param paramNames * @return */public String getBlurSelection(String[] paramNames) {if (paramNames.length > 0) {StringBuilder builder = new StringBuilder();for (String str : paramNames) {builder.append(str + " like ? and ");}if (builder.length() > 0) {builder.delete(builder.lastIndexOf("?") + 1, builder.length());}return builder.toString();}return null;}public String[] getBlurValues(String[] values) {String[] temps = new String[values.length];for (int i = 0; i < values.length; i++) {temps[i] = "%" + values[i] + "%";}return temps;}/** * 获得分页查询条件 * @param start分页开始位置 * @param max分页显示数目 * @return */public String getLimit(int start, int max) {return start + "," + max;}/** * 获得排序查询条件 * @param order排序字段集合 * @return */public String getOrder(Map<String, String> order) {if (order != null) {StringBuilder builder = new StringBuilder();Set<String> keys = order.keySet();for (String key : keys) {String str = order.get(key);if ("asc".equals(str) || "desc".equals(str)) {builder.append(key + " " + str + ", ");}}if (builder.length() > 0) {builder.delete(builder.lastIndexOf(","), builder.length());}return builder.toString();}return null;}

以上代码都有注释，也就不需要过多的解释了

不过有人会问JavaBeanReflect类代码有木有，贴在最后

下面是实现类实现的新增操作

@Overridepublic <T> long saveEntity(String tabName, T t) {if (StringUtils.isEmpty(tabName)) {return -1;}if (t == null) {return -1;}ContentValues values = setAttribute(t);SQLiteDatabase db = getWriteDataBase();db.beginTransaction();long id = db.insert(tabName, null, values);db.setTransactionSuccessful();db.endTransaction();freeResource(db, null);return id;}

不好意思，这个没有注释，大家就凑合着看吧

删除操作

public <T> int deleteByID(String tabName, int id) {if (StringUtils.isEmpty(tabName)) {return -1;}String sql = "id = ?";SQLiteDatabase db = getWriteDataBase();db.beginTransaction();int i = db.delete(tabName, sql, new String[] { String.valueOf(id) });db.setTransactionSuccessful();db.endTransaction();freeResource(db, null);return i;}

修改操作

public <T> int updateEntity(String tabName, T t) {if (StringUtils.isEmpty(tabName)) {return -1;}ContentValues values = setAttribute(t);String sql = "id = ?";SQLiteDatabase db = getWriteDataBase();db.beginTransaction();int i = db.update(tabName, values, sql,new String[] { String.valueOf(JavaBeanReflect.getInstance().getFieldValue(t, "id")) });db.setTransactionSuccessful();db.endTransaction();freeResource(db, null);return i;}

根据ID查询操作

@Overridepublic <T> T findByID(String tabName, Class<T> entity, int id) {if (StringUtils.isEmpty(tabName)) {return null;}String[] columns = getColumns(entity);String selection = "id = ?";String[] values = new String[] { String.valueOf(id) };SQLiteDatabase db = getReadDataBase();Cursor cursor = db.query(tabName, columns, selection, values, null, null, null);T t = null;while (cursor.moveToNext()) {try {t = setAttribute(cursor, entity);} catch (Exception e) {e.printStackTrace();}}freeResource(db, cursor);return t;}

查询所有数据集合(单张表)

public <T> List<T> findAll(String tabName, Class<T> entity) {if (StringUtils.isEmpty(tabName)) {return null;}String[] columns = getColumns(entity);SQLiteDatabase db = getReadDataBase();Cursor cursor = db.query(tabName, columns, null, null, null, null, null);List<T> list = new ArrayList<T>();while (cursor.moveToNext()) {try {T t = setAttribute(cursor, entity);list.add(t);} catch (Exception e) {e.printStackTrace();}}freeResource(db, cursor);return list;}

分页查询操作

public <T> List<T> findAll(String tabName, Class<T> entity, int start, int max) {if (StringUtils.isEmpty(tabName)) {return null;}String[] columns = getColumns(entity);String limit = getLimit(start, max);SQLiteDatabase db = getReadDataBase();Cursor cursor = db.query(tabName, columns, null, null, null, null, null, limit);List<T> list = new ArrayList<T>();while (cursor.moveToNext()) {try {T t = setAttribute(cursor, entity);list.add(t);} catch (Exception e) {e.printStackTrace();}}freeResource(db, cursor);return list;}

排序查询操作

public <T> List<T> findAll(String tabName, Class<T> entity, Map<String, String> order) {if (StringUtils.isEmpty(tabName)) {return null;}String[] columns = getColumns(entity);String orderBy = getOrder(order);SQLiteDatabase db = getReadDataBase();Cursor cursor = db.query(tabName, columns, null, null, null, null, orderBy);List<T> list = new ArrayList<T>();while (cursor.moveToNext()) {try {T t = setAttribute(cursor, entity);list.add(t);} catch (Exception e) {e.printStackTrace();}}freeResource(db, cursor);return list;}

分页、排序一起来的就不上代码了，上面两个结合一下就好了

条件查询操作：

public <T> List<T> findByPropery(String tabName, Class<T> entity, String[] paramNames, String[] values) {if (StringUtils.isEmpty(tabName)) {return null;}String[] columns = getColumns(entity);String selection = getSelection(paramNames);SQLiteDatabase db = getReadDataBase();Cursor cursor = db.query(tabName, columns, selection, values, null, null, null);List<T> list = new ArrayList<T>();while (cursor.moveToNext()) {try {T t = setAttribute(cursor, entity);list.add(t);} catch (Exception e) {e.printStackTrace();}}freeResource(db, cursor);return list;}

模糊查询操作

public <T> List<T> findByBlur(String tabName, Class<T> entity, String[] paramNames, String[] values) {if (StringUtils.isEmpty(tabName)) {return null;}String[] columns = getColumns(entity);String selection = getBlurSelection(paramNames);values = getBlurValues(values);SQLiteDatabase db = getReadDataBase();Cursor cursor = db.query(tabName, columns, selection, values, null, null, null);List<T> list = new ArrayList<T>();while (cursor.moveToNext()) {try {T t = setAttribute(cursor, entity);list.add(t);} catch (Exception e) {e.printStackTrace();}}freeResource(db, cursor);return list;}

JavaBeanReflect类代码

/** * 针对JavaBean操作的反射工具类 * @author Francis-ChinaFeng * @version 1.0 2013-07-08 */public class JavaBeanReflect {/** * 单例模式 */private static JavaBeanReflect reflect = new JavaBeanReflect();private JavaBeanReflect() { }public static JavaBeanReflect getInstance() {return reflect;}/** * 获得实体类中的属性信息数组 * @param entity * @return */public <T> Field[] getDeclaredFields(Class<T> entity) {return entity.getDeclaredFields();}/** * 获得实体类中属性对应的值 * @param t * @param field * @return */public <T> Object getFieldValue(T t, Field field) {field.setAccessible(true);Object obj = null;try {obj = field.get(t);} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();}field.setAccessible(false);return obj;}/** * 获得实体类中属性对应的值 * @param t * @param fieldName * @return */public <T> Object getFieldValue(T t, String fieldName) {try {Field field = t.getClass().getDeclaredField(fieldName);return getFieldValue(t, field);} catch (NoSuchFieldException e) {e.printStackTrace();}return null;}}

虽然没有将代码全贴出来，不过也就那么几个方法

增加：新增实体类，批量增加

删除：根据ID删除、根据属性删除、模糊删除

更新：根据ID更新、根据属性更新、模糊更新

查询全部，代码全贴

根据属性查询：按照全部添加分页、排序操作

模糊查询同属性查询一样

这样，DAO实现类也就封装好了

如果在实际情况中的业务需求还需要其他复杂的操作，大家可以拓展下

Hibernate也可以使用该封装方法的思想

拍砖请联系QQ：920656263

希望对大家有所帮助~~ 谢谢

* 备注：
* 因为查询操作中使用的是反射的技术
* 故以封装好的程序暂时无法支持DATE数据类型
* 如果需求DATE数据类型
* 1、请重写setAttribute(Cursor cursor, Class<T> entity)方法，或者自定义操作方法
* 2、请使用System.currentTimeMillis()方法获取long类型的时间值，在业务层进行业务处理

更多相关文章

随机推荐