How to use Spring JDBC Template with BoneCP Connection Pooling


Friends the following udemy course created by me;

      Click here to attend Spring Framework 4.x and certification course with a discount

Spring Framework and Core Spring Certification Udemy course with discount
Spring Framework and Core Spring Certification Udemy course with a discount coupon

In this post, I am going to give some information about configuring Spring Framework’s JDBC Template.

In traditional Java programming, programmers have to open connection while getting access for databases.

During this phase, programmers open connections, close connections, resultsets and statements for every data access from the database server.

However, if you use Spring Framework’s JDBC Template you don’t need to these steps and leave the work to Spring.

After including necessary jars (libraries), the Spring framework will do the work for you. And if you create a reference inside code for the JDBC Template, Spring will get a connection for you, close it also. You can also provide one time mapping for the column names inside your code and use it anywhere in your application.

By doing that, you will be available to re-use the code snippets inside your application thus you will have a clean and manageable code, a better design for traditional Java applications.

So using Spring based applications, you will only need to define connection parameters, provide SQL statements, declare parameters, set parameters and iterate over selected data sets.

What is BoneCP?

BoneCP is a Java Connection Pooling application used by including its jars and dependencies inside your project’s lib folder.

If you use BoneCP or any other connection pools similar to BoneCP, you won’t require to make any configuration on Application Server and the connection pooling framework will open many connections and store them inside a queue and make them available to use by database access requests.

This approach in Java programming will provide a good performance while accessing database servers because in every request a connection (opening new one) requires a high I/O operation on Servers.

The following application will try to give more information about integrating a connection pool (you can use a normal database access also), BoneCP, with Spring’s JDBC Template.

In this example, I am using MySQL database with its example database WORLD. (Country table in this database will be used as a Base Table)

The following operations will be done on Country table by using SpringJDBCTemplate.

Here is the Country class;

package com.tunatore.springjdbc;

/**
 *
 * @author tunatore
 */
public class Country {

 private String code;
 private String name;
 private String continent;
 private String region;

/**
 * @return the code
 */
 public String getCode() {
 return code;
 }

/**
 * @param code the code to set
 */
 public void setCode(String code) {
 this.code = code;
 }

/**
 * @return the name
 */
 public String getName() {
 return name;
 }

/**
 * @param name the name to set
 */
 public void setName(String name) {
 this.name = name;
 }

/**
 * @return the continent
 */
 public String getContinent() {
 return continent;
 }

/**
 * @param continent the continent to set
 */
 public void setContinent(String continent) {
 this.continent = continent;
 }

/**
 * @return the region
 */
 public String getRegion() {
 return region;
 }

/**
 * @param region the region to set
 */
 public void setRegion(String region) {
 this.region = region;
 }

}

CountryDAO interface

</pre>
package com.tunatore.springjdbc;
import java.util.List;

/**
 *
 * @author tunatore
 */
public interface CountryDAO {
 public boolean insertCountry(Country c);
 public boolean insertCountries(List<Country> countries);
 public boolean deleteCountryByCode(String code);
 public boolean updateCountry(Country u);
 public Country getCountryByCode(String code);
 public List<Country> getAllCountries();
}

JDBCCountryDAO class

package com.tunatore.springjdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

/**
 *
 * @author tunatore
 */
public class JDBCCountryDAO implements CountryDAO {

private JdbcTemplate jdbcTemplate;

public void setDataSource(DataSource dataSource) {
 this.jdbcTemplate = new JdbcTemplate(dataSource);
 }
 /**
 * @return the jdbcTemplate
 */
 public JdbcTemplate getJdbcTemplate() {
 return jdbcTemplate;
 }
 @Override
 public boolean insertCountry(final Country c) {
 boolean result = false;
 final String SQL = "INSERT INTO WORLD.COUNTRY (CODE,NAME,CONTINENT,REGION) "
 + " VALUES(?,?,?,?)";
 //KeyHolder keyHolder = new GeneratedKeyHolder();
 int row= this.jdbcTemplate.update(new PreparedStatementCreator(){
 @Override
 public PreparedStatement createPreparedStatement(Connection connection)
 throws SQLException {
 PreparedStatement ps =connection.prepareStatement(SQL);
 ps.setString(1, c.getCode());
 ps.setString(2, c.getName());
 ps.setString(3, c.getContinent());
 ps.setString(4, c.getRegion());
 return ps;
 }
 });
 if (row > 0)
 result=true;
 //if you want to return the generated auto increment key use keyHolder
 /*,keyHolder);
 *return keyHolder.getKey().intValue();
 */
 return result;
 }

