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.
- Refer How to Read Properties File in Spring Framework to know more about how to read properties file in Spring framework.
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
You may also like-