[原创]通用的面向对象查询的dao（二）

dao的改进版，支持简单的注解，例如@DbConfig(length = 20000,type="BLOB")，其中len为字段长度，type为映射到的类型，支持orm查询和其他查询方式。为sqllite数据库写的。

1.数据源类DataSource：

package com.lowca.robot.dao.support;

import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

public class DataSource implements javax.sql.DataSource {

	private static final String DRIVER = "org.sqlite.JDBC";
	private static final String CONNECT_URL = "jdbc:sqlite:c:/robot.db";
	private static final String USERNAME = null;
	private static final String PASSWORD = null;
	private static final Log log = LogFactory.getLog(DataSource.class);

	static {
		try {
			Class.forName(DRIVER);
		} catch (ClassNotFoundException e) {
			throw new RuntimeException("加载JDBC驱动失败:\n" + e.getMessage());
		}
	}

	@Override
	public Connection getConnection() throws SQLException {
		Connection conn = DriverManager.getConnection(CONNECT_URL);
		conn.setAutoCommit(false);
		return conn;
	}

	@Override
	public Connection getConnection(String username, String password)
			throws SQLException {
		Connection conn = DriverManager.getConnection(CONNECT_URL, USERNAME,
				PASSWORD);
		conn.setAutoCommit(false);
		return conn;
	}

	@Override
	public PrintWriter getLogWriter() throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public int getLoginTimeout() throws SQLException {
		// TODO Auto-generated method stub
		return 0;
	}

	@Override
	public void setLogWriter(PrintWriter out) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public void setLoginTimeout(int seconds) throws SQLException {
		// TODO Auto-generated method stub

	}

	@Override
	public boolean isWrapperFor(Class<?> iface) throws SQLException {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public <T> T unwrap(Class<T> iface) throws SQLException {
		// TODO Auto-generated method stub
		return null;
	}

	/**
	 * 释放连接
	 * 
	 * @param rs
	 * @param ps
	 * @param conn
	 */
	public void free(ResultSet rs, PreparedStatement ps, Connection conn) {
		try {
			if (rs != null)
				rs.close();
		} catch (Exception e) {
			log.error(this, e);
		} finally {
			try {
				if (ps != null)
					ps.close();
			} catch (Exception e) {
				log.error(this, e);
			} finally {
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
						log.error(this, e);
					}
				}
			}
		}
	}

}

2.自定义注解类DbConfig：

package com.lowca.robot.dao.support;

import java.lang.annotation.Documented;
import java.lang.annotation.ElementType;
import java.lang.annotation.Inherited;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

/**
 * 用户自定义标签,带有成员变量的DbConfig
 */
@Documented
// 将注释包含在JavaDoc中
@Inherited
// 允许子类继承父类中的注释。
@Target(value = { ElementType.METHOD, ElementType.CONSTRUCTOR })
// 标注这个注释使用的范围
@Retention(value = RetentionPolicy.RUNTIME)
// 要想使用反射得到注释信息，这个注释必须使用
public @interface DbConfig {
	/**
	 * 映射到的数据库类型
	 * 
	 * @return
	 */
	String type() default "";// 映射到的类型

	/**
	 * 字段长度
	 * 
	 * @return
	 */
	int length() default 100;// 字段长度
}

3.行集映射器类Mapper：

package com.lowca.robot.dao.support;

import java.lang.annotation.Annotation;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Date;

public class Mapper {

