Apache Commons DBCP example

寫 java 時遇到一個問題,在 DriverManager.getConnection() too slow 問題。會花掉5~6秒才能取得 connection,workaround 就是只要慢一次就好了,後面的sql 不要慢到。

Jar 檔下載:Download Apache Commons DBCP
http://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi

Download Apache Commons Pool
https://commons.apache.org/proper/commons-pool/download_pool.cgi

 

附上使用範例


主程式:

package com.dataSource.dbcp;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DBCPDataSourceExample  {

    public static void main(String[] args) throws PropertyVetoException, SQLException, IOException {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DataSource.getInstance().getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from employee");
             while (resultSet.next()) {
                 System.out.println("employeeid: " + resultSet.getString("employeeid"));
                 System.out.println("employeename: " + resultSet.getString("employeename"));
             }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (resultSet != null) try { resultSet.close(); } catch (SQLException e) {e.printStackTrace();}
            if (statement != null) try { statement.close(); } catch (SQLException e) {e.printStackTrace();}
            if (connection != null) try { connection.close(); } catch (SQLException e) {e.printStackTrace();}
        }

    }

}

副程式:


package com.dataSource.dbcp;

import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbcp.BasicDataSource;

public class DataSource {

    private static DataSource     datasource;
    private BasicDataSource ds;

    private DataSource() throws IOException, SQLException, PropertyVetoException {
        ds = new BasicDataSource();
        ds.setDriverClassName("com.mysql.jdbc.Driver");
        ds.setUsername("root");
        ds.setPassword("root");
        ds.setUrl("jdbc:mysql://localhost/test");
       
     // the settings below are optional -- dbcp can work with defaults
        ds.setMinIdle(5);
        ds.setMaxIdle(20);
        ds.setMaxOpenPreparedStatements(180);

    }

    public static DataSource getInstance() throws IOException, SQLException, PropertyVetoException {
        if (datasource == null) {
            datasource = new DataSource();
            return datasource;
        } else {
            return datasource;
        }
    }

    public Connection getConnection() throws SQLException {
        return this.ds.getConnection();
    }

}

 

附註:

void setMinIdle(int minIdle)

Sets the minimum number of idle connections in the pool.
void setMaxIdle(int maxIdle)

Sets the maximum number of connections that can remain idle in the pool.
void setMaxOpenPreparedStatements(int maxOpenStatements)

Sets the value of the maxOpenPreparedStatements property.
void setSoftMinEvictableIdleTimeMillis(long softMinEvictableIdleTimeMillis)

Sets the minimum amount of time a connection may sit idle in the pool before it is eligible for eviction by the idle object evictor, with the extra condition that at least “minIdle” connections remain in the pool.

相關文章

寫留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *