Java Tutorial: Spring JdbcTemplate + JdbcDaoSupport example

Spring provides a simplification in handling database access with the Spring JDBC Template.
The Spring JDBC Template has the following advantages compared with standard JDBC.

The Spring JDBC template allows to clean-up the resources automatically, e.g. release the database connections.
The Spring JDBC template converts the standard JDBC SQLExceptions into RuntimeExceptions. This allows the programmer to react more flexible to the errors. The Spring JDBC template converts also the vendor specific error messages into better understandable error messages.

The Spring JDBC template offers several ways to query the database. queryForList() returns a list of HashMaps. The name of the column is the key in the hashmap for the values in the table.
More convenient is the usage of ResultSetExtractor or RowMapper which allows to translates the SQL result direct into an object (ResultSetExtractor) or a list of objects (RowMapper). Both these methods will be demonstrated in the coding.

Example:

Following is the content for 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/maven-v4_0_0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>com.mahesh.common</groupId>
<artifactId>SpringJDBC-DAOSupport</artifactId>
<packaging>jar</packaging>
<version>1.0-SNAPSHOT</version>
<name>SpringJDBC-DAOSupport</name>
<url>http://maven.apache.org</url>

<dependencies>
    <dependency>
      <groupId>junit</groupId>
      <artifactId>junit</artifactId>
      <version>3.8.1</version>
      <scope>test</scope>
    </dependency>

    
    <dependency>
       <groupId>org.springframework</groupId>
       <artifactId>spring</artifactId>
       <version>2.5.6</version>
    </dependency>

    
    <dependency>
       <groupId>mysql</groupId>
       <artifactId>mysql-connector-java</artifactId>
       <version>5.1.9</version>
    </dependency>

</dependencies>
</project>

Following is the content for CustomerDAO.java file

package com.mahesh.customer.dao;

import java.util.List;
import com.mahesh.customer.model.Customer;

public interface CustomerDAO
{
    public void insert(Customer customer);

    public void insertNamedParameter(Customer customer);

    public void insertBatch(List<Customer> customer);

    public void insertBatchNamedParameter(List<Customer> customer);

    public void insertBatchNamedParameter2(List<Customer> customer);

    public void insertBatchSQL(String sql);

    public Customer findByCustomerId(int custId);

    public Customer findByCustomerId2(int custId);

    public List<Customer> findAll();

    public List<Customer> findAll2();

    public String findCustomerNameById(int custId);

    public int findTotalCustomer();

}

Following is the content for SimpleJdbcCustomerDAO.java

package com.mahesh.customer.dao.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;

import com.mahesh.customer.dao.CustomerDAO;
import com.mahesh.customer.model.Customer;
import com.mahesh.customer.model.CustomerParameterizedRowMapper;

public class SimpleJdbcCustomerDAO extends SimpleJdbcDaoSupport implements CustomerDAO
{
    //insert example
    public void insert(Customer customer){

        String sql = "INSERT INTO CUSTOMER " +
            "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

        getSimpleJdbcTemplate().update(sql, customer.getCustId(),
                    customer.getName(),customer.getAge()
        );

    }

    //insert with named parameter
    public void insertNamedParameter(Customer customer){

        String sql = "INSERT INTO CUSTOMER " +
            "(CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)";

        Map<String, Object> parameters = new HashMap<String, Object>();
        parameters.put("custId", customer.getCustId());
        parameters.put("name", customer.getName());
        parameters.put("age", customer.getAge());

        getSimpleJdbcTemplate().update(sql, parameters);

    }


    //insert batch example
    public void insertBatch(final List<Customer> customers){

        String sql = "INSERT INTO CUSTOMER " +
            "(CUST_ID, NAME, AGE) VALUES (?, ?, ?)";

        List<Object[]> parameters = new ArrayList<Object[]>();
        for (Customer cust : customers) {
            parameters.add(new Object[] {cust.getCustId(), cust.getName(), cust.getAge()});
        }
        getSimpleJdbcTemplate().batchUpdate(sql, parameters);

    }

    //insert batch with named parameter
    public void insertBatchNamedParameter(final List<Customer> customers){

        String sql = "INSERT INTO CUSTOMER " +
            "(CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)";

        List<SqlParameterSource> parameters = new ArrayList<SqlParameterSource>();
        for (Customer cust : customers) {

            parameters.add(new BeanPropertySqlParameterSource(cust));

        }
        getSimpleJdbcTemplate().batchUpdate(sql,
                parameters.toArray(new SqlParameterSource[0]));
    }

