Thursday, December 27, 2018

Spring JdbcTemplate Select Query Example

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.

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" />

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 -

  1. ResultSet - A table of data representing a database result set
  2. int - the number of the current row
and this method returns the result object for 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.

Recommendations for learning

  1. Spring Framework Master Class Course
  2. Spring & Hibernate for Beginners (Includes Spring Boot)
  3. Java In-Depth: Become a Complete Java Engineer!
  4. Complete Python Bootcamp Course
  5. React - The Complete Guide Course

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

  1. Configuring DataSource in Spring Framework
  2. Insert\Update Using NamedParameterJdbcTemplate in Spring Framework
  3. Select Query Using NamedParameterJdbcTemplate in Spring Framework
  4. How to inject prototype scoped bean in singleton bean
  5. Using Spring Profiles to Switch Environment

You may also like-

  1. Spring example program using automatic configuration
  2. Autodiscovery of Bean Using component-scan in Spring
  3. @Resource annotation in Spring autowiring
  4. interface default methods in Java 8
  5. String and thread-safety in Java
  6. Difference between Thread and Process in Java
  7. AtomicInteger in Java Concurrency
  8. BlockingDeque in Java Concurrency