 @Override
 public boolean insertCountries(final List<Country> countries) {
 boolean result = true; //you should catch the exception here in Production applications
 final String SQL = "INSERT INTO WORLD.COUNTRY (CODE,NAME,CONTINENT,REGION) "
 + " VALUES(?,?,?,?)";
 int[] updateCounts = this.jdbcTemplate.batchUpdate(SQL, new BatchPreparedStatementSetter() {
 @Override
 public void setValues(PreparedStatement ps, int i) throws SQLException {
 Country c = countries.get(i);
 ps.setString(1, c.getCode());
 ps.setString(2, c.getName());
 ps.setString(3, c.getContinent());
 ps.setString(4, c.getRegion());
 }
 @Override
 public int getBatchSize() {
 return countries.size();
 }
 });
 return result;
 }

 @Override
 public boolean deleteCountryByCode(final String code) {
 boolean result = false;
 final String SQL = "DELETE FROM WORLD.COUNTRY"
 + " WHERE CODE=?";
 int row = jdbcTemplate.update(new PreparedStatementCreator() {
 public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
 PreparedStatement ps = connection.prepareStatement(SQL);
 ps.setString(1, code);
 return ps;
 }
 });
 if (row>0)
 result = true;
 return result;
 }

@Override
 public boolean updateCountry(final Country c) {
 boolean result = false;
 final String SQL = "UPDATE WORLD.COUNTRY SET NAME=?,"
 + "CONTINENT=?,REGION=? WHERE CODE=?";
 int row = jdbcTemplate.update(new PreparedStatementCreator() {
 public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
 PreparedStatement ps = connection.prepareStatement(SQL);
 ps.setString(1, c.getName());
 ps.setString(2, c.getContinent());
 ps.setString(3, c.getRegion());
 ps.setString(4, c.getCode());
 return ps;
 }
 });
 if (row>0)
 result = true;
 return result;
 }

 @Override
 public Country getCountryByCode(String code) {
 String SQL = "SELECT * FROM WORLD.COUNTRY"
 + " WHERE code = ?";
 try {
 return (Country) this.jdbcTemplate.queryForObject(SQL,
 new CountryMapper(),new Object[]{code});
 } catch (EmptyResultDataAccessException e) {
 //this will return a null if the query cant find any countried with
 //code given by the user
 return null;
 }
 }

@Override
 public List<Country> getAllCountries() {
 String SQL = "SELECT * FROM WORLD.COUNTRY";
 List<Country> countries = this.jdbcTemplate.query(SQL,
 new RowMapper<Country>() {
 //using anonymous inner class RowMapper here
 @Override
 public Country mapRow(ResultSet rs, int rowNum) throws SQLException {
 Country c = new Country();
 c.setCode(rs.getString("code"));
 c.setName(rs.getString("name"));
 c.setContinent(rs.getString("continent"));
 c.setRegion(rs.getString("region"));
 return c;
 }
 });
 return countries;
 }

 //You should use this mapper
 //If you have dublicate RowMappers
 //this is a static Inner Class implementing RowMapper
 private static final class CountryMapper implements RowMapper<Country> {
 public Country mapRow(ResultSet rs, int rowNum) throws SQLException {
 Country c = new Country();
 c.setCode(rs.getString("code"));
 c.setName(rs.getString("name"));
 c.setContinent(rs.getString("continent"));
 c.setRegion(rs.getString("region"));
 return c;
 }
 }
}

MainProgram class

package com.tunatore.springjdbc;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/**
 *
 * @author tunatore
 * Spring JDBC Template Example
 * CRUD application fro creating a Country in the World
 * By using this software, you don't require to open and close standard
 * JDBC connection, SPRING framework will do the work for you
 */
