Spring JDBC Template with Spring MVC - Example Hello World Tutorial

In this particular blog we will discuss about Spring Jdbc template and its features. We will cover all crud operations here with a Spring MVC application.


Why to use Spring JDBC Template

Before we start coding lets take a brief introduction to why do we need to adopt spring jdbc template instead of traditional JDBC programming or ORM like hibernate ?. The answer to this question is that to deal with traditional jdbc programming is a pain for developers as we need to code more and manage a number of headache regarding connection management and writing same code again and again. On the other hand ORM tools are good but we needs to deal with a heavy part of configuration, we also need to add a number of external jars to code in orms. JDBC Template on the other hand provides a new and efficient mechanism to deal with database, we dont need to bother about any heavy xml configurations like orm's do or adding same connection code again and again as in traditional jdbc programming. In Jdbc template we puts the connection thing in one place and dont even need to add external jar files. Instead of this JDBC template cleans up the resources automatically by releasing the db connection. Jdbc template also provides a better error detection mechanism by splitting the JDBC SQLExceptions into RuntimeExceptions. So here is a sample application to deal with it, atleast give it a chance to make your work easier and more efficient.


Project Structure

Before we start lets take a look at overall project structure for today's discussion.



Libraries Used

Here is the complete list of jar files needed to start working with JDBC template in a Spring MVC application.



Database

This is the sample database that we have used to get a connection and create a table, execute the code below in your MySql query editor.
CREATE DATABASE IF NOT EXISTS `userdb` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `userdb`;

-- Dumping data for table userdb.user: ~14 rows (approximately)
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
REPLACE INTO `user` (`user_id`, `first_name`, `last_name`, `gender`, `city`) VALUES
(906, 'Ankush', 'Thakur', 'male', 'gurgaon'),
(907, 'Anamika', 'Singh', 'female', 'meerut'),
(908, 'Shweta', 'Gupta', 'female', 'gurgaon'),
(909, 'Rajesh', 'Chauhan', 'male', 'noida'),
(911, 'Andrew', 'Symonds', 'male', 'delhi');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
/*!40014 SET FOREIGN_KEY_CHECKS=1 */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;


Spring JDBC Template Example

To start with spring jdbc template all you need to do is injecting ther datasource bean to dao class and everything will be done to use on the fly.

WebContent\WEB-INF\spring-servlet.xml

  
  
  
   
   
  
   
   
  
   
   
    
    
   
  
   
   
   
  
   
   
    
    
    
    
   


src\com\beingjavaguys\dao\UserDao.java

This is a simple java interface containing unimplemented methods to perform insert, update and delete functionality in DB.
package com.beingjavaguys.dao;  
/** 
 * 
 * @author Nagesh Chauhan 
 */  
  
import java.util.List;  
import com.beingjavaguys.domain.User;  
  
public interface UserDao {  
 public void insertData(User user);  
 public List getUserList();  
 public void updateData(User user);  
 public void deleteData(String id);  
 public User getUser(String id);  
  
} 


src\com\beingjavaguys\dao\UserDaoImpl.java

This class represents actual coding to deal with jdbc template, we have autowired datasource bean here and passes it to get an object of JdbcTemplate class on. Now we will call appropriate methods on this JdbcTemplate object to manuplate data in DB. jdbcTemplate.query(sql, new UserRowMapper()) method will use the String SQL to query the database and will loop through each "row" in the ResultSet.
package com.beingjavaguys.dao;  
/** 
 * 
 * @author Nagesh Chauhan 
 */  
  
import java.util.ArrayList;  
import java.util.List;  
import javax.sql.DataSource;  
import org.springframework.beans.factory.annotation.Autowired;  
import org.springframework.jdbc.core.JdbcTemplate;  
import com.beingjavaguys.domain.User;  
import com.beingjavaguys.jdbc.UserRowMapper;  
  
public class UserDaoImpl implements UserDao {  
  
 @Autowired  
 DataSource dataSource;  
  
 public void insertData(User user) {  
  
  String sql = "INSERT INTO user "  
    + "(first_name,last_name, gender, city) VALUES (?, ?, ?,?)";  
  
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
  
  jdbcTemplate.update(  
    sql,  
    new Object[] { user.getFirstName(), user.getLastName(),  
      user.getGender(), user.getCity() });  
  
 }  
  
 public List getUserList() {  
  List userList = new ArrayList();  
  
  String sql = "select * from user";  
  
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
  userList = jdbcTemplate.query(sql, new UserRowMapper());  
  return userList;  
 }  
  
 @Override  
 public void deleteData(String id) {  
  String sql = "delete from user where user_id=" + id;  
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
  jdbcTemplate.update(sql);  
  
 }  
  
