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--->Double
typeName = 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);
}
}
// 生成sql
String 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();
// 执行sql
try {


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);
}
}
// 生成sql
String 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;


// 主键map


for (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;// 自增id
Method 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);
// 生成sql
String 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();
// 执行sql
try {
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);
else
sb.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);
} else
sb.append(c);
firstTime = false;
}
return sb.toString();
}
}