Spring MVC CRUD Example with MySql + JdbcTemplate

In this Spring MVC CRUD Example, we will be building a simple web-based Spring MVC Application (Employee management) which has the ability to perform CRUD Operations using Spring JdbcTemplate. Our Employee management application will have abilities to create a new employee, update the existing employee, get a particular employee/ all employee and finally delete the existing employee.

Creating table

Create EMPLOYEE Table, simply Copy and Paste the following SQL query in the query editor to get the table created.

CREATE TABLE EMPLOYEE
 (
    ID INT(10) NOT NULL, 
    NAME VARCHAR(255), 
    DEPT VARCHAR(255), 
    AGE INT(10), 
    PRIMARY KEY(ID)
 );

Folder Structure:

1. Create a simple Maven Project “SpringMVCTutorial” by selecting maven-archetype-webapp and create a package for our source files “com.javainterviewpoint” under  src/main/java 
2. Now add the following dependency in the 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>com.javainterviewpoint</groupId>
      <artifactId>SpringMVCTutorial</artifactId>
      <packaging>war</packaging>
      <version>0.0.1-SNAPSHOT</version>
      <name>SpringMVCTutorial Maven Webapp</name>
      <url>http://maven.apache.org</url>
      <properties>
       <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
       <springframework.version>4.3.7.RELEASE</springframework.version>
       <hibernate.version>4.3.11.Final</hibernate.version>
       <mysql.version>5.1.10</mysql.version>
       <jstl.version>1.2</jstl.version>
     </properties>
   
     <dependencies>
   
       <dependency>
          <groupId>junit</groupId>
          <artifactId>junit</artifactId>
          <version>3.8.1</version>
          <scope>test</scope>
       </dependency>
   
       <!-- Spring mvc 4 dependencies -->
       <dependency>
           <groupId>org.springframework</groupId>
           <artifactId>spring-core</artifactId>
           <version>${springframework.version}</version>
       </dependency>
       <dependency>
           <groupId>org.springframework</groupId>
           <artifactId>spring-context</artifactId>
           <version>${springframework.version}</version>
       </dependency>
       <dependency>
           <groupId>org.springframework</groupId>
           <artifactId>spring-web</artifactId>
           <version>${springframework.version}</version>
       </dependency>
       <dependency>
           <groupId>org.springframework</groupId>
           <artifactId>spring-webmvc</artifactId>
           <version>${springframework.version}</version>
       </dependency>
       <dependency>
           <groupId>org.springframework</groupId>
           <artifactId>spring-jdbc</artifactId>
           <version>${springframework.version}</version>
       </dependency>
   
       <!-- jstl for jsp page -->
       <dependency>
           <groupId>jstl</groupId>
           <artifactId>jstl</artifactId>
           <version>${jstl.version}</version>
       </dependency>
       <dependency>
           <groupId>javax.servlet</groupId>
           <artifactId>javax.servlet-api</artifactId>
           <version>3.1.0</version>
           <scope>provided</scope>
       </dependency>
       <dependency>
           <groupId>javax.servlet.jsp</groupId>
           <artifactId>javax.servlet.jsp-api</artifactId>
           <version>2.3.1</version>
           <scope>provided</scope>
       </dependency>
   
       <dependency>
           <groupId>com.fasterxml.jackson.core</groupId>
           <artifactId>jackson-databind</artifactId>
           <version>2.8.1</version>
       </dependency>
   
       <!--MYSQL Connector -->
       <dependency>
          <groupId>mysql</groupId>
          <artifactId>mysql-connector-java</artifactId>
          <version>${mysql.version}</version>
       </dependency>
     </dependencies>
     <build>
       <finalName>SpringMVCTutorial</finalName>
       <plugins>
         <plugin>
           <artifactId>maven-compiler-plugin</artifactId>
           <configuration>
             <source>1.7</source>
             <target>1.7</target>
           </configuration>
           <version>3.1</version>
         </plugin>
       </plugins>
     </build>
   </project>

3. Create the Java classes Employee.java, EmployeeController.java, EmployeeDAO.java and EmployeeDAOImpl.java under com.javainterviewpoint folder.
4. Place the employees.jsp under /WEB-INF/JSP directory.
5. Place the web.xml and SpringMVC-servlet.xml under the /WEB-INF directory

Spring MVC CRUD Example

We will be building the Employee Management system where in which you will be able to Create an Employee, Get all the Employee / particular Employee details, Modify an existing Employee and Delete the Employee. The final Screen looks like below

EmployeeController.java

