Spring Boot MVC and JDBC CRUD Example

Introduction

In my other tutorial I had shown how to create an example on Spring MVC and JDBC CRUD example using annotations only, but here I am going to use Spring Boot framework. I did not update the other tutorial keeping in mind if someone still needs the example using Spring framework only. Here also I am going to show you the same example but using Spring Boot framework.

We are going to use both gradle and maven build tools to build the application. We are going to see how to create Datasource, JdbcTemplate to perform CRUD operations on MySQL database.

Prerequisites

Eclipse 4.12, At least Java 8, Gradle 6.1.1, Maven 3.6.3, Spring Boot 2.2.4, MySQL 8.0.17

Create MySQL Table

First thing we recommend is to create a table called teacher in MySQL server under roytuts database.

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

As we are going to show some data initially when application URL is hit on the browser, so we are going to dump some data into the above table:

insert  into `teacher`(`id`,`name`,`expertise`)
values (16,'Bibhas Chandra Dhara','Statistics'),
(18,'UKR','System Programming'),
(19,'New','Expert');

Create Project

Now you may either create gradle or maven based project in the Eclipse IDE. The name of the project is spring-mvc-jdbc-crud.

If you are creating gradle based project then use below build.gradle script in order to build your project.

buildscript {
	ext {
		springBootVersion = '2.2.4.RELEASE'
	}
    repositories {
    	mavenLocal()
    	mavenCentral()
    }
    dependencies {
    	classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
    }
}

apply plugin: 'java'
apply plugin: 'org.springframework.boot'
    
sourceCompatibility = 12
targetCompatibility = 12

repositories {
	mavenLocal()
    mavenCentral()
}

dependencies {
	implementation("org.springframework.boot:spring-boot-starter-web:${springBootVersion}")
	implementation("org.springframework.boot:spring-boot-starter-jdbc:${springBootVersion}")
	implementation('org.apache.tomcat.embed:tomcat-embed-jasper:9.0.30')
    implementation('javax.servlet:jstl:1.2')
    implementation('mysql:mysql-connector-java:8.0.17')
    //required only if jdk 9 or higher version is used
    runtimeOnly('javax.xml.bind:jaxb-api:2.4.0-b180830.0359')
}

If you are creating maven based project in Eclipse, then you may use the below pom.xml file for building your project.

<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-mvc-jdbc-crud</artifactId>
	<version>0.0.1-SNAPSHOT</version>

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

	<properties>
		<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
	</properties>

	<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>
			<version>9.0.30</version>
		</dependency>
		
		<dependency>
			<groupId>javax.servlet</groupId>
			<artifactId>jstl</artifactId>
			<version>1.2</version>
		</dependency>
		
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.17</version>
		</dependency>
		
		//required only if jdk 9 or higher version is used
		<dependency>
			<groupId>javax.xml.bind</groupId>
			<artifactId>jaxb-api</artifactId>
			<version>2.4.0-b180830.0359</version>
		</dependency>
	</dependencies>

    <build>
        <plugins>
			<plugin>
				<groupId>org.apache.maven.plugins</groupId>
				<artifactId>maven-compiler-plugin</artifactId>
				<version>3.8.1</version>
				<configuration>
					<source>at least 8</source>
					<target>at least 8</target>
				</configuration>
			</plugin>
		</plugins>
	</build>
</project>

Database and View Resolver Configurations

Now we need to configure our database settings in Spring Boot application. We also need to configure view resolver as we are going to use JSP pages as view technology.

Create application.properties file under classpath directory src/main/resources with the following content.

#datasource
jdbc.driverClassName=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/roytuts
jdbc.username=root
jdbc.password=root

#view resolver
spring.mvc.view.prefix=/views/
spring.mvc.view.suffix=.jsp

Create corresponding Datasource and JdbcTemplate beans to interact with database and perform the required operations.

package com.roytuts.spring.mvc.jdbc.crud.config;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.datasource.DriverManagerDataSource;

@Configuration
public class DbConfig {

	@Autowired
	private Environment environment;

	@Bean
	public DataSource dataSource() {
		DriverManagerDataSource dataSource = new DriverManagerDataSource();

		dataSource.setDriverClassName(environment.getProperty("jdbc.driverClassName"));
		dataSource.setUrl(environment.getProperty("jdbc.url"));
		dataSource.setUsername(environment.getProperty("jdbc.username"));
		dataSource.setPassword(environment.getProperty("jdbc.password"));

		return dataSource;
	}

	@Bean
	public JdbcTemplate getJdbcTemplate() throws ClassNotFoundException {
		JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource());
		return jdbcTemplate;
	}

}