public class MainProgram {

public static void main(String[] args) {

 try {
 //you need to load the beans.xml file from the Classpath
 //Spring will initialize all the beans defined in this file
 //Database information will be also get from this file and jdbc.properties file
 //which is also referenced by beans.xml file
 //Spring will create and manage all beans defined in this file
 ApplicationContext appContext =
 new ClassPathXmlApplicationContext("com/tunatore/springjdbc/beans.xml");
 //If you want to get the implementation of JDBCCountry interface
 //getBean will get the countryDAO from Spring Application
 JDBCCountryDAO countryDAO = (JDBCCountryDAO)appContext.getBean("countryDAO");

 //now here we can use all the methods defined in JDBCCountryDAO interface
 //getting all countries
 List<Country> countries = countryDAO.getAllCountries();
 for (Iterator<Country> it = countries.iterator(); it.hasNext();) {
 Country country = it.next();
 System.out.println("Country name ==> Code: " + country.getCode());
 System.out.println("Name: " + country.getName());
 System.out.println("Continent: " + country.getContinent());
 System.out.println("Region: " + country.getRegion());
 }

 //getting a country by code
 Country USA = countryDAO.getCountryByCode("USA");
 System.out.println("Country name ==> Code: " + USA.getCode());
 System.out.println("Name: " + USA.getName());
 System.out.println("Continent: " + USA.getContinent());
 System.out.println("Region: " + USA.getRegion());

 //update a country by code
 USA.setName("United States of Java");
 countryDAO.updateCountry(USA);
 USA = countryDAO.getCountryByCode("USA");
 System.out.println("After update Name: " + USA.getName());

 //inserting a new country
 Country newCountry = new Country();
 newCountry.setCode("NNN");
 newCountry.setName("NEW COUNTRY");
 newCountry.setContinent("Asia");
 newCountry.setRegion("NEW REGION");
 boolean isInserted = countryDAO.insertCountry(newCountry);
 System.out.println("Country isInserted ==> " + isInserted);

 //inserting countries
 Country newCountry1 = new Country();
 newCountry1.setCode("NN1");
 newCountry1.setName("NEW COUNTRY");
 newCountry1.setContinent("Asia");
 newCountry1.setRegion("NEW REGION");
 Country newCountry2 = new Country();
 newCountry2.setCode("NN2");
 newCountry2.setName("NEW COUNTRY");
 newCountry2.setContinent("Asia");
 newCountry2.setRegion("NEW REGION");

 List<Country> newCountriesList = new ArrayList<Country>();
 newCountriesList.add(newCountry1);
 newCountriesList.add(newCountry2);
 boolean isCountriesInserted = countryDAO.insertCountries(newCountriesList);
 System.out.println("Countries isInserted ==> " + isCountriesInserted);

 //deleting a new country
 boolean isDeleted = countryDAO.deleteCountryByCode("NN1");
 System.out.println("Country NN1 isDeleted ==> " + isDeleted);

 } catch (Exception ex) {
 Logger.getLogger(MainProgram.class.getName()).log(Level.SEVERE, null, ex);
 }

 }
}

beans.xml file

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
 xmlns:context="http://www.springframework.org/schema/context"
 xsi:schemaLocation="
 http://www.springframework.org/schema/beans
 http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
 http://www.springframework.org/schema/context
 http://www.springframework.org/schema/context/spring-context-3.0.xsd">

 <bean id="countryDAO" class="com.tunatore.springjdbc.JDBCCountryDAO">
 <property name="dataSource" ref="dataSource"/>
 </bean>

 <bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
 <property name="driverClass" value="${jdbc.driverClass}"/>
 <property name="jdbcUrl" value="${jdbc.url}"/>
 <property name="username" value="${jdbc.username}"/>
 <property name="password" value="${jdbc.password}"/>
 <property name="idleConnectionTestPeriodInMinutes" value="60"/>
 <property name="idleMaxAgeInMinutes" value="240"/>
 <property name="maxConnectionsPerPartition" value="30"/>
 <property name="minConnectionsPerPartition" value="10"/>
 <property name="partitionCount" value="3"/>
 <property name="acquireIncrement" value="5"/>
 <property name="statementsCacheSize" value="100"/>
 <property name="releaseHelperThreads" value="3"/>
 </bean>

 <!-- if you don't want to use connection pooling you should comment out the following lines
 <bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
 <property name="driverClassName" value="${jdbc.driverClass}"/>
 <property name="url" value="${jdbc.url}"/>
 <property name="username" value="${jdbc.username}"/>
 <property name="password" value="${jdbc.password}"/>
 </bean>
 -->
 <context:property-placeholder location="com/tunatore/springjdbc/jdbc.properties"/>

</beans>

jdbc.properties file

#this file store all the neccessary information
#related to database
#in this example MySQL database is used
jdbc.driverClass=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1/world
jdbc.username=root
jdbc.password=admin

log4j.xml file content

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j='http://jakarta.apache.org/log4j/'>
<appender name="console" class="org.apache.log4j.ConsoleAppender">
 <layout class="org.apache.log4j.PatternLayout">
 <param name="ConversionPattern" value="%-4r [%t] %-5p %c %x - %m%n" />
 </layout>
