In this post we’ll see how to configure connection pooling using HikariCP in your JDBC application.
Maven dependency for HikariCP
If you are using Maven then you can add the following dependency in pom.xml which works for Java 8 to Java 11.
<dependency> <groupId>com.zaxxer</groupId> <artifactId>HikariCP</artifactId> <version>3.4.5</version> </dependency>
You can also download jars from here- https://search.maven.org/search?q=com.zaxxer.hikaricp
HikariCP Connection pooling Java example
In the example DB used is MySQL it connects to knpcode schema and table is EMPLOYEE with columns as id, FIRST_NAME, LAST_NAME and DEPARTMENT.
Best way to create a HIkariCP DataSource is to instantiate an instance of HikariConfig class and provide the properties for connecting to DB and for connection pool. Then pass that config instance to create HikariDataSource object.
DB related configurations that you need to set are Driver class, URL, user name and password.
Some of the connection pool related properties that you can set are as follows-
- autoCommit- This property controls the default auto-commit behavior of connections returned from the pool. Default is true.
- connectionTimeout- This property controls the maximum number of milliseconds that a client will wait for a connection from the pool. Default is 30 seconds.
- idleTimeout- This property controls the maximum amount of time that a connection is allowed to sit idle in the pool. Default is 10 minutes.
- maxLifetime- This property controls the maximum lifetime of a connection in the pool. Default is 30 minutes.
- maximumPoolSize- This property controls the maximum size that the pool is allowed to reach, including both idle and in-use connections. Default is 10.
For the example DB credentials and connection pool related configuration properties are kept in a properties file (db.properties).
DB.DRIVER_CLASS=com.mysql.cj.jdbc.Driver DB.DB_URL=jdbc:mysql://localhost:3306/knpcode DB.DB_USER=root DB.DB_PASSWORD=admin DB.MAX_POOL_SIZE=5
Following class is used to create a HikariDataSource by setting DB properties and connection pool properties which are read from the properties file.
import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;
import javax.sql.DataSource;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
public class DSCreator {
private static HikariDataSource ds;
static {
try {
Properties properties = new Properties();
// Loading properties file from classpath
InputStream inputStream = DSCreator.class
.getClassLoader()
.getResourceAsStream("db.properties");
if(inputStream == null){
throw new IOException("File not found");
}
properties.load(inputStream);
HikariConfig config = new HikariConfig();
// This property is optional now
config.setDriverClassName(properties.getProperty("DB.DRIVER_CLASS"));
config.setJdbcUrl(properties.getProperty("DB.DB_URL"));
config.setUsername(properties.getProperty("DB.DB_USER"));
config.setPassword(properties.getProperty("DB.DB_PASSWORD"));
config.setMaximumPoolSize(Integer.parseInt(properties.getProperty("DB.MAX_POOL_SIZE")));
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
// Create DataSource
ds = new HikariDataSource(config);
}catch(IOException e) {
e.printStackTrace();
}
}
public static DataSource getDataSource() {
return ds;
}
}
Test class that creates a connection using the returned DataSource object and queries the DB.
public class DSDemo {
public static void main(String[] args) {
DSDemo dsDemo = new DSDemo();
dsDemo.displayEmployeeById(16);
}
private void displayEmployeeById(int id){
Connection connection = null;
String selectSQL = "SELECT * FROM EMPLOYEE WHERE id = ?";
PreparedStatement prepStmt = null;
try {
DataSource ds = DSCreator.getDataSource();
connection = ds.getConnection();
prepStmt = connection.prepareStatement(selectSQL);
prepStmt.setInt(1, id);
ResultSet rs = prepStmt.executeQuery();
while(rs.next()){
System.out.println("id: " + rs.getInt("id"));
System.out.println("First Name: " + rs.getString("FIRST_NAME"));
System.out.println("Last Name: " + rs.getString("LAST_NAME"));
System.out.println("Department: " + rs.getString("DEPARTMENT"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
if(connection != null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
That's all for the topic HikariCP Connection Pooling Java Example. If something is missing or you have something to share about the topic please write a comment.
You may also like
No comments:
Post a Comment