	/**
	 * 行集映射
	 * 
	 * @param <T>
	 * @param clazz
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public static <T> T rowMapping(Class<T> clazz, ResultSet rs)
			throws SQLException {
		T t = null;
		if (rs.next()) {
			try {
				t = clazz.newInstance();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
			ResultSetMetaData metadata = rs.getMetaData();
			int size = metadata.getColumnCount();
			for (int i = 0; i < size; i++) {
				String columnName = metadata.getColumnLabel(i + 1);
				Object columnValue = rs.getObject(metadata
						.getColumnLabel(i + 1));
				if (columnValue == null)
					continue;
				// 给对象赋值
				String propertyName = NameConverter.toJavaCase(columnName);
				String methodName = "set"
						+ propertyName.substring(0, 1).toUpperCase()
						+ propertyName.substring(1);
				Method[] methods = clazz.getMethods();
				for (Method method : methods) {
					if (methodName.equals(method.getName())) {
						try {
							String propertyTypeName = method
									.getParameterTypes()[0].getName();
							String columnTypeName = columnValue.getClass()
									.getName();
							if (propertyTypeName
									.equalsIgnoreCase(columnTypeName))
								method.invoke(t, columnValue);
							else {
								// 转换长整型和日期类型的不适配问题
								if ("java.util.Date".equals(propertyTypeName)
										&& "java.lang.Long"
												.equals(columnTypeName))
									method.invoke(t, new Date(
											(Long) columnValue));
								// 转换整型为布尔型
								if ("java.lang.Boolean"
										.equals(propertyTypeName)
										&& "java.lang.Integer"
												.equals(columnTypeName))
									method.invoke(t, columnValue.toString()
											.equals("0") ? false : true);

							}
						} catch (Exception e) {
							throw new RuntimeException(e);
						}
					}
				}

			}
		}
		return t;
	}

	public static String classMapping(Class<?> objectClass,
			String propertyName, Class<?> propertyClass) {
		String propertyClassName = propertyClass.getName();
		if ("java.lang.Integer".equals(propertyClassName)
				|| "java.lang.Long".equals(propertyClassName)
				|| "java.lang.Character".equals(propertyClassName)
				|| "java.lang.Short".equals(propertyClassName))
			return "INTEGER";
		if ("java.lang.Float".equals(propertyClassName)
				|| "java.lang.Double".equals(propertyClassName))
			return "REAL";
		if ("java.util.Date".equals(propertyClassName))
			return "DATETIME";
		if ("java.lang.Boolean".equals(propertyClassName))
			return "TINYINT";
		// String类型需要根据长度来做不同的映射
		if ("java.lang.String".equals(propertyClassName)) {
			String methodName = "get"
					+ propertyName.substring(0, 1).toUpperCase()
					+ propertyName.substring(1);
			String typeName = "VARCHAR";
			try {
				Annotation[] annotations = objectClass.getDeclaredMethod(
						methodName).getAnnotations();
				for (Annotation tag : annotations) {
					if (tag instanceof DbConfig) {
						int len = ((DbConfig) tag).length();
						if (len > 255) {
							typeName = "TEXT";
							break;
						}
						String type = ((DbConfig) tag).type();
						return type;
					}
				}
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
			return typeName;
		}

		return "";
	}
}

4.命名转换器类NameConverter ：

package com.lowca.robot.dao.support;

/**
 * <p>一个做命名规范转换的支持类</p>
 * <p>用来实现在java命名规范和数据库命名规范之间的转换</p>
 * @author konglz
 *
 */
public class NameConverter {

	/**
	 * 数据库命名方式转换成java的命名方式
	 * 
	 * @param s
	 * @return
	 */
	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 = false;
		}
		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();
	}

	public static void main(String[] args) {
		// System.out
		// .println(toDbCase("theyBeganToHumWhenSeeingJacksonWalkIntoTheHall"));
		// System.out
		// .println(toJavaCase(toDbCase("theyBeganToHumWhenSeeingJacksonWalkIntoTheHall")));
		StringBuffer sb = new StringBuffer("sdsdfds1");
		sb.delete(sb.length() - 1, sb.length());
		System.out.println(sb);
	}
}

5.主类Dao：

package com.lowca.robot.dao;

import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.lowca.robot.dao.support.DataSource;
import com.lowca.robot.dao.support.Mapper;
import com.lowca.robot.dao.support.NameConverter;

