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:

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:

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 :

Proving CRUD services to the 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;
    }

}

Done!

Let’s 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");

    }

}

References:

• Create MySQL table
• JDBCTemplate

Happy Learning 🙂