Spring MVC CRUD Example using JdbcTemplate + MySQL

By Atul Rai | Last Updated: November 21, 2018 Previous       Next

In this article, we will show you how to create a Spring MVC application to create, read, update, and delete (CRUD) the student records into the database. Spring JDBC module gives the functionality to create the database connection via DriverManagerDataSource class and to query with the database it provides the JdbcTemplate class.

Steps Involved in the Application

Basically, there are 4 main steps involved in this application that are given below:

1. Capture the student records and store it into the database.
2. Fetch the student records from the database and display it on the JSP.
3. Update the existing student records into the database.
4. Delete the student records from the database.

Technologies Used

Find the list of technologies used in this application.

Eclipse Oxygen | Tomcat 9 | JDK 8 | Maven 3 | Spring5.0.2.RELEASE | MySQL Database | JSTL 1.2

Dependencies Required

These are the required dependencies that must be your build path. To get all these dependencies all the following code in your pom.xml.

<dependencies>
	<!-- spring mvc dependency -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-webmvc</artifactId>
		<version>5.0.2.RELEASE</version>
	</dependency>
	<!-- spring jdbc dependency -->
	<dependency>
		<groupId>org.springframework</groupId>
		<artifactId>spring-jdbc</artifactId>
		<version>5.0.2.RELEASE</version>
	</dependency>
	<!-- mysql databse connector -->
	<dependency>
		<groupId>mysql</groupId>
		<artifactId>mysql-connector-java</artifactId>
		<version>6.0.6</version>
	</dependency>
	<!-- jstl library -->
	<dependency>
		<groupId>jstl</groupId>
		<artifactId>jstl</artifactId>
		<version>1.2</version>
	</dependency>
</dependencies>

Project Structure

Final project structure of our application in Eclipse IDE will look like as follows:

Database Schema

Find the table structure used in this application.

CREATE TABLE `student1` (
  `stu_id` int(5) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(30) DEFAULT NULL,
  `stu_email` varchar(30) DEFAULT NULL,
  `stu_course` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`stu_id`)
);

Model Class

Create a Student model class, declare student specific variable and generates its getter and setters.

package org.websparrow.model;

public class Student {

	// Generate Getter and Setters...
	private int id;
	private String name;
	private String email;
	private String course;

}

DAO Class

Create the StudentDao interface. It will contain the method signature that we implement later.

package org.websparrow.dao;

import java.util.List;

import org.websparrow.model.Student;

public interface StudentDao {

	public int create(Student student);

	public List<Student> read();

	public List<Student> findStudentById(int studentId);

	public int update(Student student);

	public int delete(int studentId);

}

Create another class StudentDaoImpl that implements the StudentDao interface and implementation of all the methods. As we defined the methods in StudentDao interface, every method has a different responsibility.

1- create() will accept the Student type data i.e our model class and return >0 integer value if records saved in the database else return 0.

2- read() method will return the List of all students that stored into the database.

3- findStudentById() will accept the student id and return student details based on the id.

4- update() method will update the existing student records into the database returned by findStudentById() method.

5- delete() will delete student records from the database based on the student id.

package org.websparrow.dao;

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

import javax.sql.DataSource;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.websparrow.model.Student;

public class StudentDaoImpl implements StudentDao {

	private JdbcTemplate jdbcTemplate;

	public StudentDaoImpl(DataSource dataSoruce) {
		jdbcTemplate = new JdbcTemplate(dataSoruce);
	}

