Spring JDBC CRUD example using JdbcTemplate

  • Facebook
  • Google
  • LinkedIn
  • Twitter

First of all create Maven project SpringJDBC in Eclipse or STS.

Include all dependencies in POM.xml.

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/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>javatutsworld</groupId>
  <artifactId>SpringJDBC</artifactId>
  <version>0.0.1-SNAPSHOT</version>

  <properties>
      <!-- Generic properties -->
      <java.version>1.8</java.version>
      <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
      <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
 
  </properties>
 
  <dependencies>
 
      <!-- Spring framework -->
      <!-- http://mvnrepository.com/artifact/org.springframework/spring-core -->
      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-core</artifactId>
          <version>4.0.0.RELEASE</version>
      </dependency>
 
      <!-- http://mvnrepository.com/artifact/org.springframework/spring-context -->
      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-context</artifactId>
          <version>4.0.0.RELEASE</version>
      </dependency>
 
      <!-- http://mvnrepository.com/artifact/org.springframework/spring-jdbc -->
      <dependency>
          <groupId>org.springframework</groupId>
          <artifactId>spring-jdbc</artifactId>
          <version>4.0.0.RELEASE</version>
      </dependency>
   
      <!-- MySQL database driver -->
      <!-- http://mvnrepository.com/artifact/mysql/mysql-connector-java -->
      <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>5.1.36</version>
      </dependency>
 
 
  </dependencies>  
</project>

Create database table

Product.sql