package com.javainterviewpoint;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
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;

@Controller
public class EmployeeController
{
    @Autowired
    private EmployeeDAO employeeDAO;

    @RequestMapping(value = "/employee",method=RequestMethod.POST)
    public ModelAndView saveEmployee(@ModelAttribute("employee") Employee employee)
    {
        try
        {
            if(employeeDAO.getEmployeeById(employee.getId()) != null);
            employeeDAO.updateEmployee(employee);
        }
        catch(EmptyResultDataAccessException e)
        {
            System.out.println("inside catch");
            employeeDAO.saveEmployee(employee);
        }
        return new ModelAndView("redirect:/employees");
    }
    
    @RequestMapping(value = "/edit/{id}")
    public ModelAndView editEmployee(@ModelAttribute("employee") Employee employee,@PathVariable("id") int id)
    {
        ModelAndView model = new ModelAndView("employees");
        
        employee = employeeDAO.getEmployeeById(id);
        List employeeList = employeeDAO.getAllEmployees();
        
        model.addObject("employee",employee);        
        model.addObject("employeeList",employeeList);
        
        return model;
    }
    
    @RequestMapping(value = "/delete/{id}")
    public ModelAndView deleteEmployee(@ModelAttribute("employee") Employee employee,@PathVariable("id") int id)
    {
        employeeDAO.deleteEmployee(id);
        
        return new ModelAndView("redirect:/employees");
    }

    @RequestMapping(value = "/employees")
    public ModelAndView listEmployees(@ModelAttribute("employee") Employee employee)
    {
        ModelAndView model = new ModelAndView("employees");

        List employeeList = employeeDAO.getAllEmployees();
        System.out.println(employeeList);
        model.addObject("employeeList", employeeList);
        
        return model;
    }
}

Our EmployeeController will be acting as the Spring Controller, it has the below methods.

• saveEmployee() –  @ModelAttribute binds the employee object from the view. Using getEmployeeById() method of EmployeeDAOImpl class we will verify whether the employee with the id already exists or not. If the employee doesn’t exist we will be inserting the employee. If the employee already exists we will be updating the existing record.
• editEmployee() – This method gets the employee with particular id (getEmployeeById() method)which we want to edit and returns the employee back to the screen for edit. Along with this, it also sends the list of all employees as well by calling the getAllEmployees() method of EmployeeDAOImpl class.
• deleteEmployee() – This method directly calls the deleteEmployee() method EmployeeDAOImpl class passing the id of the employee which we have obtained from the view for deletion.
• listEmployees() – This method returns the list of employees available by calling the getAllEmployees() method of EmployeeDAOImpl class.

EmployeeDAO.java

package com.javainterviewpoint;

import java.util.List;

public interface EmployeeDAO
{
    public void saveEmployee(Employee employee);
    public Employee getEmployeeById(int id);
    public void updateEmployee(Employee employee);
    public void deleteEmployee(int id);
    public List<Employee> getAllEmployees();
}

EmployeeDAOImpl.java

package com.javainterviewpoint;

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

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowMapper;

public class EmployeeDAOImpl implements EmployeeDAO
{

    private JdbcTemplate jdbcTemplate;
    // JdbcTemplate setter
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate)
    {
        this.jdbcTemplate = jdbcTemplate;
    }

    // Saving a new Employee
    public void saveEmployee(Employee employee)
    {
        String sql = "insert into Employee values(?,?,?,?)";

        jdbcTemplate.update(sql, new Object[]
        { employee.getId(), employee.getAge(), employee.getDept(), employee.getName() });
    }

    // Getting a particular Employee
    public Employee getEmployeeById(int id)
    {
        String sql = "select * from Employee where id=?";
        Employee employee = (Employee) jdbcTemplate.queryForObject(sql, new Object[]
        { id }, new RowMapper()
        {
            @Override
            public Employee mapRow(ResultSet rs, int rowNum) throws SQLException
            {
                Employee employee = new Employee();
                employee.setId(rs.getInt(1));
                employee.setAge(rs.getInt(2));
                employee.setDept(rs.getString(3));
                employee.setName(rs.getString(4));
                return employee;
            }
        });
        return employee;
    }

    // Getting all the Employees
    public List<Employee> getAllEmployees()
    {
        String sql = "select * from Employee";

        List<Employee> employeeList = jdbcTemplate.query(sql, new ResultSetExtractor<List<Employee>>()
        {
            @Override
            public List<Employee> extractData(ResultSet rs) throws SQLException, DataAccessException
            {
                List<Employee> list = new ArrayList<Employee>();
                while (rs.next())
                {
                    Employee employee = new Employee();
                    employee.setId(rs.getInt(1));
                    employee.setAge(rs.getInt(2));
                    employee.setDept(rs.getString(3));
                    employee.setName(rs.getString(4));
                    list.add(employee);
                }
                return list;
            }

        });
        return employeeList;
    }

    // Updating a particular Employee
    public void updateEmployee(Employee employee)
    {
        String sql = "update Employee set age =?, dept=?,name=? where id=?";
        jdbcTemplate.update(sql, new Object[]
        { employee.getAge(), employee.getDept(), employee.getName(), employee.getId() });
    }

    // Deletion of a particular Employee
    public void deleteEmployee(int id)
    {
        String sql = "delete employee where id=?";
        jdbcTemplate.update(sql, new Object[]
        { id });
    }
}

