In Previous few Posts We had learnt about following Database Connection Pools:
MysqlDataSource example
DBCP Connection Pooling Example
C3P0 JDBC connection pool Example
In this post we will learn how to establish Jdbc connection pool using BoneCP
Note:You have to add few dependencies binaries in your project class path those dependencies you can see in the blow project lib folder
Create database.properties file in src folder as shown in the above project structure and make entry of below properties.
1 2 3 4 |
driver.class.name=com.mysql.jdbc.Driver db.url=jdbc:mysql://localhost:3306/jdbcpooldb db.username = root db.password=root |
SQL Query to create employee_table in MySQL (jdbcpooldb schema)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE DATABASE IF NOT EXISTS jdbcpooldb; CREATE TABLE IF NOT EXISTS `employee_table` ( `employee_id` int(11) NOT NULL auto_increment, `employee_name` varchar(60) NOT NULL, `email` varchar(45) NOT NULL, `salary` double default NULL, `date_of_joining` datetime default NULL, `bonus` decimal(10,0) default NULL, PRIMARY KEY (`employee_id`) ) ; INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) INSERT INTO `employee_table` (`employee_name`,`email`,`salary`,`date_of_joining`,`bonus`) |
Once you execute above db script your database schema(jdbcpooldb) will be created and employee_table will be created with three rows as below.
Utility class which is responsible to get JDBC connection object using BoneCP DataSource connection pool With MYSQL Database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 |
package com.infotech.util; import java.io.FileInputStream; import java.io.IOException; import java.sql.Connection; import java.sql.SQLException; import java.util.Properties; import com.jolbox.bonecp.BoneCP; import com.jolbox.bonecp.BoneCPConfig; /* * @author KK JavaTutorials * Utility class which is responsible to get JDBC connection object using * BoneCP DataSource connection pool With MYSQL Database. */ public class DBUtil { private static final String DB_USERNAME="db.username"; private static final String DB_PASSWORD="db.password"; private static final String DB_URL ="db.url"; private static Properties properties = null; private static BoneCP dataSource; static{ try { properties = new Properties(); properties.load(new FileInputStream("src/database.properties")); BoneCPConfig boneCPConfig = new BoneCPConfig(); boneCPConfig.setJdbcUrl(properties.getProperty(DB_URL)); boneCPConfig.setUsername(properties.getProperty(DB_USERNAME)); boneCPConfig.setPassword(properties.getProperty(DB_PASSWORD)); boneCPConfig.setMinConnectionsPerPartition(5); boneCPConfig.setMaxConnectionsPerPartition(100); boneCPConfig.setPartitionCount(4); boneCPConfig.setLazyInit(true); dataSource = new BoneCP(boneCPConfig); } catch (IOException | SQLException e) { e.printStackTrace(); } } public static Connection getConnection(){ try { return dataSource.getConnection(); } catch (SQLException e) { e.printStackTrace(); } return null; } } |
This Client class makes use of DBUtil.getDataSource() method to take connection from BoneCP connection pool With MYSQL Database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
package com.infotech.client; import java.math.BigDecimal; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import com.infotech.util.DBUtil; /* * @author KK JavaTutorials * This class makes use of DBUtil.getDataSource() method to take connection from * BoneCP connection pool With MYSQL Database. */ public class ClientTest { public static void main(String[] args) throws SQLException { try (Connection connection = DBUtil.getConnection(); Statement st = connection.createStatement();) { String SQL = "SELECT *FROM employee_table"; ResultSet rs = st.executeQuery(SQL); while (rs.next()) { int empId = rs.getInt("employee_id"); String eName = rs.getString("employee_name"); String email = rs.getString("email"); Double salary = rs.getDouble("salary"); BigDecimal bonus = rs.getBigDecimal("bonus"); System.out.println(empId + "\t" + eName + "\t" + salary + "\t" + email + "\t" + bonus); } } catch (Exception e) { e.printStackTrace(); } } } |
After running ClientTest.java program you will look below output on eclipse console:
1 2 3 |
1 Martin 71000.0 martin.cs2017@yahoo.com 400 2 Paul 91000.0 paul.cs2017@gmail.com 800 3 Sam 76000.0 sam.cs2014@gmail.com 300 |
Above eclipse console output shows we are able to read rows from MySQL database that means we are successfully taken database connection using BoneCP connection pool
That’s all about this topic BoneCP connection pool example.
You May Also Like:
Why we need a database connection pooling
MysqlDataSource example
DBCP Connection Pooling Example
C3P0 JDBC connection pool Example
Hikari Connection Pooling Example
If you have any feedback or suggestion please feel free to drop in blow comment box.