19 - Spring JDBC

19.1 Overview

Database access is a common requirement for all the applications because data is stored in a database. Applications use frameworks like JDBC, Hibernate etc to access the database.

JDBC (Java Database Connectivity) is a set of interfaces and API and every database vendor implements the APIs. With the help of JDBC, applications need not to write a vendor specific code to access the data.

DAO stands for Data Access Objects and is a pattern to access the data of database.

Spring comes with a JDBC framework with which developers need to just focus on core logic and Spring handles all low level details and repetitive tasks like opening and closing of connections, exceptions, iteration of results, transactions etc for us.

Traditional JDBC does not provide a proper hierarchy of exceptions and forces developers to catch SQLException as this is a checked exception. Ideally, we catch the exceptions which we can deal with but JDBC always throws SQLException and developer need to look much deeper to make sure if the exception can be handled or not.

Spring JDBC defines a hierarchy of exceptions and developer needs to handle the ones he should deal with. All the Spring JDBC exceptions are the child of DataAccessException and DataAccessException is a Runtime Exception which means developers are not forced to handle the exceptions.

19.2 Spring Data Access Approaches

There are four approaches with which database can be accessed.

JdbcTemplate – JdbcTemplate approach works for JDK 1.4 onwards and is widely used. JdbcTemplate is used internally in other three approaches. JdbcTemplate handles the repetitive operations like opening and closing of connections and can execute select, insert, update statements, calls to stored procedures. This class is thread safe.
NamedParameterJdbcTemplate – This approach supports the use of named parameters in place of traditional place holders ( ? ) and works with JDK 1.4 onwards.
SimpleJdbcTemplate- This approach uses both JdbcTemplate and NamedParameterJdbcTemplate approaches and provides additional support of Java 5 features like autoboxing, generics etc. This approach requires JDF 5 onwards.
SimpleJdbcInsert and SimpleJdbcCall – This approach utilizes the database metadata and hence reduces the configuration overhead.

19.3 Database

In this tutorial we would need a database. There are several databases available in the market but and our code will not be tied to any database. We would need a vendor specific jar file and need to configure the database.

I will be using MySQL database in this tutorial because MySQL is freely available, lightweight and easy to install.

You can also download and install MySQL from http://dev.mysql.com/downloads/windows/installer/5.5.html

MySQL server does not ship with any GUI tool so if you want, you can install MySQL workbench as well from http://dev.mysql.com/downloads/workbench

Let's create a schema named “SpringJDBC”. To do so you can run below query

create schema SpringJDBC;

Now create a table named “Employee” with four columns

ID
Name
Age
Salary

You can use below SQL query to create a “Employee” table

CREATE TABLE 'springjdbc'. 'Employee' (

'D' INT NOT NULL ,

'Name' VARCHAR(45) NULL ,

'Age' INT NULL ,

'Salary' INT NULL ,

PRIMARY KEY ('ID') );

Insert one record in the Employee table using below Query

insert into 'SpringJDBC'.'Employee' value ( 100, 'Employee A' ,23 , 1000 );

19.4 Configure Build Path

Add the spring-jdbc-4.1.1.RELEASE.jar , spring-tx-4.1.1.RELEASE.jar and mysql-connector-java-5.1.18-bin.jar file in build path as described in Chapter 3 and 4.

19.5 Data Source Configuration

DataSource is the database configuration and would be required by template and Dao classes.

To configure Data Source add below configuration in beans.xml file

<bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
           <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
           <property name="url" value="jdbc:mysql://localhost:3306/SpringJDBC"/>
           <property name="username" value="root"/>
           <property name="password" value="password"/>
      </bean>

a) Make sure the id of bean is “dataSource” only because template and DAO classes has a corresponding field.

b) com.mysql.jdbc.Driver is the vendor Driver class name. In our examples we will use MySQL database so we will use mysql driver

c) jdbc:mysql://localhost:3306/SpringJDBC is the URL of the database. localhost:3306 specifies that database server is running on localhost and on 3306 port number. SpringJDBC is the name of Schema.

d) Username and password, specify the username and password to of the database.

19.6 RowMapper Interface

