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--->Double
						typeName = 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--->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 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);
			}
		}
		// 生成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")) {
					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);
	}
}