Sunday , June 25 2017
Home / Spring / Spring JdbcTemplate CRUD Application

Spring JdbcTemplate CRUD Application

In this tutorial, we are going to implement Spring JdbcTemplate Example with all CRUD operations. Before going in  to a deep example, in prior we need to understand what is Spring JdbcTemplate ? and How do we use it ? Here is the theory:

The Spring JdbcTemplate is the basic element of spring JDBC abstraction framework, which includes the most common functionalities like creation of connection, creation of statement and execution of statement.

Spring JdbcTemplate provides a huge support of exception handling with the more explanatory manner; that is the JdbcTemplate converts the standard Jdbc exceptions to more explanatory manner to the developers. Those are defined in the org.springframework.dao package.

The Spring JdbcTemplate can be used to execute all types of SQL statements and stored procedure calls. The JdbcTemplate is a central class of spring JDBC abstraction framework is packaged into org.springframework.core package.

It is a non-abstract class and we can be instantiated using any of the following three constructors.

The Spring JdbcTemplate class instances are thread-safe.

Creating JdbcTemplte object :

public JdbcTemplate()

This is used to construct a new JdbcTemplate object. This constructor is provided to allow Java Bean style of instantiation.

public JdbcTemplate(DataSource datasource)

This constructor is used to create a new JdbcTemplate object initializing it with the given DataSource to obtain connections.

public JdbcTemplate(DataSource datasource,boolean lazyInit)

This constructor is used to create a new JdbcTemplate object initializing it with the given DataSource to obtain connections. And the Boolean value describes the lazy initialization of the SQL Exception translator.

By having the above clarification, we can go with the Spring JdbcTemplate example:

Spring JdbcTemplate Example :

Create customer table like below :


