dbcp 数据库连接池配置及对数据库的增、删、改、查

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();
		}
	}
}