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

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

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

import com.lowca.robot.Crawler;

/**
 * <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 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(Crawler.class);

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

	/**
	 * 获取连接
	 * 
	 * @return
	 * @throws SQLException
	 */
	private Connection getConnection() throws SQLException {
		Connection conn = null;
		if (USERNAME != null && PASSWORD != null)
			conn = DriverManager.getConnection(CONNECT_URL, USERNAME, USERNAME);
		else
			conn = DriverManager.getConnection(CONNECT_URL);
		conn.setAutoCommit(false);
		return conn;
	}

	/**
	 * 行集映射
	 * 
	 * @param <T>
	 * @param clazz
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	private <T> T mapping(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 = 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;
	}

	/**
	 * 释放连接
	 * 
	 * @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);
					}
				}
			}
		}
	}

	/**
	 * 查询方法
	 * 
	 * @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 = 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 = mapping(clazz, rs);
		free(rs, 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 = 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 = mapping(clazz, rs)) != null) {
			list.add(t);
		}
		free(rs, 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 clazz
	 * @return
	 * @throws SQLException
	 */
	public int count(Class<?> clazz) throws SQLException {
		Connection conn = getConnection();
		String tableName = toDbCase(clazz.getSimpleName());
		String sql = "select count(*) from " + tableName;
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		int num = 0;
		if (rs.next())
			num = rs.getInt(1);
		free(rs, ps, conn);
		return num;
	}

	/**
	 * 根据sql统计
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public int count(String sql, Object[] values) throws SQLException {
		Connection conn = 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);
		free(rs, 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 = toDbCase(clazz.getSimpleName());
		String sql = "select * from " + tableName + " where id="
				+ id.intValue();
		Connection conn = getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		T t = mapping(clazz, rs);
		free(rs, ps, conn);
		return t;
	}

	/**
	 * 删除对象
	 * 
	 * @param clazz
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public int delete(Class<?> clazz, Integer id) throws SQLException {
		String tableName = toDbCase(clazz.getSimpleName());
		String sql = "delete from " + tableName + " where id=" + id.intValue();
		Connection conn = getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		int rowCount = ps.executeUpdate();
		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 = 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 = 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 = 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);
		}
		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 = 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 = 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 = 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);
		free(null, ps, conn);
	}

	/**
	 * 执行sql语句
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public boolean execute(String sql, Object[] values) throws SQLException {
		Connection conn = 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);
		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 = toDbCase(clazz.getSimpleName());
		String sql = "SELECT COUNT(*) AS table_count FROM sqlite_master WHERE TYPE='table' AND NAME=?;";
		Connection conn = 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");
		}
		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, String> kvMap = new HashMap<String, String>();
		for (Method method : methods) {
			if (method.getName().startsWith("set")) {
				String property = method.getName().substring(3, 4)
						.toLowerCase()
						+ method.getName().substring(4);
				String propertyClassName = method.getParameterTypes()[0]
						.getName();
				kvMap.put(property, propertyClassName);
			}
		}
		// 生成sql
		String tableName = toDbCase(clazz.getName());
		StringBuffer sb = new StringBuffer("CREATE TABLE " + tableName
				+ " (id INTEGER PRIMARY KEY,");
		for (String key : kvMap.keySet()) {
			if (key.equalsIgnoreCase("id")) {
				continue;
			}
			String columnName = toDbCase(key);
			String propertyClassName = kvMap.get(key);
			String dbTypeName = getDbTypeName(propertyClassName);
			sb.append(columnName + " " + dbTypeName + ",");
		}
		if (sb.charAt(sb.length() - 1) == ',')
			sb.delete(sb.length() - 1, sb.length());
		sb.append(");");
		String sql = sb.toString();
		// 执行sql
		Connection conn = getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		conn.setAutoCommit(true);
		ps.execute();
		conn.setAutoCommit(false);
		free(null, ps, conn);
	}

	/**
	 * 使用sql来建表
	 * 
	 * @param sql
	 * @throws SQLException
	 */
	public void createTable(String sql) throws SQLException {
		// 创建表
		Connection conn = getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		conn.setAutoCommit(true);
		ps.execute();
		conn.setAutoCommit(false);
		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 = toDbCase(clazz.getSimpleName());
		String sql = "DROP TABLE IF EXISTS " + tableName;
		execute(sql);
	}

	private String getDbTypeName(String propertyClassName) {
		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";
		if ("java.lang.String".equals(propertyClassName))
			return "VARCHAR";
		return "";
	}

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

}