ORIGIN

JDBC Database Connection Pool

JDBC 4 mins751 words

This article is note about learning Connection Pool.

Connection Pool

Connection pooling means that connections are reused rather than created each time a connection is requested. To facilitate connection reuse, a memory cache of database connections, called a connection pool, is maintained by a connection pooling module as a layer on top of any standard JDBC driver product.

Some open source Connection Pool

  • DBCP: offered by Apache, faster than c3p0, but has some bugs
  • C3P0: offered by open source organization. relatively slow, but with good stability. Advised by hibernate.
  • Proxool: an open project under sourceforge, can monitor the status of connection pool, but not so stable compared to c3p0
  • BoneCP: fast speed
  • Druid: offered by Alibaba, combine the best of BDCP, C3P0, Proxool, but speed is not sure to faster than BoneCP

Implements of Connection Pools

C3P0

  1. download the source on sourceforge add \lib\c3p0-x.x.x.x.jar to /lib folder
  2. then build path for it.

Method 1

  1. get c3p0 database connection pool

    1
    2
    3
    4
    5
    ComboPooledDataSource cpds = new ComboPooledDataSource();
    cpds.setDriverClass("com.mysql.jdbc.Driver"); // loads the jdbc driver
    cpds.setJdbcUrl("jdbc:mysql://localhost:3306/testdb");
    cpds.setUser("dbuser");
    cpds.setPassword("dbpassword");
  2. by setting related parameters, helps to manage the connection pool

1
cpds.setInitialPoolSize(10);
  1. return connection

    1
    2
    Connection conn = cpds.getConnection();
    return conn;

Method 2 - xml config

  1. create a new c3p0-config.xml file in /src folder

  2. write the xml flie

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    <?xml version="1.0" encoding="UTF-8"?>
    <c3p0-config>

    <named-config name="intergalactoApp">
    <!-- four base information for getting a connection -->
    <property name="driverClass">com.mysql.jdbc.Driver</property>
    <property name="jdbcUrl ">jdbc:mysql://localhost:3306/test</property>
    <property name="user">root</property>
    <property name="password">123abc</property>

    <!-- basic properties to manage connection pool -->
    <property name="acquireIncrement">5</property>
    <property name="initialPoolSize">10</property>
    <property name="minPoolSize">10</property>
    <property name="maxPoolSize">100</property>
    <property name="maxStatements">50</property>
    <property name="maxStatementsPerConnection">2</property>

    </named-config>
    </c3p0-config>
    • acquireIncrement: when the connection is not enough, the number of connection that c3p0 will apply for to database server.
    • initialPoolSize: number of connection when initialize
    • minPoolSize: minimum number of connection the pool will maintain
    • maxPoolSize: maximum number of connection the pool will maintain
    • maxStatements: maximum number of Statement the pool will maintain
    • maxStatementsPerConnection: maximum number of Statement each connection can use
  3. call the xml file

    1
    2
    ComboPooledDataSource cpds = new ComboPooledDataSource("intergalactoApp");
    Connection conn = cpds.getConnection();

    but actually, it’s the new ComboPooledDataSource("intergalactoApp") should be write out side function wrapper. To avoid creating a pool each time getting a connection.

    1
    2
    3
    4
    5
    private static ComboPooledDataSource cpds = new ComboPooledDataSource("intergalactoApp");
    public Connection testGetConnection2() throws Exception {
    Connection conn = cpds.getConnection();
    return conn;
    }

DBCP

Download the bin file here https://commons.apache.org/proper/commons-dbcp/download_dbcp.cgi

Add commons-dbcp2-x.x.x.jar to file path

Also need to download the commons-pool2 and do the same thing. https://commons.apache.org/proper/commons-pool/download_pool.cgi

Method 1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
public Connection testGetConnection() throws Exception {
// create DBCP connection pool
BasicDataSource source = new BasicDataSource();

// set basic information
source.setDriverClassName("com.mysql.jdbc.Driver");
source.setUrl("jdbc://mysql///test");
source.setUsername("root");
source.setPassword("1122");

// settting about manage pool
source.setInitialSize(10);
source.setMaxTotal(10);

Connection conn = source.getConnection();
return conn;
}

Method 2 - config file

  1. creating a properties file

    1
    2
    3
    4
    5
    6
    username=root
    password=123
    url=jdbc:mysql://localhost:3306/test
    driverClassName=com.mysql.jdbc.Driver

    initialSize=10
  2. load file

    1
    2
    3
    4
    5
    6
    // method 1
    InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
    // method 2
    FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));

    pros.load(is);

whole code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
private static DataSource source;
static {
try {
Properties pros = new Properties();
// InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("dbcp.properties");
FileInputStream is = new FileInputStream(new File("src/dbcp.properties"));
pros.load(is);
source = BasicDataSourceFactory.createDataSource(pros);
} catch (Exception e) {
e.printStackTrace();
}
}
public Connection testGetConnection2() throws Exception {
// create DBCP connection pool
Connection conn = source.getConnection();
return conn;
}
TOP
COMMENT
  • ABOUT
  • |
o_oyao
  The Jigsaw puzzle is incomplete with even one missing piece. And I want to be the last piece to make the puzzle complete.
Like my post?
Default QR Code
made with ❤️ by o_oyao
©o_oyao 2019-2024

|