JdbcTemplate

JdbcTemplate

In this tutorial, we are going to discuss JdbcTemplate in the spring framework. In Spring Applications, if we want to use JdbcTemplate [JDBC Module], then we have to use the following steps.

• Create a DAO interface with the required methods.
• Create DAO implementation class with implementation for DAO interface methods.
• In Configuration File, provide configuration for DataSource class, JdbcTemplate class and DAO implementation class.
• Prepare Test Application to access Dao methods.

In Spring configuration file we have to configure DataSource with the following properties.

• driverClassName
• url
• username
• Password

In Spring applications, to configure DataSource, Spring has provided a separate predefined Datasource class in the form of “org.springframework.jdbc.datasource. DriverManagerDataSource”, it is not suggestible for production mode. It is suggestible for the testing mode of our applications. In spring applications, it is always suggestible to use third-party Connection Pooling mechanisms like DBCP, C3P0, Proxool, etc.

JdbcTemplate class provides the basic environment to interact with Database like Loading Driver class, Getting Connection between Java application and DB, Creating Statement, PreparedStatement, and CallableStatement and closing the connection with the help of the provided Datasource and JdbcTemplate class has provided the following methods to execute SQL Queries.

1. For Non-Select SQL queries and DML SQL queries

public int update(String query)

2. For DDL SQL Queries

public void execute(String query)

3. For Select SQL queries

public int queryForInt(String query)
public long queryForLong(String query)
public String queryForString(String query)
public Object queryForObject(String query)
public List query(String query)
public List queryForList(String query)
public Map queryForMap(String query)
public RowSet queryForRowSet(String query)

While performing retrieval operations to convert data from ResultSet object[records] to Bean objects, Spring Framework has provided a predefined interface in the form of “org.springframework.jdbc.core.RowMapper”, which contains the following method.

public Object mapRow(ResultSet rs, int rowCount)

E.g

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;

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.dao;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import com.ashok.spring.dto.Student;

/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentDaoImpl implements StudentDao {
   private static Logger logger = LoggerFactory.getLogger(StudentDaoImpl.class.getName());

   private JdbcTemplate jdbcTemplate;
   String status = "";

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

   @Override
   public String add(Student std) {
      try {
         jdbcTemplate.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 = jdbcTemplate.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 {
         jdbcTemplate.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 {
         jdbcTemplate.update("delete from student where sid='" + sid + "'");
         status = "success";
      } catch (Exception e) {
         status = "failure";
         logger.error(e.getMessage(), e);
      }
      return status;
   }
}

package com.ashok.spring.dao;

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<Student> {
   @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;
   }
}

<?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.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;

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/config/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      StudentDao dao = (StudentDao) context.getBean("studentDao");
      
      // ----Inserting Records------
      Student std = new Student();
      std.setSid("S-111");
      std.setSname("Ashok");
      std.setSaddr("Bhimavaram");
      String status = dao.add(std);
      System.out.println("Student Insertion :" + status);
      std.setSid("S-222");
      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-111");
      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-111");
      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-111");
      System.out.println("Student Deletion :"+status);
   }
}

Output

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

In Spring JDBC Applications, we will also use positional parameters [?] in SQL queries that we are providing along with JdbcTemplate class provided query execution methods. If we provide positional parameters in SQL queries, then JdbcTemplate class will use “PreparedStatement” internally to execute SQL query instead of Statement.

To provide values to the Positional parameters in SQL Queries, we have to use Object [] with values as a parameter to all JdbcTemplate class-provided query execution methods.

public int update(String query, Object[] paramValues)
public int queryForInt(String query, Object[] paramValues)
public long queryForLong(String query, Object[] paramValues)
public Object queryForObject(String query, Object[] paramValues, RowMapper rm)

E.g

String query = "insert into student values(?, ?, ?)";
int rowCount = jdbcTemplate.update(query, new Object[]{"S-111", "Ashok Kumar", "Hyd"});

E.g

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.preparedstatement;

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.dao.preparedstatement;

import org.springframework.jdbc.core.JdbcTemplate;

import com.ashok.spring.dto.Student;
/**
 * 
 * @author ashok.mariyala
 *
 */
public class StudentDaoImpl implements StudentDao {
   private JdbcTemplate jdbcTemplate;
   String status = "";

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

   @Override
   public String add(Student std) {
      try {
         String query = "insert into student values(?,?,?)";
         jdbcTemplate.update(query, new Object[] { std.getSid(), std.getSname(), std.getSaddr() });
         status = "success";
      } catch (Exception e) {
         status = "failure";
         e.printStackTrace();
      }
      return status;
   }

   @Override
   public Student search(String sid) {
      Student std = null;
      try {
         std = jdbcTemplate.queryForObject("select * from student where sid=?", new Object[] { sid },
               new StudentMapper());
      } catch (Exception e) {
         e.printStackTrace();
      }
      return std;
   }

   @Override
   public String update(Student std) {
      try {
         jdbcTemplate.update("update student set sname=?, saddr=? where sid=?",
               new Object[] { std.getSname(), std.getSaddr(), std.getSid() });
         status = "success";
      } catch (Exception e) {
         status = "failure";
         e.printStackTrace();
      }
      return status;
   }

   @Override
   public String delete(String sid) {
      try {
         jdbcTemplate.update("delete from student where sid=?", new Object[] { sid });
         status = "success";
      } catch (Exception e) {
         status = "failure";
         e.printStackTrace();
      }
      return status;
   }
}

package com.ashok.spring.dao.preparedstatement;

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;
   }
}

<?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.preparedstatement.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.preparedstatement;

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/preparedstatement/config/applicationContext.xml";
      ApplicationContext context = new ClassPathXmlApplicationContext(configFile);
      StudentDao dao = (StudentDao) context.getBean("studentDao");
      
      // ----Inserting Records------
      Student std = new Student();
      std.setSid("S-1111");
      std.setSname("Ashok");
      std.setSaddr("Bhimavaram");
      String status = dao.add(std);
      System.out.println("Student Insertion :" + status);
   
      std.setSid("S-2222");
      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-1111");
      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-1111");
      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-1111");
      System.out.println("Student Deletion :"+status);
   }
}

Output

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