创建SqlHelper、SqlHelperCP

创建SqlHelper
package JDBC;
import java.sql.DriverManager;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;

public class SqlHelper {
	static String driver;
	static String url;
	static String user;
	static String password;
	// 初始化静态属性
	static {
		try {
			InputStream in = SqlHelper.class.getClassLoader().getResourceAsStream("db.properties");
			Properties cfg = new Properties();
			cfg.load(in);
			driver = cfg.getProperty("jdbc.driver");
			url = cfg.getProperty("jdbc.url");
			user = cfg.getProperty("jdbc.user");
			password = cfg.getProperty("jdbc.password");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection() throws Exception {
		try {
			Connection conn = DriverManager.getConnection(url, user, password);
			return conn;
		}catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException();
		}
	}
	
	public static void close(Connection conn) {
		try {
			if(conn != null) {
				conn.close();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void rollback(Connection conn) {
		try {
			if(conn != null) {
				conn.rollback();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

创建SqlHelperCP
package ConnectionPool;

import java.io.InputStream;
import java.sql.Connection;
import java.util.Properties;
import org.apache.commons.dbcp.BasicDataSource;

public class SqlHelperCP {
	private static String driver;
	private static String url;
	private static String user;
	private static String password;
	private static int initialSize;
	private static int maxActive;
	private static int maxIdle;
	private static int minIdle;
	private static BasicDataSource bds;
	
	static {
		try {
			bds = new BasicDataSource();
			InputStream in = SqlHelperCP.class.getClassLoader().getResourceAsStream("db.properties");
			Properties cfg = new Properties();
			cfg.load(in);
			driver = cfg.getProperty("jdbc.driver");
			url = cfg.getProperty("jdbc.url");
			user = cfg.getProperty("jdbc.user");
			password = cfg.getProperty("jdbc.password");
			initialSize = Integer.parseInt(cfg.getProperty("jdbc.initialSize"));
			maxActive = Integer.parseInt(cfg.getProperty("jdbc.maxActive"));
			maxIdle = Integer.parseInt(cfg.getProperty("jdbc.maxIdle"));
			minIdle = Integer.parseInt(cfg.getProperty("jdbc.minIdle"));
			in.close();
			
			bds.setDriverClassName(driver);
			bds.setUrl(url);
			bds.setUsername(user);
			bds.setPassword(password);
			bds.setInitialSize(initialSize);
			bds.setMaxActive(maxActive);
			bds.setMaxIdle(maxIdle);
			bds.setMinIdle(minIdle);
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		try {
				Connection conn = bds.getConnection();  // 有空闲连接则进行连接，否则等待连接
				return conn;
		}catch(Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}
	}
	
	public static void close(Connection conn) {
		try {
			if(conn != null) {
				conn.close();  // 归还给连接池
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void rollback(Connection conn) {
		try {
			if(conn != null) {
				conn.rollback();
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}