 @Override  
 public void updateData(User user) {  
  
  String sql = "UPDATE user set first_name = ?,last_name = ?, gender = ?, city = ? where user_id = ?";  
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
  
  jdbcTemplate.update(  
    sql,  
    new Object[] { user.getFirstName(), user.getLastName(),  
      user.getGender(), user.getCity(), user.getUserId() });  
  
 }  
  
 @Override  
 public User getUser(String id) {  
  List userList = new ArrayList();  
  String sql = "select * from user where user_id= " + id;  
  JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);  
  userList = jdbcTemplate.query(sql, new UserRowMapper());  
  return userList.get(0);  
 }  
  
}  


src\com\beingjavaguys\controller\HomePageController.java

This is simple spring mvc controller to hold code for flowing data across application.

package com.beingjavaguys.controller; /** * * @author Nagesh Chauhan */ import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.ModelAttribute; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.servlet.ModelAndView; import com.beingjavaguys.domain.User; import com.beingjavaguys.services.UserService; @Controller public class HomePageController { @Autowired UserService userService; @RequestMapping("/register") public ModelAndView registerUser(@ModelAttribute User user) { List genderList = new ArrayList(); genderList.add("male"); genderList.add("female"); List cityList = new ArrayList(); cityList.add("delhi"); cityList.add("gurgaon"); cityList.add("meerut"); cityList.add("noida"); Map map = new HashMap(); map.put("genderList", genderList); map.put("cityList", cityList); return new ModelAndView("register", "map", map); } @RequestMapping("/insert") public String inserData(@ModelAttribute User user) { if (user != null) userService.insertData(user); return "redirect:/getList"; } @RequestMapping("/getList") public ModelAndView getUserLIst() { List userList = userService.getUserList(); return new ModelAndView("userList", "userList", userList); } @RequestMapping("/edit") public ModelAndView editUser(@RequestParam String id, @ModelAttribute User user) { user = userService.getUser(id); List genderList = new ArrayList(); genderList.add("male"); genderList.add("female"); List cityList = new ArrayList(); cityList.add("delhi"); cityList.add("gurgaon"); cityList.add("meerut"); cityList.add("noida"); Map map = new HashMap(); map.put("genderList", genderList); map.put("cityList", cityList); map.put("user", user); return new ModelAndView("edit", "map", map); } @RequestMapping("/update") public String updateUser(@ModelAttribute User user) { userService.updateData(user); return "redirect:/getList"; } @RequestMapping("/delete") public String deleteUser(@RequestParam String id) { System.out.println("id = " + id); userService.deleteData(id); return "redirect:/getList"; } }


RowMapper in Spring JDBC Template

RowMapper is a useful interface provided by spring JDBC, it can be used to map rows in a resultset on per row basis. RowMapper with ResultSetExtractor can be used as a powerful combination to map relational database rows to domain object.

src\com\beingjavaguys\jdbc\UserRowMapper.java

package com.beingjavaguys.jdbc;  
/** 
 * 
 * @author Nagesh Chauhan 
 */  
  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import org.springframework.jdbc.core.RowMapper;  
import com.beingjavaguys.domain.User;  
  
public class UserRowMapper implements RowMapper {  
  
 @Override  
 public User mapRow(ResultSet resultSet, int line) throws SQLException {  
  UserExtractor userExtractor = new UserExtractor();  
  return userExtractor.extractData(resultSet);  
 }  
  
} 


ResultSetExtractor in Spring JDBC Template

ResultSetExtractor is an useful interface provided by Spring JDBC, it accept a resultset as input parameter and returns list implementation of extracted data from database.

src\com\beingjavaguys\jdbc\UserExtractor.java

package com.beingjavaguys.jdbc;  
/** 
 * 
 * @author Nagesh Chauhan 
 */  
  
  
import java.sql.ResultSet;  
import java.sql.SQLException;  
import org.springframework.dao.DataAccessException;  
import org.springframework.jdbc.core.ResultSetExtractor;  
import com.beingjavaguys.domain.User;  
  
public class UserExtractor implements ResultSetExtractor {  
  
 public User extractData(ResultSet resultSet) throws SQLException,  
   DataAccessException {  
    
  User user = new User();  
    
  user.setUserId(resultSet.getInt(1));  
  user.setFirstName(resultSet.getString(2));  
  user.setLastName(resultSet.getString(3));  
  user.setGender(resultSet.getString(4));  
  user.setCity(resultSet.getString(5));  
    
  return user;  
 }  
  
} 


src\com\beingjavaguys\services\UserService.java

package com.beingjavaguys.services;  
/** 
 * 
 * @author Nagesh Chauhan 
 */  
  