CREATE TABLE `customer` (
  `custid` int(11) NOT NULL,
  `custname` varchar(255) DEFAULT NULL,
  `city` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`custid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Project Structure:

Spring JdbcTemplateRequired Dependencies:

pom.xml


<dependencies>
        <!-- Spring and Transactions -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>${spring-framework.version}</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>${spring-framework.version}</version>
        </dependency>
 
        <!-- Spring JDBC Support -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>${spring-framework.version}</version>
        </dependency>
         
        <!-- MySQL Driver -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.0.5</version>
        </dependency>
 
        <!-- Logging with SLF4J & LogBack -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>${slf4j.version}</version>
            <scope>compile</scope>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>${logback.version}</version>
            <scope>runtime</scope>
        </dependency>
    </dependencies>

Spring configuration file :

springconfiguration.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:context="http://www.springframework.org/schema/context"
    xmlns:jdbc="http://www.springframework.org/schema/jdbc"
    xsi:schemaLocation="http://www.springframework.org/schema/jdbc http://www.springframework.org/schema/jdbc/spring-jdbc-4.2.xsd
        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-4.0.xsd">
    <bean id="db" class="com.onlinetutorialspoint.business.DemoBean">
        <property name="customerDAO" ref="customerDAO" />
    </bean>
    <bean id="customerDAO" class="com.onlinetutorialspoint.dao.CustomerDAOImpl">
        <property name="jdbcTemplate" ref="jdbcTemplate" />
    </bean>
    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <constructor-arg ref="dataSource" />
    </bean>
    <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/onlinetutorialspoint"/>
        <property name="username" value="otp"/>
        <property name="password" value="123456"/>
    </bean>
</beans>

Spring Beans :

Business class : DemoBean.java


package com.onlinetutorialspoint.business;

import org.springframework.beans.factory.annotation.Autowired;

import com.onlinetutorialspoint.dao.CustomerDAO;
import com.onlinetutorialspoint.dto.Customer;

public class DemoBean {
    @Autowired
    private CustomerDAO customerDAO;

    public void setCustomerDAO(CustomerDAO customerDAO) {
        this.customerDAO = customerDAO;
    }

    public void insertCustomer(int cid, String custName, String addr) {
        Customer customer = new Customer();
        customer.setCustomerId(cid);
        customer.setCustomerName(custName);
        customer.setCustomerAddress(addr);
        int res = customerDAO.insert(customer);
        System.out.println(res + ": Records inserted");
    }

    public int deleteCustomer(int customerId) {
        return customerDAO.deleteCustomer(customerId);
    }

    public Customer selectCustomer(int customerId) {
        return customerDAO.selectCustomer(customerId);
    }

    public void updateCustomer(Customer customer) {
        customerDAO.updateCustomer(customer);
    }

}

DAO Classes :

CustomerDAO.java


package com.onlinetutorialspoint.dao;

import com.onlinetutorialspoint.dto.Customer;

public interface CustomerDAO {
    Customer selectCustomer(int cistomerId);

    int insert(Customer c);

    int deleteCustomer(int customerId);

    void updateCustomer(Customer customer);
}

CustomerDAOImpl.java


package com.onlinetutorialspoint.dao;

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

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.onlinetutorialspoint.dto.Customer;

public class CustomerDAOImpl implements CustomerDAO {

    private JdbcTemplate jdbcTemplate;

    public JdbcTemplate getJdbcTemplate() {
        return jdbcTemplate;
    }

    public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public int insert(Customer c) {
        int custId = c.getCustomerId();
        String name = c.getCustomerName();
        String address = c.getCustomerAddress();
        int rows = jdbcTemplate.update("insert into customer values(?,?,?)", custId, name, address);

        return rows;
    }

    @Override
    public int deleteCustomer(int customerId) {
        String query = "DELETE from customer where custid=?";
        return jdbcTemplate.update(query, new Object[] { Integer.valueOf(customerId) });

    }

    @Override
    public void updateCustomer(Customer customer) {
        String query = "UPDATE customer SET custname=?,city=? WHERE custid=?";
        jdbcTemplate.update(query,
                new Object[] { 
                        customer.getCustomerName(),customer.getCustomerAddress(), Integer.valueOf(customer.getCustomerId()) 
                        });

    }

    @Override
    public Customer selectCustomer(int customerId) {
        final Customer customer = new Customer();
        String quer = "SELECT * FROM customer WHERE custid='" + customerId+"'";
        return (Customer) jdbcTemplate.query(quer, new ResultSetExtractor<Customer>() {
            public Customer extractData(ResultSet rs) throws SQLException, DataAccessException {
                if (rs.next()) {
                    customer.setCustomerId(rs.getInt(1));
                    customer.setCustomerName(rs.getString(2));
                    customer.setCustomerAddress(rs.getString(3));
                }
                return customer;
            }
        });
    }

}

DTO (Data Transfer Object) Classes :

Customer.java


package com.onlinetutorialspoint.dto;

public class Customer {
    private int customerId;
    private String customerName;
    private String customerAddress;
    public int getCustomerId() {
        return customerId;
    }
    public void setCustomerId(int customerId) {
        this.customerId = customerId;
    }
    public String getCustomerName() {
        return customerName;
    }
    public void setCustomerName(String customerName) {
        this.customerName = customerName;
    }
    public String getCustomerAddress() {
        return customerAddress;
    }
    public void setCustomerAddress(String customerAddress) {
        this.customerAddress = customerAddress;
    }
    
}

Well! its done. Lets test our Spring JdbcTemplate example:

Main.java


import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;

import com.onlinetutorialspoint.business.DemoBean;
import com.onlinetutorialspoint.dto.Customer;

public class Main {

    public static void main(String[] args) {
        ApplicationContext context = new ClassPathXmlApplicationContext("applicationcontext.xml");
        DemoBean demo = (DemoBean) context.getBean("db");

        // Inserting Data into customer table..

        demo.insertCustomer(1, "chandra", "Hyd");

        // Selecting data from customer table
        Customer customer = demo.selectCustomer(1);
        System.out.println("Inserted Customer Name : " + customer.getCustomerName());

        // updating customer data in customer table..
        Customer customer2 = new Customer();
        customer2.setCustomerId(1);
        customer2.setCustomerAddress("Vizag");
        customer2.setCustomerName("shekhar");

        demo.updateCustomer(customer2);

        // Deleting customer data from customer table..

         int count = demo.deleteCustomer(1);
         System.out.println(count > 0 ? "Deleted Successfully" : "No Records found");

    }

}

Download Example

About chandrashekhar

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Check Also

Spring Bean Autowire ByType Example

In this tutorial, we are going to learn about one of the autowire strategy in …

3 comments

  1. dhirajmasodgi007@rediffmail.com'

    Good one for beginners. Simple.

  2. nirala.manoj@gmail.com'
    Manoj Kumar Nirala

    Hi Chandrashekhar, a really good example to start with. Could you please post an example with Spring mvc, Apache Tiles, JdbcTemplate with CRUD operations ?

    Thanks

Leave a Reply

Your email address will not be published. Required fields are marked *