dbcp连接池连db2

1.新建项目test

2.在webroot/web-inf/lib下面，加入dbcp驱动包：commons-dbcp-1.3.jar，commons-pool-1.6.jar，org.apache.commons.collections_3.2.0.v201005080500.jar

3.加入db2的jdbc驱动包：db2java.jar

4.创建配置文件：config.properties

driverClassName=COM.ibm.db2.jdbc.app.DB2Driver
url=jdbc:db2:dbctrldb
username=db2admin
password=db2admin
initialSize=10
maxActive=50
maxIdle=20
minIdle=5
maxWait=60000
connectionProperties=useUnicode=true;characterEncoding=gbk
defaultAutoCommit=true
defaultTransactionIsolation=READ_UNCOMMITTED

5.创建管理类：

package com.liang.stock.db;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDriver;
import org.apache.commons.pool.ObjectPool;
import org.apache.commons.pool.impl.GenericObjectPool;
  
public class PoolManager {
    private static String
            driver="COM.ibm.db2.jdbc.app.DB2Driver",//驱动
            url = "jdbc:db2:dbctrldb",//URL
            Name="db2admin",//用户名
            Password="db2admin";//密码
 
    private static Class driverClass = null;
    private static ObjectPool connectionPool = null;
 
    public PoolManager(){
    }
    private static void loadProperties(){
        try {
            java.io.InputStream stream = new java.io.FileInputStream ("config.properties");
            java.util.Properties props = new java.util.Properties();
            props.load(stream);
 
            driver = props.getProperty("ORACLE_DRIVER");
            url = props.getProperty("ORACLE_URL");
            Name = props.getProperty("ORACLE_LOGIN_NAME");
            Password = props.getProperty("ORACLE_LOGIN_PASSWORD");
        } catch (FileNotFoundException e) {
            System.out.println("读取配置文件异常");
        } catch(IOException ie){
            System.out.println("读取配置文件时IO异常");
        }
    }
   
    private static synchronized void initDataSource() {
        if (driverClass == null) {
            try {
                driverClass = Class.forName(driver);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
    }
  
    public static void StartPool() {
        loadProperties();
        initDataSource();
        if (connectionPool != null) {
            ShutdownPool();
        }
        try {
            connectionPool = new GenericObjectPool(null);
            ConnectionFactory connectionFactory = new
                      DriverManagerConnectionFactory(url, Name, Password);
            PoolableConnectionFactory poolableConnectionFactory = new
                     PoolableConnectionFactory(connectionFactory, connectionPool, null, null,
                     false, true);
            Class.forName("org.apache.commons.dbcp.PoolingDriver");
            PoolingDriver driver = (PoolingDriver) DriverManager.getDriver
                     ("jdbc:apache:commons:dbcp:");
            driver.registerPool("dbpool", connectionPool);
            System.out.println("装配连接池OK");
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
  
    public static void ShutdownPool() {
        try {
            PoolingDriver driver = (PoolingDriver) DriverManager.getDriver("jdbc:apache:commons:dbcp:");
            driver.closePool("dbpool");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
   
    public static Connection getConnection() {
        Connection conn = null;
        if(connectionPool == null)
            StartPool();
        try {
            conn = DriverManager.getConnection ("jdbc:apache:commons:dbcp:dbpool");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }
 
    public static Connection getConnection(String name){
        return getConnection();
    }
   
    public static void freeConnection(Connection conn){
        if(conn != null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    public static void freeConnection (String name,Connection con){
                freeConnection(con);
    } 
    public static void main(String[] args) {
        try {
            Connection conn = PoolManager.getConnection();
            if(conn != null){
                Statement statement = conn.createStatement();
                ResultSet rs = statement.executeQuery("select * from db.tets");
                int c = rs.getMetaData().getColumnCount();
                while(rs.next()){
                    System.out.println();
                    for(int i=1;i<=c;i++){
                        System.out.print(rs.getObject(i));
                    }
                }
                rs.close();
            }
            PoolManager.freeConnection(conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

5.测试成功。

需要注意的是db2的驱动程序有2个：COM.ibm.db2.jdbc.net.DB2Driver和COM.ibm.db2.jdbc.app.DB2Driver，这2个对应的url写法是不一样的，如果写错会报错：

java.sql.SQLException: No suitable driver

1、app的时候

1. Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");  
2. DriverManager.getConnection("jdbc:db2:TEST","db2admin", "db2admin"); 

2、net的时候

1. Class.forName("COM.ibm.db2.jdbc.net.DB2Driver");  
2. DriverManager.getConnection("jdbc:db2://localhost:5000/TEST","db2admin", "db2admin");