In the post Spring JdbcTemplate Insert, Update And Delete Example I have already discussed how JdbcTemplate can be used for inserting and updating data in the DB. I left behind the part to read from Database using Select query. Purpose for doing that is to discuss in detail the callback part of the JdbcTemplate. This post shows how to use Select query using JdbcTemplate in Spring framework and also talks about the callback methods in detail which shows how you can do resultset to model mapping using RowMapper implementation.
In the post Data access in Spring framework it has been discussed in detail how Spring framework provides templates to manage the fixed part and uses call back to handle the variable part. Fetching data from DB using select query has, as usual, the fixed part like getting connection, cleaning up, handling exception but at the same time Spring framework does need help to map the fetched data to the model. That’s where callback comes into picture.
Technologies used
- Spring 5.0.4
- Apache DBCP2
- MYSQL 5.1.39
- Java 8
- Apache Maven 3.3.3
In this Spring JdbcTemplate select query example Apache DBCP is used for providing pooled datasource and MYSQL is used as the back end.
Maven dependencies
If you are using 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 example
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`) ) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
Configuring datasource dependency
First thing is to set up DataSource as a bean. I have used properties file to configure datasource where all the properties are there in the db.properties file.
<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>
Where as db.properties file 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 of the properties 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.
To use properties file you need to put following configuration in your XML.
<context:property-placeholder location="classpath:config/db.properties" />
- Refer How to Read Properties File in Spring Framework to see in detail how to to read properties file in Spring framework.
Spring JDBCTemplate configuration
DataSource bean has to be provided as a reference in JdbcTemplate.
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
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();
public Employee findEmployee(int empId);
}
EmployeeDAOImpl class
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
@Autowired
private JdbcTemplate jdbcTemplate;
final String SELECT_BY_ID_QUERY = "SELECT id, name, age from EMPLOYEE where id = ?";
final String SELECT_ALL_QUERY = "SELECT id, name, age from EMPLOYEE";
public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
this.jdbcTemplate = jdbcTemplate;
}
public Employee findEmployee(int empId) {
return this.jdbcTemplate.queryForObject(SELECT_BY_ID_QUERY, new EmployeeMapper(),
empId);
}
public List<Employee> findAllEmployees() {
return this.jdbcTemplate.query(SELECT_ALL_QUERY, new EmployeeMapper());
}
private static final class EmployeeMapper implements RowMapper<Employee> {
public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
Employee emp = new Employee();
emp.setEmpId(rs.getInt("id"));
emp.setEmpName(rs.getString("name"));
emp.setAge(rs.getInt("age"));
return emp;
}
}
}
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.
Spring RowMapper
Main thing to demonstrate in this Spring JdbcTemplate select query example is how callback works. Here template callbacks are used to query the DB and then map the returned result set to the model (Employee) object(s).
If you have noticed in findEmployee(int empId) method queryForObject method of JdbcTemplate is used which takes 3 parameters-
- SQL query String
- RowMapper object that maps a single result row to a Java object via a RowMapper
- varargs to bind parameters to the query
Whereas in findAllEmployees() method query method is used which takes only two parameters –
- SQL query String
- RowMapper object
as there are no parameters to be passed to the SQL so varargs are not needed in this case.
Main thing here is RowMapper object which in this example is the object of class EmployeeMapper
implementing the RowMapper interface.
RowMapper
interface has a single method mapRow which takes two arguments -
- ResultSet - A table of data representing a database result set
- int - the number of the current row
For every row in the result set, JdbcTemplate calls the mapRow() method of the RowMapper interface implementing class. Arguments passed are ResultSet and an integer which is the number of the current row in the result set. Using that row number cursor is moved to the given row in the result set.
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"
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:component-scan base-package="org.netjs.daoimpl" />
<!-- For reading properties files -->
<context:property-placeholder location="classpath:config/db.properties" />
<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
<property name="dataSource" ref="dataSource"></property>
</bean>
<!-- <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl">
<property name="jdbcTemplate" ref="jdbcTemplate"></property>
</bean> -->
<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>
</beans>
If you are not using component scanning, then you can uncomment the bean definition for the EmployeeDAO.
Test class
You can use the following code in order to test the code -
import java.util.List;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class App {
public static void main(String[] args) {
ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext
("appcontext.xml");
EmployeeDAO dao = (EmployeeDAO)context.getBean("employeeDAOImpl");
// Uncomment this to find employee by ID
/*Employee emp = dao.findEmployee(5);
System.out.println("Name - "+ emp.getEmpName() + " Age - " + emp.getAge());*/
List<Employee> empList = dao.findAllEmployees();
System.out.println("Name - "+ empList.get(1).getEmpName() + " Age - "
+ empList.get(1).getAge());
}
}
RowMapper implementation as Lambda Expression
RowMapper interface has only single method mapRow which means it is a functional interface. Starting Java 8 it can be implemented as a lambda expression. Since same implementation is used by two methods findEmployee() and findAllEmployees() so it is better to implement it as a lambda block rather than as an inline lambda.
In that case findEmployee() and findAllEmployees() methods will change like this -
public Employee findEmployee(int EmpId) {
return this.jdbcTemplate.queryForObject(SELECT_BY_ID_QUERY, getMap(), EmpId);
}
public List<Employee> findAllEmployees() {
return this.jdbcTemplate.query(SELECT_ALL_QUERY, getMap());
}
private RowMapper<Employee> getMap(){
// Lambda block
RowMapper<Employee> empMap = (rs, rowNum) -> {
Employee emp = new Employee();
emp.setEmpId(rs.getInt("id"));
emp.setEmpName(rs.getString("name"));
emp.setAge(rs.getInt("age"));
return emp;
};
return empMap;
}
Here it can be seen that lambda block is implemented inside method getMap(). Here lambda is assigned to a functional interface (RowMapper in this case) variable. It has two arguments rs and rowNum, since it is implementing mapRow() method of the RowMapper class so compiler will infer that rs and rowNum are of type ResultSet and int respectively.
That's all for this topic Spring JdbcTemplate Select Query 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-