Spring JdbcTemplate Example + JdbcDaoSupport

Spring framework provides excellent support to JDBC, it provides a super powerful utility class called “JdbcTemplate” which helps us avoid boiler-plate code from our database operations such as Creating Connection, Statement, Closing the Resultset and Connection, Exception handling, Transaction management etc. In this Spring JdbcTemplate Example, let’s understand how JdbcTemplate eases the development effort.

Let’s take a look into a simple Spring JDBC example without the implementation of the JdbcTemplate.

Creating table

Create EMPLOYEE Table, simply Copy and Paste the following SQL query in the query editor to get the table created.

CREATE TABLE "EMPLOYEE" 
 ( 
    "ID" NUMBER(10) NOT NULL ENABLE, 
    "AGE" NUMBER(10), 
    "DEPT" VARCHAR2(255 CHAR), 
    "NAME" VARCHAR2(255 CHAR), 
     PRIMARY KEY ("ID")
 );

Folder Structure:

1. Create a simple Maven Project “SpringJDBC” by selecting maven-archetype-quickstart  and create a package for our source files “com.javainterviewpoint” under  src/main/java 
2. Now add the following dependency in the POM.xml

   <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>com.javainterviewpoint</groupId>
    <artifactId>SpringJDBC</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <packaging>jar</packaging>
   
    <name>SpringJDBC</name>
    <url>http://maven.apache.org</url>
   
    <properties>
       <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
       <springframework.version>4.3.7.RELEASE</springframework.version>
       <oracle.connector.version>11.2.0</oracle.connector.version>
    </properties>
    
    <dependencies>
       <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>3.8.1</version>
          <scope>test</scope>
       </dependency>
       <!-- Spring Dependency-->
       <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-core</artifactId>
          <version>${springframework.version}</version>
       </dependency>
       <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-jdbc</artifactId>
          <version>${springframework.version}</version>
       </dependency>
       <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-context</artifactId>
          <version>${springframework.version}</version>
       </dependency>
       <!-- Oracle Dependency-->
       <dependency>
          <groupId>com.oracle</groupId>
          <artifactId>ojdbc14</artifactId>
          <version>${oracle.connector.version}</version>
       </dependency>
     </dependencies>
   </project>

3. Create the Java classes Employee.java,EmployeeDAOImpl.java and SpringJDBCExample.java under com.javainterviewpoint folder.

Spring JdbcTemplate Example

Employee.java

Our Employee class is a simple POJO class consisting getters and setters of Employee properties id, name, age, dept.

package com.javainterviewpoint;

import java.io.Serializable;

public class Employee implements Serializable 
{
    private static final long serialVersionUID = -1280037900360314186L;

    private int id;
    private String name;
    private int age;
    private String dept;
    
    public Employee()
    {
        super();
    }

    public Employee(int id, String name, int age, String dept)
    {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
        this.dept = dept;
    }
    
    public int getId()
    {
        return id;
    }
    public void setId(int id)
    {
        this.id = id;
    }
    
    public String getName()
    {
        return name;
    }
    public void setName(String name)
    {
        this.name = name;
    }
    
    public int getAge()
    {
        return age;
    }
    public void setAge(int age)
    {
        this.age = age;
    }
    
    public String getDept()
    {
        return dept;
    }
    public void setDept(String dept)
    {
        this.dept = dept;
    }
    @Override
    public String toString()
    {
        return "Employee [id=" + id + ", name=" + name + ", age=" + age + ", dept=" + dept + "]";
    }
    @Override
    public int hashCode()
    {
        final int prime = 31;
        int result = 1;
        result = prime * result + age;
        result = prime * result + ((dept == null) ? 0 : dept.hashCode());
        result = prime * result + id;
        result = prime * result + ((name == null) ? 0 : name.hashCode());
        return result;
    }
    @Override
    public boolean equals(Object obj)
    {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Employee other = (Employee) obj;
        if (age != other.age)
            return false;
        if (dept == null)
        {
            if (other.dept != null)
                return false;
        } else if (!dept.equals(other.dept))
            return false;
        if (id != other.id)
            return false;
        if (name == null)
        {
            if (other.name != null)
                return false;
        } else if (!name.equals(other.name))
            return false;
        return true;
    }
}

SpringConfig.xml

In our configuration file, we have defined the EmployeeDAOImpl and DriverManagerDataSource classes as beans. DriverManagerDataSource contains database related configurations such as driver class name, connection URL, username and password. We will be referencing our bean DriverManagerDataSource with the dataSource property of EmployeeDAOImpl class.

