[オリジナル]共通の対象向けクエリのdao(二)

33549 ワード
java dao データベース jdbc orm
もっと読む
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    :
" + 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 unwrap(Class 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 
	 * @param clazz
	 * @param rs
	 * @return
	 * @throws SQLException
	 */
	public static  T rowMapping(Class 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;

/**
 * 
             

 * 
     java                 

 * @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;

/**
 * 

 *   dao ，       ：
 * 

 * 

 * 1.     
 * 

 * 

 * 2.      
 * 

 * 

 * 3.          java pojo         /p>
 * 

 * 4.      “id”，    Integer  ，                 
 * 

 * 

 * 5.       
 * 

 * 
 * @author kong
 * 
 */
public class Dao {

	private final DataSource dataSource = new DataSource();

	/**
	 *     
	 * 
	 * @param 
	 * @param clazz
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public  T query(Class 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 
	 * @param clazz
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public  T query(Class clazz, String sql) throws SQLException {
		return query(clazz, sql, null);
	}

	/**
	 *       
	 * 
	 * @param 
	 * @param clazz
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public  List queryList(Class 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 list = new ArrayList();
		T t = null;
		while ((t = Mapper.rowMapping(clazz, rs)) != null) {
			list.add(t);
		}
		dataSource.free(null, ps, conn);
		return list;
	}

	/**
	 *       （    ）
	 * 
	 * @param 
	 * @param clazz
	 * @param sql
	 * @return
	 * @throws SQLException
	 */
	public  List queryList(Class 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 list = new ArrayList();
		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 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 result = new ArrayList();
		List list = new ArrayList();
		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 queryForArrayList(String sql) throws SQLException {
		return queryForArrayList(sql, null);
	}

	/**
	 *      map
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public Map 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 map = new HashMap();
		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 queryForMap(String sql) throws SQLException {
		return queryForMap(sql, null);
	}

	/**
	 *      map   
	 * 
	 * @param sql
	 * @param values
	 * @return
	 * @throws SQLException
	 */
	public List> 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> list = new ArrayList>();
		Map map = new HashMap();
		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> 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 
	 * @param clazz
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public  T get(Class 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 kvMap = new HashMap();
		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 kvMap = new HashMap();
		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> kvMap = new HashMap>();
		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;
	}

}
木の鎖が分断する
Java比較器Compratorインターフェース