Monday, 7 November 2016

Insert\Update using JDBCTemplate in Spring framework

In the post Data access in Spring framework we have already seen how Spring provides templates for various persistence methods and how templates divide the data access code into fixed part and variable part. Where Spring framework manages the fixed part and custom code which is provided by the user is handled through callbacks. In this post we’ll see how to use JDBCTemplate to insert/update data into the database.

Note that JDBCTemplate 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 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 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

import org.netjs.model.Employee;

public interface EmployeeDAO {
 public int save(Employee employee);
 
 public void update(Employee employee);
 
 public void deleteEmpById(int empId);
}

EmployeeDAOImpl class

import org.netjs.dao.EmployeeDAO;
import org.netjs.model.Employee;
import org.springframework.jdbc.core.JdbcTemplate;

public class EmployeeDAOImpl implements EmployeeDAO {
    private JdbcTemplate jdbcTemplate;  
    final String INSERT_QUERY = "insert into employee (name, age) values (?, ?)";
    final String UPDATE_QUERY = "update employee set age = ? where id = ?";
    final String DELETE_QUERY = "delete from employee where id = ?";
 
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
        this.jdbcTemplate = jdbcTemplate;  
    }
    
    public int save(Employee employee) {
        return jdbcTemplate.update(INSERT_QUERY, employee.getEmpName(), employee.getAge());   
    }

    public void update(Employee employee) {
        int status = jdbcTemplate.update(UPDATE_QUERY, employee.getAge(), employee.getEmpId()); 
        if(status != 0){
            System.out.println("Employee data updated for ID " + employee.getEmpId());
        }else{
            System.out.println("No Employee found with ID " + employee.getEmpId());
        }
        
    }

    public void deleteEmpById(int empId) {
        int status = jdbcTemplate.update(DELETE_QUERY, empId);
        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 jdbcTemplate property which will be injected by the Spring framework. In the save method insert query is executed and the parameters are provided to it. Note these are indexed parameters.

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

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: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 = "com.mysql.jdbc.Driver" />
        <property name="url" value = "jdbc:mysql://localhost:3306/netjs" />
        <property name="username" value = "root" />
        <property name="password" value = "admin" />
        <property name="initialSize" value = "5" />
    </bean>

</beans>

Test class

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

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(12);
     emp.setAge(35);
     
     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 -

@Repository
public class EmployeeDAOImpl implements EmployeeDAO {
 @Autowired
 private JdbcTemplate jdbcTemplate; 
 
 final String INSERT_QUERY = "insert into employee (name, age) values (?, ?)";
 final String UPDATE_QUERY = "update employee set age = ? where id = ?";
 final String DELETE_QUERY = "delete from employee where id = ?";
 
 public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {  
     this.jdbcTemplate = jdbcTemplate;  
 }
 
 public int save(Employee employee) {
            return jdbcTemplate.update(INSERT_QUERY, employee.getEmpName(), employee.getAge());   
 }

 public void update(Employee employee) {
     int status = jdbcTemplate.update(UPDATE_QUERY, employee.getAge(), employee.getEmpId()); 
     if(status != 0){
  System.out.println("Employee data updated for ID " + employee.getEmpId());
     }else{
  System.out.println("No Employee found with ID " + employee.getEmpId());
     }
  
 }

 public void deleteEmpById(int empId) {
     int status = jdbcTemplate.update(DELETE_QUERY, empId);
     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" />
    
    <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>

That's all for this topic Insert\Update 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. What is Dependency Injection in Spring
  4. Autowiring using annotations in Spring
  5. How to inject prototype scoped bean in singleton bean

You may also like -

>>>Go to Spring tutorial page

No comments:

Post a Comment