Spring Boot MVC JdbcTemplate CRUD Example

Introduction

The primary motivation behind developing Spring Boot is to simplify the process for configuring and deploying the spring applications. Spring Boot will get rid of all the fuss involved on configuring the dependencies for building the enterprise applications. In Spring based applications, configurations are loaded with a bunch of XML files, now these configurations should not be maintained in a separate file which is extra work for the developers. Developers need to focus on only writing the application while Spring Boot will simplify the deployment process by packaging and deploying application without any explicit configurations.

Related Posts:

Spring Boot makes it easy to create stand-alone, production-grade Spring based Applications that you can “just run”. We take an opinionated view of the Spring platform and third-party libraries so you can get started with minimum fuss. Most Spring Boot applications need very little Spring configuration.

Features Of Spring Boot

  1. Create stand-alone Spring applications
  2. Embed Tomcat, Jetty or Undertow directly (no need to deploy WAR files)
  3. Provide opinionated ‘starter’ POMs to simplify your Maven configuration
  4. Automatically configure Spring whenever possible
  5. Provide production-ready features such as metrics, health checks and externalized configuration
  6. Absolutely no code generation and no requirement for XML configuration

Prerequisites

Java 1.8+, Maven 3.6.3 – 3.8.2, Spring Boot 1.4.0.RELEASE – 2.6.1, MySQL 5.x – 8.0.26

Project Setup

The maven based project can be setup in any tool or IDE and the following pom.xml file can be a reference to it

<?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.roytuts</groupId>
	<artifactId>spring-boot-mvc-jdbctemplate-crud</artifactId>
	<version>0.0.1-SNAPSHOT</version>

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
		<maven.compiler.source>16</maven.compiler.source>
		<maven.compiler.target>16</maven.compiler.target>
	</properties>

	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.6.1</version>
	</parent>

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

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

		<dependency>
			<groupId>org.apache.tomcat.embed</groupId>
			<artifactId>tomcat-embed-jasper</artifactId>
			<scope>provided</scope>
		</dependency>

		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>

		<!--required only if jdk 9 or higher version is used -->
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<scope>runtime</scope>
		</dependency>
	</dependencies>

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

MySQL table and Data

The following teacher table under roytuts database is created and some sample data are stored to test the application right away.

For MySQL version 5.x, use the following structure:

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `expertise` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

For MySQL version 8.x, use the following table structure:

DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
  `id` int unsigned COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
  `name` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `expertise` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Sample data inserted into the teacher table:

insert  into `teacher`(`id`,`name`,`expertise`)
values (1,'Bibhas Chandra Dhara','Statistics'),
(2,'UKR','System Programming'),(3,'New','Expert');

application.properties

The src/main/resources/application.properties file contains the configurations for Spring Boot application. Here I have defined the datasource configuration and also specified the view resolver configuration.

#database configuration
spring.datasource.url=jdbc:mysql://localhost:3306/roytuts
spring.datasource.username=root
spring.datasource.password=root
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
 
#view resolver
spring.mvc.view.prefix=/views/
spring.mvc.view.suffix=.jsp

The view resolver configuration tells Spring that the application is going to use JSP pages as views and these JSP pages will be stored under src/main/webapp/views folder.

Model Class

The following model class maps Java fields to the columns of the table.

public class Teacher {

	private int id;
	private String name;
	private String expertise;

	public Teacher() {
	}

	public Teacher(int id, String name, String expertise) {
		this.id = id;
		this.name = name;
		this.expertise = expertise;
	}
}

Mapper Class

The row mapper class maps each row data with Java object:

public class TeacherRowMapper implements RowMapper<Teacher> {

	@Override
	public Teacher mapRow(ResultSet rs, int row) throws SQLException {
		Teacher teacher = new Teacher();
		teacher.setId(rs.getInt("id"));
		teacher.setName(rs.getString("name"));
		teacher.setExpertise(rs.getString("expertise"));
		return teacher;
	}

}

DAO Class

The DAO (Data Access Object) class is responsible for interacting with database for performing the required operations, such as, inserting or saving, updating, deleting and fetching data.

@Repository
@Transactional
public class TeacherDao {
	@Autowired
	private JdbcTemplate jdbcTemplate;

	public Teacher getTeacher(final int id) {
		Teacher teacher = jdbcTemplate.queryForObject("select * from teacher where id = ?", new TeacherRowMapper(), id);
		return teacher;
	}

	public List<Teacher> getTeachers() {
		List<Teacher> teachers = jdbcTemplate.query("select * from teacher", new TeacherRowMapper());
		return teachers;
	}

	public void addTeacher(final Teacher teacher) {
		jdbcTemplate.update("insert into teacher(name, expertise) values(?, ?)",
				new Object[] { teacher.getName(), teacher.getExpertise() });
	}