Create Model Class

We need a POJO class that will simply represent database table.

package com.roytuts.spring.mvc.jdbc.crud.model;

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;
	}

	//getters and setters

}

Create Row Mapper

We will create a row mapper class that implements Spring’s RowMapper interface to map the model class attributes to database table columns.

package com.roytuts.spring.mvc.jdbc.crud.row.mapper;

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

import org.springframework.jdbc.core.RowMapper;

import com.roytuts.spring.mvc.jdbc.crud.model.Teacher;

public class TeacherRowMapper implements RowMapper<Teacher> {

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

}

Repository Class

The repository is sole responsible for fetching data, updating data, inserting data and deleting data into database table.

We have applied Spring’s transaction to rollback the any write to database in case any exception occurs. Generally Transactional annotation should be used for write operations only (on methods) but for simplicity I have put it on class level.

package com.roytuts.spring.mvc.jdbc.crud.dao;

import java.util.List;

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

import com.roytuts.spring.mvc.jdbc.crud.model.Teacher;
import com.roytuts.spring.mvc.jdbc.crud.row.mapper.TeacherRowMapper;

@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 Object[] { id },
				new TeacherRowMapper());
		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

Any business operation is performed in the service class.

package com.roytuts.spring.mvc.jdbc.crud.service;

import java.util.List;

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

import com.roytuts.spring.mvc.jdbc.crud.dao.TeacherDao;
import com.roytuts.spring.mvc.jdbc.crud.model.Teacher;

@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

Create Spring controller class which will handle user request and response. This class exposes the endpoint through which client sends or receives the required data.

package com.roytuts.spring.mvc.jdbc.crud.controller;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;

import com.roytuts.spring.mvc.jdbc.crud.model.Teacher;
import com.roytuts.spring.mvc.jdbc.crud.service.TeacherService;

@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) {
		teacherModel.addAttribute("id", id);
		Teacher teacher = teacherService.getTeacher(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(id,name,expertise);
		/*
		 * 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 delted successfully");
		return "redirect:/teachers";
	}

}

Create Main Class

A class with main method and @SpringBootApplication is enough to deploy the Spring Boot application into embedded Tomcat server.

package com.roytuts.spring.mvc.jdbc.crud;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpringMvcJdbcCrudApp {

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

}

Web Pages

As this application is Spring Boot MVC, so we will create few web pages to perform our CRUD (Create Read Update Delete) operations.

We will put web pages (JSP) under src/main/webapp/views directory.

teachers.jsp

This page will show all teachers from database. This page has a link called Add that will give you the page for adding new record.

<%@ 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"%>
<html>
<title>Spring Boot MVC and JDBC CRUD Example</title>
<body>
	<h2>Spring Boot MVC and JDBC CRUD Example</h2>
	<a href="<%=request.getContextPath()%>/addTeacher">Add</a>
	<c:if test="${not empty msg}">
        ${msg}
    </c:if>
	<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 DB!
        </c:otherwise>
	</c:choose>
</body>
</html>

teacher.jsp

This page shows details of a particular teacher when you click on link Details.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<html>
<title>Spring Boot MVC and JDBC CRUD Example</title>
<body>
	<h2>Spring Boot MVC and JDBC CRUD Example</h2>
	Id : ${teacher.id}
	<br /> Name : ${teacher.name}
	<br /> Expertise : ${teacher.expertise}
</body>
</html>

add.jsp

This page has the input fields which will be used to add new records.

<%@ 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"%>
<html>
<title>Spring Boot MVC and JDBC CRUD Example</title>
<body>
	<h2>Spring Boot MVC and JDBC CRUD Example</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>

update.jsp

This page is responsible fir updating the existing records. The input fields on this page will be populated with the existing records in edit mode.

<%@ 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"%>
<html>
<title>Spring Boot MVC and JDBC CRUD Example</title>
<body>
	<h2>Spring Boot MVC and JDBC CRUD Example</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>

Testing the Application

Once you execute the class having main method and hit the URL http://localhost:8080/teachers on the browser you will see the following page:

Let’s say you want to read or see single teacher details then you can hit URL http://localhost:8080/teacher/16

You can also click on the Details link to see the details:

Let’s say now we want to update the last record that has name “New” with the following records:

Once updated successfully you will be redirected to the list of teachers page:

Let’s say we want to add new record as shown below. Click on the Add link on list of teachers page.

You will be redirected to the list of teachers page with new record added.

Let’s say we want to delete record. So we want to delete the new record we just added.

You will get a confirm box whether you really want to delete the record.

So your record will be deleted and you will be redirected to the list of teachers page.

Source Code

Download

Thanks for reading.