import java.util.List;  
import com.beingjavaguys.domain.User;  
  
public interface UserService {  
  
 public void insertData(User user);  
 public List getUserList();  
 public void deleteData(String id);  
 public User getUser(String id);  
 public void updateData(User user);  
  
}  


src\com\beingjavaguys\services\UserServiceImpl.java

package com.beingjavaguys.services;  
/** 
 * 
 * @author Nagesh Chauhan 
 */  
  
import java.util.List;  
  
import org.springframework.beans.factory.annotation.Autowired;  
import com.beingjavaguys.dao.UserDao;  
import com.beingjavaguys.domain.User;  
  
  
public class UserServiceImpl implements UserService {  
  
 @Autowired  
 UserDao userdao;  
  
 @Override  
 public void insertData(User user) {  
  userdao.insertData(user);  
 }  
  
 @Override  
 public List getUserList() {  
  return userdao.getUserList();  
 }  
  
 @Override  
 public void deleteData(String id) {  
  userdao.deleteData(id);  
    
 }  
  
 @Override  
 public User getUser(String id) {  
  return userdao.getUser(id);  
 }  
  
 @Override  
 public void updateData(User user) {  
  userdao.updateData(user);  
    
 }  
  
  
   
}  


src\com\beingjavaguys\domain\User.java

package com.beingjavaguys.domain;  
/** 
 * 
 * @author Nagesh Chauhan 
 */  
  
public class User {  
  
 private int userId;  
 private String firstName;  
 private String lastName;  
 private String gender;  
 private String city;  
  
 public int getUserId() {  
  return userId;  
 }  
  
 public void setUserId(int userId) {  
  this.userId = userId;  
 }  
  
 public String getFirstName() {  
  return firstName;  
 }  
  
 public void setFirstName(String firstName) {  
  this.firstName = firstName;  
 }  
  
 public String getLastName() {  
  return lastName;  
 }  
  
 public void setLastName(String lastName) {  
  this.lastName = lastName;  
 }  
  
 public String getGender() {  
  return gender;  
 }  
  
 public void setGender(String gender) {  
  this.gender = gender;  
 }  
  
 public String getCity() {  
  return city;  
 }  
  
 public void setCity(String city) {  
  this.city = city;  
 }  
}


WebContent\WEB-INF\jsp\register.jsp

<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>  
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>  
<html>  
<head>  
<title>Being Java Guys | Spring DI Hello World</title>  
<style>  
body {  
 font-size: 20px;  
 color: teal;  
 font-family: Calibri;  
}  
  
td {  
 font-size: 15px;  
 color: black;  
 width: 100px;  
 height: 22px;  
 text-align: left;  
}  
  
.heading {  
 font-size: 18px;  
 color: white;  
 font: bold;  
 background-color: orange;  
 border: thick;  
}  
</style>  
</head>  
<body>  
 <center>  
  <br /> <br /> <br /> <b>Being Java Guys | Registration Form </b> <br />  
  <br />  
  <div>  
   <form:form method="post" action="/insert" modelAttribute="user">  
    <table>  
     <tr>  
      <td>First Name :</td>  
      <td><form:input path="firstName" /></td>  
     </tr>  
     <tr>  
      <td>Last Name :</td>  
      <td><form:input path="lastName" /></td>  
     </tr>  
     <tr>  
      <td>Gender :</td>  
      <td><form:radiobuttons path="gender"  
        items="${map.genderList}" /></td>  
     </tr>  
     <tr>  
      <td>City :</td>  
      <td><form:select path="city" items="${map.cityList}" /></td>  
     </tr>  
     <tr>  
      <td> </td>  
      <td><input type="submit" value="Save" /></td>  
     </tr>  
     <tr>  
        
      <td colspan="2"><a href="getList">Click Here to See User List</a></td>  
     </tr>  
    </table>  
   </form:form>  
  </div>  
 </center>  
</body>  
</html>


WebContent\WEB-INF\jsp\userList.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"  
 pageEncoding="ISO-8859-1"%>  
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">  
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>  
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>  
<html>  
<head>  
<title>Being Java Guys | Spring DI Hello World</title>  
<style>  
body {  
 font-size: 20px;  
 color: teal;  
 font-family: Calibri;  
}  
  