Spring provides a RowMapper interface under org.springframework.jdbc.core.RowMapper package. This interface is used to map a Result set returned by SQL to a custom object per row. The developer needs to implement this interface and implement the actual logic of mapping rows of result set to custom object.

This interface defines one method with the below signature

public Object mapRow(ResultSet resultSet , int rownumber)throws SQLException

19.7 JDBC template API

JDBC template requires DataSource object which can be injected either programmatically or declaratively. We will use declarative way because that is configurable and any change in database configuration will not require code changes.

Following are the key methods of JdbcTemplate class

queryForInt(Sting SQL) – This method returns the integer value so input SQL must return a integer value

Refer below code snippet to understand how we can use this API

int count = jdbcTemplate.queryForInt("select count (*) from employee");

queryForInt(Sting SQL, Object[]) – This method returns the integer value and allow us to bind the query parameters . SQL must return a integer value.

Refer below code snippet to understand how we can use this API

int count = jdbcTemplate.queryForInt("select count (*) from employee where name = ?", new Object[]{"Employee A"});

queryForObject(String sql, Object[], RowMapper)- This method returns one Object and allow us to bind the query parameters . Refer below code snippet to understand how we can use this API

Employee emp = (Employee)jdbcTemplate.queryForObject
("select id, name , age , salary  from Employee  where name = ?", 
new Object[]{"Employee A"},
      new RowMapper(){
      @Override
      public Object mapRow(ResultSet rs, int rowNum)
             throws SQLException {
                 Employee emp = new Employee();
    
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setAge(rs.getInt("age"));
                emp.setSalary(rs.getInt("salary"));
                return emp;
                } 
    }        
);

Note: Above 3 API must return exactly one Object else exception will be raised.

a. query(String sql, Object[], RowMapper)- This method returns a list of Objects and allow us to bind the query parameters . Refer below code snippet to understand how we can use this API

List<Employee> emp = jdbcTemplate.queryForObject
("select id, name , age , salary  from Employee where name = ?  ", 
new Object[]{"Employee A"},
     new RowMapper(){
     @Override
     public Object mapRow(ResultSet rs, int rowNum)
            throws SQLException {             
               Employee emp = new Employee();    
               emp.setId(rs.getInt("id"));
               emp.setName(rs.getString("name"));
               emp.setAge(rs.getInt("age"));
               emp.setSalary(rs.getInt("salary"));
               return emp;
              }            
    }        
);

e. update(String, Object[]) – this API is used to execute insert delete and update SQL statements.

Refer below code snippet.

jdbcTemplate.update("delete from employee where name = ?", new Object[]{"Employee A"});

jdbcTemplate.update("update employee set name = ? where id = ?",

new Object[]{"Employee A", new Integer(100)});

19.8 JdbcTemplate Example

Below example will use the employee table created under springJdbc schema.

a. Employee.java – This is simple POJO class and is the implementation of Employee Object

package jdbc;
public class Employee {

     private  int id;
     private String name;
     private int age;
     private int salary;
     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 int getSalary() {
        return salary;
     }
     public void setSalary(int salary) {
        this.salary = salary;
     }
     @Override
     public String toString() {
     return "Employee [id=" + id + ", name=" + name + ", age=" + age
                + ", salary=" + salary + "]";
     }
}

b. EmployeeRowMapper.java – RowMapper implementation for Employee Object

package jdbc;

import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;
public class EmployeeRowMapper implements RowMapper<Employee> {

       public Employee mapRow(ResultSet rs, int rowNum)
            throws SQLException {
 
             Employee emp = new Employee();
             emp.setId(rs.getInt("id"));
             emp.setName(rs.getString("name"));
             emp.setAge(rs.getInt("age"));
             emp.setSalary(rs.getInt("salary"));             
             return emp;
       }
}

c. EmployeeJdbcTemplate- Template class to implement CRUD operations

package jdbc;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;

public class EmployeeJdbcTemplate {
    private JdbcTemplate jdbcTemplate;
    private DataSource dataSource;

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

    public int  getTotalNumberOfEmployees(){
        String sql = "select count(*) from employee";
        int count = jdbcTemplate.queryForInt(sql);
        return count;
    }

    public Employee getEmployeeById(int id){
         String sql = "select * from employee where id= ?";
         Employee employee = jdbcTemplate.queryForObject(sql, new Object[]{new Integer(id)} , new EmployeeRowMapper());
         return employee;
    }

