Thursday, October 18, 2018

Spring JdbcTemplate With ResultSetExtractor Example

In the post Select Query Using JDBCTemplate in Spring Framework we have already seen an example of extracting data from ResultSet using RowMapper. A RowMapper is usually a simpler choice for ResultSet processing, mapping one result object per row but there is another option in Spring framework known as ResultSetExtractor which gives one result object for the entire ResultSet.

In this post we’ll see an example of Spring JdbcTemplate with ResultSetExtractor. Since ResultSetExtractor is a callback interface used by JdbcTemplate's query methods so you can use an instance of ResultSetExtractor with JdbcTemplate’s query method.


query method signature with ResultSetExtractor

public <T> T query(java.lang.String sql, ResultSetExtractor<T> rse) throws DataAccessException

After the execution of the query, ResultSet can be read with a ResultSetExtractor.

ResultSetExtractor interface in Spring

ResultSetExtractor interface is a functional interface used by JdbcTemplate’s query method. It has a single abstract method extractData().

T extractData(java.sql.ResultSet rs) throws java.sql.SQLException, DataAccessException

Implementing class must implement this method to process the entire ResultSet.

Spring JdbcTemplate with ResultSetExtractor example

Technologies used

Spring 5.0.4
Apache DBCP2
MYSQL 5.1.39
Java 8
Apache Maven 3.3.3

Maven dependencies

If you are using Apache Maven then you can provide dependencies in your pom.xml.

Refer Creating a Maven project in Eclipse to see how to set up Maven project.

With all the dependencies your pom.xml should look something like this-

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>

  <groupId>org.netjs.prog</groupId>
  <artifactId>maven-spring</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>jar</packaging>

  <name>maven-spring</name>
  <url>http://maven.apache.org</url>

  <properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <spring.version>5.0.4.RELEASE</spring.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>4.11</version>
      <scope>test</scope>
    </dependency>
    
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-core</artifactId>
        <version>${spring.version}</version>
    </dependency>
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-context</artifactId>
        <version>${spring.version}</version>
    </dependency>
    
     <dependency>
        <groupId>javax.inject</groupId>
        <artifactId>javax.inject</artifactId>
        <version>1</version>
    </dependency>
    
    <!-- Spring JDBC Support -->
    <dependency>
        <groupId>org.springframework</groupId>
        <artifactId>spring-jdbc</artifactId>
        <version>${spring.version}</version>
    </dependency>
    
   <!-- MySQL Driver -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.39</version>
    </dependency>
    
    <!--  Apache DBCP connection pool -->
    <dependency>
       <groupId>org.apache.commons</groupId>
       <artifactId>commons-dbcp2</artifactId>
       <version>2.1</version>
    </dependency>
  </dependencies>
</project>

Alternatively you can download the jars and add them to the class path.

Database table

For this example I have created a table called employee with the columns id, name and age in the MYSQL DB. Column id is configured as auto increment checked so no need to pass id from your query as DB will provide value for it.

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(35) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Setting up bean dependencies

In the bean for JdbcTemplate you will need to set the DataSource bean as dependency. In the DataSource bean you will need to provide DB properties. It is better if you read DB configuration parameters from a properties file.

Property file db.properties which is under the config folder has all the properties.

db.properties

db.driverClassName=com.mysql.jdbc.Driver
db.url=jdbc:mysql://localhost:3306/netjs
db.username=
db.password=
pool.initialSize=5

Description for the properties used here is as-

driver class name is the JDBC driver for the DB used. Since MYSQL is used here so the jdbc driver for the same (com.mysql.jdbc.Driver) is provided.

Url – You need to provide url to access your DB server. I have created a schema called netjs and DB is running on the same system so url is jdbc:mysql://localhost:3306/netjs.

Username and password for the DB.

IntialSize is the initial size of the connection pool. It is given as 5 so initially 5 connections will be created and stored in the pool.

XML Configuration

<?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:aop="http://www.springframework.org/schema/aop"
    xmlns:context="http://www.springframework.org/schema/context"
    xmlns:c="http://www.springframework.org/schema/c"
    xmlns:p="http://www.springframework.org/schema/p"
    xsi:schemaLocation="http://www.springframework.org/schema/beans 
    http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
    http://www.springframework.org/schema/context
    http://www.springframework.org/schema/context/spring-context.xsd">
   
      <context:property-placeholder location="classpath:config/db.properties" />                  

     <context:component-scan base-package="org.netjs.daoimpl" />
    
    <bean id="dataSource" class="org.apache.commons.dbcp2.BasicDataSource">
      <property name="driverClassName" value = "${db.driverClassName}" />
      <property name="url" value = "${db.url}" />
      <property name="username" value = "${db.username}" />
      <property name="password" value = "${db.password}" />
      <property name="initialSize" value = "${pool.initialSize}" />
    </bean>
    
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">  
      <property name="dataSource" ref="dataSource"></property>  
    </bean>
    