EmployeeDAOImpl class implements the interface EmployeeDAO and overrides all the unimplemented methods. We have the below methods in our EmployeeDAOImpl class

• setJdbcTemplate() – Through Spring setter injection we will be injecting the jdbcTemplate from the Spring configuration file.
• getAllEmployee() – In order to fetch all the records from the database we just need to pass the SQL and the instance of the ResultSetExtractor to the query() method of jdbcTemplate. ResultSetExtractor interface accepts the ResultSet and returns a Java List. We need to override the extractData() method and map each ResultSet to an Employee object add to a list.
• getEmployeeById() – In order to fetch a particular record we just need to pass the SQL and the instance of the RowMapper to the queryForObject() method of jdbcTemplate. RowMapper interface internally iterates the ResultSet and adds it to the Collection (Map). Hence there is no need for us to iterate the ResultSet as we do in the ResultSetExtractor.
• updateEmployee() – We will be updating the corresponding employee by calling the update() method of the jdbcTemplate passing the SQL and the parameters.
• deleteEmployee() – In order to delete an employee, we need to call the update() method of the jdbcTemplate passing the SQL and the id.

Employee.java

Our Employee class is a simple POJO class consisting getters and setters of Employee properties id, name, age, dept.

package com.javainterviewpoint;

import java.io.Serializable;

public class Employee implements Serializable 
{
    private static final long serialVersionUID = -1280037900360314186L;

    private Integer id;
    private String name;
    private Integer age;
    private String dept;
    public Employee()
    {
        super();
    }
    public Employee(Integer id, String name, Integer age, String dept)
    {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
        this.dept = dept;
    }
    public Integer getId()
    {
        return id;
    }
    public void setId(Integer id)
    {
        this.id = id;
    }
    public String getName()
    {
        return name;
    }
    public void setName(String name)
    {
        this.name = name;
    }
    public Integer getAge()
    {
        return age;
    }
    public void setAge(Integer age)
    {
        this.age = age;
    }
    public String getDept()
    {
        return dept;
    }
    public void setDept(String dept)
    {
        this.dept = dept;
    }
    
    @Override
    public int hashCode()
    {
        final int prime = 31;
        int result = 1;
        result = prime * result + ((age == null) ? 0 : age.hashCode());
        result = prime * result + ((dept == null) ? 0 : dept.hashCode());
        result = prime * result + ((id == null) ? 0 : id.hashCode());
        result = prime * result + ((name == null) ? 0 : name.hashCode());
        return result;
    }
    @Override
    public boolean equals(Object obj)
    {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        Employee other = (Employee) obj;
        if (age == null)
        {
            if (other.age != null)
                return false;
        } else if (!age.equals(other.age))
            return false;
        if (dept == null)
        {
            if (other.dept != null)
                return false;
        } else if (!dept.equals(other.dept))
            return false;
        if (id == null)
        {
            if (other.id != null)
                return false;
        } else if (!id.equals(other.id))
            return false;
        if (name == null)
        {
            if (other.name != null)
                return false;
        } else if (!name.equals(other.name))
            return false;
        return true;
    }
    @Override
    public String toString()
    {
        return "Employee [id=" + id + ", name=" + name + ", age=" + age + ", dept=" + dept + "]";
    }
}

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app 
    xmlns="http://java.sun.com/xml/ns/javaee"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/javaee	http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd"
    version="3.0">
  <display-name>Spring MVC CRUD Example</display-name>
  <servlet>
   <servlet-name>SpringMVC</servlet-name>
   <servlet-class>org.springframework.web.servlet.DispatcherServlet</servlet-class>
  </servlet>
  <servlet-mapping>
   <servlet-name>SpringMVC</servlet-name>
   <url-pattern>/</url-pattern>
  </servlet-mapping>
  <welcome-file-list>
    <welcome-file>UserManagement.jsp</welcome-file>
  </welcome-file-list>
