DBCP 資料連線池的配置和使用

DBCP(DataBase connection pool),資料庫連線池。是 apache 上的一個 java 連線池專案，也是 tomcat 使用的連線池元件。

單獨使用dbcp需要3個包：common-dbcp.jar,common-pool.jar,common-collections.jar

由於建立資料庫連線是一個非常耗時耗資源的行為，所以通過連線池預先同資料庫建立一些連線，放在記憶體中，應用程式需要建立資料庫連線時直接到連線池中申請一個就行，用

完後再放回去。

獲取資料庫連線的類：DbcpConnection.java

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Properties;

import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSource;
public class DbcpConnection {
	private static DataSource dataSource;
	private static Connection connection;
	
	public static void initDataSource(){
		FileInputStream is = null;
		Properties properties = new Properties();
		
		String driverClassName = null;
		String url = null;
		String username = null;
		String password = null;
		
		int initialSize = 0;
		int minIdle = 0;
		int maxIdle = 0;
		int maxWait = 0;
		int maxActive = 0;
		
		try {
			String path = System.getProperty("user.dir")+"\\src\\com\\xiami\\db\\connection\\";
			is = new FileInputStream(path+"dbcp.properties");
			properties.load(is);
			
			driverClassName = properties.getProperty("dbcp.driverClassName");
			url = properties.getProperty("dbcp.url");
			username = properties.getProperty("dbcp.username");
			password = properties.getProperty("dbcp.password");
			initialSize = Integer.parseInt((properties.getProperty("dbcp.initialSize").trim()));
			minIdle = Integer.parseInt((properties.getProperty("dbcp.minIdle")).trim());
			maxIdle = Integer.parseInt((properties.getProperty("dbcp.maxIdle")).trim());
			maxWait = Integer.parseInt((properties.getProperty("dbcp.maxWait")).trim());
			maxActive = Integer.parseInt((properties.getProperty("dbcp.maxActive")).trim());
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException ioe){
			ioe.printStackTrace();
		}finally{
			try {
				is.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		BasicDataSource bds = new BasicDataSource();
		
		bds.setUrl(url);
		bds.setDriverClassName(driverClassName);
		bds.setUsername(username);
		bds.setPassword(password);
		bds.setInitialSize(initialSize);
		bds.setMaxActive(maxActive);
		bds.setMinIdle(minIdle);
		bds.setMaxIdle(maxIdle);
		bds.setMaxWait(maxWait);
		
		dataSource = bds;
	}
	
	public static Connection  getConnection() throws  SQLException {
        if (dataSource == null) {   
        	initDataSource();   
        }   
        Connection conn = null;   
        if (dataSource != null) {   
            conn = dataSource.getConnection();   
        }   
        return conn;   
	}
}

連線資料庫資訊的配置檔案：dbcp.properties

dbcp.driverClassName=com.ibm.db2.jcc.DB2Driver
dbcp.url=jdbc\:db2\://192.168.3.11\:50000/DBNAME
dbcp.username=db2admin
dbcp.password=password
dbcp.initialSize=30  
dbcp.minIdle=10 
dbcp.maxIdle=10  
dbcp.maxWait=1000 
dbcp.maxActive=30 

在Service層使用的時候：ZhiDianJieDu.java (業務邏輯處理的類)

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.xiami.db.connection.DbcpConnection;

public class ZhiDianJieDu {
	DbcpConnection dbcpcon = new DbcpConnection();
	Connection conn = null;
	PreparedStatement pst = null;
	ResultSet rs = null;
	String ZLJGBM = null;
	
	
	//通過A機構編碼獲得B機構程式碼
	public void getZLJGBMFromDRJGBM(String DRJGBM){
		String sql = "select ZLJGBM from T_CONTRAST_ZL_TO_NEUSOFT where DRJGBM = '"+DRJGBM+"'";
		try {
			conn = dbcpcon.getConnection();
			pst = conn.prepareStatement(sql);
			rs = pst.executeQuery();
			while(rs.next()){
				ZLJGBM = rs.getString("ZLJGBM");
			}
			System.out.println(ZLJGBM);
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}