    public List<Employee> getAllEmployees(){
         String sql = "select * from employee ";
         List<Employee> employee = jdbcTemplate.query(sql,  new EmployeeRowMapper());
         return employee;
    }

    public Employee updateEmployee(String name, int id){
         String sql ="update employee set name = ? where id = ?";
         jdbcTemplate.update(sql, new Object[]{name,new Integer(id)}); 
         Employee emp = getEmployeeById(id);
         return emp;
     }

    public void insertEmployee(int id, String name , int age, int salary){
         String sql = "insert into Employee value (?,?,?,? )";
        jdbcTemplate.update(sql, new Object[]{new Integer(id), name,new Integer(age),new Integer(salary) }); 
    }

    public void deleteEmployee(int id){
         String sql ="delete from employee where id = ?";
        jdbcTemplate.update(sql, new Object[]{new Integer(id)}); 
    }
}

d. Beans.xml

<?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-3.0.xsd
       http://www.springframework.org/schema/context
       http://www.springframework.org/schema/context/spring-context-3.0.xsd
       http://www.springframework.org/schema/aop 
       http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

      <bean id="dataSource"
         class="org.springframework.jdbc.datasource.DriverManagerDataSource">
            <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
            <property name="url" value="jdbc:mysql://localhost:3306/SpringJDBC"/>
            <property name="username" value="root"/>
            <property name="password" value="password"/>
      </bean>

      <bean id="employeeJdbcTemplate" 
         class="jdbc.EmployeeJdbcTemplate">
            <property name="dataSource"  ref="dataSource" />    
      </bean>
</beans>

e. TestEmployeeJdbcTemplate.java

package jdbc;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestEmployeeJdbcTemplate {
 
    public static void main(String args[]){
         ApplicationContext context = 
             new ClassPathXmlApplicationContext("beans.xml");
             EmployeeJdbcTemplate employeeJdbcTemplate = 
                (EmployeeJdbcTemplate)context.getBean("employeeJdbcTemplate");

           // Display current state of Employee table 
           List<Employee> emp = employeeJdbcTemplate.getAllEmployees();
           System.out.println("Current State of employee table -");
           System.out.println(emp); 

           // insert new employee
           employeeJdbcTemplate.insertEmployee(200, "Employee B", 31, 2000);

           // Display inserted employee  
           Employee insertedEmployee = employeeJdbcTemplate.getEmployeeById(200);
           System.out.println("Inserted Employee Information from Employee Table - ");
           System.out.println(insertedEmployee);

           // update employee
           Employee updatedEmployee =         employeeJdbcTemplate.updateEmployee("Employee B updated ", 200);
           System.out.println("Updated Employee Information from Employee Table - ");
           System.out.println(updatedEmployee);
        
           //delete employee    
           employeeJdbcTemplate.deleteEmployee(100);

           // display total number of employees        
           int count = employeeJdbcTemplate.getTotalNumberOfEmployees();        
           System.out.println("Total number of Employees in employee table ");
           System.out.println(count);

           emp = employeeJdbcTemplate.getAllEmployees();
           System.out.println("Current State of employee table -");
           System.out.println(emp);
    }
}

f. Run TestEmployeeJdbcTemplate.java

19.9 JdbcDaoSupport

Spring provides JdbcDaoSupport classes which internally handles the template creation part and requires only datasource. Custom Dao classes need to extend JdbcDaoSupport class and can get the template object from getjdbcTemplate() method provided by JdbcDaoSupport class.

Lets create following classes which will use Employee and EmployeeRowMapper classes created in section 19.8.

a. EmployeeJdbcDaoSupport.java

package jdbc;

import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

public class EmployeeJdbcDaoSupport extends JdbcDaoSupport {
 
    public int  getTotalNumberOfEmployees(){
        String sql = "select count(*) from employee";
        int count = getJdbcTemplate().queryForInt(sql);
        return count;
    }

    public Employee getEmployeeById(int id){
        String sql = "select * from employee where id= ?";
        Employee employee = 
           getJdbcTemplate().queryForObject(sql, new Object[]{new Integer(id)} , new EmployeeRowMapper());
        return employee;
    }

