dbcp 数据库连接池配置及对数据库的增、删、改、查_JAVA_编程开发_程序员俱乐部

class="java">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 getPool() {
		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 static int update(String procedureName, Object[] obj) {
		Connection connection = null;
		CallableStatement cstmt = null;
		try {
			DataSourceFactory pool = DataSourceFactory.getPool();
			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);
		}
	}
	
	
	/*public static int batchUpdateByProcedure(String[] procedureNames, List<Object[]> objs){
		Connection connection = null;
		CallableStatement cstmt = null;
		try {
			if(procedureNames.length != objs.size()){
				return -1;
			}
			DataSourceFactory pool = DataSourceFactory.getPool();
			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;
		}
	} */
	
	/**
	 * sql语句的批处理
	 * 
	 * @param sqlArray
	 * @return
	 */
	public static int batchUpdateBySql(String[] sqlArray) {
		Connection connection = null;
		Statement st = null;
		try {
			DataSourceFactory pool = DataSourceFactory.getPool();
			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 static long findCount(String sql) {
		long result = -1;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			DataSourceFactory pool = DataSourceFactory.getPool();
			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 static <T> List<T> getObjListBySql(Class<T> cls, String sql) {
		List<T> list = new ArrayList<T>();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			DataSourceFactory pool = DataSourceFactory.getPool();
			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 static <T> T getObejectBySql(Class<T> cls, String sql) {
		T obj = null;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			DataSourceFactory pool = DataSourceFactory.getPool();
			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 static <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 procedureName
	 *            存储过程名
	 * @param obj
	 *            存储过程参数
	 * @return
	 */
	public static <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.getPool();
			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);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}

?