Wednesday, 9 November 2016

Select query using JDBCTemplate in Spring framework

In the post Insert\Update using JDBCTemplate in Spring framework 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 to discuss in detail the callback part of the JDBCTemplate.

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.

In this post Apache DBCP is used for providing pooled datasource and MYSQL is used as the back end.

Technologies used

  • Spring 4.2.1
  • Apache DBCP2
  • MYSQL 5.1.39
  • Java 8
  • Apache Maven 3.3.3

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>4.2.1.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`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

Setting up dependencies

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 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.

To use properties file you need to put following configuration in your XML.

<context:property-placeholder location="classpath:config/db.properties" />

Configuring JDBC Template

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.

But the main thing to demonstrate here 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 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 automatic configuration, 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 Select query using JDBCTemplate in Spring framework. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Data access in Spring framework
  2. Configuring DataSource in Spring Framework
  3. How to inject prototype scoped bean in singleton bean
  4. Bean definition inheritance in Spring
  5. Spring example program using automatic configuration

You may also like -

>>>Go to Spring tutorial page

No comments:

Post a Comment