CREATE TABLE `Product` (
  `P_ID` int(11) unsigned NOT NULL,
  `P_Number` varchar(20) DEFAULT NULL,
  `P_Name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`P_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Create Dao interface and declare methods for crud application.

ProductDao.java

package dao;
import java.util.List;

import pojo.Product;
public interface ProductDao {
  
    //Create new product
    public void save(Product product);
    
    //Read product using product id
    public Product getById(int id);
    
    //Read all product
    public List<Product> getAllProduct();
    
    //Update product
    public void update(Product product);
    
    //Delete product
    public int deleteById(int id);
}

Create Dao implementation class and implement all methods for crud application.

ProductImplDao.java

package impldao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.sql.DataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import dao.ProductDao;
import pojo.Product;
  
public class ProductImplDao implements ProductDao {
 
  private DataSource dataSource;
  private JdbcTemplate jdbcTemplateObject;
  
  public void setDataSource(DataSource dataSource) {
     this.dataSource = dataSource;
     this.jdbcTemplateObject = new JdbcTemplate(dataSource);
  }

  public void save(Product product) {
     String SQL = "insert into product (P_Number, P_Name) values (?, ?)";
     Object[] obj = new Object[]{product.getProducttNumber(), product.getProductName()};
     int checkupdate=jdbcTemplateObject.update( SQL, obj);
     if(checkupdate!=0){
    	 System.out.println("Created Record Product Number = " + product.getProducttNumber() + " Product Name = " + product.getProductName());    
     }
     else{
    	 System.out.println("Record not created");    
     }
  }

  public Product getById(int id) {
     String SQL = "select * from Product where P_ID = ?";
     Product product = jdbcTemplateObject.queryForObject(SQL, new Object[]{id}, new ProductMapper());
     return product;
  }

  public List<Product> getAllProduct() {
     String SQL = "select * from Product";
     List <Product> products = jdbcTemplateObject.query(SQL, new ProductMapper());
     return products;
  }

  public void update(Product product){
     String SQL = "update Product set P_Name = ? where P_ID = ?";
     jdbcTemplateObject.update(SQL, product.getProductName(), product.getProductId());
     System.out.println("Updated product Record with product ID = " + product.getProductId() );
     return;
  }

  public int deleteById(int id){
     String SQL = "delete from Product where P_ID = ?";
     int checkDel=jdbcTemplateObject.update(SQL, id);
     if(checkDel!=0){
       System.out.println("Deleted product Record with product ID = " + id );
     }
     return checkDel;
  }	
}

Create mapper class which implement RowMapper interface.

ProductMapper.java

package impldao;

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

import org.springframework.jdbc.core.RowMapper;

import pojo.Product;

public class ProductMapper implements RowMapper<Product> {
   public Product mapRow(ResultSet rs, int rowNum) throws SQLException {
      Product product = new Product();
      product.setProductId(rs.getInt("P_ID"));
      product.setProducttNumber(rs.getString("P_Number"));
      product.setProductName(rs.getString("P_Name"));
      return product;
   }
}

Create domain pojo class.

Product.java (pojo)

package pojo;

public class Product {
 
  private int productId;
  private String producttNumber;
  private String productName;

  public Product() {
	}
  
public Product(int productId, String producttNumber, String productName) {
	this.productId = productId;
	this.producttNumber = producttNumber;
	this.productName = productName;
}
public int getProductId() {
	return productId;
}
public void setProductId(int productId) {
	this.productId = productId;
}
public String getProducttNumber() {
	return producttNumber;
}
public void setProducttNumber(String producttNumber) {
	this.producttNumber = producttNumber;
}
public String getProductName() {
	return productName;
}
public void setProductName(String productName) {
	this.productName = productName;
}
}

Create bean and configure bean class and mysql database.

Bean.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" 
    xsi:schemaLocation="http://www.springframework.org/schema/beans
    http://www.springframework.org/schema/beans/spring-beans-3.0.xsd ">

   <!-- Initialization for data source -->
   <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/sys"/>
      <property name="username" value="root"/>
      <property name="password" value="admin123"/>
   </bean>

   <!-- Definition for productJDBCTemplate bean -->
   <bean id="productJDBCTemplate" class="impldao.ProductImplDao">
      <property name="dataSource"  ref="dataSource" />    
   </bean>
      
</beans>

Create test main class to check the crud application is working fine.

TestApp.java

package test;

import java.util.List;

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

import impldao.ProductImplDao;
import pojo.Product;

public class TestApp {
   public static void main(String[] args) {
      ApplicationContext context = new ClassPathXmlApplicationContext("Beans.xml");

      ProductImplDao productJDBCTemplate = (ProductImplDao)context.getBean("productJDBCTemplate");
      
      System.out.println("------New Product Creation--------" );
      Product pd = new Product();
      pd.setProducttNumber("001");
      pd.setProductName("Laptop");
      productJDBCTemplate.save(pd);

      System.out.println("------View product Records using id--------" );
      Product product = productJDBCTemplate.getById(1);
      System.out.println("Product ID : " + product.getProductId());
      System.out.println("Product Number : " + product.getProducttNumber());
      System.out.println("Product Name : " + product.getProductName());

      System.out.println("------View all products Records--------" );
      List<Product> products = productJDBCTemplate.getAllProduct();
      for(Product prod:products){
          System.out.println("Product ID : " + prod.getProductId());
          System.out.println("Product Number : " + prod.getProducttNumber());
          System.out.println("Product Name : " + prod.getProductName());
      }
      
      System.out.println("----Updating product Record with product ID = 1 -----" );
      
      productJDBCTemplate.update(product);
      pd.setProductId(1);
      pd.setProductName("Computer");
      
      System.out.println("----Deleting product Record with product ID = 1 -----" );
      
      int checkDel=productJDBCTemplate.deleteById(1);
	  
   }
}

Output:

Mar 26, 2016 1:19:59 PM org.springframework.context.support.AbstractApplicationContext prepareRefresh
INFO: Refreshing org.springframework.context.support.ClassPathXmlApplicationContext@2e817b38: startup date [Sat Mar 26 13:19:59 EDT 2016]; root of context hierarchy
Mar 26, 2016 1:19:59 PM org.springframework.beans.factory.xml.XmlBeanDefinitionReader loadBeanDefinitions
INFO: Loading XML bean definitions from class path resource [Beans.xml]
Mar 26, 2016 1:20:00 PM org.springframework.jdbc.datasource.DriverManagerDataSource setDriverClassName
INFO: Loaded JDBC driver: com.mysql.jdbc.Driver
------New Product Creation--------
Created Record Product Number = 001 Product Name = Laptop
------View product Records using id--------
Product ID : 1
Product Number : 001
Product Name : Laptop
------View all products Records--------
Product ID : 1
Product Number : 001
Product Name : Laptop
Product ID : 2
Product Number : 001
Product Name : Laptop
Product ID : 3
Product Number : 001
Product Name : Laptop
Product ID : 4
Product Number : 001
Product Name : Laptop
Product ID : 5
Product Number : 001
Product Name : Laptop
Product ID : 6
Product Number : 001
Product Name : Laptop
----Updating product Record with product ID = 1 -----
Updated product Record with product ID = 1
----Deleting product Record with product ID = 1 -----
Deleted product Record with product ID = 1
      Advertisements