    //insert batch with named parameter
    public void insertBatchNamedParameter2(final List<Customer> customers){

        SqlParameterSource[] params = SqlParameterSourceUtils.createBatch(customers.toArray());
        getSimpleJdbcTemplate().batchUpdate(
                "INSERT INTO CUSTOMER (CUST_ID, NAME, AGE) VALUES (:custId, :name, :age)",
                params);

    }

    //insert batch example with SQL
    public void insertBatchSQL(final String sql){

        getJdbcTemplate().batchUpdate(new String[]{sql});

    }

    //query single row with ParameterizedRowMapper
    public Customer findByCustomerId(int custId){

        String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";

        Customer customer = getSimpleJdbcTemplate().queryForObject(
                sql, new CustomerParameterizedRowMapper(), custId);

        return customer;
    }

    //query single row with ParameterizedBeanPropertyRowMapper (Customer.class)
    public Customer findByCustomerId2(int custId){

        String sql = "SELECT * FROM CUSTOMER WHERE CUST_ID = ?";

        Customer customer = getSimpleJdbcTemplate().queryForObject(
                sql,ParameterizedBeanPropertyRowMapper.newInstance(Customer.class), custId);

        return customer;
    }

    //query mutiple rows with ParameterizedBeanPropertyRowMapper (Customer.class)
    public List<Customer> findAll(){

        String sql = "SELECT * FROM CUSTOMER";

        List<Customer> customers =
            getSimpleJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));

        return customers;
    }

    //query mutiple rows with ParameterizedBeanPropertyRowMapper (Customer.class)
    public List<Customer> findAll2(){

        String sql = "SELECT * FROM CUSTOMER";

        List<Customer> customers =
            getSimpleJdbcTemplate().query(sql, ParameterizedBeanPropertyRowMapper.newInstance(Customer.class));

        return customers;
    }

    public String findCustomerNameById(int custId){

        String sql = "SELECT NAME FROM CUSTOMER WHERE CUST_ID = ?";

        String name = getSimpleJdbcTemplate().queryForObject(
                sql, String.class, custId);

        return name;

    }

    public int findTotalCustomer(){

        String sql = "SELECT COUNT(*) FROM CUSTOMER";

        int total = getSimpleJdbcTemplate().queryForInt(sql);

        return total;
    }
}

Following is the content for Customer.java

package com.mahesh.customer.model;

import java.io.Serializable;

public class Customer implements Serializable
{
    long custId;
    String name;
    int age;

    public Customer(){
    }

    public Customer(long custId, String name, int age) {
        this.custId = custId;
        this.name = name;
        this.age = age;
    }

    public long getCustId() {
        return custId;
    }
    public void setCustId(long custId) {
        this.custId = custId;
    }
    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;
    }

    @Override
    public String toString() {
        return "Customer [age=" + age + ", custId=" + custId + ", name=" + name
                + "]";
    }

}

Following is the content for CustomerParameterizedRowMapper.java

package com.mahesh.customer.model;

import java.sql.ResultSet;
import java.sql.SQLException;

import org.springframework.jdbc.core.simple.ParameterizedRowMapper;

public class CustomerParameterizedRowMapper implements ParameterizedRowMapper<Customer>
{
    public Customer mapRow(ResultSet rs, int rowNum) throws SQLException {
        Customer customer = new Customer();
        customer.setCustId(rs.getInt("CUST_ID"));
        customer.setName(rs.getString("NAME"));
        customer.setAge(rs.getInt("AGE"));
        return customer;
    }

}

Following is the content for Spring-Customer.xml

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">


    <bean id="customerSimpleDAO" class="com.mahesh.customer.dao.impl.SimpleJdbcCustomerDAO">
        <property name="dataSource" ref="dataSource" />
    </bean>

</beans>

Following is the content for Spring-Datasource.xml

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.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/springjdbctemplate" />
        <property name="username" value="root" />
        <property name="password" value="root" />
    </bean>

</beans>

Following is the content for Spring-Module.xml

<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <import resource="database/Spring-Datasource.xml"/>
    <import resource="customer/Spring-Customer.xml"/>

