Spring JdbcTemplate CRUD Application

By | 2018-05-14T03:26:46+00:00 December 25th, 2015|Spring|4 Comments

In this tutorial, I am going to show you how to implement Spring JdbcTemplate Example with all CRUD operations.

What is Spring JdbcTemplate :

  • The JdbcTemplate is a central class in Spring JDBC Framework, it is responsible for the creation of connections and releasing the resources. It is also responsible to perform the basic JDBC workflow, creating statements and executing the statements.

Spring JdbcTemplate Example :

Database :

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 JdbcTemplate

Required Dependencies:

Recommended: SpringBoot With JdbcTemplate CRUD Operations Example

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 :

spring configuration.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

Proving CRUD services to repository.


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

Containing all CRUD operation implementations.


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&amp;amp;amp;lt;Customer&amp;amp;amp;gt;() {
            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);<br />        
System.out.println(count > 0 ? "Deleted Successfully" : "No Records found");

    }

}

Happy Learning 🙂

Download Example

About the Author:

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+ ,

4 Comments

  1. mytechnicalkeeda@gmail.com'
    Yashwant December 27, 2016 at 2:40 pm - Reply

    Nice tutorial 🙂

  2. dhirajmasodgi007@rediffmail.com'
    DKM February 17, 2017 at 9:14 pm - Reply

    Good one for beginners. Simple.

  3. nirala.manoj@gmail.com'
    Manoj Kumar Nirala April 12, 2017 at 9:27 am - Reply

    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

  4. kavita11.kavita22@gmail.com'
    kavita October 5, 2017 at 8:42 am - Reply

    Hi,
    nice example, but where are the view file,
    could please send me the code on this email id : kavita11.kavita22@gmail.com

Leave A Comment