/**
 * <p>
 * 通用dao类，用法和说明如下：
 * </p>
 * <p>
 * 1.不支持事务
 * </p>
 * <p>
 * 2.不支持连接池
 * </p>
 * <p>
 * 3.命名方法必须严格遵守java pojo和数据库的命名规范/p>
 * <p>
 * 4.主键必须叫做“id”，且必须为Integer类型，其他基本类型的属性必须为其封装类型
 * </p>
 * <p>
 * 5.不支持复杂映射
 * </p>
 * 
 * @author kong
 * 
 */
public class Dao {

	private final DataSource dataSource = new DataSource();

	/**
	 * 查询方法
	 * 
	 * @param <T>
	 * @param clazz
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public <T> T query(Class<T> clazz, String sql, Object[] values)
			throws SQLException {
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		ResultSet rs = ps.executeQuery();
		T t = Mapper.rowMapping(clazz, rs);
		dataSource.free(null, ps, conn);
		return t;

	}

	/**
	 * 查询方法（不带参数）
	 * 
	 * @param <T>
	 * @param clazz
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public <T> T query(Class<T> clazz, String sql) throws SQLException {
		return query(clazz, sql, null);
	}

	/**
	 * 查询多个对象
	 * 
	 * @param <T>
	 * @param clazz
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public <T> List<T> queryList(Class<T> clazz, String sql, Object[] values)
			throws SQLException {
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		ResultSet rs = ps.executeQuery();
		List<T> list = new ArrayList<T>();
		T t = null;
		while ((t = Mapper.rowMapping(clazz, rs)) != null) {
			list.add(t);
		}
		dataSource.free(null, ps, conn);
		return list;
	}

	/**
	 * 查询多个对象（不带参数）
	 * 
	 * @param <T>
	 * @param clazz
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public <T> List<T> queryList(Class<T> clazz, String sql)
			throws SQLException {
		return queryList(clazz, sql, null);
	}

	/**
	 * 查询某列第一行的值
	 * 
	 * @param sql
	 * @param values
	 * @param columnName
	 *            列名
	 * @return
	 * @throws SQLException
	 */
	public Object queryForObject(String sql, Object[] values, String columnName)
			throws SQLException {
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		ResultSet rs = ps.executeQuery();
		Object object = null;
		if (rs.next()) {
			object = rs.getObject(columnName);
		}
		dataSource.free(null, ps, conn);
		return object;
	}

	/**
	 * 查询某列第一行的值（不带参数）
	 * 
	 * @param sql
	 * @param columnName
	 *            列名
	 * @return
	 * @throws SQLException
	 */
	public Object queryForObject(String sql, String columnName)
			throws SQLException {
		return queryForObject(sql, null, columnName);
	}

	/**
	 * 查询某列所有行的值作为一个数组返回
	 * 
	 * @param sql
	 * @param values
	 * @param columnName
	 *            列名
	 * @return
	 * @throws SQLException
	 */
	public Object[] queryForArray(String sql, Object[] values, String columnName)
			throws SQLException {
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		ResultSet rs = ps.executeQuery();
		List<Object> list = new ArrayList<Object>();
		while (rs.next()) {
			list.add(rs.getObject(columnName));
		}
		dataSource.free(null, ps, conn);
		return list.toArray();
	}

	/**
	 * 查询某列所有值作为一个数组返回（不带参数）
	 * 
	 * @param sql
	 * @param columnName
	 * @return
	 * @throws SQLException
	 */
	public Object[] queryForArray(String sql, String columnName)
			throws SQLException {
		return queryForArray(sql, null, columnName);
	}