</appender>
<root>
 <level value="error" />
 <appender-ref ref="console" />
</root>
</log4j:configuration>

Note that if you change the level value to debug, you will see many debug logs inside the console. It is better to set it to debug if you want to get more information regarding the Spring application initialization.

And on the following screenshot you can see the required libraries for the program. Most of the libraries are required for Spring Framework and BoneCP.

And if you run the MainProgram  you will see an output like the following one;

….
….
….

Country name ==> Code: VNM
Name: Vietnam
Continent: Asia
Region: Southeast Asia
Country name ==> Code: VUT
Name: Vanuatu
Continent: Oceania
Region: Melanesia
Country name ==> Code: WLF
Name: Wallis and Futuna
Continent: Oceania
Region: Polynesia
Country name ==> Code: WSM
Name: Samoa
Continent: Oceania
Region: Polynesia
Country name ==> Code: YEM
Name: Yemen
Continent: Asia
Region: Middle East
Country name ==> Code: YUG
Name: Yugoslavia
Continent: Europe
Region: Southern Europe
Country name ==> Code: ZAF
Name: South Africa
Continent: Africa
Region: Southern Africa
Country name ==> Code: ZMB
Name: Zambia
Continent: Africa
Region: Eastern Africa
Country name ==> Code: ZWE
Name: Zimbabwe
Continent: Africa
Region: Eastern Africa
Country name ==> Code: USA
Name: United States of Java
Continent: North America
Region: North America
After update Name: United States of Java
Country isInserted ==> true
Countries isInserted ==> true
Country NN1 isDeleted ==> true

Download Source Code for SpringJDBCTemplate with BoneCP

How to use BoneCP Java Database Connection pool (JDBC Pool) library


Hi Friends, the following course is created by me;

      Click here to attend Spring Framework 4.x and certification course with a discount

Spring Framework and Core Spring Certification Udemy course with discount
Spring Framework and Core Spring Certification Udemy course with a discount coupon

In this post, basically I am going to show you how to configure and get a connection from BoneCP Java Database Connection by providing code examples. This configuration option allows you to define Connection Pool manually without configuring server for the Connection Pool (You don’t need to do anything on Application Server Machine).

By using this solution, you can define the CP from Java Code. And this example is designed to work on any Java EE Servers. If you investigate the documentation of BoneCP, there are some points omitted when you first look at but I will try to explain it by providing more information on this example.

To use the BoneCP first you should download the required jars from the following link;

http://repo1.maven.org/maven2/com/jolbox/bonecp/0.7.1.RELEASE/bonecp-0.7.1.RELEASE.jar

Downloading BoneCP is not enough because there are some dependencies as you can download from here;

BoneCP uses a logging framework so you should download it http://www.slf4j.org/download.html (file name:slf4j-1.6.4.zip)
And for some the of underlying operations it uses Google’s Guava Library can be downloaded here

http://search.maven.org/remotecontent?filepath=com/google/guava/guava/10.0.1/guava-10.0.1.jar

So basically, in this web application, we are going to implement the basic approach (I will provide a better approach such as using Spring’s JDBC template and BoneCP together on my future blog post)  for getting a connections from the database and we will open and close it manually by configuring the BoneCP localy for opening and closing connections.

BoneCP will open connections and  store inside it for us. You can configure how many connections you want by changing the BoneCPConfig config = new BoneCPConfig(); object’s parameters.

First, for this web application we need a ServletContextListener to initialize the connection pool from locally.

Here is the code;

package com.tunatore.db;

import javax.servlet.ServletContextEvent;
import javax.servlet.ServletContextListener;

public final class ContextListener implements ServletContextListener {

    public void contextInitialized(ServletContextEvent sce) {
        ConnectionManager.configureConnPool();
    }

    public void contextDestroyed(ServletContextEvent sce) {
        ConnectionManager.shutdownConnPool();
    }

}

The main purpose of this listener is to initialize the connection pool and make the necessary configurations inside the main ConnectionManager class. And there are two methods,

Inside the first method;
ConnectionManager.configureConnPool();
method is used for configuring Conn. Pool and opening the connections automatically for the application. (Or you can set lazy initialization functionality by changing the BoneCPConfig’s config.setLazyInit(true); property. and this allows the program open a connection when you call the getConnection() method for the first time from your application.)

The second method  ConnectionManager.shutdownConnPool(); is used to shutdown the pool and close all connections . This method is important because you have to close the pool otherwise all connections will stay alive for some  time (time-out) If you forget to call this method and run your code, there will be many connections unclosed by the Application.  So the contextDestroyed() method will call it only once when the web application un-deployed and this is what we need.

