BoneCP Connection Pooling Example
BoneCP Connection Pooling Example exaplains about how to create and configure a Connection pool using BoneCP Datasource
Creating and establishing a database connections are relatively very expensive because of establishing a network connection, initializng database session, authorization in the back end database etc.
Due to this issues, it is good practice to use a connection pool in your applications in order to increase the performance and scalability. By using Connection Pool, you can re-use already existing connections and prepared statements, so that you can avoid the cost of establishing the connection.
BoneCP is a fast, free, open-source, Java database connection pool (JDBC Pool) library. If you are familiar with C3P0 and DBCP then you already know what this means. For the rest, this is a library that will manage a database connection for you to get faster database access in your application
Reference -> http://jolbox.com/
Required Libraries
You need to download
Following jar must be in classpath
- bonecp-0.7.1.RELEASE.jar
- guava-16.0.1.jar
- slf4j-api-1.7.6.jar
- slf4j-jdk14-1.7.6.jar
- mysql-connector-java-5.1.28-bin.jar
Create Table Structure
CREATE TABLE `employee` ( `EMPLOYEEID` bigint(20) NOT NULL AUTO_INCREMENT, `EMPLOYEENAME` varchar(255) DEFAULT NULL, PRIMARY KEY (`EMPLOYEEID`) );
Project Structure
DataSource.java
import java.beans.PropertyVetoException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.SQLException;
import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;
public class DataSource {
private static DataSource datasource;
private BoneCP connectionPool;
private DataSource() throws IOException, SQLException, PropertyVetoException {
try {
// load the database driver (make sure this is in your classpath!)
Class.forName("com.mysql.jdbc.Driver");
} catch (Exception e) {
e.printStackTrace();
return;
}
try {
// setup the connection pool using BoneCP Configuration
BoneCPConfig config = new BoneCPConfig();
// jdbc url specific to your database, eg jdbc:mysql://127.0.0.1/yourdb
config.setJdbcUrl("jdbc:mysql://localhost/test");
config.setUsername("root");
config.setPassword("root");
config.setMinConnectionsPerPartition(5);
config.setMaxConnectionsPerPartition(10);
config.setPartitionCount(1);
// setup the connection pool
connectionPool = new BoneCP(config);
} catch (Exception e) {
e.printStackTrace();
return;
}
}
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.connectionPool.getConnection();
}
}
BoneCPDataSourceExample.java
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 BoneCPDataSourceExample {
public static void main(String[] args) throws PropertyVetoException, SQLException, IOException, ClassNotFoundException {
Connection connection = null;
Statement statement = null;
ResultSet resultSet = null;
try {
// fetch a connection
connection = DataSource.getInstance().getConnection();
if (connection != null) {
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();}
}
}
}
Output
employeeId: 1 employeename: Rockey employeeId: 2 employeename: Jose