	public void updateTeacher(final Teacher teacher) {
		jdbcTemplate.update("update teacher set name = ?, expertise = ? where id = ?",
				new Object[] { teacher.getName(), teacher.getExpertise(), teacher.getId() });
	}

	public void deleteTeacher(final int id) {
		jdbcTemplate.update("delete from teacher where id = ?", new Object[] { id });
	}

}

Service Class

The service class which is responsible for performing business logic for the application. Here I am defining methods for performing CRUD operations.

@Service
public class TeacherService {

	@Autowired
	private TeacherDao teacherDao;

	public Teacher getTeacher(final int id) {
		return teacherDao.getTeacher(id);
	}

	public List<Teacher> getTeachers() {
		return teacherDao.getTeachers();
	}

	public void addTeacher(final Teacher teacher) {
		teacherDao.addTeacher(teacher);
	}

	public void updateTeacher(final Teacher teacher) {
		teacherDao.updateTeacher(teacher);
	}

	public void deleteTeacher(final int id) {
		teacherDao.deleteTeacher(id);
	}

}

Controller Class

The controller class is responsible for handling requests and responses from the end users or clients.

@Controller
public class TeacherController {

	@Autowired
	private TeacherService teacherService;

	@GetMapping("/teacher/{id}")
	public String getTeacher(@PathVariable int id, ModelMap teacherModel) {
		Teacher teacher = teacherService.getTeacher(id);

		teacherModel.addAttribute("teacher", teacher);

		return "teacher";
	}

	@GetMapping("/teachers")
	public String getTeachers(ModelMap teacherModel) {
		List<Teacher> teachers = teacherService.getTeachers();

		teacherModel.addAttribute("teachers", teachers);

		return "teachers";
	}

	@GetMapping("addTeacher")
	public String addPage() {
		return "add";
	}

	@PostMapping("/add/teacher")
	public String addTeacher(@RequestParam(value = "name", required = true) String name,
			@RequestParam(value = "expertise", required = true) String expertise, ModelMap teacherModel) {
		Teacher teacher = new Teacher();
		teacher.setName(name);
		teacher.setExpertise(expertise);

		teacherService.addTeacher(teacher);

		teacherModel.addAttribute("msg", "Teacher added successfully");
		List<Teacher> teachers = teacherService.getTeachers();
		teacherModel.addAttribute("teachers", teachers);

		return "redirect:/teachers";
	}

	@GetMapping("update/teacher/{id}")
	public String updatePage(@PathVariable("id") int id, ModelMap teacherModel) {
		Teacher teacher = teacherService.getTeacher(id);

		teacherModel.addAttribute("id", id);
		teacherModel.addAttribute("teacher", teacher);

		return "update";
	}

	@PostMapping("/update/teacher")
	public String updateTeacher(@RequestParam int id, @RequestParam(value = "name", required = true) String name,
			@RequestParam(value = "expertise", required = true) String expertise, ModelMap teacherModel) {
		Teacher teacher = new Teacher();
		teacher.setId(id);
		teacher.setName(name);
		teacher.setExpertise(expertise);

		teacherService.updateTeacher(teacher);

		List<Teacher> teachers = teacherService.getTeachers();
		teacherModel.addAttribute("teachers", teachers);
		teacherModel.addAttribute("id", id);
		teacherModel.addAttribute("msg", "Teacher updated successfully");

		return "redirect:/teachers";
	}

	@GetMapping("/delete/teacher/{id}")
	public String deleteTeacher(@PathVariable int id, ModelMap teacherModel) {
		teacherService.deleteTeacher(id);

		List<Teacher> teachers = teacherService.getTeachers();
		teacherModel.addAttribute("teachers", teachers);
		teacherModel.addAttribute("msg", "Teacher deleted successfully");

		return "redirect:/teachers";
	}

}

I have defined several methods for fetching, adding, updating and deleting records from user interface.

I am redirecting to the /teachers endpoint when adding a new teacher information, deleting an existing teacher information, updating an existing teacher information and adding a new teacher information. So, after performing these operations, you will be redirected to the main page of the application where a list of teachers are displayed. You can also find details for a teacher.

Views

The views or JSP pages are kept under src/main/webapp/views folder. The following JSP code displays all teachers from the database:

<%@ 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"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Spring Boot MVC JdbcTemplate CRUD Example</title>
</head>
<body>
       <h2>Spring Boot MVC JdbcTemplate CRUD Example - List of Teachers</h2>
       <c:if test="${not empty msg}">
        ${msg}
    </c:if>
    <a href="<%=request.getContextPath()%>/addTeacher">Add New Teacher</a>
       <c:choose>
             <c:when test="${teachers != null}">
                    <h3>List of Teachers</h3>
                    <table cellpadding="5" cellspacing="5">
                          <thead>
                                 <tr>
                                       <th>ID</th>
                                       <th>Name</th>
                                       <th>Expertise</th>
                                        <th>Actions</th>
                                 </tr>
                          </thead>
                          <tbody>
                                 <c:forEach var="t" items="${teachers}">
                                       <tr>
                                              <td>${t.id}</td>
                                              <td>${t.name}</td>
                                              <td>${t.expertise}</td>
                                              <td><a
                                                    href="<%=request.getContextPath()%>/teacher/${t.id}">Details</a>
                                                    &nbsp; <a
                                                    href="<%=request.getContextPath()%>/update/teacher/${t.id}">Update</a>
                                                    &nbsp; <a
                                                    href="<%=request.getContextPath()%>/delete/teacher/${t.id}"
                                                     onclick="return confirm('Do you really want to delete?')">Delete</a></td>
                                       </tr>
                                 </c:forEach>
                          </tbody>
                    </table>
             </c:when>
             <c:otherwise>
             No User found in the database!
        </c:otherwise>
       </c:choose>
</body>
</html>

The above JSP code is written into teachers.jsp file.

The data are shown in the HTML tabular format. There are some actions, such as, Details, Update and Delete. So, these operations are self-explanatory.

To check a single teacher details, use the following code in JSP file – teacher.jsp:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
       pageEncoding="ISO-8859-1"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Spring Boot MVC JdbcTemplate CRUD Example</title>
</head>
<body>
       <h2>Spring Boot MVC JdbcTemplate CRUD Example - Teacher Details</h2>
       <a href="<%=request.getContextPath()%>/teachers">List of Teachers</a>
       <p />
       Id : ${teacher.id}
       <br /> Name : ${teacher.name}
       <br /> Expertise : ${teacher.expertise}
</body>
</html>

The following JSP page – add.jsp – is used to add new teacher.

<%@ 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"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Spring Boot MVC JdbcTemplate CRUD Example</title>                                                             
</head>
<body>
       <h2>Spring Boot MVC JdbcTemplate CRUD Example - Add New Teacher</h2>
       <c:if test="${not empty msg}">
        ${msg}
    </c:if>
       <h3>Add User</h3>
       <form method="POST" name="add_teacher"
             action="<%=request.getContextPath()%>/add/teacher">
             Name: <input name="name" value="${name}" type="text" /> <br /> <br />
             Expertise: <input name="expertise" value="${expertise}" type="text" />
             <br /> <br /> <input value="Add Teacher" type="submit" />
       </form>
</body>
</html>

The following JSP page – update.jsp – is used to update the existing teacher information.

<%@ 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"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Spring Boot MVC JdbcTemplate CRUD Example</title>
</head>
<body>
       <h2>Spring Boot MVC JdbcTemplate CRUD Example - Update Existing Teacher</h2>
       <c:if test="${not empty msg}">
        ${msg}
    </c:if>
       <h3>Update User</h3>
       <form method="POST" name="update_teacher"
             action="<%=request.getContextPath()%>/update/teacher">
             <input hidden="hidden" name="id" value="${id}" type="text" /> Name: <input
                    name="name" value="${teacher.name}" type="text" /> <br /> <br />
             Expertise: <input name="expertise" value="${teacher.expertise}"
                    type="text" /> <br /> <br /> <input value="Update User"
                    type="submit" />
       </form>
</body>
</html>

Main Class

The main class with @SpringBootApplication annotation will start the application automatically.

@SpringBootApplication
public class SpringBootMvcJdbcTemplateCrudApp {

	public static void main(String[] args) {
		SpringApplication.run(SpringBootMvcJdbcTemplateCrudApp.class, args);
	}

}

Testing – Spring Boot MVC JdbcTemplate CRUD

Access the URL http://localhost:8080/teachers in the browser and you will see the following page:

spring boot mvc jdbctemplate

You will find several hyperlinks for performing different operations. The Add New Teacher will take you to the New Page where you want to add new teacher information.

You can see the teacher information details using the Details link. You can update using the Update and delete using the Delete link.

You can update an existing teacher using Update link:

spring boot mvc jdbctemplate

To delete teacher a teacher you can use the Delete link:

spring boot mvc jdbctemplate

To see a particular of the teacher you may use Details link:

spring boot mvc jdbctemplate

To add a new teacher, you can click on Add New Teacher link on the home page:

spring boot mvc jdbctemplate

You will see the new teacher added and saved into the database:

spring boot mvc jdbctemplate

That’s all about how to build Spring Boot MVC JdbcTemplate CRUD example.

Source Code

Download

Leave a Reply

Your email address will not be published. Required fields are marked *