The main class is;

package com.tunatore.db;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import com.jolbox.bonecp.BoneCP;
import com.jolbox.bonecp.BoneCPConfig;

public class ConnectionManager {

private static BoneCP connectionPool = null;

public static void configureConnPool() {

try {
Class.forName("com.mysql.jdbc.Driver"); //also you need the MySQL driver
BoneCPConfig config = new BoneCPConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/testproject");
config.setUsername("root");
config.setPassword("admin");
config.setMinConnectionsPerPartition(5); //if you say 5 here, there will be 10 connection available   config.setMaxConnectionsPerPartition(10);
config.setPartitionCount(2); //2*5 = 10 connection will be available
//config.setLazyInit(true); //depends on the application usage you should chose lazy or not
//setting Lazy true means BoneCP won't open any connections before you request a one from it.
connectionPool = new BoneCP(config); // setup the connection pool
System.out.println("contextInitialized.....Connection Pooling is configured");
System.out.println("Total connections ==&gt; " + connectionPool.getTotalCreatedConnections());
ConnectionManager.setConnectionPool(connectionPool);

} catch (Exception e) {
e.printStackTrace(); //you should use exception wrapping on real-production code
}

}

public static void shutdownConnPool() {

try {
BoneCP connectionPool = ConnectionManager.getConnectionPool();
System.out.println("contextDestroyed....");
if (connectionPool != null) {
 connectionPool.shutdown(); //this method must be called only once when the application stops.
//you don't need to call it every time when you get a connection from the Connection Pool
System.out.println("contextDestroyed.....Connection Pooling shut downed!");
}

} catch (Exception e) {
e.printStackTrace();
}
}

public static Connection getConnection() {

Connection conn = null;
try {
 conn = getConnectionPool().getConnection();
//will get a thread-safe connection from the BoneCP connection pool.
//synchronization of the method will be done inside BoneCP source

} catch (Exception e) {
e.printStackTrace();
}
return conn;

}

public static void closeStatement(Statement stmt) {
try {
if (stmt != null)
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}

}

public static void closeResultSet(ResultSet rSet) {
try {
if (rSet != null)
rSet.close();
} catch (Exception e) {
e.printStackTrace();
}

}

public static void closeConnection(Connection conn) {
try {
if (conn != null)
conn.close(); //release the connection - the name is tricky but connection is not closed it is released
   //and it will stay in pool
} catch (SQLException e) {
e.printStackTrace();
}

}

public static BoneCP getConnectionPool() {
return connectionPool;
}

public static void setConnectionPool(BoneCP connectionPool) {
ConnectionManager.connectionPool = connectionPool;
}

}

Here I will code some unit test for getting connection from the Conn. Pool.

package com.tunatore.db;

import static org.junit.Assert.*;
import java.sql.Connection;
import com.tunatore.db.ConnectionManager;
import org.junit.After;
import org.junit.AfterClass;
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;

public class TestConnectionManager {

   //you application server will call the following two methods for you in production env.
@BeforeClass
public static void setUpBeforeClass() throws Exception {
ConnectionManager.configureConnPool();
}

@AfterClass
public static void tearDownAfterClass() throws Exception {
 ConnectionManager.shutdownConnPool();
}

@Before
public void setUp() throws Exception {
}

@After
public void tearDown() throws Exception {
}

@Test
public void testGetConnection() {
Connection conn = ConnectionManager.getConnection();
 assertNotNull(conn);
 ConnectionManager.closeConnection(conn);      //releasing the connection

}

}

The output;

contextInitialized…..Connection Pooling is configured
Total connections ==> 10
705 [main] INFO com.jolbox.bonecp.BoneCP – Shutting down connection pool…
710 [main] INFO com.jolbox.bonecp.BoneCP – Connection pool has been shutdown.
contextDestroyed….
contextDestroyed…..Connection Pooling shut downed!

IMPORTANT: If you want to use this manual  approach,  do not forget to close statements and resulset objects before releasing the connection to the Conn. Pool. You can close all these inside finally block.

I will post How to use BoneCP and Spring Framework post later and show why using Spring JDBC Template provides a good solution for accessing the database,opening connections,closing connections or similar operations.

****update: Here you can find new post regarding BoneCP and Spring Framework****

https://tunatore.wordpress.com/2011/11/16/how-to-use-spring-jdbc-template-with-bonecp-connection-pooling/