CRUD Operations using Spring Boot, JdbcTemplate, and MySQL

This tutorial helps build a Spring Boot RESTful CRUD API that uses JdbcTemplate to interact with the MySQL database.

Overview:
The Spring Framework provides a comprehensive programming and configuration model for modern Java-based enterprise applications on any deployment platform. Spring Boot is one of the modules of Spring Framework that allows us to create a stand-alone application with minimal or zero configurations.

The CRUD Operations
CRUD acronym refers to the four operations necessary to implement a persistent storage application. The CRUD acronym stands for Create, Read, Update, and Delete. The CRUD operations help users to perform different operations on particular data within the database.

JdbcTemplate
Spring JdbcTemplate enables us to connect to the database and execute SQL queries. JdbcTemplate internally uses JDBC API and eliminates multiple problems with JDBC API.

Problems with JDBC API
The issues with JDBC API:
- We need to write a lot of code before and after executing the query, such as creating connections, statements, closing resultset, and connections, etc.
- We need to perform exception handling code on the database logic.
- We need to handle transactions.

Advantages of Spring JdbcTemplate
Spring JdbcTemplate eliminates the mentioned problems. JdbcTemplate provides the methods to write the queries directly, saving a lot of work and time.

Overview of StudentManagement CRUD RESTful API
Each Student has an id, name, contactNumber, and address.
APIs will help to create, retrieve, update, and delete student(s).

The APIs that we’ll be creating are:

API Method, URL, and Action

Project Structure:

StudentManagement API Project Structure

Brief introduction about the files:

• Student.java model class corresponds to entity and table student.
• StudentRepository.java is an interface that provides abstract methods for CRUD methods. It will be autowired in StudentController.
• StudentRepositoryImpl.java is a class that implements the StudentRepository interface. It uses JbdcTemplate to interact with the database.
• StudentController.java is a RestController containing request mapping methods for RESTful requests such as createStudent, getAllStudents, getStudentById, updateStudent, and deleteStudent.
• application.properties contains configuration for Spring Datasource.
• pom.xml has dependencies for Spring Boot, Spring Data JDBC, and MySQL.

Spring Boot Project Creation and Setup

Use the Spring Initializr or any IDE such as Spring Tool Suite, Eclipse, or IntelliJ to create a Spring Boot project.

Get Gaurav Shah’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

Once the project is created, open the pom.xml file and add the following dependency:

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-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>

Configure application.properties
Under the src/main/resources folder, open the application.properties and add the following properties:

# A DataSource is a factory for connections to the physical databases. It uses a Driver class name, URL, username, and password to establish the database connection.
spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver
spring.datasource.url = jdbc:mysql://localhost:3306/employeemanagement
spring.datasource.username = root
spring.datasource.password = admin

Spring Boot Entry Point
The entry point of the spring boot application is the class containing the main method and the @SpringBootApplication annotation.
The @SpringBootApplication annotation is equivalent to using @Component, @ComponentScan, and @EnableAutoConfiguration.

The StudentManagementApplication.java looks like this:

package com.spring.jdbctemplate;

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

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

The Data Model
Our Data Model is Student, which consists of id, name, contactNumber, and address.
The Student.java looks like this:

package com.spring.jdbctemplate.model;

public class Student {
  private int id;
  private String name;
  private long contactNumber;
  private String address;
 
  public Student() {
   super();
  }

  public Student(int id, String name, long contactNumber, String address) {
   super();
   this.id = id;
   this.name = name;
   this.contactNumber = contactNumber;
   this.address = address;
  }

  public int getId() {
   return id;
  }
  public void setId(int id) {
   this.id = id;
  }
  public String getName() {
   return name;
  }
  public void setName(String name) {
   this.name = name;
  }
  public long getContactNumber() {
   return contactNumber;
  }
  public void setContactNumber(long contactNumber) {
   this.contactNumber = contactNumber;
  }
  public String getAddress() {
   return address;
  }
  public void setAddress(String address) {
   this.address = address;
  }
  @Override
  public String toString() {
   return "Student ID : " + id + ", Student name : " + name + ", Contact number : " + contactNumber + ", Address : " + address;
  }
}

The JDBC Repository
The JDBC Repository interface provides abstract methods for CRUD methods.
The StudentRepository.java looks like this:

package com.spring.jdbctemplate.repository;

import java.util.List;
import com.spring.jdbctemplate.model.Student;

public interface StudentRepository {
 int addStudent(Student student);
 List<Student> getAllStudents();
 Student getStudentById(int id);
 int updateStudent(Student student);
 int deleteStudentById(int id);
}

