Monday, 28 November 2016

Insert\Update using NamedParameterJDBCTemplate in Spring framework

In the post Insert\Update using JDBCTemplate in Spring framework we have already seen how JDBCTemplate can be used in Spring framework for data access. If you have noticed the examples, in the SQL queries there indexed parameters are used with the place holder '?'.

With indexed parameter it is very important that you pass the parameters in the correct order in the query. Problem with indexed parameter is that any change in the query may require a change in order of the parameters too. If you want to avoid that another option is named parameters. As the name suggests here each parameter is given a name and you bind the parameters to the query using those names.

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 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 int save(Employee employee);
 
 public void update(Employee employee);
 
 public void deleteEmpById(int empId);
}

EmployeeDAOImpl class

import java.util.HashMap;

import java.util.Map;
import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;

public class EmployeeDAOImpl implements EmployeeDAO {
    private NamedParameterJdbcTemplate namedJdbcTemplate; 
    
    final String INSERT_QUERY = "insert into employee (name, age) values (:name, :age)";
    final String UPDATE_QUERY = "update employee set age = :age where id = :id";
    final String DELETE_QUERY = "delete from employee where id = :id";
    
    public NamedParameterJdbcTemplate getNamedJdbcTemplate() {
        return namedJdbcTemplate;
    }

    public void setNamedJdbcTemplate(NamedParameterJdbcTemplate namedJdbcTemplate) {
        this.namedJdbcTemplate = namedJdbcTemplate;
    }

    @Override
    public int save(Employee employee) {
        // Creating map with all required params
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("name", employee.getEmpName());
        paramMap.put("age", employee.getAge());
        // Passing map containing named params
        return namedJdbcTemplate.update(INSERT_QUERY, paramMap);  
    }

    @Override
    public void update(Employee employee) {
        // Adding params using MapSqlParameterSource class
        SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("age", employee.getAge()).addValue("id", employee.getEmpId());
        int status = namedJdbcTemplate.update(UPDATE_QUERY, namedParameters); 
        if(status != 0){
            System.out.println("Employee data updated for ID " + employee.getEmpId());
        }else{
            System.out.println("No Employee found with ID " + employee.getEmpId());
        }
    }

    @Override
    public void deleteEmpById(int empId) {
        // Adding params using MapSqlParameterSource class
        SqlParameterSource namedParameters = new MapSqlParameterSource("id", empId);
        int status = namedJdbcTemplate.update(DELETE_QUERY, namedParameters);
        if(status != 0){
            System.out.println("Employee data deleted for ID " + empId);
        }else{
            System.out.println("No Employee found with ID " + empId);
        }
    }
}

This class contains namedJdbcTemplate property which will be wired by the Spring framework. In the save method named parameters are stored in a Map and that map is passed. In the update method another option MapSqlParameterSource class is used which has addValue method. Using the addValue method key, value pair is stored and later passed to the query.

Also 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 itself.
If there is any SQLException thrown that is also caught by JDBCTemplate and translated to one of the DataAccessException and rethrown.

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

Here note that in the bean definition for NamedParameterJdbcTemplate, data source property is passed as a constructor argument.

Test class

You can use the following code in order to test the insertion, update and deletion.

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("employeeDAO");  
        Employee emp = new Employee();
        emp.setEmpName("John");
        emp.setAge(25);
        int status = dao.save(emp);  
        System.out.println(status);  
        // For update
        emp.setEmpId(9);
        emp.setAge(25);
        dao.update(emp);
        
        // For delete
        dao.deleteEmpById(10);
    }
}

Using automatic configuration with @Repository annotation

You can also use component scanning to automatically scan and wire the classes. For that you can use @Repository annotation with your DAO implementation classes and @Autowired annotation to automatically wire dependencies.

In that case your EmployeeDAOImpl will look like this -

import java.util.HashMap;
import java.util.Map;

import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
    private NamedParameterJdbcTemplate namedJdbcTemplate; 
    
    final String INSERT_QUERY = "insert into employee (name, age) values (:name, :age)";
    final String UPDATE_QUERY = "update employee set age = :age where id = :id";
    final String DELETE_QUERY = "delete from employee where id = :id";
    
    @Autowired
    public EmployeeDAOImpl(NamedParameterJdbcTemplate namedJdbcTemplate){
        this.namedJdbcTemplate = namedJdbcTemplate;
    }
    

    @Override
    public int save(Employee employee) {
        // Creating map with all required params
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("name", employee.getEmpName());
        paramMap.put("age", employee.getAge());
        // Passing map containing named params
        return namedJdbcTemplate.update(INSERT_QUERY, paramMap);  
    }

    @Override
    public void update(Employee employee) {
        // Adding params using MapSqlParameterSource class
        SqlParameterSource namedParameters = new MapSqlParameterSource().addValue("age", employee.getAge()).addValue("id", employee.getEmpId());
        int status = namedJdbcTemplate.update(UPDATE_QUERY, namedParameters); 
        if(status != 0){
            System.out.println("Employee data updated for ID " + employee.getEmpId());
        }else{
            System.out.println("No Employee found with ID " + employee.getEmpId());
        }
    }

    @Override
    public void deleteEmpById(int empId) {
        // Adding params using MapSqlParameterSource class
        SqlParameterSource namedParameters = new MapSqlParameterSource("id", empId);
        int status = namedJdbcTemplate.update(DELETE_QUERY, namedParameters);
        if(status != 0){
            System.out.println("Employee data deleted for ID " + empId);
        }else{
            System.out.println("No Employee found with ID " + empId);
        }
    }
}

XML Configuration

XML configuration will also change as you have to provide the base package to scan and you can also comment the bean definition for EmployeeDAO as it will be done automatically.

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

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


Related Topics

  1. Select query using JDBCTemplate in Spring framework
  2. Data access in Spring framework
  3. Configuring DataSource in Spring Framework
  4. How to inject prototype scoped bean in singleton bean
  5. What is Dependency Injection in Spring

You may also like -

>>>Go to Spring tutorial page

1 comment:

  1. Thank you for sharing such a nice and interesting blog with us. Hope it might be much useful for us. keep on updating...!!
    seo company in india
    digital marketing company in india

    ReplyDelete