Spring JdbcTemplate CRUD Web Application using Spring Boot, Bootstrap 4 and Mysql

Overview

In this tutorial, we show you how to develop and Bootstrap a Spring Boot CRUD Web Application with Spring Boot JDBC, MySQL Database and Webjars Bootstrap 4. This Web Application can display the list of employee, create, edit, update and delete it.
Follow the steps mentioned below to develop the CRUD Web Application.

Watch tutorial

Technologies Used

• Eclipse Oxygen and Install Spring Tool Suite for Eclipse IDE
• Spring Boot 2.1.0.BUILD-SNAPSHOT
• spring-boot-starter-jdbc
• spring-boot-starter-web
• spring-boot-devtools
• JSTL - JSP Standard Tag Library
• tomcat-embed-jasper
• mysql-connector-java
• Bootstrap 4
• Java 8

MySQL create database and tables

Find the MySQL database and table used in this tutorial.

CREATE DATABASE `jackrutorial` /*!40100 DEFAULT CHARACTER SET utf8 */;

DROP TABLE IF EXISTS `jackrutorial`.`employees`;
CREATE TABLE  `jackrutorial`.`employees` (
  `employee_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(45) NOT NULL default '',
  `last_name` varchar(45) NOT NULL default '',
  `email` varchar(45) NOT NULL default '',
  `phone` varchar(20) NOT NULL default '',
  `job_title` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`employee_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Project Directory

The following screenshot shows final structure of the project.

Creating a Spring Boot Project with Eclipse STS

Launch Eclipse IDE. Go to File -> New -> Other... Select Spring Starter Project under Spring Boot category then click Next as shown below

In the next screen, you enter the content as shown below then click Next

In the next step, you choose Spring Boot Version is 2.1.0 (SNAPSHOT) and choose the Web + DevTools + JDBC + MySQL, then click Finish.

Project Dependencies

We will add the required dependencies to Maven pom.xml File

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
</dependency>
<dependency>
 <groupId>javax.servlet</groupId>
 <artifactId>jstl</artifactId>
</dependency>
<dependency>
 <groupId>org.webjars</groupId>
 <artifactId>bootstrap</artifactId>
 <version>4.0.0</version>
</dependency>
<dependency>
 <groupId>org.apache.tomcat.embed</groupId>
 <artifactId>tomcat-embed-jasper</artifactId>
 <scope>provided</scope>
</dependency>

The updated pom.xml file will have the following code

<?xml version="1.0" encoding="UTF-8"?>
<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>WebApplicationSpringBoot</artifactId>
 <version>0.0.1-SNAPSHOT</version>
 <packaging>jar</packaging>

 <name>WebApplicationSpringBoot</name>
 <description>Demo project for Spring Boot</description>

 <parent>
  <groupId>org.springframework.boot</groupId>
  <artifactId>spring-boot-starter-parent</artifactId>
  <version>2.1.0.BUILD-SNAPSHOT</version>
  <relativePath /> <!-- lookup parent from repository -->
 </parent>

 <properties>
  <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
  <java.version>1.8</java.version>
 </properties>

 <dependencies>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-jdbc</artifactId>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-web</artifactId>
  </dependency>

  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-devtools</artifactId>
   <scope>runtime</scope>
  </dependency>
  <dependency>
   <groupId>org.springframework.boot</groupId>
   <artifactId>spring-boot-starter-test</artifactId>
   <scope>test</scope>
  </dependency>
  <dependency>
   <groupId>javax.servlet</groupId>
   <artifactId>jstl</artifactId>
  </dependency>
  <dependency>
   <groupId>org.webjars</groupId>
   <artifactId>bootstrap</artifactId>
   <version>4.0.0</version>
  </dependency>
  <dependency>
   <groupId>org.apache.tomcat.embed</groupId>
   <artifactId>tomcat-embed-jasper</artifactId>
   <scope>provided</scope>
  </dependency>
  <dependency>
   <groupId>mysql</groupId>
   <artifactId>mysql-connector-java</artifactId>
   <scope>runtime</scope>
  </dependency>
 </dependencies>

 <build>
  <plugins>
   <plugin>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-maven-plugin</artifactId>
   </plugin>
  </plugins>
 </build>

 <repositories>
  <repository>
   <id>spring-snapshots</id>
   <name>Spring Snapshots</name>
   <url>https://repo.spring.io/snapshot</url>
   <snapshots>
    <enabled>true</enabled>
   </snapshots>
  </repository>
  <repository>
   <id>spring-milestones</id>
   <name>Spring Milestones</name>
   <url>https://repo.spring.io/milestone</url>
   <snapshots>
    <enabled>false</enabled>
   </snapshots>
  </repository>
 </repositories>

 <pluginRepositories>
  <pluginRepository>
   <id>spring-snapshots</id>
   <name>Spring Snapshots</name>
   <url>https://repo.spring.io/snapshot</url>
   <snapshots>
    <enabled>true</enabled>
   </snapshots>
  </pluginRepository>
  <pluginRepository>
   <id>spring-milestones</id>
   <name>Spring Milestones</name>
   <url>https://repo.spring.io/milestone</url>
   <snapshots>
    <enabled>false</enabled>
   </snapshots>
  </pluginRepository>
 </pluginRepositories>


</project>

Model

Create a Employee class under com.jackrutorial.model package with the following code.

package com.jackrutorial.model;

public class Employee {
 private Integer employeeId;
 private String firstName;
 private String lastName;
 private String email;
 private String phone;
 private String jobTitle;
 public Integer getEmployeeId() {
  return employeeId;
 }
 public void setEmployeeId(Integer employeeId) {
  this.employeeId = employeeId;
 }
 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 getEmail() {
  return email;
 }
 public void setEmail(String email) {
  this.email = email;
 }
 public String getPhone() {
  return phone;
 }
 public void setPhone(String phone) {
  this.phone = phone;
 }
 public String getJobTitle() {
  return jobTitle;
 }
 public void setJobTitle(String jobTitle) {
  this.jobTitle = jobTitle;
 }
}

The RowMapper is used to map a single row to a single domain object. We will create a EmployeeRowMapper implementing RowMapper interface under com.jackrutorial.model package with the code.

package com.jackrutorial.model;

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

import org.springframework.jdbc.core.RowMapper;

public class EmployeeRowMapper implements RowMapper<Employee> {

 @Override
 public Employee mapRow(ResultSet rs, int rowNum) throws SQLException {
  Employee employee = new Employee();
  employee.setEmployeeId(rs.getInt("employee_id"));
  employee.setFirstName(rs.getString("first_name"));
  employee.setLastName(rs.getString("last_name"));
  employee.setEmail(rs.getString("email"));
  employee.setPhone(rs.getString("phone"));
  employee.setJobTitle(rs.getString("job_title"));
  return employee;
 }

}

DAO Layer

Create a EmployeeDao 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.*;

public interface EmployeeDao {
 
 public List<Employee> getAllEmployees();
 
 public Employee findeEmployeeById(int id);
 
 public void addEmployee(Employee employee);
 
 public void updateEmployee(Employee employee);
 
 public void deleteEmployee(int id);
}

Create a EmployeeDaoImpl class implements EmployeeDao interface under com.jackrutorial.dao package and write the following code in it.

package com.jackrutorial.dao;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.jackrutorial.model.Employee;
import com.jackrutorial.model.EmployeeRowMapper;

@Transactional
@Repository
public class EmployeeDaoImpl implements EmployeeDao {
 
 @Autowired
 private JdbcTemplate jdbcTemplate;

 @Override
 public List<Employee> getAllEmployees() {
  String query = "SELECT * from employees";
  RowMapper<Employee> rowMapper = new EmployeeRowMapper();
  List<Employee> list = jdbcTemplate.query(query, rowMapper);
  
  return list;
 }

 @Override
 public Employee findeEmployeeById(int id) {
  String query = "SELECT * FROM employees WHERE employee_id = ?";
  RowMapper<Employee> rowMapper = new BeanPropertyRowMapper<Employee>(Employee.class);
  Employee employee = jdbcTemplate.queryForObject(query, rowMapper, id);
  
  return employee;
 }

 @Override
 public void addEmployee(Employee employee) {
  String query = "INSERT INTO employees(employee_id, first_name, last_name, email, phone, job_title) VALUES(?, ?, ?, ?, ?, ?)";
  jdbcTemplate.update(query, employee.getEmployeeId(), employee.getFirstName(), employee.getLastName(), employee.getEmail(), employee.getPhone(), employee.getJobTitle());
  
 }

 @Override
 public void updateEmployee(Employee employee) {
  String query = "UPDATE employees SET first_name=?, last_name=?, email=?, phone=?, job_title=? WHERE employee_id=?";
  jdbcTemplate.update(query, employee.getFirstName(), employee.getLastName(), employee.getEmail(), employee.getPhone(), employee.getJobTitle(), employee.getEmployeeId());
  
 }

 @Override
 public void deleteEmployee(int id) {
  String query = "DELETE FROM employees WHERE employee_id=?";
  jdbcTemplate.update(query, id);
 }

}

In code snippet above, we use JdbcTemplate query(), queryForObject() methods to query data from mysql database and use the JdbcTemplate update() method for updating records in database.

Service Layer

Create a EmployeeService 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.Employee;

public interface EmployeeService {
 public List<Employee> getAllEmployees();
 
 public Employee findEmployeeById(int id);
 
 public void addEmployee(Employee employee);
 
 public void updateEmployee(Employee employee);
 
 public void deleteEmployee(int id);
}

Create a EmployeeServiceImpl class implements EmployeeService 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.EmployeeDaoImpl;
import com.jackrutorial.model.Employee;

@Service
public class EmployeeServiceImpl implements EmployeeService {
 
 @Autowired
 private EmployeeDaoImpl employeeDao;

 @Override
 public List<Employee> getAllEmployees() {
  return employeeDao.getAllEmployees();
 }

 @Override
 public Employee findEmployeeById(int id) {
  return employeeDao.findeEmployeeById(id);
 }

 @Override
 public void addEmployee(Employee employee) {
  employeeDao.addEmployee(employee);
 }

 @Override
 public void updateEmployee(Employee employee) {
  employeeDao.updateEmployee(employee);
 }

 @Override
 public void deleteEmployee(int id) {
  employeeDao.deleteEmployee(id);
 }

}

Employee Controller

We create a controller class named EmployeeController to integrate with the MySQL database using the EmployeeServiceImpl class. Create a EmployeeController class 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.Employee;
import com.jackrutorial.service.EmployeeServiceImpl;

@Controller
@RequestMapping("/employee")
public class EmployeeController {

 @Autowired
 private EmployeeServiceImpl employeeService;
 
 @RequestMapping(value= {"/", "/list"}, method=RequestMethod.GET)
 public ModelAndView getAllEmployees() {
  ModelAndView model = new ModelAndView();
  List<Employee> list = employeeService.getAllEmployees();
  
  model.addObject("employee_list", list);
  model.setViewName("employee_list");
  return model;
 }
 
 @RequestMapping(value="/update/{id}", method=RequestMethod.GET)
 public ModelAndView editEmployee(@PathVariable int id) {
  ModelAndView model = new ModelAndView();
  
  Employee employee = employeeService.findEmployeeById(id);
  model.addObject("employeeForm", employee);
  
  model.setViewName("employee_form");
  return model;
 }
 
 @RequestMapping(value="/add", method=RequestMethod.GET)
 public ModelAndView addEmployee() {
  ModelAndView model = new ModelAndView();
  
  Employee employee = new Employee();
  model.addObject("employeeForm", employee);
  
  model.setViewName("employee_form");
  return model;
 }
 
 @RequestMapping(value="/save", method=RequestMethod.POST)
 public ModelAndView saveOrUpdate(@ModelAttribute("employeeForm") Employee employee) {
  if(employee.getEmployeeId() != null) {
   employeeService.updateEmployee(employee);
  } else {
   employeeService.addEmployee(employee);
  }
  
  return new ModelAndView("redirect:/employee/list");
 }
 
 @RequestMapping(value="/delete/{id}", method=RequestMethod.GET)
 public ModelAndView deleteEmployee(@PathVariable("id") int id) {
  employeeService.deleteEmployee(id);
  
  return new ModelAndView("redirect:/employee/list");
 }
}

Configuring Spring Boot for MySQL and view resolvers

In the sources folder, we will look for this project's a resource file under src/main/resources/application.properties. Open application.properties file and add the following properties.

spring.datasource.url=jdbc:mysql://localhost:3306/jackrutorial
spring.datasource.username=root
spring.datasource.password=root

spring.mvc.view.prefix=/WEB-INF/jsp/
spring.mvc.view.suffix=.jsp

View Layer

Create jsp folder under src\main\webapp\WEB-INF\ folder.
Create employee_list.jsp and employee_form.jsp file under src\main\webapp\WEB-INF\jsp\ folder and write the following code in it.
employee_list.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" 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>Employee List</title>
 <link href="../webjars/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet" />
 <script src="../webjars/bootstrap/4.0.0/js/bootstrap.min.js" ></script>
 <script src="../webjars/jquery/3.0.0/js/jquery.min.js" ></script>
</head>
<body>
 <div class="container">
  <h2>Employee List</h2>
  <table class="table table-striped">
   <thead>
    <tr>
     <th scope="row">Employee Id</th>
     <th scope="row">First Name</th>
     <th scope="row">Last Name</th>
     <th scope="row">Email</th>
     <th scope="row">Phone</th>
     <th scope="row">Job Title</th>
     <th scope="row">Edit</th>
     <th scope="row">Delete</th>
    </tr>
   </thead>
   <tbody>
    <c:forEach items="${employee_list }" var="employee" >
     <tr>
      <td>${employee.employeeId }</td>
      <td>${employee.firstName }</td>
      <td>${employee.lastName }</td>
      <td>${employee.email }</td>
      <td>${employee.phone }</td>
      <td>${employee.jobTitle }</td>
      <td>
       <spring:url value="/employee/update/${employee.employeeId }" var="updateURL" />
       <a class="btn btn-primary" href="${updateURL }" role="button">Update</a>
      </td>
      <td>
       <spring:url value="/employee/delete/${employee.employeeId }" var="deleteURL" />
       <a class="btn btn-primary" href="${deleteURL }" role="button">Delete</a>
      </td>
     </tr>
    </c:forEach>
   </tbody>
  </table>
  <spring:url value="/employee/add" var="addURL" />
  <a class="btn btn-primary" href="${addURL }" role="button">Add New Employee</a>
 </div>
</body>
</html>

employee_form.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" 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>Employees</title>
 <link href="http://localhost:8080/webjars/bootstrap/4.0.0/css/bootstrap.min.css" rel="stylesheet" />
 <script src="http://localhost:8080/webjars/bootstrap/4.0.0/js/bootstrap.min.js" ></script>
 <script src="http://localhost:8080/webjars/jquery/3.0.0/js/jquery.min.js" ></script>
</head>
<body>
 <div class="container">
  <spring:url value="/employee/save" var="saveURL" />
  <h2>Employee</h2>
  <form:form modelAttribute="employeeForm" method="post" action="${saveURL }" cssClass="form">
   <form:hidden path="employeeId"/>
   <div class="form-group">
    <lable for="firstName">First Name</lable>
    <form:input path="firstName" cssClass="form-control" id="firstName" />
   </div>
   <div class="form-group">
    <lable for="lastName">Last Name</lable>
    <form:input path="lastName" cssClass="form-control" id="lastName" />
   </div>
   <div class="form-group">
    <lable for="email">Email</lable>
    <form:input path="email" cssClass="form-control" id="email" />
   </div>
   <div class="form-group">
    <lable for="phone">Phone</lable>
    <form:input path="phone" cssClass="form-control" id="phone" />
   </div>
   <div class="form-group">
    <lable for="jobTitle">Job Title</lable>
    <form:input path="jobTitle" cssClass="form-control" id="jobTitle" />
   </div>
   <button type="submit" class="btn btn-primary">Save</button>
  </form:form>
 </div>
</body>
</html>

Run Spring Boot Application

• Right click to the Project and follow the below steps:
• select Run As -> Maven clean.
• select Run As -> Maven install.
• select Run As -> Spring Boot App.

View console output in eclipse, you will see following output:

INFO 15804 --- [  restartedMain] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/employee/add],methods=[GET]}" onto public org.springframework.web.servlet.ModelAndView com.jackrutorial.controller.EmployeeController.addEmployee()
INFO 15804 --- [  restartedMain] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/employee/delete/{id}],methods=[GET]}" onto public org.springframework.web.servlet.ModelAndView com.jackrutorial.controller.EmployeeController.deleteEmployee(int)
INFO 15804 --- [  restartedMain] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/employee/update/{id}],methods=[GET]}" onto public org.springframework.web.servlet.ModelAndView com.jackrutorial.controller.EmployeeController.editEmployee(int)
INFO 15804 --- [  restartedMain] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/employee/save],methods=[POST]}" onto public org.springframework.web.servlet.ModelAndView com.jackrutorial.controller.EmployeeController.saveOrUpdate(com.jackrutorial.model.Employee)
INFO 15804 --- [  restartedMain] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/employee/ || /employee/list],methods=[GET]}" onto public org.springframework.web.servlet.ModelAndView com.jackrutorial.controller.EmployeeController.getAllEmployees()
INFO 15804 --- [  restartedMain] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error]}" onto public org.springframework.http.ResponseEntity<java.util.Map<java.lang.String, java.lang.Object>> org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController.error(javax.servlet.http.HttpServletRequest)
INFO 15804 --- [  restartedMain] s.w.s.m.m.a.RequestMappingHandlerMapping : Mapped "{[/error],produces=[text/html]}" onto public org.springframework.web.servlet.ModelAndView org.springframework.boot.autoconfigure.web.servlet.error.BasicErrorController.errorHtml(javax.servlet.http.HttpServletRequest,javax.servlet.http.HttpServletResponse)
INFO 15804 --- [  restartedMain] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/webjars/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
INFO 15804 --- [  restartedMain] o.s.w.s.handler.SimpleUrlHandlerMapping  : Mapped URL path [/**] onto handler of type [class org.springframework.web.servlet.resource.ResourceHttpRequestHandler]
INFO 15804 --- [  restartedMain] o.s.b.d.a.OptionalLiveReloadServer       : LiveReload server is running on port 35729
INFO 15804 --- [  restartedMain] o.s.j.e.a.AnnotationMBeanExporter        : Registering beans for JMX exposure on startup
INFO 15804 --- [  restartedMain] o.s.j.e.a.AnnotationMBeanExporter        : Bean with name 'dataSource' has been autodetected for JMX exposure
INFO 15804 --- [  restartedMain] o.s.j.e.a.AnnotationMBeanExporter        : Located MBean 'dataSource': registering with JMX server as MBean [com.zaxxer.hikari:name=dataSource,type=HikariDataSource]
INFO 15804 --- [  restartedMain] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
INFO 15804 --- [  restartedMain] c.j.WebApplicationSpringBootApplication  : Started WebApplicationSpringBootApplication in 3.427 seconds (JVM running for 4.655)

Employee List
Type the following URLs in browser's address bar to open the Employee List page.

http://localhost:8080/employee/list

Add New Employee
Type the following URLs in browser's address bar to open the Add New Employee page.

http://localhost:8080/employee/add

Update Employee
Type the following URLs in browser's address bar to open the Update Employee page.

http://localhost:8080/employee/update/1

• Tweet
• Share
• Share
• Share
• Share