	/**
	 * 查询所有行所有列的值，作为一个数组的列表返回
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public List<Object[]> queryForArrayList(String sql, Object[] values)
			throws SQLException {
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		ResultSet rs = ps.executeQuery();
		List<Object[]> result = new ArrayList<Object[]>();
		List<Object> list = new ArrayList<Object>();
		ResultSetMetaData metadata = rs.getMetaData();
		while (rs.next()) {
			int size = metadata.getColumnCount();
			for (int i = 0; i < size; i++) {
				Object columnValue = rs.getObject(metadata
						.getColumnLabel(i + 1));
				list.add(columnValue);
			}
			result.add(list.toArray());
		}
		dataSource.free(null, ps, conn);
		return result;
	}

	/**
	 * 查询所有行所有列的值，作为一个数组的列表返回（不带参数）
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public List<Object[]> queryForArrayList(String sql) throws SQLException {
		return queryForArrayList(sql, null);
	}

	/**
	 * 查询成一个map
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> queryForMap(String sql, Object[] values)
			throws SQLException {
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		ResultSet rs = ps.executeQuery();
		Map<String, Object> map = new HashMap<String, Object>();
		ResultSetMetaData metadata = rs.getMetaData();
		if (rs.next()) {
			int size = metadata.getColumnCount();
			for (int i = 0; i < size; i++) {
				String columnName = metadata.getColumnLabel(i + 1);
				Object columnValue = rs.getObject(metadata
						.getColumnLabel(i + 1));
				map.put(columnName, columnValue);
			}
		}
		dataSource.free(null, ps, conn);
		return map;
	}

	/**
	 * 查询成一个map（不带参数）
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public Map<String, Object> queryForMap(String sql) throws SQLException {
		return queryForMap(sql, null);
	}

	/**
	 * 查询成一个map的列表
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> queryForMapList(String sql, Object[] values)
			throws SQLException {
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		ResultSet rs = ps.executeQuery();
		List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
		Map<String, Object> map = new HashMap<String, Object>();
		ResultSetMetaData metadata = rs.getMetaData();
		if (rs.next()) {
			int size = metadata.getColumnCount();
			for (int i = 0; i < size; i++) {
				String columnName = metadata.getColumnLabel(i + 1);
				Object columnValue = rs.getObject(metadata
						.getColumnLabel(i + 1));
				map.put(columnName, columnValue);
			}
			list.add(map);
		}
		dataSource.free(null, ps, conn);
		return list;
	}

	/**
	 * 查询成一个map的列表（不带参数）
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> queryForMapList(String sql)
			throws SQLException {
		return queryForMapList(sql, null);
	}

	/**
	 *统计一类对象的个数
	 * 
	 * @param clazz
	 * @return
	 * @throws SQLException
	 */
	public int count(Class<?> clazz) throws SQLException {
		Connection conn = dataSource.getConnection();
		String tableName = NameConverter.toDbCase(clazz.getSimpleName());
		String sql = "SELECT COUNT(id) FROM " + tableName;
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		int num = 0;
		if (rs.next())
			num = rs.getInt(1);
		dataSource.free(null, ps, conn);
		return num;
	}

	/**
	 * 根据sql统计
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public int count(String sql, Object[] values) throws SQLException {
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		ResultSet rs = ps.executeQuery();
		int num = 0;
		if (rs.next())
			num = rs.getInt(1);
		dataSource.free(null, ps, conn);
		return num;
	}

	/**
	 * 根据sql统计（不带参数）
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public int count(String sql) throws SQLException {
		return count(sql, null);
	}

	/**
	 * 根据id获取
	 * 
	 * @param <T>
	 * @param clazz
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public <T> T get(Class<T> clazz, Integer id) throws SQLException {
		String tableName = NameConverter.toDbCase(clazz.getSimpleName());
		String sql = "SELECT * FROM " + tableName + " WHERE id="
				+ id.intValue();
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		T t = Mapper.rowMapping(clazz, rs);
		dataSource.free(null, ps, conn);
		return t;
	}

	/**
	 * 删除对象
	 * 
	 * @param clazz
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public int delete(Class<?> clazz, Integer id) throws SQLException {
		String tableName = NameConverter.toDbCase(clazz.getSimpleName());
		String sql = "DELETE FROM " + tableName + " WHERE id=" + id.intValue();
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		int rowCount = ps.executeUpdate();
		dataSource.free(null, ps, conn);
		return rowCount;
	}

	/**
	 * 保存对象
	 * 
	 * @param object
	 * @throws SQLException
	 */
	public void save(Object object) throws SQLException {
		// 通过反射提取属性和属性值
		Method[] methods = object.getClass().getMethods();
		Map<String, Object> kvMap = new HashMap<String, Object>();
		for (Method method : methods) {
			if (method.getName().startsWith("set")) {
				String key = method.getName().substring(3, 4).toLowerCase()
						+ method.getName().substring(4);
				Method getMethod = null;
				Object value = null;
				try {
					getMethod = object.getClass().getDeclaredMethod(
							method.getName().replaceFirst("set", "get"));
					value = getMethod.invoke(object);
				} catch (Exception e) {
					throw new RuntimeException(e);
				}
				kvMap.put(key, value);
			}
		}
		// 生成sql
		String tableName = NameConverter.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 = NameConverter.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
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		conn.setAutoCommit(true);
		ps.execute();
		conn.setAutoCommit(false);
		// 获取主键
		ps = conn.prepareStatement("SELECT LAST_INSERT_ROWID()");
		ResultSet rs = ps.executeQuery();
		Integer pk = 0;
		if (rs.next())
			pk = rs.getInt(1);
		// 给对象赋主键的值
		try {
			Method method = object.getClass().getDeclaredMethod("setId",
					Integer.class);
			method.invoke(object, pk);
		} catch (Exception e) {
			throw new RuntimeException(e);
		}
		dataSource.free(null, ps, conn);
	}