    public List<Employee> getAllEmployees(){
        String sql = "select * from employee ";
        List<Employee> employee = 
              getJdbcTemplate().query(sql,  new EmployeeRowMapper());
        return employee;
    }

    public Employee updateEmployee(String name, int id){
        String sql ="update employee set name = ? where id = ?";
        getJdbcTemplate().update(sql, new Object[]{name,new Integer(id)}); 
        Employee emp = getEmployeeById(id);
        return emp;
    }
 
    public void insertEmployee(int id, String name , int age, int salary){
        String sql = "insert into Employee value (?,?,?,? )";
        getJdbcTemplate().update(sql, new Object[]{new Integer(id), name,new Integer(age),new Integer(salary) }); 
    }
    
    public void deleteEmployee(int id){
         String sql ="delete from employee where id = ?";
         getJdbcTemplate().update(sql, new Object[]{new Integer(id)});
    }

}

b. beans.xml

<?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-3.0.xsd
        http://www.springframework.org/schema/context
        http://www.springframework.org/schema/context/spring-context-3.0.xsd
        http://www.springframework.org/schema/aop 
        http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

   <bean id="dataSource"
       class="org.springframework.jdbc.datasource.DriverManagerDataSource">
       <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
       <property name="url" value="jdbc:mysql://localhost:3306/SpringJDBC"/>
       <property name="username" value="root"/>
       <property name="password" value="password"/>
   </bean>

   <bean id="employeeJdbcDaoSupport" 
      class="jdbc.EmployeeJdbcDaoSupport">
     <property name="dataSource"  ref="dataSource" />    
   </bean>
</beans>

c. TestEmployeeJdbcDaoSupport.java

package jdbc;

import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestEmployeeJdbcDaoSupport {

import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationCont

public class TestEmployeeJdbcDaoSupport {
     
       public static void main(String args[]){
            ApplicationContext context = 
                new ClassPathXmlApplicationContext("beans.xml");        
            EmployeeJdbcDaoSupport employeeJdbcTemplate = 
                (EmployeeJdbcDaoSupport)context.getBean("employeeJdbcDaoSupport");
 
            // Display current state of Employee table         
            List<Employee> emp = employeeJdbcTemplate.getAllEmployees();
            System.out.println("Current State of employee table -");
            System.out.println(emp);

            // insert new employee    
            employeeJdbcTemplate.insertEmployee(200, "Employee B", 31, 2000);

            // Display inserted employee        
            Employee insertedEmployee = employeeJdbcTemplate.getEmployeeById(200);
            System.out.println("Inserted Employee Information from Employee Table - ");
            System.out.println(insertedEmployee);

            // update employee        
            Employee updatedEmployee = employeeJdbcTemplate.updateEmployee("Employee B updated ", 200);
            System.out.println("Updated Employee Information from Employee Table - ");        
            System.out.println(updatedEmployee);
  
            //delete employee        
            employeeJdbcTemplate.deleteEmployee(100);

            // display total number of employees
            int count = employeeJdbcTemplate.getTotalNumberOfEmployees();
            System.out.println("total number of employees : " + count);        
            emp = employeeJdbcTemplate.getAllEmployees();
            System.out.println("Current State of employee table -");
            System.out.println(emp); 
     }
}

19.10 Calling Stored Procedures

Spring JDBC framework provides SimpleJdbcCall class to call the stored procedures and functions and supports IN and OUT parameters as well.

a. Lets create a product table with below create table statement.

CREATE TABLE 'products' (

'id' int(11) NOT NULL,

'name' varchar(45) DEFAULT NULL,

'brand' varchar(45) DEFAULT NULL,

'price' int(11) DEFAULT NULL,

PRIMARY KEY ('id')

) ;

b. Lets create a Stored procedure to insert a record in products table .

DELIMITER $$

CREATE PROCEDURE 'springJdbc'. 'insert_product_procedure'(

IN product_id INTEGER,

IN product_name VARCHAR(45),

IN brand VARCHAR(45),

IN price INTEGER)

BEGIN

insert into products values (product_id,product_name,brand,price);

END$$

DELIMITER ;

c. Create Product.java

package jdbc;

public class Product {
 
       private int productId;
       private String productName;
       private int price;
       private String brand;
       public int getProductId() {
           return productId;
       }
       public void setProductId(int productId) {
          this.productId = productId;
       }
       public String getProductName() {
          return productName;
       }
       public void setProductName(String productName) {
          this.productName = productName;
       }
       public int getPrice() {
          return price;
       }
       public void setPrice(int price) {
           this.price = price;
       }
       public String getBrand() {
           return brand;
       }
       public void setBrand(String brand) {
          this.brand = brand;
       }
       @Override
       public String toString() {
          return "Product [productId=" + productId + ", productName="
                + productName + ", price=" + price + ", brand=" + brand + "]";
       }

}

d. Create ProductMapper.java

package jdbc;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.RowMapper;

public class ProductRowMapper implements RowMapper<Product> {
    public Product mapRow(ResultSet rs, int rowNum)
            throws SQLException {

             Product product = new Product();

             product.setProductId(rs.getInt("id"));
             product.setProductName(rs.getString("name"));
             product.setBrand(rs.getString("brand"));
             product.setPrice(rs.getInt("price"));

            return product;
    }
}

e. Create ProductJdbcCall.java

package jdbc;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcCall;

public class ProductJdbcCall  {
    private JdbcTemplate jdbcTemplate;    
    private SimpleJdbcCall jdbcCall;

    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.jdbcCall= new SimpleJdbcCall(dataSource).withProcedureName("insert_product_procedure");
    }

    public List<Product> getAllProducts(){
        String sql = "select * from products";
        List<Product> products = jdbcTemplate.query(sql, new ProductRowMapper()); 
        return products;
    }

    public void  callProcedure(int id,String name , String brand , int price ){
        Map<String,Object> inputMap = new HashMap<String,Object>();
        inputMap.put("product_id",id);
        inputMap.put("product_name", name);
        inputMap.put("price", price);
        inputMap.put("brand", brand);        
        jdbcCall.execute(inputMap);
    }

}