The JDBC Repository Implementation
The JDBC Repository Implementation class implements the JDBC Repository interface. It uses JdbcTemplate’s object to interact with Students from the database.
The StudentRepositoryImpl.java looks like this:

package com.spring.jdbctemplate.repository;

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.stereotype.Repository;
import com.spring.jdbctemplate.model.Student;

@Repository
public class StudentRepositoryImpl implements StudentRepository {
 
 @Autowired
 JdbcTemplate jdbcTemplate;
 
 @Override
 public int addStudent(Student student) {
  String insertQuery = "insert into student(id, name, contact_number, address) values (?,?,?,?)";
  return jdbcTemplate.update(insertQuery,student.getId(),student.getName(),student.getContactNumber(), student.getAddress());
 }
 @Override
 public List<Student> getAllStudents() {
  String selectAllQuery = "select id, name, contact_number, address from student";
  List<Student> allStudentsList = jdbcTemplate.query(selectAllQuery, new BeanPropertyRowMapper<Student>(Student.class));
  return allStudentsList;
 }
 @Override
 public Student getStudentById(int id) {
  String selectByIdQuery = "select id, name, contact_number, address from student where id = ?";
  Student student = jdbcTemplate.queryForObject(selectByIdQuery, BeanPropertyRowMapper.newInstance(Student.class), id);
  return student;
 }
 @Override
 public int updateStudent(Student student) {
  String updateQuery = "update student set id = ?, name = ?, contact_number = ?, address = ? where id = ?";
  return jdbcTemplate.update(updateQuery,student.getId(),student.getName(),student.getContactNumber(), student.getAddress(),student.getId());
 }
 @Override
 public int deleteStudentById(int id) {
  String deleteByIdQuery = "delete from student where id = ?";
  return jdbcTemplate.update(deleteByIdQuery, id);
 }
}

The @Repository annotation indicates that the class provides the mechanism to create, retrieve, update, and delete operations on objects.
The @Autowired allows us to inject the JdbcTemplate bean into a local variable.
The JdbcTemplate is a mechanism that provides us with methods like update(), query(), queryForObject(), etc.
The BeanPropertyRowMapper converts a table row into a new instance of the specified mapped target class (Student).

The Rest APIs Controller
The controller provides APIs for creating, retrieving, updating, and deleting Students.
The StudentController.java looks like this:

package com.spring.jdbctemplate.controller;

import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.DeleteMapping;
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.PutMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.spring.jdbctemplate.model.Student;
import com.spring.jdbctemplate.repository.StudentRepository;

@RestController
@RequestMapping("/api")
public class StudentController {
 
 @Autowired
 StudentRepository studentRepository;
 
 @PostMapping("/student")
 public int addStudent(@RequestBody Student student) {
  return studentRepository.addStudent(student);
 }
 
 @GetMapping("/students")
 public List<Student> getAllStudents(){
  return studentRepository.getAllStudents();
 }
 
 @GetMapping("/student/{id}")
 public Student getStudentById(@PathVariable("id") int id) {
  return studentRepository.getStudentById(id);
 }
 
 @PutMapping("/student")
 public int updateStudent(@RequestBody Student student) {
  return studentRepository.updateStudent(student);
 }
 
 @DeleteMapping("/student/{id}")
 public int deleteStudentById(@PathVariable("id") int id) {
  return studentRepository.deleteStudentById(id);
 }
}

The @RestController annotation allows the class to handle the requests made by the client.
The @RequestMapping annotation enables us to map HTTP requests to the handler methods REST controller.
The @Autowired injects the StudentRepository bean to a local variable.
The @PathVariable annotation enables us to extract the value of the template variables and assign their value to a method variable.
The @RequestBody annotation sends and receives data via the REST API.
The @PostMapping annotation maps HTTP POST requests onto specific handler methods.
The @GetMapping annotation maps HTTP GET requests onto specific handler methods.
The @PutMapping annotation maps HTTP PUT requests onto specific handler methods.
The @DeleteMapping annotation maps HTTP DELETE requests onto specific handler methods.

Database Creation and Setup
Go to the MySQL workbench and run the following query:

create database studentmanagement;
use studentmanagement;
create table student (
 id int not null primary key,
 name varchar(255) not null,
 contact_number bigint not null,
 address varchar(255) not null
);

Testing the APIs using Postman
Postman is an API platform for building and using APIs. It also allows us to manually test the APIs.

The Postman Documentation for the entire project is available at:
https://documenter.getpostman.com/view/22576021/VUjFrTXy

The Entire code of the project is available at:
https://github.com/GauravShah97/StudentManagement

For more Java and Spring-Boot-related projects, check out my profile:
https://medium.com/@gauravshah97

Reach me at:
https://www.linkedin.com/in/gauravshah97/