	@Override
	public int create(Student student) {

		String sql = "insert into student1(stu_name,stu_email,stu_course) values(?,?,?)";

		try {

			int counter = jdbcTemplate.update(sql,
					new Object[] { student.getName(), student.getEmail(), student.getCourse() });

			return counter;

		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public List<Student> read() {
		List<Student> studentList = jdbcTemplate.query("SELECT * FROM STUDENT1", new RowMapper<Student>() {

			@Override
			public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
				Student student = new Student();

				student.setId(rs.getInt("stu_id"));
				student.setName(rs.getString("stu_name"));
				student.setEmail(rs.getString("stu_email"));
				student.setCourse(rs.getString("stu_course"));

				return student;
			}

		});

		return studentList;
	}

	@Override
	public List<Student> findStudentById(int studentId) {

		List<Student> studentList = jdbcTemplate.query("SELECT * FROM STUDENT1 where stu_id=?",
				new Object[] { studentId }, new RowMapper<Student>() {

					@Override
					public Student mapRow(ResultSet rs, int rowNum) throws SQLException {
						Student student = new Student();

						student.setId(rs.getInt("stu_id"));
						student.setName(rs.getString("stu_name"));
						student.setEmail(rs.getString("stu_email"));
						student.setCourse(rs.getString("stu_course"));

						return student;
					}

				});

		return studentList;
	}

	@Override
	public int update(Student student) {
		String sql = "update  student1 set stu_name=?, stu_email=?, stu_course=? where stu_id=?";

		try {

			int counter = jdbcTemplate.update(sql,
					new Object[] { student.getName(), student.getEmail(), student.getCourse(), student.getId() });

			return counter;

		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

	@Override
	public int delete(int studentId) {
		String sql = "delete from student1 where stu_id=?";

		try {

			int counter = jdbcTemplate.update(sql, new Object[] { studentId });

			return counter;

		} catch (Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

}

Front Controller & MVC Configuration

I have used annotation based configuration, so the front controller class will be:

package org.websparrow.config;

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

public class FrontControllerConfig extends AbstractAnnotationConfigDispatcherServletInitializer {

	@Override
	protected Class<?>[] getRootConfigClasses() {

		return new Class[] { WebMvcConfig.class };
	}

	@Override
	protected Class<?>[] getServletConfigClasses() {

		return null;
	}

	@Override
	protected String[] getServletMappings() {

		return new String[] { "/" };
	}
}

And the Spring MVC configuration class will be given below.

package org.websparrow.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.ComponentScan;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.view.InternalResourceViewResolver;
import org.websparrow.dao.StudentDao;
import org.websparrow.dao.StudentDaoImpl;

@Configuration
@EnableWebMvc
@ComponentScan("org.websparrow")
public class WebMvcConfig {

	@Bean
	InternalResourceViewResolver viewResolver() {

		InternalResourceViewResolver vr = new InternalResourceViewResolver();

		vr.setPrefix("/");
		vr.setSuffix(".jsp");
		return vr;
	}

	@Bean
	DriverManagerDataSource getDataSource() {
		DriverManagerDataSource ds = new DriverManagerDataSource();
		ds.setDriverClassName("com.mysql.jdbc.Driver");
		ds.setUrl("jdbc:mysql://localhost:3306/websparrow");
		ds.setUsername("root");
		ds.setPassword("");

		return ds;
	}

	@Bean
	public StudentDao getUserDao() {
		return new StudentDaoImpl(getDataSource());
	}
}

Controller Class

I have divided the controller class into the 4 parts:

CreateController class will capture student data from the JSP page and call the respective method to store the data into the database.

package org.websparrow.controller;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import org.websparrow.dao.StudentDao;
import org.websparrow.model.Student;

@Controller
public class CreateController {

	@Autowired
	private StudentDao studentDao;

	@RequestMapping(value = "/create", method = RequestMethod.POST)
	public ModelAndView createStudent(@RequestParam("name") String name, @RequestParam("email") String email,
			@RequestParam("course") String course, ModelAndView mv) {

		Student student = new Student();
		student.setName(name);
		student.setEmail(email);
		student.setCourse(course);

		int counter = studentDao.create(student);

		if (counter > 0) {
			mv.addObject("msg", "Student registration successful.");
		} else {
			mv.addObject("msg", "Error- check the console log.");
		}

		mv.setViewName("create");

		return mv;
	}
}

ReadController class will fetch the student from the database and display it on the JSP page. You can take reference from my another article for fetching data from the database.

package org.websparrow.controller;

import java.io.IOException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import org.websparrow.dao.StudentDao;
import org.websparrow.model.Student;

@Controller
public class ReadController {

	@Autowired
	private StudentDao studentDao;

	@RequestMapping(value = "/read")
	public ModelAndView readStudent(ModelAndView model) throws IOException {

		List<Student> listStudent = studentDao.read();
		model.addObject("listStudent", listStudent);
		model.setViewName("read");

		return model;
	}
}

UpdateController class will fetch the specific student records from the database and display it on the JSP page to be updated by calling respective DAO method.

package org.websparrow.controller;

import java.io.IOException;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
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.bind.annotation.RequestParam;
import org.springframework.web.servlet.ModelAndView;
import org.websparrow.dao.StudentDao;
import org.websparrow.model.Student;

@Controller
public class UpdateController {

	@Autowired
	private StudentDao studentDao;

	@RequestMapping(value = "/update/{studentId}")
	public ModelAndView findStudentById(ModelAndView model, @PathVariable("studentId") int studentId)
			throws IOException {

		List<Student> listStudent = studentDao.findStudentById(studentId);
		model.addObject("listStudent", listStudent);
		model.setViewName("update");

		return model;
	}

	@RequestMapping(value = "/update", method = RequestMethod.POST)
	public ModelAndView updateStudent(@RequestParam("id") int id, @RequestParam("name") String name,
			@RequestParam("email") String email, @RequestParam("course") String course, ModelAndView mv) {

		Student student = new Student();
		student.setId(id);
		student.setName(name);
		student.setEmail(email);
		student.setCourse(course);

		int counter = studentDao.update(student);

		if (counter > 0) {
			mv.addObject("msg", "Student records updated against student id: " + student.getId());
		} else {
			mv.addObject("msg", "Error- check the console log.");
		}

		mv.setViewName("update");

		return mv;
	}
}

DeleteController class will delete the specific student from the database.

package org.websparrow.controller;

import java.io.IOException;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.ModelAndView;
import org.websparrow.dao.StudentDao;

@Controller
public class DeleteController {

	@Autowired
	private StudentDao studentDao;

	@RequestMapping(value = "/delete/{studentId}")
	public ModelAndView deleteStudentById(ModelAndView mv, @PathVariable("studentId") int studentId)
			throws IOException {

		int counter = studentDao.delete(studentId);

		if (counter > 0) {
			mv.addObject("msg", "Student records deleted against student id: " + studentId);
		} else {
			mv.addObject("msg", "Error- check the console log.");
		}

		mv.setViewName("delete");

		return mv;
	}

}

JSP Page

View pages that interact with the user.

<html>
<head>
<title>Create</title>
</head>
<body>
	<h2>Spring MVC Create, Read, Update and Delete (CRUD) Example</h2>	
<form action="create" method="post">
<pre>
    <strong>Create Here | <a href="./read">Click for Read</a></strong>
		
	Name: <input type="text" name="name" />
	
	Email: <input type="text" name="email" />
	
	Course: <input type="text" name="course" />

	<input type="submit" value="Create" />
</pre>
</form>
${msg}
</body>
</html>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
<title>Read</title>
</head>
<body>
	<h2>Spring MVC Create, Read, Update and Delete (CRUD) Example</h2>
	<p><strong>Student List is Here | <a href="create.jsp">Click for Create</a></strong></p>
	<table border="1">
		<tr>
			<th>Id</th>
			<th>Name</th>
			<th>Email</th>
			<th>Course</th>
			<th>Action</th>
		</tr>
		<c:forEach var="student" items="${listStudent}">
			<tr>
				<td>${student.id}</td>
				<td>${student.name}</td>
				<td>${student.email}</td>
				<td>${student.course}</td>
				<td><a href="update/<c:out value='${student.id}'/>">Update</a> | <a
					href="delete/<c:out value='${student.id}'/>">Delete</a></td>
			</tr>
		</c:forEach>
	</table>
</body>
</html>

<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<html>
<head>
<title>Update</title>
</head>
<body>
	<h2>Spring MVC Create, Read, Update and Delete (CRUD) Example</h2>
	<p><strong>Update Here | <a href="../read">Click for Read</a></strong></p>
<form action="../update" method="post">
<pre>
<c:forEach var="student" items="${listStudent}">
		
Id:    	<input type="text" name="dispId" value="${student.id}" disabled="disabled"/>
		<input type="hidden" name="id" value="${student.id}"/>			
Name:  	<input type="text" name="name" value="${student.name}" />
		
Email: 	<input type="text" name="email" value="${student.email}" />
	    
Course:	<input type="text" name="course" value="${student.course}" />
		    
        <input type="submit" value="Update" />	
</c:forEach>	
</pre>
</form>
${msg}
</body>
</html>

<html>
<head>
<title>Delete</title>
</head>
<body>
	<h2>Spring MVC Create, Read, Update and Delete (CRUD) Example</h2>

	<p><strong><a href="../read">Click for Read</a></strong></p>
	${msg}
</body>
</html>

Output

Run your application and hit the following URL’s  in your browser address bar to create, read, update, and delete student:

1- Create – http://localhost:8090/spring-mvc-crud/

2- Read – http://localhost:8090/spring-mvc-crud/read

3- Update – http://localhost:8090/spring-mvc-crud/update/6  where 6 is the student id.

4- Delete – http://localhost:8090/spring-mvc-crud/delete/6  where 6 is the student id.

References

1. Spring MVC user registration and login example
2. Fetch data from database in Spring MVC
3. Spring MVC Java-based Database Connectivity