Wednesday, 30 November 2016

Select query using NamedParameterJDBCTemplate in Spring framework

In the post Insert\Update using NamedParameterJDBCTemplate in Spring framework I have already discussed how NamedParameterJDBCTemplate can be used for inserting and updating data in the DB. In this post we’ll see how to read data from DB using named parameters. Main intention to have it as a separate post is to discuss callback part in detail.

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

NamedParameterJdbcTemplate

Spring framework provides NamedParameterJdbcTemplate class which adds support for programming JDBC statements using named parameters, as opposed to programming JDBC statements using only classic placeholder ('?') arguments.

Here one thing to note is NamedParameterJdbcTemplate class wraps a JdbcTemplate, and delegates to the wrapped JdbcTemplate to do much of its work.

NamedParameterJdbcTemplate with Select query example

Note that NamedParameterJdbcTemplate needs a DataSource in order to perform its management of fixed part like getting a DB connection, cleaning up resources.

In this post Apache DBCP is used which provides 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 NamedParameterJDBCTemplate

DataSource bean has to be provided as a reference in NamedParameterJDBCTemplate.

<bean id="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">  
    <constructor-arg ref="dataSource"></constructor-arg> 
</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 insert, update and delete 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

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.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
    private NamedParameterJdbcTemplate namedJdbcTemplate; 
    
    final String SELECT_BY_ID_QUERY = "SELECT id, name, age from EMPLOYEE where id = :id";
    final String SELECT_ALL_QUERY = "SELECT id, name, age from EMPLOYEE";
    
    @Autowired
    public EmployeeDAOImpl1(NamedParameterJdbcTemplate namedJdbcTemplate){
        this.namedJdbcTemplate = namedJdbcTemplate;
    }

    @Override
    public List<Employee> findAllEmployees() {
        return this.namedJdbcTemplate.query(SELECT_ALL_QUERY, new EmployeeMapper());
    }

    @Override
    public Employee findEmployee(int empId) {
        return this.namedJdbcTemplate.queryForObject(SELECT_BY_ID_QUERY, new MapSqlParameterSource("id", empId), 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;
        }
    }
}
If you have more than one named parameter you can also use a hashMap or create a chain of addValue() methods with a MapSqlParameterSource class object. Refer Insert\Update using NamedParameterJDBCTemplate in Spring framework to see an example.

Notice how you are not writing any code for getting or closing connection, exception handling. All that fixed part is managed by the template class.

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.
  • Object of type SQLParameterSource that is where MapSqlParameterSource object is passed which stores all the named parameters to be bound to the query.
  • RowMapper object that maps a single result row to a Java object via a RowMapper.

Whereas in findAllEmployees() method query method is used which takes only two parameters –

  • SQL query String
  • RowMapper object

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

Full 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="namedJdbcTemplate" class="org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate">  
        <constructor-arg ref="dataSource"></constructor-arg> 
    </bean> 
    <!-- <bean id="employeeDAO" class="org.netjs.daoimpl.EmployeeDAOImpl">
        <property name="namedJdbcTemplate" ref="namedJdbcTemplate"></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");  
               
        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(3).getEmpName() + " Age - " + empList.get(3).getAge());
    }
}

That's all for this topic Select query using NamedParameterJDBCTemplate in Spring framework. If you have any doubt or any suggestions to make please drop a comment. Thanks!


Related Topics

  1. Spring example program using automatic configuration
  2. Select query using JDBCTemplate in Spring framework
  3. Configuring DataSource in Spring Framework
  4. Autodiscovery of bean using componenent-scan in Spring
  5. How to read properties file in Spring Framework

You may also like -

>>>Go to Spring tutorial page

No comments:

Post a Comment