<?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:tx="http://www.springframework.org/schema/tx"
 xsi:schemaLocation="http://www.springframework.org/schema/beans	http://www.springframework.org/schema/beans/spring-beans.xsd
 http://www.springframework.org/schema/aop	http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
 http://www.springframework.org/schema/tx	http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">

    <bean id="employeeDAOImpl" class="com.javainterviewpoint.EmployeeDAOImpl">
       <property name="dataSource" ref="dataSource" />
    </bean>

    <!-- Database Configurations -->
    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
        <property name="url" value="jdbc:oracle:thin:@rsh2:40051:mydb" />
        <property name="username" value="root" />
        <property name="password" value="root" />
     </bean>

     <!-- <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
         <property name="dataSource" ref="dataSource"/>
     </bean> -->
 </beans>

SpringJDBCExample.java

package com.javainterviewpoint;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class SpringJDBCExample
{
    public static void main(String[] args)
    {
        //Reading the configuration
        ClassPathXmlApplicationContext context = new ClassPathXmlApplicationContext("springConfig.xml");
        //Get EmployeeDAOImpl bean instance
        EmployeeDAOImpl dao = (EmployeeDAOImpl) context.getBean("employeeDAOImpl");
        //Create a new Employee object
        Employee employee = new Employee();
        employee.setId(99);
        employee.setName("JavaInterviewPoint");
        employee.setDept("Blog");
        employee.setAge(99);
        //Save the Employee object
        dao.saveEmployee(employee);
        
    }
}

• ClassPathXmlApplicationContext class reads our Configuration File(SpringConfig.xml)
• We will get our EmployeeDAOImpl Class instance by calling the getBean() method over the context.
• The String passed to getBean() method should be equivalent to the id defined in the SpringConfig.xml
• Create Employee object and set the value to its properties and pass the employee object to the saveEmployee() method.

EmployeeDAOImpl.java (Without JdbcTemplate)

package com.javainterviewpoint;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.sql.DataSource;

public class EmployeeDAOImpl 
{
    DataSource dataSource;

    public void setDataSource(DataSource dataSource)
    {
        this.dataSource = dataSource;
    }

    public void saveEmployee(Employee employee)
    {
        String sql = "insert into Employee values(?,?,?,?)";

        Connection connection = null;
        try
        {
            connection = dataSource.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(sql);

            preparedStatement.setInt(1, employee.getId());
            preparedStatement.setInt(2, employee.getAge());
            preparedStatement.setString(3, employee.getDept());
            preparedStatement.setString(4, employee.getName());
            preparedStatement.executeUpdate();
            preparedStatement.close();

        } catch (SQLException se)
        {
            se.printStackTrace();
        } finally
        {
            if (connection != null)
            {
                try
                {
                    connection.close();
                } catch (SQLException e)
                {
                    e.printStackTrace();
                }
            }
        }
   }
}

Without JDBCTemplate in the saveEmployee() method we need to write boilerplate code such as Creating Connection, Statement, Closing the Resultset and Connection, Exception handling etc.

EmployeeDAOImpl.java (With JdbcTemplate)

JdbcTemplate handles all the database related boilerplate codes, with JdbcTemplate all we have to do is the below steps.

• Create a new JdbcTemplate object passing the dataSources as the constructor argument.
• Call the update() method over the jdbcTemplate instance with the SQL and employee properties as the arguments.

package com.javainterviewpoint;

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;

public class EmployeeDAOImpl
{
    DataSource dataSource;

    public void setDataSource(DataSource dataSource)
    {
        this.dataSource = dataSource;
    }

    public void saveEmployee(Employee employee)
    {
        String sql = "insert into Employee values(?,?,?,?)";

        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.update(sql, new Object[] { employee.getId(), 
             employee.getAge(), employee.getDept(), employee.getName() });
     }
}

EmployeeDAOImpl.java (With JdbcDaoSupport)

By extending the JdbcDaoSupport class in our EmployeeDAOImpl class then there is no need of dataSource setter and JdbcTemplate in our EmployeeDAOImpl class, all we need to do is just inject the correct datasource into EmployeeDAOImpl(SpringConfig.xml) and we can get the JdbcTemplate instance by using a getJdbcTemplate() method.

package com.javainterviewpoint;

import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class EmployeeDAOImpl extends JdbcDaoSupport
{
    // No DataSource setter required
   
    public void saveEmployee(Employee employee)
    {
        String sql = "insert into Employee values(?,?,?,?)";

        getJdbcTemplate().update(sql, new Object[]
        { employee.getId(), employee.getAge(), employee.getDept(), employee.getName() });
    }
}