td {  
 font-size: 15px;  
 color: black;  
 width: 100px;  
 height: 22px;  
 text-align: center;  
}  
.heading {  
 font-size: 18px;  
 color: white;  
 font: bold;  
 background-color: orange;  
 border: thick;  
}  
</style>  
</head>  
<body>  
 <center>  
  <br /> <br /> <br /> <b>User  
   List | Being Java Guys </b><br /> <br />  
     
    
  
  <table border="1">  
   <tr>  
    <td class="heading">User Id</td>  
    <td class="heading">First Name</td>  
    <td class="heading">Last Name</td>  
    <td class="heading">Gender</td>  
    <td class="heading">City</td>  
    <td class="heading">Edit</td>  
    <td class="heading">Delete</td>  
   </tr>  
   <c:forEach var="user" items="${userList}">  
    <tr>  
     <td>${user.userId}</td>  
     <td>${user.firstName}</td>  
     <td>${user.lastName}</td>  
     <td>${user.gender}</td>  
     <td>${user.city}</td>  
     <td><a href="edit?id=${user.userId}">Edit</a></td>  
     <td><a href="delete?id=${user.userId}">Delete</a></td>  
    </tr>  
   </c:forEach>  
   <tr><td colspan="7"><a href="register">Add New User</a></td></tr>  
  </table>  
  
 </center>  
</body>  
</html>  


WebContent\WEB-INF\jsp\edit.jsp

<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form"%>  
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>  
<%@ taglib uri="http://www.springframework.org/tags" prefix="spring"%>  
<html>  
<head>  
<title>Being Java Guys | Spring DI Hello World</title>  
<style>  
body {  
 font-size: 20px;  
 color: teal;  
 font-family: Calibri;  
}  
  
td {  
 font-size: 15px;  
 color: black;  
 width: 100px;  
 height: 22px;  
 text-align: center;  
}  
  
.heading {  
 font-size: 18px;  
 color: white;  
 font: bold;  
 background-color: orange;  
 border: thick;  
}  
</style>  
</head>  
<body>  
 <center>  
  <br /> <br /> <br /> <b>Edit User Details </b><br /> <br />  
  <div>  
   <form:form method="post" action="/update" modelAttribute="user">  
    <table>  
     <tr>  
      <td>First Name :</td>  
      <td><form:input path="firstName"  
        value="${map.user.firstName}" />  
      </td>  
     </tr>  
     <tr>  
      <td>Last Name :</td>  
      <td><form:input path="lastName" value="${map.user.lastName}" />  
      </td>  
     </tr>  
     <tr>  
      <td>Gender :</td>  
      <td><spring:bind path="gender">  
        <c:forEach items='${map.genderList}' var='genderName'>  
         <c:choose>  
          <c:when test="${genderName eq map.user.gender}">  
           <input type="radio" name="gender" value="${genderName}"  
            checked="checked">${genderName}  
          </c:when>  
          <c:otherwise>  
           <input type="radio" name="gender" value="${genderName}">${genderName}  
          </c:otherwise>  
         </c:choose>  
        </c:forEach>  
       </spring:bind>  
      </td>  
     </tr>  
     <tr>  
  
      <td>City :</td>  
      <td><spring:bind path="city">  
        <select name="city">  
         <c:forEach items='${map.cityList}' var='cityName'>  
          <c:choose>  
           <c:when test="${cityName eq map.user.city}">  
            <option value="${cityName}" selected="true">${cityName}</option>  
           </c:when>  
           <c:otherwise>  
            <option value="${cityName}">${cityName}</option>  
           </c:otherwise>  
          </c:choose>  
         </c:forEach>  
        </select>  
       </spring:bind></td>  
     </tr>  
     <tr>  
      <td> </td>  
      <td><input type="submit" value="Save" />  
      </td>  
     </tr>  
    </table>  
    <form:hidden path="userId" value="${map.user.userId}" />  
  
   </form:form>  
  </div>  
 </center>  
</body>  
</html>  


WebContent\index.jsp


<%response.sendRedirect("register");%>


WebContent\WEB-INF\web.xml

<?xml version="1.0" encoding="UTF-8"?>  
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
 xmlns="http://java.sun.com/xml/ns/javaee" xmlns:web="http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"  
 xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd"  
 id="WebApp_ID" version="2.5">  
 <display-name>SpringJDBCTemplate</display-name>  
 <welcome-file-list>  
  <welcome-file>index.jsp</welcome-file>  
 </welcome-file-list>  
  
 <servlet>  
  <servlet-name>spring</servlet-name>  
  <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>  
  <load-on-startup>1</load-on-startup>  
 </servlet>  
 <servlet-mapping>  
  <servlet-name>spring</servlet-name>  
  <url-pattern>/</url-pattern>  
 </servlet-mapping>  
</web-app>  
This is all about Spring JDBC template and its usage with a Spring MVC application, if everything goes right you will see following screens on your browser.





In this particular blog we came across what is spring jdbc template and how to use it with spring mvc application to perform insert, update and delete functionality. In upcoming blogs we will see more about Spring and other Java Technologies.