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

Database table

DB table used for this example is 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 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" />

Spring NamedParameterJdbcTemplate configuration

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 EmployeeDAOImpl(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 Spring NamedParameterJdbcTemplate Insert, Update And Delete Example 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.

Explanation of RowMapper callbacks

Main thing to demonstrate in this Spring NamedParameterJdbcTemplate 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.
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");  
    // finding by ID       
    Employee emp = dao.findEmployee(5);
    System.out.println("Name - "+ emp.getEmpName() + " Age - " + emp.getAge());  
    // finding all       
    List<Employee> empList = dao.findAllEmployees();
    System.out.println("Name - "+ empList.get(3).getEmpName() + 
      " Age - " + empList.get(3).getAge());
  }
}

That's all for this topic Spring NamedParameterJdbcTemplate 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-

Tech Tutorials

Sunday, June 18, 2023