dbcp 数据库连接池配置及对数据库的增、删、改、查
编程技术 / houtizong 发布于 3年前 181
package com.pro.lottery.util;import java.lang.reflect.Field;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.ResultSetMetaData;import java.sql.SQLException;import java.sql.Statement;import java.util.ArrayList;import java.util.List;import org.apache.commons.dbcp.BasicDataSource;import org.apache.commons.dbcp.DataSourceConnectionFactory;import com.pro.lottery.modle.UserTable;/*** * @description： 数据库连接池配置及存储过程调用 * @author Caixu * @dateTime 2014-11-27 * */public class DataSourceFactory {private static BasicDataSource dbcp;private static DataSourceConnectionFactory dscf;String className = "com.microsoft.sqlserver.jdbc.SQLServerDriver";String url = "jdbc:sqlserver://localhost:1433;database=PEpos";String userName = "sa";String password = "woaixua52110";/* * String className = "oracle.jdbc.driver.OracleDriver"; String url = * "jdbc:oracle:thin:@172.27.35.2:1521:orcl"; String userName = "scott"; * String password = "tiger"; */private DataSourceFactory() {dbcp = new BasicDataSource();dbcp.setDriverClassName(className);dbcp.setUrl(url);dbcp.setUsername(userName);dbcp.setPassword(password);// 最大获取连接数dbcp.setMaxActive(100);// 最大可用空闲连接数dbcp.setMaxIdle(10);dbcp.setMaxWait(6000);dscf = new DataSourceConnectionFactory(dbcp);}private static DataSourceFactory Pool;public synchronized static DataSourceFactory getInstance() {if (Pool == null) {Pool = new DataSourceFactory();}return Pool;}public Connection getConnection() {Connection con = null;try {con = dscf.createConnection();} catch (Exception e) {e.printStackTrace();}return con;}/** * 调用存储过程执行 insert update delete操作 * * @param procedureName * 存储过程名 * @param obj * 存储过程参数 * @return */public int update(String procedureName, Object[] obj) {Connection connection = null;CallableStatement cstmt = null;try {DataSourceFactory pool = DataSourceFactory.getInstance();connection = pool.getConnection();// 得到拼接的存储和参数String proStr = getProcedureStr(procedureName, obj);cstmt = connection.prepareCall(proStr);for (int i = 0; i < obj.length; i++) {cstmt.setObject(i + 1, obj[i]);}cstmt.execute();int count = cstmt.getUpdateCount();return count;} catch (Exception e) {e.printStackTrace();return -1;} finally {close(connection, cstmt, null);}}/** * 批量执行存储过程 如果出现异常则不会提交事务 * @param procedureNames * @param objs * @return */public int batchUpdateByProcedure(String[] procedureNames, List<Object[]> objs){Connection connection = null;CallableStatement cstmt = null;try {if(procedureNames.length != objs.size()){return -1;}DataSourceFactory pool = DataSourceFactory.getInstance();connection = pool.getConnection();connection.setAutoCommit(false);boolean flag = false;int resCount = 0;for(int j=0; j<objs.size(); j++){// 得到拼接的存储和参数String proStr = getProcedureStr(procedureNames[j], objs.get(j));cstmt = connection.prepareCall(proStr);for (int i = 0; i < objs.get(j).length; i++) {cstmt.setObject(i + 1, objs.get(j)[i]);}cstmt.execute();int count = cstmt.getUpdateCount();resCount = resCount + count;if(count == 0){flag = true;resCount = 0;break;}}//如果有一个失败 则回滚if(flag){connection.rollback();}connection.commit();return resCount;} catch (Exception e) {e.printStackTrace();return -1;}}/** * sql语句的批处理 * * @param sqlArray * @return */public int batchUpdateBySql(String[] sqlArray) {Connection connection = null;Statement st = null;try {DataSourceFactory pool = DataSourceFactory.getInstance();connection = pool.getConnection();st = connection.createStatement();for (String sql : sqlArray) {st.addBatch(sql);}} catch (Exception e) {e.printStackTrace();return -1;}// 取消自动提交try {//是否回滚boolean flag = false;connection.setAutoCommit(false);int[] count = st.executeBatch();int resCount = 0;for (int i : count) {resCount = resCount + i;if(i == 0){flag = true;resCount = 0;break;}}//如果有一个失败 则回滚if(flag){connection.rollback();}connection.commit();return resCount;} catch (Exception e) {e.printStackTrace();// 如果出错了，则应该把数据回滚try {connection.rollback();} catch (SQLException e1) {e1.printStackTrace();}return -1;}finally {close(connection, st, null);}}/** * 拼接程储过程 * @param procedureName * @param obj * @return */public static String getProcedureStr(String procedureName, Object[] obj) {StringBuffer proStr = new StringBuffer();proStr.append("{call " + procedureName + "(");for (Object objStr : obj) {proStr.append("?").append(",");}String temp = "";if (obj.length == 0) {temp = proStr.toString();} else {temp = proStr.substring(0, proStr.length() - 1);}temp = temp + ")}";return temp;}/** * 查询行记录 * * @param sql * @return */public long findCount(String sql) {long result = -1;Connection con = null;PreparedStatement ps = null;ResultSet rs = null;try {DataSourceFactory pool = DataSourceFactory.getInstance();con = pool.getConnection();ps = con.prepareStatement(sql);rs = ps.executeQuery();if (rs.next()) {result = rs.getLong(1);}} catch (Exception e) {e.printStackTrace();} finally {close(con, ps, rs);}return result;}/** * 执行返回泛型集合的SQL语句 * * @param cls * 泛型类型 * @param sql * 查询SQL语句 * @return 泛型集合 */public <T> List<T> getObjectListBySql(Class<T> cls, String sql) {List<T> list = new ArrayList<T>();Connection con = null;PreparedStatement ps = null;ResultSet rs = null;try {DataSourceFactory pool = DataSourceFactory.getInstance();con = pool.getConnection();ps = con.prepareStatement(sql);rs = ps.executeQuery();while (rs.next()) {T obj = executeResultSet(cls, rs);list.add(obj);}} catch (Exception e) {e.printStackTrace();} finally {close(con, ps, rs);}return list;}/** * 执行数据的SQL语句 此方法不用写实体类 * * @param cls * 泛型类型 * @param sql * 查询SQL语句 * @return 泛型集合 */public List<Object[]> getObjectListBySql_fields(String sql) {List<Object[]> list = new ArrayList<Object[]>();Connection con = null;PreparedStatement ps = null;ResultSet rs = null;String temp = sql.trim().substring("select".length(), sql.indexOf("from"));String[] fields = temp.split(",");try {DataSourceFactory pool = DataSourceFactory.getInstance();con = pool.getConnection();ps = con.prepareStatement(sql);rs = ps.executeQuery();while (rs.next()) {Object[] obj = executeResultSet(fields, rs);list.add(obj);}} catch (Exception e) {e.printStackTrace();} finally {close(con, ps, rs);}return list;}/** * 执行返回泛型类型对象的SQL语句 * * @param cls * 泛型类型 * @param sql * SQL语句 * @return 泛型类型对象 */public <T> T getObejectBySql(Class<T> cls, String sql) {T obj = null;Connection con = null;PreparedStatement ps = null;ResultSet rs = null;try {DataSourceFactory pool = DataSourceFactory.getInstance();con = pool.getConnection();ps = con.prepareStatement(sql);rs = ps.executeQuery();while (rs.next()) {obj = executeResultSet(cls, rs);break;}} catch (Exception e) {e.printStackTrace();} finally {close(con, ps, rs);}return obj;}/** * 将一条记录转成一个对象 * * @param cls * 泛型类型 * @param rs * ResultSet对象 * @return 泛型类型对象 * @throws InstantiationException * @throws IllegalAccessException * @throws SQLException */private <T> T executeResultSet(Class<T> cls, ResultSet rs)throws InstantiationException, IllegalAccessException, SQLException {T obj = cls.newInstance();ResultSetMetaData rsm = rs.getMetaData();int columnCount = rsm.getColumnCount();// Field[] fields = cls.getFields();Field[] fields = cls.getDeclaredFields();for (int i = 0; i < fields.length; i++) {Field field = fields[i];String fieldName = field.getName();String fieldType = field.getType().toString();for (int j = 1; j <= columnCount; j++) {String columnName = rsm.getColumnName(j);if (fieldName.equalsIgnoreCase(columnName)) {Object value = rs.getObject(j);//解决空指针问题if (value == null && ("int".equals(fieldType) || "long".equals(fieldType) || "double".equals(fieldType) || "float".equals(fieldType))) {value = 0;}field.setAccessible(true);field.set(obj, value);break;}}}return obj;}/** * 将一条记录转成一个数据 * @param cls * 泛型类型 * @param rs * ResultSet对象 * @return 泛型类型对象 * @throws InstantiationException * @throws IllegalAccessException * @throws SQLException */@SuppressWarnings("unused")private Object[] executeResultSet(String[] fields, ResultSet rs) throws SQLException {Object[] obj = new Object[fields.length];ResultSetMetaData rsm = rs.getMetaData();int columnCount = rsm.getColumnCount();// Field[] fields = cls.getFields();//Field[] fields = cls.getDeclaredFields();for (int i = 0; i < fields.length; i++) {String fieldName = fields[i].trim();for (int j = 1; j <= columnCount; j++) {String columnName = rsm.getColumnName(j);if (fieldName.equalsIgnoreCase(columnName)) {Object value = rs.getObject(j);obj[i] = value;break;}}}return obj;}/** * 执行返回泛型集合的存储过程 * * @param procedureName * 存储过程名 * @param obj * 存储过程参数 * @return */public <T> List<T> getObjListByProcedure(Class<T> cls, String procedureName,Object[] object) {List<T> list = new ArrayList<T>();Connection con = null;CallableStatement cstmt = null;ResultSet rs = null;try {DataSourceFactory pool = DataSourceFactory.getInstance();con = pool.getConnection();// 得到拼接的存储和参数String proStr = getProcedureStr(procedureName, object);cstmt = con.prepareCall(proStr);for (int i = 0; i < object.length; i++) {cstmt.setString(i + 1, (String) object[i]);}rs = cstmt.executeQuery();while (rs.next()) {T obj = executeResultSet(cls, rs);list.add(obj);}return list;} catch (Exception e) {e.printStackTrace();return null;} finally {close(con, cstmt, rs);}}/** * 关闭JDBC对象，释放资源。 * * @param con * 连接对象 * @param ps * 命令对象 * @param rs * 结果集对象 */private static void close(Connection con, Statement ps, ResultSet rs) {try {if (rs != null) {rs.close();rs = null;}if (ps != null) {ps.close();ps = null;}if (con != null) {con.close();con = null;}} catch (SQLException e) {e.printStackTrace();}}/** * @param args */public static void main(String[] args) {Statement sm = null;ResultSet rs = null;try {/* * DataSourceFactory pool = DataSourceFactory.getPool(); Connection * connection = pool.getConnection(); System.out.println("dfad"); sm * = connection.createStatement(); rs = * sm.executeQuery("select * from lotteryInfo"); while (rs.next()) { * System.out.println(rs.getString(1)); * System.out.println(rs.getString(2)); * System.out.println(rs.getString(4)); * System.out.println(rs.getString(5)); } *//* * List<ReqLottery> list = DataSourceFactory.getListObject( * ReqLottery.class, "select * from lotteryInfo"); for (ReqLottery * reqLottery : list) { * System.out.println(reqLottery.getLotteryDate()); * System.out.println(reqLottery.getLotteryId()); * System.out.println(reqLottery.getLotteryIssue()); * System.out.println(reqLottery.getLotteryNum()); * System.out.println(reqLottery.getLotteryType()); } *//* * long count = * DataSourceFactory.findCount("select count(*) from lotteryinfo"); * System.out.println(count); *//* * List<ReqLottery> list = DataSourceFactory.getObjList( * ReqLottery.class, "LOTTERYINFO_FINDLOTTERYINFO_FIND", new * Object[]{"1001"}); for (ReqLottery reqLottery : list) { * System.out.println(reqLottery.getLotteryDate()); * System.out.println(reqLottery.getLotteryId()); * System.out.println(reqLottery.getLotteryIssue()); * System.out.println(reqLottery.getLotteryNum()); * System.out.println(reqLottery.getLotteryType1()); } *//* * List<UserTable> list = * DataSourceFactory.getObjList(UserTable.class, * "select * from UserTable"); for (UserTable u : list) { * System.out.println(u.getId()); System.out.println(u.getAge()); * System.out.println(u.getName()); System.out.println(u.getSex()); * } *//*String[] sqlArray = {"update dept set deptCount = 20 where deptId = 1","update UserTable set age = 20" };int count = DataSourceFactory.batchUpdateBySql(sqlArray);System.out.println("成功了多少条：" + count);*//*String[] proName = {"TEST_BATCH_1","TEST_BATCH_2"};List<Object[]> objs = new ArrayList<Object[]>();objs.add(new Object[]{1});objs.add(new Object[]{5});int count = DataSourceFactory.batchUpdateByProcedure(proName, objs);System.out.println("成功了多少条：" + count);*/String[] fields = {"id", "name", "sex", "age"};List<Object[]> lists = DataSourceFactory.getInstance().getObjectListBySql_fields("select id, name, sex, age from UserTable");for (Object[] objects : lists) {for (Object object : objects) {System.out.print(object+" ");}System.out.println();}} catch (Exception e) {e.printStackTrace();}}}
上一篇：lua自定义log
请勿发布不友善或者负能量的内容。与人为善，比聪明更重要！
技术博客集 - 网站简介：
前后端技术：
后端基于Hyperf2.1框架开发,前端使用Bootstrap可视化布局系统生成
网站主要作用：
1.编程技术分享及讨论交流，内置聊天系统;
2.测试交流框架问题，比如：Hyperf、Laravel、TP、beego;
3.本站数据是基于大数据采集等爬虫技术为基础助力分享知识，如有侵权请发邮件到站长邮箱，站长会尽快处理;
4.站长邮箱：[email protected];
文章归档
文章标签
友情链接
