SimpleJdbcTemplate

In this tutorial, we are going to discuss SimpleJdbcTemplate in the spring framework. In the Spring JDBC module, the main intention of SimpleJdbcTemplate class is to support JDK5.0 version features like Auto Boxing, Auto Unboxing, Var-Arg methods.

SimpleJdbcTemplate class was provided in Spring 2.5 version only, and it was deprecated in the later versions Spring 3.x and Spring 4.x, in Spring5.x version, SimpleJdbcTemplate class was removed.

If we want to use SimpleJdbcTemplate class, we have to use Spring 2.5 version jar files in Spring applications. To execute SQL queries, SimpleJdbcTemplate class has provided the following methods.

public Object execute(String sqlQuery)

Note

To use this method we have to get JdbcOperations class by using getJdbcOperations() method.

public int update(String query, Object ... params)
public Object queryForInt(String query, Object ... params)
public Object queryForLong(String query, Object ... params)
public Object query(String query, Object ... params)
public Object queryForObject(String query,Object ... params)

In the case of SimpleJdbcTemplate class, we have to use “ParameterizedRowMapper” in place of the RowMapper interface to perform retrieval operations.

DAO Support Classes

In Spring JDBC, we have to prepare DAO implementation classes with XXXTemplate property and the corresponding setXXX() method to inject XXXTemplate class. In Spring JDBC applications, if we want to get XXXTemplate classes without declaring Template properties and corresponding setXXX() methods, we must use DAO Support classes provided Spring JDBC module.

There are three types of DAOSupport classes in order to get Template object in DAO classes.

JdbcDaoSupport
NamedParameterJdbcDaoSupport
SimpleJdbcDaoSupport

Where JdbcDaoSupport class will provide JdbcTemplate reference in DAO classes by using the following method.

public JdbcTemplate getJdbcTemplate()

Where NamesParameterJdbcDaoSupport class will provide NamedParameterJdbcTempate reference in DAO classes by using the following method.

public NamedParameterJdbcTemplate getNamedparameterJdbctemplate()

Where SimpleJdbcDaoSupport class is able to provide SimpleJdbctemplate reference in DAO class by using the following method.

public SimpleJdbcTemplate getSimpleJdbcTemplate()

E.g

package com.ashok.spring.dao.jdbcdaosupport;

import org.springframework.jdbc.core.JdbcTemplate;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public interface StudentDao {
   public void setJdbcTemplate(JdbcTemplate jdbcTemplate);
   public String add(Student std);
   public Student search(String sid);
   public String update(Student std);
   public String delete(String sid);
}

package com.ashok.spring.dto;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class Student {
   private String sid;
   private String sname;
   private String saddr;

   public String getSid() {
      return sid;
   }

   public void setSid(String sid) {
      this.sid = sid;
   }

   public String getSname() {
      return sname;
   }

   public void setSname(String sname) {
      this.sname = sname;
   }

   public String getSaddr() {
      return saddr;
   }

   public void setSaddr(String saddr) {
      this.saddr = saddr;
   }
}

package com.ashok.spring.dao.jdbcdaosupport;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.ashok.kafka.ConsumerDemoAssignSeek;
import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentDaoImpl extends JdbcDaoSupport implements StudentDao {
   private static Logger logger = LoggerFactory.getLogger(ConsumerDemoAssignSeek.class.getName());

   String status = "";

   @Override
   public String add(Student std) {
      try {
         getJdbcTemplate().update("insert into student values('" + std.getSid() + "','" + std.getSname() + "','" + std.getSaddr() + "')");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }

   @Override
   public Student search(String sid) {
      Student std = null;
      try {
         std = getJdbcTemplate().queryForObject("select * from student where sid='" + sid + "'", new StudentMapper());
      } catch (Exception e) {
         logger.error(e.getMessage(), e);
      }
      return std;
   }

   @Override
   public String update(Student std) {
      try {
         getJdbcTemplate().update("update student set sname='" + std.getSname() + "',saddr='" + std.getSaddr() + "' where sid='" + std.getSid() + "'");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }

   @Override
   public String delete(String sid) {
      try {
         getJdbcTemplate().update("delete from student where sid='" + sid + "'");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }
}

<?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"
   xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/context
http://www.springframework.org/schema/context/spring-context.xsd">
   <bean id="studentDao" class="com.ashok.spring.dao.jdbcdaosupport.StudentDaoImpl">
      <property name="jdbcTemplate" ref="jdbcTemplate" />
   </bean>
   <bean id="jdbcTemplate"
      class="org.springframework.jdbc.core.JdbcTemplate">
      <property name="dataSource" ref="dataSource" />
   </bean>
   <bean id="dataSource"
      class="org.springframework.jdbc.datasource.DriverManagerDataSource">
      <property name="driverClassName" value="com.mysql.cj.jdbc.Driver" />
      <property name="url" value="jdbc:mysql://localhost:3306/student" />
      <property name="username" value="root" />
      <property name="password" value="ashok" />
   </bean>
</beans>

package com.ashok.spring.dao.jdbcdaosupport;

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

import org.springframework.jdbc.core.RowMapper;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentMapper implements RowMapper {
   @Override
   public Student mapRow(ResultSet rs, int rowNo) throws SQLException {
      Student std = new Student();
      std.setSid(rs.getString("SID"));
      std.setSname(rs.getString("SNAME"));
      std.setSaddr(rs.getString("SADDR"));
      return std;
   }
}

package com.ashok.spring.dao.jdbcdaosupport;

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

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class TestSpringDaoApplication {
   public static void main(String[] args) {
      String configFile = "/com/ashok/spring/dao/jdbcdaosupport/config/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      StudentDao dao = (StudentDao) context.getBean("studentDao");
      
      // ----Inserting Records------
      Student std = new Student();
      std.setSid("S-555");
      std.setSname("Ashok");
      std.setSaddr("Bhimavaram");
      String status = dao.add(std);
      System.out.println("Student Insertion :" + status);
      std.setSid("S-666");
      std.setSname("Vinod Kumar");
      std.setSaddr("Banglore");
      status = dao.add(std);
      System.out.println("Student Insertion :" + status);
      System.out.println();
      
      // ----Retriving Record-----
      Student std1 = dao.search("S-555");
      if (std1 == null) {
         System.out.println("Student Search Status :NotExisted");
      } else {
         System.out.println("Student Details");
         System.out.println("--------------------");
         System.out.println("Student Id :" + std1.getSid());
         System.out.println("Student Name :" + std1.getSname());
         System.out.println("Student Address :" + std1.getSaddr());
      }
      System.out.println();
      
      // ----Updating a Record------
      std.setSid("S-555");
      std.setSname("Ashok Kumar");
      std.setSaddr("Bhimavaram");
      status = dao.update(std);
      System.out.println("Student Updation :" + status);
      System.out.println();
      
      //----Deleting a record-----
      status = dao.delete("S-555");
      System.out.println("Student Deletion :"+status);
   }
}

Output

Student Insertion :success
Student Insertion :success
Student Details
--------------------
Student Id :S-555
Student Name :Ashok
Student Address :Bhimavaram
Student Updation: success
Student Deletion: success

SimpleJdbcTemplate