	/**
	 * 更新对象
	 * 
	 * @param object
	 * @throws SQLException
	 */
	public void update(Object object) throws SQLException {
		// 通过反射提取属性和属性值
		Method[] methods = object.getClass().getMethods();
		Map<String, Object> kvMap = new HashMap<String, Object>();
		for (Method method : methods) {
			if (method.getName().startsWith("set")) {
				String key = method.getName().substring(3, 4).toLowerCase()
						+ method.getName().substring(4);
				Method getMethod = null;
				Object value = null;
				try {
					getMethod = object.getClass().getDeclaredMethod(
							method.getName().replaceFirst("set", "get"));
					value = getMethod.invoke(object);
				} catch (Exception e) {
					throw new RuntimeException(e);
				}
				kvMap.put(key, value);
			}
		}
		// 生成sql
		String tableName = NameConverter.toDbCase(object.getClass()
				.getSimpleName());
		Object[] values = new Object[kvMap.size()];
		StringBuffer sb = new StringBuffer("UPDATE " + tableName + " ");
		int index = 0;
		Integer id = 0;
		boolean firstTime = true;
		for (String key : kvMap.keySet()) {
			String columnName = NameConverter.toDbCase(key);
			if (key.equalsIgnoreCase("id")) {
				id = (Integer) kvMap.get(key);
				continue;
			}
			sb.append((firstTime ? " SET " : "") + columnName + "=?,");
			firstTime = false;
			values[index] = kvMap.get(key);
			index++;
		}
		values[index] = id;
		if (sb.charAt(sb.length() - 1) == ',')
			sb.delete(sb.length() - 1, sb.length());
		sb.append(" WHERE id=?;");
		String sql = sb.toString();
		// 执行sql
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		conn.setAutoCommit(true);
		ps.executeUpdate();
		conn.setAutoCommit(false);
		dataSource.free(null, ps, conn);
	}

	/**
	 * 执行sql语句
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public boolean execute(String sql, Object[] values) throws SQLException {
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		if (values != null) {
			for (int i = 0; i < values.length; i++) {
				ps.setObject(i + 1, values[i]);
			}
		}
		conn.setAutoCommit(true);
		boolean r = ps.execute();
		conn.setAutoCommit(false);
		dataSource.free(null, ps, conn);
		return r;
	}

	/**
	 * 执行sql语句，不带参数
	 * 
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public boolean execute(String sql) throws SQLException {
		return execute(sql, null);
	}

	/**
	 * 检查数据库是否有这个表
	 * 
	 * @param clazz
	 * @return
	 * @throws SQLException
	 */
	public boolean existTable(Class<?> clazz) throws SQLException {
		String tableName = NameConverter.toDbCase(clazz.getSimpleName());
		String sql = "SELECT COUNT(*) AS table_count FROM sqlite_master WHERE TYPE='table' AND NAME=?;";
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setObject(1, tableName);
		ResultSet rs = ps.executeQuery();
		int num = 0;
		if (rs.next()) {
			num = rs.getInt("table_count");
		}
		dataSource.free(null, ps, conn);
		return num > 0 ? true : false;
	}

