java中利用泛型反射创建数据库

package com.tsutils.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;import android.util.Log;import com.tsutils.db.sql.DataSql;/** * 数据库操作 *  * @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 <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) {// Date curDate = new Date(System.currentTimeMillis());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")) {if (!method.getName().equals("setFlag")) {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);}}package com.tsutils.db;/** * 表名格式化 *  * @author sl *  */public class TableName {public static String toJavaCase(String s) {if (s == null || s.trim().length() == 0)return s;StringBuffer sb = new StringBuffer();String[] array = s.split("_");boolean firstTime = true;for (String e : array) {if (e.length() == 0)continue;else if (e.length() == 1)sb.append(firstTime ? e.toUpperCase() : e);elsesb.append(firstTime ? (e.substring(0, 1).toUpperCase() + e.substring(1)) : e);firstTime = true;}return sb.toString();}/*** Java命名方式转换成数据库的命名方式* * @param s* @return*/public static String toDbCase(String s) {if (s == null || s.trim().length() == 0)return s;char[] chars = s.toCharArray();boolean firstTime = true;StringBuffer sb = new StringBuffer();for (char c : chars) {if (c >= 'A' && c <= 'Z') {char c1 = (char) (c + 32);sb.append(firstTime ? c1 : "_" + c1);} elsesb.append(c);firstTime = false;}return sb.toString();}}