java通过反射实现数据库的编写

通过反射机制实现数据库的编写：

package com.demo.db;import java.lang.reflect.Field;import java.lang.reflect.Method;import android.database.Cursor;/** * 数据库反射 *  */public class Mapper {private static DataUtils dataUtils = new DataUtils();/** *  * @param clazz * @param rs * @return */public static <T> T rowMapping(Class<T> clazz, Cursor c) {T t = null;Field[] fields = clazz.getDeclaredFields();String[] names = new String[fields.length];for (int i = 0; i < names.length; i++) {fields[i].setAccessible(true);names[i] = fields[i].getName();}try {if (c.moveToNext()) {t = clazz.newInstance();for (Field f : fields) {if (f.getAnnotations().length != 0) {f.setAccessible(true);// /用反射来调用相应的方法// /先构造出方法的名字String typeName = f.getType().getSimpleName();// /int --> Int,doble--->DoubletypeName = typeName.substring(0, 1).toUpperCase()+ typeName.substring(1);// /cuosor 的方法的名字if ("Date".equals(typeName)) {typeName = "String";}String methodName = "get" + typeName;// /得到方法Method method = c.getClass().getMethod(methodName,int.class);Object retValue = method.invoke(c, c.getColumnIndex(TableName.toDbCase(f.getName())));invoke(t, f.getName(), retValue + "");}}}} catch (Exception e) {e.printStackTrace();throw new RuntimeException(e);}return t;}public static void invoke(Object o, String fieldName, String value) {try {String setMethodName = "set" + TableName.toJavaCase(fieldName);String getMethodName = "get" + TableName.toJavaCase(fieldName);Method getMethod = o.getClass().getMethod(getMethodName);Method setMethod = o.getClass().getMethod(setMethodName,getMethod.getReturnType());if (getMethod.getReturnType().isPrimitive()) {setMethod.invoke(o,new Object[] { dataUtils.convertPrimitiveValue(getMethod.getReturnType(), value) });} else {setMethod.invoke(o,new Object[] { dataUtils.convertObjectValue(getMethod.getReturnType(), value) });}} catch (Exception e) {e.printStackTrace();}}/** * 数据类型，反射成数据表定义类型 *  * @param objectClass * @param propertyName * @param propertyClass * @return */public static String classMapping(Class<?> objectClass,String propertyName, Class<?> propertyClass) {return "";}}

第二个类的实现：

package com.demo.db;import java.lang.reflect.Field;import java.lang.reflect.InvocationTargetException;import java.lang.reflect.Method;import java.lang.reflect.Modifier;import java.sql.SQLException;import java.text.ParseException;import java.text.SimpleDateFormat;import java.util.ArrayList;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;import org.apache.commons.lang.StringUtils;import android.content.Context;import android.database.Cursor;import android.database.sqlite.SQLiteDatabase;/** * 数据库操作 *  * @author sl *  */public class SQLiteDao {private static SQLiteDao instance;private SQLiteDatabase sqlDb;public static SQLiteDao getInstance(Context context) {if (instance == null) {instance = new SQLiteDao(context);}return instance;}public SQLiteDao(Context context) {super();sqlDb = context.openOrCreateDatabase("st.db", Context.MODE_PRIVATE,null);}public final static byte[] _writeLock = new byte[0];/** *  * @param sql */public void execute(String sql) {sqlDb.execSQL(sql);}/*** * 获取指定类型的所有的数据 *  * @param cls * @return */public <T> List<T> getArrays(Class<T> cls, String selection,String[] selectionArgs, String groupBy, String having,String orderBy, String limit) {Field[] fields = cls.getDeclaredFields();List<String> names = new ArrayList<String>();for (int i = 0; i < fields.length; i++) {fields[i].setAccessible(true);if (fields[i].getAnnotations().length != 0)names.add(TableName.toDbCase(fields[i].getName()));}String tableName = cls.getSimpleName();List<T> entities = new ArrayList<T>();Cursor cursor = sqlDb.query(TableName.toDbCase(tableName),names.toArray(new String[0]), selection, selectionArgs,groupBy, having, orderBy, limit);try {SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");while (cursor.moveToNext()) {T entity = cls.newInstance();for (Field f : fields) {if (f.getAnnotations().length == 0)continue;f.setAccessible(true);// /用反射来调用相应的方法// /先构造出方法的名字String typeName = f.getType().getSimpleName();// /int --> Int,doble--->DoubletypeName = typeName.substring(0, 1).toUpperCase()+ typeName.substring(1);// /cuosor 的方法的名字if ("Date".equals(typeName)) {typeName = "String";}String methodName = "get" + typeName;// /得到方法Method method = cursor.getClass().getMethod(methodName,int.class);Object retValue = method.invoke(cursor, cursor.getColumnIndex(TableName.toDbCase(f.getName())));if (f.getType() == Date.class)try {retValue = dateFormat.parse(retValue.toString());} catch (ParseException e) {e.printStackTrace();}f.set(entity, retValue);// f.set(entity, cursor.)}entities.add(entity);}} catch (NullPointerException ex) {} catch (SecurityException e) {e.printStackTrace();} catch (NoSuchMethodException e) {e.printStackTrace();} catch (IllegalArgumentException e) {e.printStackTrace();} catch (IllegalAccessException e) {e.printStackTrace();} catch (InvocationTargetException e) {e.printStackTrace();} catch (InstantiationException e) {e.printStackTrace();} finally {if (cursor != null) {cursor.close();}}return entities;}/** * 获取同步表中的最大时间戳 *  * @param clazz * @return */public long getMaxtimestamp(Class<?> clazz) {long timestamp = 0l;Cursor c = null;try {c = sqlDb.query(TableName.toDbCase(clazz.getSimpleName()),new String[] { "ctimestamp" }, null, null, null, null,"ctimestamp desc", "1");if (c != null && c.moveToNext()) {String date = c.getString(c.getColumnIndex("ctimestamp"));if (date != null) {java.text.SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");timestamp = sdf.parse(date).getTime();}}} catch (Exception e) {e.printStackTrace();} finally {if (c != null) {c.close();}}return timestamp;}/** * 查询方法 *  * @param <T> * @param clazz * @param sql * @param values * @return * @throws SQLException */public <T> T query(Class<T> clazz, String[] columns, String selection,String[] selectionArgs) throws SQLException {T t = null;Cursor c = null;try {c = sqlDb.query(TableName.toDbCase(clazz.getSimpleName()), columns,selection, selectionArgs, null, null, null);t = Mapper.rowMapping(clazz, c);} finally {if (c != null) {c.close();}}return t;}/** * 查询方法 *  * @param <T> * @param clazz * @param sql * @param values * @return * @throws SQLException */public <T> T query(Class<T> clazz, String[] columns, String selection,String[] selectionArgs, String groupBy, String having,String orderBy) throws SQLException {T t = null;Cursor c = null;try {c = sqlDb.query(TableName.toDbCase(clazz.getSimpleName()), columns,selection, selectionArgs, groupBy, having, orderBy);t = Mapper.rowMapping(clazz, c);} finally {if (c != null) {c.close();}}return t;}/** * 统计一类对象的个数 *  * @param clazz * @return * @throws SQLException */public int count(Class<?> clazz, String selection, String[] selectionArgs,String groupBy, String having, String orderBy, String limit)throws SQLException {return count(TableName.toDbCase(clazz.getSimpleName()), selection,selectionArgs, groupBy, having, orderBy, limit);}/** * 统计一类对象的个数 *  * @param tableName * @return * @throws SQLException */public int count(String tableName, String selection,String[] selectionArgs, String groupBy, String having,String orderBy, String limit) {int num = 0;Cursor c = null;try {c = sqlDb.query(tableName, new String[] { "count(1)" }, selection,selectionArgs, groupBy, having, orderBy, limit);if (c != null && c.moveToNext()) {num = c.getInt(0);}} catch (Exception e) {e.printStackTrace();} finally {if (c != null) {c.close();}}return num;}/** * 忽略不带annotation的属性 *  * @return */public boolean ignoreTableColumn() {return false;}/** * 保存对象 通过反射提取属性和属性值 自增id目前去掉该功能 *  * @param object * @throws SQLException */public void save(Object object) throws SQLException {Map<String, Object> kvMap = new HashMap<String, Object>();Field[] fields = object.getClass().getDeclaredFields();SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");for (Field field : fields) {TableColumn column = field.getAnnotation(TableColumn.class);if (column == null)continue;String md = "set" + StringUtils.capitalize(field.getName());try {Method method = object.getClass().getDeclaredMethod(md,field.getType());if (method.getName().startsWith("set")) {String key = field.getName();if (key.equals("id")) {if (!column.type().equals("")&& column.type().toUpperCase().contains("AUTOINCREMENT")) // 过滤id{continue;}}Method getMethod = object.getClass().getDeclaredMethod(method.getName().replaceFirst("set", "get"));Object value = getMethod.invoke(object);if (value instanceof Date) {value = dateFormat.format((Date) value);}kvMap.put(key, value);}} catch (Exception e) {throw new RuntimeException(e);}}// 生成sqlString tableName = TableName.toDbCase(object.getClass().getSimpleName());Object[] values = new Object[kvMap.size()];StringBuffer sb = new StringBuffer("INSERT INTO " + tableName + "(");StringBuffer params = new StringBuffer();int index = 0;for (String key : kvMap.keySet()) {String columnName = TableName.toDbCase(key);sb.append(columnName + ",");params.append(values[index] = kvMap.get(key) + "','");index++;}if (sb.charAt(sb.length() - 1) == ',')sb.delete(sb.length() - 1, sb.length());if (params.charAt(params.length() - 1) == '\'')params.delete(params.length() - 3, params.length());sb.append(") VALUES('").append(params).append("');");String sql = sb.toString();// 执行sqltry {sqlDb.execSQL(sql);} catch (Exception e) {e.printStackTrace();} finally {sql = null;tableName = null;kvMap = null;}}/** * 更新对象 *  * @param object *            对象 * @param primaryKeys *            主键 * @throws SQLException */public void update(Object object, String[] primaryKeys) throws SQLException {Map<String, Object> kvMap = new HashMap<String, Object>();// 所有的属性与属性值的对应关系Field[] fields = object.getClass().getDeclaredFields();SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");for (Field field : fields) {TableColumn column = field.getAnnotation(TableColumn.class);if (column == null)continue;if (Modifier.isStatic(field.getModifiers()))continue;String md = "set" + StringUtils.capitalize(field.getName());try {Method method = object.getClass().getDeclaredMethod(md,field.getType());if (method.getName().startsWith("set")) {String key = field.getName();Method getMethod = object.getClass().getDeclaredMethod(method.getName().replaceFirst("set", "get"));Object value = getMethod.invoke(object);if (value instanceof Date) {// Log.i("info", "value:" + value);value = dateFormat.format((Date) value);}kvMap.put(key, value);}} catch (Exception e) {throw new RuntimeException(e);}}// 生成sqlString tableName = TableName.toDbCase(object.getClass().getSimpleName());Object[] values = new Object[kvMap.size() + primaryKeys.length]; //StringBuffer sb = new StringBuffer("UPDATE " + tableName + " ");int index = 0;boolean firstTime = true;// 主键mapfor (String key : kvMap.keySet())// kvMap{String columnName = TableName.toDbCase(key);sb.append((firstTime ? " SET " : "") + columnName + "='"+ (values[index] = kvMap.get(key)) + "',");firstTime = false;;index++;}if (sb.charAt(sb.length() - 1) == ',')sb.delete(sb.length() - 1, sb.length());// 拼凑主键条件for (int i = 0; i <= primaryKeys.length - 1; i++) {String primaryKey = primaryKeys[i];if (i == 0) {sb.append(" WHERE " + TableName.toDbCase(primaryKey) + "='"+ (values[index] = kvMap.get(primaryKey)) + "'");} else {sb.append(" and " + TableName.toDbCase(primaryKey) + "='"+ (values[index] = kvMap.get(primaryKey)) + "'");};index++;}String sql = sb.toString();try {sqlDb.execSQL(sql);} catch (Exception e) {e.printStackTrace();} finally {}}/** * 执行sql语句 *  * @param sql * @param values * @return * @throws SQLException *//** * 检查数据库是否有这个表 *  * @param clazz * @return * @throws SQLException */public boolean existTable(Class<?> clazz) throws SQLException {String tableName = TableName.toDbCase(clazz.getSimpleName());int num = count("sqlite_master", "TYPE=? and name=?", new String[] {"table", tableName }, null, null, null, null);return num > 0 ? true : false;}/** * 判断某张表是否存在 *  * @param tabName *            表名 * @return */public boolean tabIsExist(Class<?> clazz) {String tableName = TableName.toDbCase(clazz.getSimpleName());boolean result = false;if (tableName == null) {return false;}Cursor cursor = null;try {String sql = "select count(*) as c from sqlite_master where type ='table' and name ='"+ tableName + "' ";cursor = sqlDb.rawQuery(sql, null);if (cursor.moveToNext()) {int count = cursor.getInt(0);if (count > 0) {result = true;}}} catch (Exception e) {// TODO: handle exception} finally {if (cursor != null) {cursor.close();}}return result;}/** * 通过反射类的属性,创建表 delIfExist 如果表已存在，true表示删除旧表并重新建表，false表示保留旧表不再重新建 *  * @param clazz * @param delIfExist * @param version * @throws SQLException */public void createTable(Class<?> clazz, boolean delIfExist, int version)throws SQLException {boolean existTable = tabIsExist(clazz);if (!delIfExist && existTable) {return;}if (delIfExist && existTable) {deleteTable(clazz);}String tableName = TableName.toDbCase(clazz.getSimpleName());StringBuffer sb = new StringBuffer("CREATE TABLE " + tableName + " (");Field[] fields = clazz.getDeclaredFields();TableColumn column = null;String primaryKey = "";for (Field f : fields) {column = f.getAnnotation(TableColumn.class);if (column != null) {if (StringUtils.isNotBlank(column.primaryKey())) {primaryKey = " unique ("+ TableName.toDbCase(column.primaryKey()) + ")";}String fieldName = column.column().equals("") ? f.getName(): column.column();// 字段名称String fieldType = column.type().equals("") ? "varchar": column.type();// 字段类型fieldName = TableName.toDbCase(fieldName);// 名称转换sb.append(fieldName + " " + fieldType + ",");}}sb.append(primaryKey);if (sb.charAt(sb.length() - 1) == ',')sb.delete(sb.length() - 1, sb.length());sb.append(");");String sql = sb.toString();if (version != 0) {sqlDb.setVersion(version);}sqlDb.execSQL(sql);}/** * 创建表，如果表已经存在，则不新建 *  * @param clazz * @throws SQLException */public void createTable(Class<?> clazz) {try {createTable(clazz, false, 1);} catch (Exception e) {// Log.e("DBRROR", e.getMessage());e.printStackTrace();}}/** * 按类名删除表 *  * @param clazz * @throws SQLException */public void deleteTable(Class<?> clazz) throws SQLException {String tableName = TableName.toDbCase(clazz.getSimpleName());String sql = "DROP TABLE IF EXISTS " + tableName;execute(sql);}/** * 通过主键查询对象 *  * @param object *            对象 * @param clazz *            类别 * @param primaryKeys *            主键 * @return */public <T> T queryObj(Object object, Class<T> clazz, String[] primaryKeys) {T t = null;try {String selection = "";String[] objects = new String[primaryKeys.length];for (int i = 0; i <= primaryKeys.length - 1; i++) {String primaryKey = TableName.toDbCase(primaryKeys[i]);if (i == 0) {selection += primaryKey + "=?";} else {selection += " and " + primaryKey + "=?";}// 获取主键值String getMethodName = "get" + TableName.toJavaCase(primaryKey);Method getMethod = object.getClass().getMethod(getMethodName);objects[i] = getMethod.invoke(object).toString();}// 通过主键查询对象是否存在t = query(clazz, null, selection, objects);} catch (Exception e) {e.printStackTrace();} finally {}return t;}/** * 保存对象 (orderinfo信息) 通过反射提取属性和属性值 自增id目前去掉该功能 *  * @param object * @throws SQLException */public void save(Object object, String orderKey) throws SQLException {Map<String, Object> kvMap = new HashMap<String, Object>();Field[] fields = object.getClass().getDeclaredFields();for (Field field : fields) {TableColumn column = field.getAnnotation(TableColumn.class);if (column == null)continue;String md = "set" + StringUtils.capitalize(field.getName());try {Method method = object.getClass().getDeclaredMethod(md,field.getType());if (method.getName().startsWith("set")) {String key = field.getName();if (key.equals("id"))continue;// 自增idMethod getMethod = object.getClass().getDeclaredMethod(method.getName().replaceFirst("set", "get"));Object value = getMethod.invoke(object);kvMap.put(key, value);}} catch (Exception e) {throw new RuntimeException(e);}}if (orderKey != null)kvMap.put("orderno", orderKey);// 生成sqlString tableName = TableName.toDbCase(object.getClass().getSimpleName());Object[] values = new Object[kvMap.size()];StringBuffer sb = new StringBuffer("INSERT INTO " + tableName + "(");StringBuffer params = new StringBuffer();int index = 0;for (String key : kvMap.keySet()) {String columnName = TableName.toDbCase(key);sb.append(columnName + ",");params.append("?,");values[index] = kvMap.get(key);index++;}if (sb.charAt(sb.length() - 1) == ',')sb.delete(sb.length() - 1, sb.length());if (params.charAt(params.length() - 1) == ',')params.delete(params.length() - 1, params.length());sb.append(") VALUES(").append(params).append(");");String sql = sb.toString();// 执行sqltry {sqlDb.execSQL(sql);} catch (Exception e) {} finally {sql = null;tableName = null;kvMap = null;}}public String getPrimaryKey(Class<?> clazz) {Field[] fields = clazz.getDeclaredFields();String primaryKey = "id"; // 默认值for (Field field : fields) {TableColumn column = field.getAnnotation(TableColumn.class);if (column != null && !column.primaryKey().equals("")) {primaryKey = column.primaryKey();break;}}return primaryKey;}/** * 保存并更新 *  * @param object * @param clazz * @throws Exception */public <T> void saveOrUpdate(Object object, Class<T> clazz){try {String[] primaryKeys = getPrimaryKey(clazz).split("[,]");T t = queryObj(object, clazz, primaryKeys);if (t == null) {save(object);} else {update(object, primaryKeys);}} catch (Exception e) {e.printStackTrace();}}/** * 删除N天前数据 *  */public void deleteTimeBefore(Class<?> clazz) throws SQLException {String tableName = TableName.toDbCase(clazz.getSimpleName());String sql = "delete from " + tableName+ " where strftime('%Y-%m-%d',ctimestamp) < '"+ DataSql.getDateNDaysAgo() + "'";execute(sql);}/** * 删除N天前数据 *  */public void deleteTimeBeforeCreate(Class<?> clazz) throws SQLException {String tableName = TableName.toDbCase(clazz.getSimpleName());String sql = "delete from " + tableName+ " where strftime('%Y-%m-%d',ctimestamp) < '"+ DataSql.getDateNDaysAgo() + "'";execute(sql);}}