	/**
	 * 使用类来创建表
	 * 
	 * @param clazz
	 * @param delIfExist
	 *            如果表已存在，true表示删除旧表并重新建表，false表示保留旧表不再重新建
	 * @throws SQLException
	 */
	public void createTable(Class<?> clazz, boolean delIfExist)
			throws SQLException {
		// 如果表已经存在，则看看是否需要删除
		boolean existTable = existTable(clazz);
		if (!delIfExist && existTable) {
			return;
		}
		if (delIfExist && existTable) {
			deleteTable(clazz);
		}
		// 通过反射提取属性和属性值
		Method[] methods = clazz.getMethods();
		Map<String, Class<?>> kvMap = new HashMap<String, Class<?>>();
		for (Method method : methods) {
			if (method.getName().startsWith("set")) {
				String property = method.getName().substring(3, 4)
						.toLowerCase()
						+ method.getName().substring(4);
				Class<?> propertyClass = method.getParameterTypes()[0];
				kvMap.put(property, propertyClass);
			}
		}
		// 生成sql
		String tableName = NameConverter.toDbCase(clazz.getSimpleName());
		StringBuffer sb = new StringBuffer("CREATE TABLE " + tableName
				+ " (id INTEGER PRIMARY KEY,");
		for (String key : kvMap.keySet()) {
			if (key.equalsIgnoreCase("id")) {
				continue;
			}
			String columnName = NameConverter.toDbCase(key);
			Class<?> propertyClass = kvMap.get(key);
			String dbTypeName = Mapper.classMapping(clazz, key, propertyClass);
			sb.append(columnName + " " + dbTypeName + ",");
		}
		if (sb.charAt(sb.length() - 1) == ',')
			sb.delete(sb.length() - 1, sb.length());
		sb.append(");");
		String sql = sb.toString();
		System.out.println(sql);
		// 执行sql
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		conn.setAutoCommit(true);
		ps.execute();
		conn.setAutoCommit(false);
		dataSource.free(null, ps, conn);
	}

	/**
	 * 使用sql来建表
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public void createTable(String sql) throws SQLException {
		// 创建表
		Connection conn = dataSource.getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		conn.setAutoCommit(true);
		ps.execute();
		conn.setAutoCommit(false);
		dataSource.free(null, ps, conn);
	}

	/**
	 * 创建表，如果表已经存在，则不新建
	 * 
	 * @param clazz
	 * @throws SQLException
	 */
	public void createTable(Class<?> clazz) throws SQLException {
		createTable(clazz, false);
	}

	/**
	 * 按类名删除表
	 * 
	 * @param clazz
	 * @throws SQLException
	 */
	public void deleteTable(Class<?> clazz) throws SQLException {
		String tableName = NameConverter.toDbCase(clazz.getSimpleName());
		String sql = "DROP TABLE IF EXISTS " + tableName;
		execute(sql);
	}

	/**
	 * 按表名删除表
	 * 
	 * @param tableName
	 * @throws SQLException
	 */
	public void deleteTable(String tableName) throws SQLException {
		String sql = "DROP TABLE IF EXISTS " + tableName;
		execute(sql);
	}

	/**
	 * 删除所有表
	 * 
	 * @throws SQLException
	 */
	public int deleteAllTable() throws SQLException {
		String sql = "SELECT name FROM sqlite_master WHERE TYPE='table';";
		Object[] objects = queryForArray(sql, "name");
		int size  = objects.length;
		for (Object o : objects)
			deleteTable(o.toString());
		return size;
	}

}