Spring 4 MVC CRUD Operations with Spring JDBC Template and Mysql Database Tutorial

Overview

In this tutorial, we show you how to create User CRUD (Create, Read, Update, Delete) Web Application using Spring 4 MVC with Spring JDBC Template using Eclipse IDE, Mysql Database.

Follow the steps mentioned below to develop this application.

Video Tutorials

Database & Table Creation

The following MySQL script is used to create a database called jack_rutorial_demo with table users.

CREATE DATABASE `jack_rutorial_demo`;

CREATE TABLE  `jack_rutorial_demo`.`users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(45) NOT NULL DEFAULT '',
  `lastname` varchar(100) NOT NULL DEFAULT '',
  `address` varchar(100) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Project Structure

Create Maven Project

• Launch Eclipse IDE.
• Go to File-> New-> Others... Select Maven Project under Maven category then click Next.
• In New Maven Project wizard, select "Create a simpel project(skip archetype selection)" and click on Next
• In next wizard, type "com.jackrutorial" in the "Group ID:" field
• Type "SpringMvcCRUDExample" in the "Artifact Id:" field
• Packaging -> War
• Click Finish.

Maven Dependencies

We specify the dependency for the Spring WebMVC, Spring Jdbc, jstl and Servlet api. The rest dependencies will be automatically resolved by Maven. The updated pom.xml file will have the following code:

<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>com.jackrutorial</groupId>
 <artifactId>SpringMvcCRUDExample</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>war</packaging>

 <dependencies>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-webmvc</artifactId>
   <version>4.3.0.RELEASE</version>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-tx</artifactId>
   <version>4.3.0.RELEASE</version>
  </dependency>
  <dependency>
   <groupId>jstl</groupId>
   <artifactId>jstl</artifactId>
   <version>1.2</version>
  </dependency>
  <dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>javax.servlet-api</artifactId>
   <version>3.1.0</version>
   <scope>provided</scope>
  </dependency>
  <dependency>
   <groupId>org.springframework</groupId>
   <artifactId>spring-jdbc</artifactId>
   <version>4.3.0.RELEASE</version>
  </dependency>
 </dependencies>

 <build>
  <pluginManagement>
   <plugins>
    <plugin>
     <groupId>org.apache.maven.plugins</groupId>
     <artifactId>maven-war-plugin</artifactId>
     <version>2.4</version>
     <configuration>
      <warSourceDirectory>src/main/webapp</warSourceDirectory>
      <failOnMissingWebXml>false</failOnMissingWebXml>
     </configuration>
    </plugin>
   </plugins>
  </pluginManagement>
 </build>

</project>

Configure WebApp

• Right-click on src/main/java folder, New -> Package
• Enter  the package name as: "com.jackrutorial.config"
• Click Finish

Create a WebConfig class under com.jackrutorial.config package and write the following code in it.

package com.jackrutorial.config;

import javax.naming.NamingException;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jndi.JndiTemplate;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.ResourceHandlerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurerAdapter;
import org.springframework.web.servlet.view.InternalResourceViewResolver;
import org.springframework.web.servlet.view.JstlView;

@Configuration
@EnableWebMvc
@ComponentScan(basePackages = { "com.jackrutorial" })
public class WebConfig extends WebMvcConfigurerAdapter {
 
 @Autowired
 DataSource dataSource;
 
 @Bean
 public NamedParameterJdbcTemplate geNamedParameterJdbcTemplate(){
  return new NamedParameterJdbcTemplate(dataSource);
 }
 
 @Bean
 public DataSource getDataSource() throws NamingException{
  JndiTemplate jndiTemplate = new JndiTemplate();
  DataSource dataSource = (DataSource) jndiTemplate.lookup("java:comp/env/jdbc/springmvc");
  
  return dataSource;
 }
 
 @Override
 public void addResourceHandlers(ResourceHandlerRegistry registry) {
  registry.addResourceHandler("/resources/**").addResourceLocations("/resources/");
 }
 
 @Bean
 public InternalResourceViewResolver viewResolver(){
  InternalResourceViewResolver viewResolver = new InternalResourceViewResolver();
  viewResolver.setViewClass(JstlView.class);
  viewResolver.setPrefix("/WEB-INF/jsp/");
  viewResolver.setSuffix(".jsp");
  
  return viewResolver;
 }
}

Create a WebInitializer class under com.jackrutorial.config package and write the following code in it.

package com.jackrutorial.config;

import org.springframework.web.servlet.support.AbstractAnnotationConfigDispatcherServletInitializer;

public class WebInitializer extends AbstractAnnotationConfigDispatcherServletInitializer  {

 @Override
    protected Class[] getRootConfigClasses() {
        return new Class[] { WebConfig.class};
    }
  
    @Override
    protected Class[] getServletConfigClasses() {
        return null;
    }
  
    @Override
    protected String[] getServletMappings() {
        return new String[] { "/" };
    }
}

Creating Model Layer

Create a UserInfo class under package com.jackrutorial.model, this class simply maps a row in the users table to a Java object and write the following code in it.

package com.jackrutorial.model;

public class User {
 private Integer id;
 private String firstname;
 private String lastname;
 private String address;
 
 public User() {
  super();
 }
 
 public User(Integer id) {
  super();
  this.id = id;
 }

 public Integer getId() {
  return id;
 }
 public void setId(Integer id) {
  this.id = id;
 }
 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 getAddress() {
  return address;
 }
 public void setAddress(String address) {
  this.address = address;
 }
}

Creating DAO Layer

Create a UserDao Interface under com.jackrutorial.dao package and write the following code in it.

package com.jackrutorial.dao;

import java.util.List;

import com.jackrutorial.model.User;

public interface UserDao {
 
 public List listAllUsers();
 
 public void addUser(User user);
 
 public void updateUser(User user);
 
 public void deleteUser(int id);
 
 public User findUserById(int id);
}

Create a UserDaoImpl class implements UserDao Interface under com.jackrutorial.dao package and write the following code in it.

package com.jackrutorial.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import com.jackrutorial.model.User;

@Repository
public class UserDaoImpl implements UserDao {
 
 NamedParameterJdbcTemplate namedParameterJdbcTemplate;
 
 @Autowired
 public void setNamedParameterJdbcTemplate(NamedParameterJdbcTemplate namedParameterJdbcTemplate) {
  this.namedParameterJdbcTemplate = namedParameterJdbcTemplate;
 }

 public List listAllUsers() {
  String sql = "SELECT id, firstname, lastname, address FROM users";
  
  List list = namedParameterJdbcTemplate
.query(sql, getSqlParameterByModel(null), new UserMapper());
  
  return list;
 }
 
 private SqlParameterSource getSqlParameterByModel(User user){
  MapSqlParameterSource parameterSource = new MapSqlParameterSource();
  if(user != null){
   parameterSource.addValue("id", user.getId());
   parameterSource.addValue("firstname", user.getFirstname());
   parameterSource.addValue("lastname", user.getLastname());
   parameterSource.addValue("address", user.getAddress());
  }
  return parameterSource;
 }
 
 private static final class UserMapper implements RowMapper{

  public User mapRow(ResultSet rs, int rowNum) throws SQLException {
   User user = new User();
   user.setId(rs.getInt("id"));
   user.setFirstname(rs.getString("firstname"));
   user.setLastname(rs.getString("lastname"));
   user.setAddress(rs.getString("address"));
   
   return user;
  }
  
 }

 public void addUser(User user) {
  String sql = "INSERT INTO users(firstname, lastname, address) 
VALUES(:firstname, :lastname, :address)";
  
  namedParameterJdbcTemplate.update(sql, getSqlParameterByModel(user));
 }

 public void updateUser(User user) {
  String sql = "UPDATE users SET firstname = :firstname, lastname = :lastname
, address = :address WHERE id = :id";
  
  namedParameterJdbcTemplate.update(sql, getSqlParameterByModel(user));
 }

 public void deleteUser(int id) {
  String sql = "DELETE FROM users WHERE id = :id";
  
  namedParameterJdbcTemplate.update(sql, getSqlParameterByModel(new User(id)));
 }

 public User findUserById(int id) {
  String sql = "SELECT * FROM users WHERE id = :id";
  
  return namedParameterJdbcTemplate
.queryForObject(sql, getSqlParameterByModel(new User(id)), new UserMapper());
 }

}

Creating Service Layer

Create a UserService Interface under com.jackrutorial.service package and write the following code in it.

package com.jackrutorial.service;

import java.util.List;

import com.jackrutorial.model.User;

public interface UserService {

 public List listAllUsers();
 
 public void addUser(User user);
 
 public void updateUser(User user);
 
 public void deleteUser(int id);
 
 public User findUserById(int id);
}

Create a UserServiceImpl class implements UserService Interface under com.jackrutorial.service package and write the following code in it.

package com.jackrutorial.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jackrutorial.dao.UserDao;
import com.jackrutorial.model.User;

@Service
public class UserServiceImpl implements UserService {
 
 UserDao userDao;
 
 @Autowired
 public void setUserDao(UserDao userDao) {
  this.userDao = userDao;
 }

 public List listAllUsers() {
  return userDao.listAllUsers();
 }

 public void addUser(User user) {
  userDao.addUser(user);
 }

 public void updateUser(User user) {
  userDao.updateUser(user);
 }

 public void deleteUser(int id) {
  userDao.deleteUser(id);
 }

 public User findUserById(int id) {
  return userDao.findUserById(id);
 }

}

Creating Controller Layer

Create a UserController under com.jackrutorial.controller package and write the following code in it.

package com.jackrutorial.controller;

import java.util.List;

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.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.servlet.ModelAndView;

import com.jackrutorial.model.User;
import com.jackrutorial.service.UserService;

@Controller
@RequestMapping(value="/user")
public class UserController {
 
 @Autowired
 UserService userService;
 
 @RequestMapping(value="/list", method=RequestMethod.GET)
 public ModelAndView list(){
  ModelAndView model = new ModelAndView("user/user_page");
  
  List list = userService.listAllUsers();
  model.addObject("listUser", list);
  
  return model;
 }
 
 @RequestMapping(value="/add", method=RequestMethod.GET)
 public ModelAndView add(){
  ModelAndView model = new ModelAndView("user/user_form");
  
  User user = new User();
  model.addObject("userForm", user);
  
  return model;
 }
 
 @RequestMapping(value="/update/{id}", method=RequestMethod.GET)
 public ModelAndView update(@PathVariable("id") int id){
  ModelAndView model = new ModelAndView("user/user_form");
  
  User user = userService.findUserById(id);
  model.addObject("userForm", user);
  
  return model;
 }
 
 @RequestMapping(value="/save", method=RequestMethod.POST)
 public ModelAndView save(@ModelAttribute("userForm") User user){
  if(user != null && user.getId() != null){
   userService.updateUser(user);
  } else {
   userService.addUser(user);
  }
  
  return new ModelAndView("redirect:/user/list");
 }
 
 @RequestMapping(value="/delete/{id}", method=RequestMethod.GET)
 public ModelAndView delete(@PathVariable("id") int id){
  userService.deleteUser(id);
  
  return new ModelAndView("redirect:/user/list");
 }
}

Creating JSP Views

Create user folder under src\main\webapp\WEB-INF\jsp folder.
Create user_page.jsp file under src\main\webapp\WEB-INF\jsp\user folder and write the following code in it.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://www.springframework.org/tags" prefix="spring" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
 "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>User Page</title>
</head>
<body>
 <spring:url value="/user/add" var="addURL" />
 <a href="${addURL }">Add User</a>

 <h1>Users List</h1>
 <table width="100%" border="1">
  <tr>
   <th>ID</th>
   <th>Firstname</th>
   <th>Lastname</th>
   <th>Address</th>
   <th colspan="2">Action</th>
  </tr>
  <c:forEach items="${listUser }" var="user" >
   <tr>
    <td>${user.id }</td>
    <td>${user.firstname }</td>
    <td>${user.lastname }</td>
    <td>${user.address }</td>
    <td>
     <spring:url value="/user/update/${user.id }" var="updateURL" />
     <a href="${updateURL }">Update</a>
    </td>
    <td>
     <spring:url value="/user/delete/${user.id }" var="deleteURL" />
     <a href="${deleteURL }">Delete</a>
    </td>
   </tr>    
  </c:forEach>
 </table>
 
</body>
</html>

Create user_form.jsp file under src\main\webapp\WEB-INF\jsp\user folder and write the following code in it.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ taglib uri="http://www.springframework.org/tags" prefix="spring" %>
<%@ taglib uri="http://www.springframework.org/tags/form" prefix="form" %>
    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>User Form</title>
</head>
<body>
 <spring:url value="/user/save" var="saveURL" />
 <form:form modelAttribute="userForm" method="post" action="${saveURL }" >
  <form:hidden path="id"/>
  <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>Address: </td>
    <td>
     <form:input path="address"/>
    </td>
   </tr>
   <tr>
    <td></td>
    <td>
     <button type="submit">Save</button>
    </td>
   </tr>
  </table>
  
 </form:form>
 
</body>
</html>

Building

• Right click this project
• Select Run As -> Maven clean
• Right click this project
• Select Run As -> Maven install

Configuring Apache Tomcat

• Under Servers tab, click link "No servers are available. Click this link to create a new server ...", select Apache tomcat 7
• Click Finish

 

• Right click "Tomcat v7.0 Server at localhost [Stopped, Republish]", select "Add and Remove ..."
• Add SpringMvcCRUDExample project, then Click Finish
• Open server.xml file under Servers Folder
• Find line

<Context docBase="SpringMvcCRUDExample" path="/SpringMvcCRUDExample" reloadable="true" source="org.eclipse.jst.jee.server:SpringMvcCRUDExample" />

Update its as below:

<Context docBase="<Project Folder Location>\SpringMvcCRUDExample\target\SpringMvcCRUDExample-0.0.1-SNAPSHOT\" path="/SpringMvcCRUDExample" reloadable="true" >
        <Resource
                    name="jdbc/springmvc"
                    auth="Container"
                    type="javax.sql.DataSource"
                    username="root"
                     password="root"
                     driverClassName="com.mysql.jdbc.Driver"
                     url="jdbc:mysql://localhost:3306/jack_rutorial_demo"
                     maxActive="5"
                     maxIdle="3"
                      />
</Context>


Watch video add Apache Tomcat Server in Eclipse IDE 

• Run application & Check result
• Start Apache Tomcat from Eclipse IDE.
• Type the following URLs in browser's address bar to open the user list from.

http://localhost:8080/SpringMvcCRUDExample/user/list

Users List Screen:

Users Add New Screen:

Users Update Screen:

Required Libraries

• mysql-connector-java-5.1.45-bin.jar
• Apache tomcat 7

• Tweet
• Share
• Share
• Share
• Share