</beans>

To run the application following is the conent SimpleJdbcTemplateApp.java

package com.mahesh.common;

import java.util.ArrayList;
import java.util.List;

import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.mahesh.customer.dao.CustomerDAO;
import com.mahesh.customer.model.Customer;

public class SimpleJdbcTemplateApp
{
    public static void main( String[] args )
    {
        //if you have time,
        //it's better to create an unit test rather than testing like this :)

        ApplicationContext context =
            new ClassPathXmlApplicationContext("Spring-Module.xml");

        CustomerDAO customerSimpleDAO = (CustomerDAO) context.getBean("customerSimpleDAO");
        Customer customer1 = new Customer(1, "mahesh1",21);
        Customer customer3 = new Customer(2, "mahesh2",22);
        Customer customer2 = new Customer(3, "mahesh3",23);

        List<Customer>customers = new ArrayList<Customer>();
        customers.add(customer1);
        customers.add(customer2);
        customers.add(customer3);

        customerSimpleDAO.insertBatch(customers);

        String sql = "UPDATE CUSTOMER SET NAME ='BATCHUPDATE'";
        customerSimpleDAO.insertBatchSQL(sql);

        System.out.println("Batch Insert Done!");

        Customer customerA = customerSimpleDAO.findByCustomerId(1);
        System.out.println("Customer A : " + customerA);

        Customer customerB = customerSimpleDAO.findByCustomerId2(1);
        System.out.println("Customer B : " + customerB);

        List<Customer> customerAs = customerSimpleDAO.findAll();
        for(Customer cust: customerAs){
            System.out.println("Customer As : " + customerAs);
        }

        List<Customer> customerBs = customerSimpleDAO.findAll2();
        for(Customer cust: customerBs){
            System.out.println("Customer Bs : " + customerBs);
        }

        String customerName = customerSimpleDAO.findCustomerNameById(1);
        System.out.println("Customer Name : " + customerName);

        int total = customerSimpleDAO.findTotalCustomer();
        System.out.println("Total : " + total);


        Customer customer4 = new Customer(4, "mahesh4",24);
        Customer customer5 = new Customer(5, "mahesh5",25);
        Customer customer6 = new Customer(6, "mahesh6",26);

        List<Customer>customers2 = new ArrayList<Customer>();
        customers2.add(customer4);
        customers2.add(customer5);
        customers2.add(customer6);

        customerSimpleDAO.insertBatchNamedParameter2(customers2);

        //delete all records
        customerSimpleDAO.insertBatchSQL("DELETE FROM CUSTOMER");
        System.out.println("Records Deleted!");
    }
}

OutPut:

Batch Insert Done!
Customer A : Customer [age=21, custId=1, name=BATCHUPDATE]
Customer B : Customer [age=21, custId=1, name=BATCHUPDATE]
Customer As : [Customer [age=21, custId=1, name=BATCHUPDATE], Customer [age=22, custId=2, name=BATCHUPDATE], Customer [age=23, custId=3, name=BATCHUPDATE]]
Customer As : [Customer [age=21, custId=1, name=BATCHUPDATE], Customer [age=22, custId=2, name=BATCHUPDATE], Customer [age=23, custId=3, name=BATCHUPDATE]]
Customer As : [Customer [age=21, custId=1, name=BATCHUPDATE], Customer [age=22, custId=2, name=BATCHUPDATE], Customer [age=23, custId=3, name=BATCHUPDATE]]
Customer Bs : [Customer [age=21, custId=1, name=BATCHUPDATE], Customer [age=22, custId=2, name=BATCHUPDATE], Customer [age=23, custId=3, name=BATCHUPDATE]]
Customer Bs : [Customer [age=21, custId=1, name=BATCHUPDATE], Customer [age=22, custId=2, name=BATCHUPDATE], Customer [age=23, custId=3, name=BATCHUPDATE]]
Customer Bs : [Customer [age=21, custId=1, name=BATCHUPDATE], Customer [age=22, custId=2, name=BATCHUPDATE], Customer [age=23, custId=3, name=BATCHUPDATE]]
Customer Name : BATCHUPDATE
Total : 3
Records Deleted!

Java Tutorial

Monday 13 January 2014

Spring JdbcTemplate + JdbcDaoSupport example