jdbc模仿hibernat增删改(用到了数据库连接池，用了批量提交)

package com.jvsoft.common;import java.lang.reflect.Field;import java.lang.reflect.Method;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.HashMap;import java.util.List;import java.util.Map;import java.util.Vector;import javassist.expr.Instanceof;import org.apache.poi.ss.formula.functions.T;import sun.jdbc.odbc.ee.ConnectionPool;public class HzDbConnection {// 初始化private HzDbConnection() {init();}private String DRIVER = null;private String URL = null;private String DB_USERNAME = null;private String DB_PASSWORD = null;private Connection conn = getConnection();private PreparedStatement pstmt = null;private ResultSet rs = null;private Vector<Connection> pool;private int poolSize = 1;public static HzDbConnection instance = null;// 获取连接private Connection getConnection() {if (pool != null) {if (pool.size() == 0) {init();}conn = pool.get(0);pool.remove(0);} else {init();conn = pool.get(0);pool.remove(0);}return conn;}// 向数据池增加连接private void addConn() {Connection conn = null;for (int i = 0; i < poolSize; i++) {try {Class.forName(DRIVER);conn = DriverManager.getConnection(URL, DB_USERNAME,DB_PASSWORD);pool.add(conn);} catch (Exception e) {e.printStackTrace();}}}// 初始化public void init() {loadConfig();pool = new Vector<Connection>(poolSize);addConn();}// 读取配置文件private void loadConfig() {DRIVER = EnvHz.getInstance().getProperty("hzDriver");URL = EnvHz.getInstance().getProperty("hzDriverUrl");DB_USERNAME = EnvHz.getInstance().getProperty("hzUser");DB_PASSWORD = EnvHz.getInstance().getProperty("hzPassWord");poolSize = Integer.parseInt(EnvHz.getInstance().getProperty("hzPoolSize"));}// 获取构造函数public static HzDbConnection getInstance() {if (instance == null) {return new HzDbConnection();}return instance;}/** * 根据表名和ID删除表全部记录 *  * @param tableName * @return */public int delDbByTableKey(String tableName, String paramykey, Long id) {int count = 0;String sql = "delete CRM_HZ_INST." + tableName + " where " + paramykey+ "=" + id;try {conn = getConnection();pstmt = conn.prepareStatement(sql);System.out.println(sql);count = pstmt.executeUpdate();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return count;}/** * 根据表名删除表全部记录 *  * @param tableName * @return */public int delDbByTableName(String tableName) {int count = 0;String sql = "delete CRM_HZ_INST." + tableName;try {conn = getConnection();pstmt = conn.prepareStatement(sql);count = pstmt.executeUpdate();} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}return count;}/** * 将JavaBean中有值的属性写到数据库相应的字段中，要求构建的Bean的属性名与数据库的字段名相同 * JavaBean中的set和get方法要求全是对象，如int应写成Integer *  * @param tableName *            数据库的表名 * @param beanObject *            已经有数据的Bean的对象 * @return 真表示插入成功 */@SuppressWarnings("unchecked")public int writeListBeanToDB(String tableName, List listbeanObject)throws SQLException {int count = 0;conn = getConnection();conn.setAutoCommit(false);List<Map<Integer, Object>> map = new ArrayList<Map<Integer, Object>>();Object beanObject = listbeanObject.get(0);int[] resultnum=null;// 得到它的Bean的类型if (beanObject == null) {throw new SQLException("第二个Object参数不能为空");}Class beanClassName = beanObject.getClass();// 转成相应的类型beanClassName.cast(beanObject);// bean的方法名字Method beanAttrMethod[] = beanClassName.getDeclaredMethods();// 构建查询语句String sqlStr = "insert into " + tableName;String finalFieldName = "";String fieldValues = "";try {for (int i = 0; i < beanAttrMethod.length; i++) {// 只要得到所有的方法String methodName = beanAttrMethod[i].getName();// 因为没有原始的boolean类型，所以不用考虑is开头的，只考虑get方法if (methodName.startsWith("get")) {// 先调用一次要返回是否为null，即这一项没有赋值Object invokeResult = beanAttrMethod[i].invoke(beanObject);// 暂时去掉了非null判断，因为可能每次新增的记录的字段为null数不一样// if (invokeResult != null) {String fieldName = methodName.substring(3,methodName.length());finalFieldName = finalFieldName + "," + fieldName;fieldValues += "?,";// }}}/* * 构建一条insert语句 形如： insert into employee * (Id,Name,Age,Salary,Sex,Jointime,Birthday) values * (?,?,?,?,?,?,?); */sqlStr = sqlStr + " ("+ finalFieldName.substring(1, finalFieldName.length())+ ") values ("+ fieldValues.substring(0, fieldValues.length() - 1) + ")";for (Object ob : listbeanObject) {Class beanClass = beanObject.getClass();// 转成相应的类型beanClass.cast(ob);// bean的方法名字Method beanMethod[] = beanClass.getDeclaredMethods();int index = 1;Map<Integer, Object> valMap = new HashMap<Integer, Object>();for (int i = 0; i < beanAttrMethod.length; i++) {// 只要得到所有的get和is开头的方法就可以了，而且顺序与前面的匹配String methodName = beanAttrMethod[i].getName();if (methodName.startsWith("get")) {Object invokeResult = beanAttrMethod[i].invoke(beanObject);// if (invokeResult != null) {// 设置每个?号的值valMap.put(index++,beanAttrMethod[i].invoke(beanObject));// pstmt.setObject(index++,// beanAttrMethod[i].invoke(beanObject));// }}}map.add(valMap);}pstmt = conn.prepareStatement(sqlStr);for (Map<Integer, Object> m : map) {for (Integer i : m.keySet()) {pstmt.setObject(i.intValue(), m.get(i));}pstmt.addBatch();}System.out.println(sqlStr);resultnum = pstmt.executeBatch();conn.commit();} catch (Exception ex) {ex.printStackTrace();throw new SQLException(ex);} finally {closeConnection(conn, pstmt, null);}return resultnum.length;}/** * 将JavaBean中有值的属性修改到数据库相应的字段中，要求构建的Bean的属性名与数据库的字段名相同 * JavaBean中的set和get方法要求全是对象，如int应写成Integer *  * @param tableName *            数据库的表名 * @param beanObject *            已经有数据的Bean的对象 * @return 真表示修改成功 */@SuppressWarnings("unchecked")public int updateListBeanToDB(String tableName, List listbeanObject,String paramyKey, Long id) throws SQLException {int count = 0;Object beanObject = listbeanObject.get(0);List<Map<Integer, Object>> map = new ArrayList<Map<Integer, Object>>();conn = getConnection();int[] resultnum=null;// 得到它的Bean的类型if (beanObject == null) {throw new SQLException("第二个Object参数不能为空");}Class beanClassName = beanObject.getClass();// 转成相应的类型beanClassName.cast(beanObject);// 得到所有的属性，判断其是否是对象类型，不是原始类型Field fieldBean[] = beanClassName.getDeclaredFields();for (int i = 0; i < fieldBean.length; i++) {// 如果对象类型名的首字母是小写，则表示它是原始的数据类型if (Character.isLowerCase(fieldBean[i].getType().getSimpleName().charAt(0))) {throw new SQLException(beanObject.getClass().getSimpleName()+ "中的属性：" + fieldBean[i].getName() + "为原始数据类型"+ fieldBean[i].getType().getSimpleName()+ "。调用本方法要求Java Bean的所有属性用对象类型");}}// bean的方法名字Method beanAttrMethod[] = beanClassName.getDeclaredMethods();// 构建查询语句String sqlStr = "update CRM_HZ_INST." + tableName + " set ";String finalFieldName = "";String fieldValues = "";try {for (int i = 0; i < beanAttrMethod.length; i++) {// 只要得到所有的方法String methodName = beanAttrMethod[i].getName();// 因为没有原始的boolean类型，所以不用考虑is开头的，只考虑get方法if (methodName.startsWith("get")) {// 先调用一次要返回是否为null，即这一项没有赋值Object invokeResult = beanAttrMethod[i].invoke(beanObject);// if (invokeResult != null) {String fieldName = methodName.substring(3,methodName.length());finalFieldName = finalFieldName + fieldName + "=?,";// }}}/* * 构建一条insert语句 形如： insert into employee * (Id,Name,Age,Salary,Sex,Jointime,Birthday) values * (?,?,?,?,?,?,?); */finalFieldName = finalFieldName.substring(0,finalFieldName.length() - 1);sqlStr = sqlStr + finalFieldName + " where " + paramyKey + "=" + id;System.out.println(sqlStr);for (Object ob : listbeanObject) {Class beanClass = beanObject.getClass();// 转成相应的类型beanClass.cast(ob);// bean的方法名字Method beanMethod[] = beanClass.getDeclaredMethods();int index = 1;Map<Integer, Object> valMap = new HashMap<Integer, Object>();for (int i = 0; i < beanAttrMethod.length; i++) {// 只要得到所有的get和is开头的方法就可以了，而且顺序与前面的匹配String methodName = beanAttrMethod[i].getName();if (methodName.startsWith("get")) {Object invokeResult = beanAttrMethod[i].invoke(beanObject);// if (invokeResult != null) {// 设置每个?号的值valMap.put(index++,beanAttrMethod[i].invoke(beanObject));// pstmt.setObject(index++,// beanAttrMethod[i].invoke(beanObject));// }}}map.add(valMap);}pstmt = conn.prepareStatement(sqlStr);for (Map<Integer, Object> m : map) {for (Integer i : m.keySet()) {pstmt.setObject(i.intValue(), m.get(i));}pstmt.addBatch();}resultnum=pstmt.executeBatch();conn.commit();} catch (Exception ex) {ex.printStackTrace();throw new SQLException(ex);} finally {closeConnection(conn, pstmt, null);}return resultnum.length;}/** * 将JavaBean中有值的属性修改到数据库相应的字段中，要求构建的Bean的属性名与数据库的字段名相同 * JavaBean中的set和get方法要求全是对象，如int应写成Integer *  * @param tableName *            数据库的表名 * @param beanObject *            已经有数据的Bean的对象 * @return 真表示修改成功 */@SuppressWarnings("unchecked")public int updateBeanToDB(String tableName, Object beanObject,String paramyKey, Long id) throws SQLException {conn = getConnection();int count = 0;// 得到它的Bean的类型if (beanObject == null) {throw new SQLException("第二个Object参数不能为空");}Class beanClassName = beanObject.getClass();// 转成相应的类型beanClassName.cast(beanObject);// 得到所有的属性，判断其是否是对象类型，不是原始类型Field fieldBean[] = beanClassName.getDeclaredFields();for (int i = 0; i < fieldBean.length; i++) {// 如果对象类型名的首字母是小写，则表示它是原始的数据类型if (Character.isLowerCase(fieldBean[i].getType().getSimpleName().charAt(0))) {throw new SQLException(beanObject.getClass().getSimpleName()+ "中的属性：" + fieldBean[i].getName() + "为原始数据类型"+ fieldBean[i].getType().getSimpleName()+ "。调用本方法要求Java Bean的所有属性用对象类型");}}// bean的方法名字Method beanAttrMethod[] = beanClassName.getDeclaredMethods();// 构建查询语句String sqlStr = "update CRM_HZ_INST." + tableName + " set ";String finalFieldName = "";String fieldValues = "";try {for (int i = 0; i < beanAttrMethod.length; i++) {// 只要得到所有的方法String methodName = beanAttrMethod[i].getName();// 因为没有原始的boolean类型，所以不用考虑is开头的，只考虑get方法if (methodName.startsWith("get")) {// 先调用一次要返回是否为null，即这一项没有赋值Object invokeResult = beanAttrMethod[i].invoke(beanObject);// if (invokeResult != null) {String fieldName = methodName.substring(3,methodName.length());finalFieldName = finalFieldName + fieldName + "=?,";// }}}/* * 构建一条insert语句 形如： insert into employee * (Id,Name,Age,Salary,Sex,Jointime,Birthday) values * (?,?,?,?,?,?,?); */finalFieldName = finalFieldName.substring(0,finalFieldName.length() - 1);sqlStr = sqlStr + finalFieldName + " where " + paramyKey + "=" + id;pstmt = conn.prepareStatement(sqlStr);int index = 1;for (int i = 0; i < beanAttrMethod.length; i++) {// 只要得到所有的get和is开头的方法就可以了，而且顺序与前面的匹配String methodName = beanAttrMethod[i].getName();if (methodName.startsWith("get")) {Object invokeResult = beanAttrMethod[i].invoke(beanObject);// if (invokeResult != null) {// 设置每个?号的值pstmt.setObject(index++,beanAttrMethod[i].invoke(beanObject));// }}}System.out.println(sqlStr);count = pstmt.executeUpdate();} catch (Exception ex) {ex.printStackTrace();throw new SQLException(ex);} finally {closeConnection(conn, pstmt, null);}return count;}/** * 将JavaBean中有值的属性写到数据库相应的字段中，要求构建的Bean的属性名与数据库的字段名相同 * JavaBean中的set和get方法要求全是对象，如int应写成Integer *  * @param tableName *            数据库的表名 * @param beanObject *            已经有数据的Bean的对象 * @return 真表示插入成功 */@SuppressWarnings("unchecked")public int writeBeanToDB(String tableName, Object beanObject)throws SQLException {conn = getConnection();int count = 0;// 得到它的Bean的类型if (beanObject == null) {throw new SQLException("第二个Object参数不能为空");}Class beanClassName = beanObject.getClass();// 转成相应的类型beanClassName.cast(beanObject);// 得到所有的属性，判断其是否是对象类型，不是原始类型Field fieldBean[] = beanClassName.getDeclaredFields();for (int i = 0; i < fieldBean.length; i++) {// 如果对象类型名的首字母是小写，则表示它是原始的数据类型if (Character.isLowerCase(fieldBean[i].getType().getSimpleName().charAt(0))) {throw new SQLException(beanObject.getClass().getSimpleName()+ "中的属性：" + fieldBean[i].getName() + "为原始数据类型"+ fieldBean[i].getType().getSimpleName()+ "。调用本方法要求Java Bean的所有属性用对象类型");}}// bean的方法名字Method beanAttrMethod[] = beanClassName.getDeclaredMethods();// 构建查询语句String sqlStr = "insert into CRM_HZ_INST." + tableName;String finalFieldName = "";String fieldValues = "";try {for (int i = 0; i < beanAttrMethod.length; i++) {// 只要得到所有的方法String methodName = beanAttrMethod[i].getName();// 因为没有原始的boolean类型，所以不用考虑is开头的，只考虑get方法if (methodName.startsWith("get")) {// 先调用一次要返回是否为null，即这一项没有赋值Object invokeResult = beanAttrMethod[i].invoke(beanObject);if (invokeResult != null) {String fieldName = methodName.substring(3,methodName.length());finalFieldName = finalFieldName + "," + fieldName;fieldValues += "?,";}}}/* * 构建一条insert语句 形如： insert into employee * (Id,Name,Age,Salary,Sex,Jointime,Birthday) values * (?,?,?,?,?,?,?); */sqlStr = sqlStr + " ("+ finalFieldName.substring(1, finalFieldName.length())+ ") values ("+ fieldValues.substring(0, fieldValues.length() - 1) + ")";pstmt = conn.prepareStatement(sqlStr);int index = 1;for (int i = 0; i < beanAttrMethod.length; i++) {// 只要得到所有的get和is开头的方法就可以了，而且顺序与前面的匹配String methodName = beanAttrMethod[i].getName();if (methodName.startsWith("get")) {Object invokeResult = beanAttrMethod[i].invoke(beanObject);if (invokeResult != null) {// 设置每个?号的值pstmt.setObject(index++,beanAttrMethod[i].invoke(beanObject));}}}System.out.println(sqlStr);count = pstmt.executeUpdate();} catch (Exception ex) {ex.printStackTrace();throw new SQLException(ex);} finally {closeConnection(conn, pstmt, null);}return count;}/** * 关闭连接 *  * @param conn *            数据库连接 * @param ps *            PreparedStatement对象 * @param rs *            结果集 */public static void closeConnection(Object ...object) {try {for(Object o:object){if((o instanceof ResultSet)&&(o!=null)){ResultSet rs=(ResultSet)o;rs.close();System.out.println("关闭:ResultSet");}else if((o instanceof PreparedStatement)&&(o!=null)){PreparedStatement pstm=(PreparedStatement)o;pstm.close();System.out.println("关闭:PreparedStatement");}else if((o instanceof Statement)&&(o!=null)){Statement st=(Statement)o;st.close();System.out.println("关闭:Statement");}else if((o instanceof Connection)&&(o!=null)){Connection conn=(Connection)o;conn.close();System.out.println("关闭:Connection");}}} catch (SQLException se) {se.printStackTrace();}}}