</beans>

Java Classes

Since Spring always promotes to use interfaces and there is also a JEE design pattern for database layer called DAO which also says the same thing - Separate low level data access code from the business layers.

So we have a EmployeeDAO interface with find methods and its implementing class EmployeeDAOImpl. There is also a model class Employee with all the getters/setters.

Employee.java class

public class Employee {
 private int empId;
 private String empName;
 private int age;
 
 public int getEmpId() {
  return empId;
 }
 public void setEmpId(int empId) {
  this.empId = empId;
 }
 public String getEmpName() {
  return empName;
 }
 public void setEmpName(String empName) {
  this.empName = empName;
 }
 public int getAge() {
  return age;
 }
 public void setAge(int age) {
  this.age = age;
 }
}
EmployeeDAO interface
import org.netjs.model.Employee;
public interface EmployeeDAO {
    public List<Employee> findAllEmployees();
}

EmployeeDAOImpl class

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
    @Autowired
    private JdbcTemplate jdbcTemplate; 
    
    final String SELECT_ALL_QUERY = "SELECT id, name, age from EMPLOYEE";
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
        this.jdbcTemplate = jdbcTemplate;  
    }
    public List<Employee> findAllEmployees() {
        return this.jdbcTemplate.query(SELECT_ALL_QUERY, 
         new ResultSetExtractor<List<Employee>>() {

            @Override
            public List<Employee> extractData(ResultSet rs)
                    throws SQLException, DataAccessException {
                List<Employee> list = new ArrayList<Employee>();  
                while(rs.next()){
                    Employee emp = new Employee();
                    emp.setEmpId(rs.getInt("id"));
                    emp.setEmpName(rs.getString("name"));
                    emp.setAge(rs.getInt("age"));
                    list.add(emp);
                }
                return list;
            }  
        });
    }
}

Notice how you are not writing any code for getting or closing connection, exception handling. All that fixed part is managed by the JdbcTemplate class. Its the JdbcTemplate which is getting the connection using the DataSource provided to it, creating and executing the statement and closing the connection.

If there is any SQLException thrown that is also caught by JdbcTemplate and translated to one of the DataAccessException and rethrown.

ResultSetExtractor interface is implemented as an anonymous inner class and you can see the implementation of extractData method where the returned ResultSet is processed.

Test class

You can use the following code in order to test the code -
public class App {
    public static void main(String[] args) {
        
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("appcontext.xml");
        EmployeeDAO dao = (EmployeeDAO)context.getBean("employeeDAOImpl");  
        List<Employee> empList = dao.findAllEmployees();
        for(Employee emp : empList){
            System.out.println("Name - "+ emp.getEmpName() + " Age - " 
          + emp.getAge());
        }
        context.close();    
    }
}

ResultSetExtractor implemented as a Lambda expression

Since ResultSetExtractor is a functional interface so Java 8 onwards it can also be implemented as lambda expression.

public List<Employee> findAllEmployees() {
      return this.jdbcTemplate.query(SELECT_ALL_QUERY, (ResultSet rs) -> {
           List<Employee> list = new ArrayList<Employee>();  
           while(rs.next()){
               Employee emp = new Employee();
               emp.setEmpId(rs.getInt("id"));
               emp.setEmpName(rs.getString("name"));
               emp.setAge(rs.getInt("age"));
               list.add(emp);
           }
           return list;
      });
}

That's all for this topic Spring JdbcTemplate With ResultSetExtractor Example. If you have any doubt or any suggestions to make please drop a comment. Thanks!

>>>Return to Spring Tutorial Page


Related Topics

  1. Insert\Update Using NamedParameterJDBCTemplate in Spring Framework
  2. Select Query Using NamedParameterJDBCTemplate in Spring Framework
  3. registerShutdownHook() Method in Spring Framework
  4. Excluding Bean From Autowiring in Spring
  5. @Resource Annotation in Spring Autowiring

You may also like-

  1. Using Conditional Annotation in Spring Framework
  2. Using util-namespace For Wiring Collection in Spring
  3. Circular Dependency in Spring Framework
  4. AutoBoxing And UnBoxing in Java
  5. Volatile in Java
  6. AtomicInteger in Java Concurrency
  7. StringBuilder in Java
  8. Array in Java