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

Advertisements

2 thoughts on “How to use Spring JDBC Template with BoneCP Connection Pooling

  1. Huge thanks for this example! The usage shown in MainProgram is really nice, but wow that is a lot of configuration and complication for one table. It almost seems simpler to just do some basic JDBC and get it over with. Maybe I’ll try just using BoneCP and not Spring, and make my own object/data access code. Then if I see a possible advantage with Spring, I can go from there.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s