使用dbcp构建数据库连接池

数据库连接池

搭建连接池需要三个jar包：

1. org.apache.commons.dbcp.jar
2. org.apache.commons.pool.jar
3. mysql-connector-java-5.1.23-bin.jar

如果运行报错ClassNotFound，少哪个就补哪个jar包进去

---

首先，写个连接池类

public class MysqlConnection {
    private static DataSource dataSource;

    private static String Driver = "";
    private static String Url = "";
    private static String UserName = "";
    private static String Password = "";

    private static int InitialSize = 1;//初始化连接池大小
    private static int MaxIdle = 10;//最大空闲连接
    private static int MinIdle = 1;//最小空闲连接
    private static int MaxActive = 20;//最大连接
    private static int MaxWait = 6000;//等待时间

    private static final String MySQLConnectMessagePath = "dbcp.properties";
    private static Properties properties = new Properties();

    public MysqlConnection() throws URISyntaxException, IOException{
        InitializeDataSource();
    }

    /**
     * 初始化数据池连接
     * @throws IOException */
    private void InitializeDataSource() throws URISyntaxException, IOException {
        getProperties();
        Driver = properties.getProperty("Driver");
        Url = properties.getProperty("Url");
        UserName = properties.getProperty("UserName");
        Password = properties.getProperty("Password");
        InitialSize = Integer.parseInt(properties.getProperty("InitialSize"));
        MaxIdle = Integer.parseInt(properties.getProperty("MaxIdle"));
        MinIdle = Integer.parseInt(properties.getProperty("MinIdle"));
        MaxActive = Integer.parseInt(properties.getProperty("MaxActive"));
        MaxWait = Integer.parseInt(properties.getProperty("MaxWait"));
        BasicDataSource bs = new BasicDataSource();
        bs.setDriverClassName(Driver);
        bs.setUrl(Url);
        bs.setUsername(UserName);
        bs.setPassword(Password);
        bs.setInitialSize(InitialSize);
        bs.setMaxIdle(MaxIdle);
        bs.setMinIdle(MinIdle);
        bs.setMaxActive(MaxActive);
        bs.setMaxWait(MaxWait);
        dataSource = bs;        
    }

    /**
     * 读取配置文件
     * @throws IOException */
    private void getProperties() throws URISyntaxException, IOException {
        String path = this.getClass().getClassLoader().getResource("").toURI().getPath();
        FileInputStream fs = new FileInputStream(new File(path+MySQLConnectMessagePath));
        try {
            properties.load(fs);
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
            fs.close();
        }
    }

    /**
    * 该函数只在多个DAO类下有用，DAO类初始化可以直接实例化本类，但多个DAO会造成无法连接。这就意味着，你可以在自启动的Servlet中初始化连接池，然后其他DAO类直接调用该函数，避免连接冲突*/
    public static DataSource getDataSource() {
        return dataSource;
    }

/**
* 此方法可以不要*/
    public static void setDataSource(DataSource dataSource) {
        MysqlConnection.dataSource = dataSource;
    }

    /**
     * 获取数据库连接(释放可直接调用Connection.close()方法)
     * @return Connection*/
    public Connection getConnection(){
        Connection conn = null;
        try{
            conn = dataSource.getConnection();
        }catch(Exception e){
            e.printStackTrace();
        }
        return conn;
    }
}

配置dbcp.properties,放在src目录下

#必须根据自己情况配置
Driver = com.mysql.jdbc.Driver
Url = jdbc:mysql://example.com/database_name
UserName = name
Password = password
#可根据自己情况配置
InitialSize = 10
MinIdle = 10
MaxIdle = 10
MaxActive = 200
MaxWait = 1000

初始化连接池

1、第一种方法：只有一个DAO类

//代码手写的。。
public class DAO(){
    private static MysqlConnection mysqlconnetion = null;

    public DAO(){
        try{
            mysqlconnetion = new MysqlConnetion();
        }catch(Exception e){
            e.printStackTrace();
        }
    }

    //后面直接写方法
    //public void method(){}
    //public *** method(){}
    //示例：
    public boolean execute(String sql){
        Connection conn = mysqlconnetion.getConnection();
        Statement st = null;
        boolean flag = false;
        try {
            st = conn.createStatement();
            st.execute(sql);
            flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }
}

2、 多个DAO类共享一个连接池

//创建一个DatabaseConnectionServlet，加个注释@WebServlet(loadOnStartup=1,urlPatterns="/DatabaseConnectionServlet")
//loadOnStartup是必须的。。一定不要忘了
//然后servlet构造函数可以这么写
public DatabaseConnectionServlet() {
        try {
            MysqlConnection mysqlConnection = new MysqlConnection();
            System.out.println("数据库连接池初始化完成");
        } catch (URISyntaxException | IOException e) {
            e.printStackTrace();
        }
    }

---

//DAO类可以这么写
public class DAO {
    private static DataSource dataSource = null;

    /**
     * 构造函数，获取连接*/
    public DAO(){
        dataSource = MysqlConnection.getDataSource();//servlet已经初始化好了
    }

    /**
     * 获取连接*/
    public Connection getConnection(){
        Connection conn = null;
        try{
            conn = dataSource.getConnection();
        }catch(Exception e){
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 无参数
     * 如果执行语句不抛出异常返回值就是true
     * @return boolean*/
    public boolean execute(String sql){
        Connection conn = getConnection();
        Statement st = null;
        boolean flag = false;
        try {
            st = conn.createStatement();
            st.execute(sql);
            flag = true;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return flag;
    }

最后，可以写个语句测试一波，这里不赘述了。。

---

如果有问题，可以留言，我看到了一定回复的