</web-app>

• The web.xml has everything about the application that a server needs to know, which is placed under the WEB-INF directory.  It contains the name of the SpringConfiguration file, when the DispatcherServlet is initialized the framework will try to load a configuration file “[servlet-name]-servlet.xml” under the WEB-INF directory.

SpringMVC-servlet.xml

• <context:component-scan> will let the Spring Container to search for all the annotation under the package “com.javainteriviewpoint”. 
• <mvc:annotation-driven/> annotation will activate the @Controller, @RequestMapping, @Valid etc annotations.
• The view is resolved through “org.springframework.web.servlet.view.InternalResourceViewResolver” which searches for the jsp files under the /WEB-INF/Jsp/ directory.

In our configuration file, we have defined the three beans

1. DriverManagerDataSource – DriverManagerDataSource contains database related configurations such as driver class name, connection URL, username and password.
2. JdbcTemplate – We will be referencing the dataSource id (DriverManagerDataSource ) to the property dataSource of the JdbcTemplate class.
3. EmployeeDAOImpl – We will be referencing the jdbcTemplate id to the property jdbcTemplate of the EmployeeDAOImpl class.

<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"
	xmlns:mvc="http://www.springframework.org/schema/mvc"
	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
		http://www.springframework.org/schema/mvc	http://www.springframework.org/schema/mvc/spring-mvc.xsd
		http://www.springframework.org/schema/aop	http://www.springframework.org/schema/aop/spring-aop-4.0.xsd
		http://www.springframework.org/schema/tx	http://www.springframework.org/schema/tx/spring-tx-4.0.xsd">
	<mvc:annotation-driven />
	<context:component-scan base-package="com.javainterviewpoint" />


	<bean id="employeeDAOImpl" class="com.javainterviewpoint.EmployeeDAOImpl">
		<property name="jdbcTemplate" ref="jdbcTemplate" />
	</bean>

	<!-- Database Configurations -->
	<bean id="dataSource"
		class="org.springframework.jdbc.datasource.DriverManagerDataSource">
		<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />
		<property name="url" value="jdbc:oracle:thin:@rsh2:40051:dev" />
		<property name="username" value="root" />
		<property name="password" value="root" />
	</bean>

	<bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
		<property name="dataSource" ref="dataSource" />
	</bean>

	<bean
		class="org.springframework.web.servlet.view.InternalResourceViewResolver">
		<property name="prefix" value="/WEB-INF/JSP/" />
		<property name="suffix" value=".jsp" />
	</bean>
</beans>

employees.jsp

<%@ 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/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>Insert title here</title>
     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
   </head>
   <body>
      <h3>Add / Edit Employee!!!</h3>

      <form:form method="post" action="/SpringMVCTutorial/employee.html" commandName="employee">
        <div class="table-responsive">
          <table class="table table-bordered" style="width: 300px">
            <tr>
              <td>Id :</td>
              <td><form:input type="text" path="id" /></td>
            </tr>
            <tr>
              <td>Name :</td>
              <td><form:input type="text" path="name" /></td>
            </tr>
            <tr>
              <td>Age :</td>
              <td><form:input type="text" path="age" /></td>
            </tr>
            <tr>
              <td>Department :</td>
              <td><form:input type="text" path="dept" /></td>
            </tr>
            <tr>
              <td></td>
              <td><input class="btn btn-primary btn-sm" type="submit" value="Submit" /></td>
            </tr>
          </table>
        </div>
      </form:form>
     <br>
     <h3>List of Employees</h3>
       <table class="table table-bordered" style="width: 300px">
         <tr>
           <th>Id</th>
           <th>Name</th>
           <th>Age</th>
           <th>Department</th>
           <th>Edit/Delete</th>
         </tr>
         <c:forEach items="${employeeList}" var="employee">
         <tr>
           <td width="60" align="center">${employee.id}</td>
           <td width="60" align="center">${employee.name}</td>
           <td width="60" align="center">${employee.age}</td>
           <td width="60" align="center">${employee.dept}</td>
           <td width="60" align="center"><a href="edit/${employee.id}">Edit</a>/<a href="delete/${employee.id}">Delete</a></td>
         </tr>
      </c:forEach>
    </table>
  </body>
</html>

    Download Source Code