e. Beans.xml

<?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-3.0.xsd
           http://www.springframework.org/schema/context
           http://www.springframework.org/schema/context/spring-context-3.0.xsd
           http://www.springframework.org/schema/aop 
           http://www.springframework.org/schema/aop/spring-aop-3.0.xsd">

    <bean id="dataSource"
        class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="jdbc:mysql://localhost:3306/SpringJDBC"/>
        <property name="username" value="root"/>
        <property name="password" value="password"/>
    </bean>

    <bean id="productJdbcCall" 
        class="jdbc.ProductJdbcCall">
        <property name="dataSource"  ref="dataSource" />    
   </bean>

</beans>

g. Create TestProductJdbcCall.java

package jdbc;

import java.util.List;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

public class TestProductJdbcCall {    
      public static void main(String args[]){
            ApplicationContext context = 
                new ClassPathXmlApplicationContext("beans.xml");        
            ProductJdbcCall productJdbcCall = (ProductJdbcCall)context.getBean("productJdbcCall");        
            productJdbcCall.callProcedure(104,"Product A","Brand A",230);        
            productJdbcCall.callProcedure(105,"Product B","Brand B",130);        
            List<Product> products = productJdbcCall.getAllProducts();
            System.out.println(products);
     }
}

h. Run TestProductJdbcCall.java

Like us on Facebook

Wideskills

Wideskills

C
H
A
P
T
E
R
S Spring Tutorial

O
T
H
E
R

S
U
B
J
E
C
T
S Subjects

19 - Spring JDBC

19.1 Overview

19.2 Spring Data Access Approaches

19.3 Database

19.4 Configure Build Path

19.5 Data Source Configuration

19.6 RowMapper Interface

19.7 JDBC template API

19.8 JdbcTemplate Example

19.9 JdbcDaoSupport

19.10 Calling Stored Procedures

Like us on Facebook

Search form

19 - Spring JDBC

19.1 Overview

19.2 Spring Data Access Approaches

19.3 Database

19.4 Configure Build Path

19.5 Data Source Configuration

19.6 RowMapper Interface

19.7 JDBC template API

19.8 JdbcTemplate Example

19.9 JdbcDaoSupport

19.10 Calling Stored Procedures

Like us on Facebook