Spring Boot with MySQL

In this tutorial, we will build a Spring Boot Application, connect it with a MySQL database and create REST APIs to insert, read, update, and delete data from the MySQL database.

Follow the steps below to complete this tutorial:

Create a Spring Boot Application

1. Go to Spring Initializr at https://start.spring.io and create a Spring Boot application with details as follows:
• Project: Choose Gradle Project or Maven Project.
• Language: Java
• Spring Boot: Latest stable version of Spring Boot is selected by default. So leave it as is.
• Project Metadata: Provide group name in the Group field. The group name is the id of the project. In Artifact field, provide the name of your project. In the package field, provide package name for your project. Next, select your preferred version of Java that is installed on your computer and is available on your local environment.
• Dependencies: Add dependencies for Spring Web, Spring Boot DevTools, MySQL Driver, and Spring Data JPA.

Refer to the image below for example:


2. Click the GENERATE button and save/download the project zip bundle.
3. Extract the project to your preferred working directory.
4. Import the project to your preferred Java development IDE such as Eclipse or IntelliJ IDEA.

The final project structure of our sample application will look something like this after completion in a hierarchical package presentation view:

Add Dependency

To ensure that the user correctly inputs the data, we will use spring-boot-starter-validation to validate the dto (Data Transfer Object) object. At the time of writing this tutorial, the current version of spring-boot-starter-validation is 2.4.5. Find the latest version of spring-boot-starter-validation in the spring-boot-starter-validation Maven Repository.

For Gradle

implementation group: 'org.springframework.boot', name: 'spring-boot-starter-validation', version: '2.4.5'

For Maven

Add Configurations

Open the application.properties file to add configuration for your application port and MySQL database connection. Do not forget to update the values of spring.datasource.url, the spring.datasource.username and spring.datasource.password with values relevant to your project.

#port on which the application should run
server.port= 8080

#mysql database connection
spring.datasource.url = jdbc:mysql://localhost:3306/test_buddy
spring.datasource.username = root
spring.datasource.password = Testing123
spring.datasource.timeBetweenEvictionRunsMillis = 60000
spring.datasource.maxIdle = 1
#below properties will automatically creates and updates database schema
spring.jpa.generate-ddl=true                                                        
spring.jpa.hibernate.ddl-auto=update

Create Entity Class

To save students data in database, lets create an entity class. An entity class is an object wrapper for a database table. The attributes of the entity class are mapped to the columns of the database table. The entity class should be annotated with @Entity and @Table.

Spring provides @CreatedBy, @LastModifiedBy, @CreatedDate, @LastModifiedDate to support the tracking of who created or modified an entity. To benefit from that functionality, you should annotate your entity class with @EntityListeners and your main configuration class with @EnableJpaAuditing.

package com.springboot.rest.example.model;

import java.time.LocalDateTime;
import javax.persistence.Entity;
import javax.persistence.EntityListeners;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.jpa.domain.support.AuditingEntityListener;

@Entity
@Table(name = "student")
@EntityListeners(AuditingEntityListener.class)
public class Student {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Integer id;
    private String firstName;
    private String lastName;
    private String email;
    private String contactNumber;
    private String courseName;
    @CreatedDate
    private LocalDateTime created;
    @LastModifiedDate
    private LocalDateTime modified;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    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 getContactNumber() {
        return contactNumber;
    }

    public void setContactNumber(String contactNumber) {
        this.contactNumber = contactNumber;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }

    public LocalDateTime getCreated() {
        return created;
    }

    public void setCreated(LocalDateTime created) {
        this.created = created;
    }

    public LocalDateTime getModified() {
        return modified;
    }

    public void setModified(LocalDateTime modified) {
        this.modified = modified;
    }

}
 

Create DTO Classes

We will create DTO classes to aggregate and encapsulate data for transfer. DTO is short for Data Transfer Object and is one of the enterprise application architecture patterns. A DTO class should only contains getter/setter methods with serialization and deserialization mechanism but should not contain any business logic.

Using DTOs, you can decide which data to return and which data to not return in remote calls to promote security and loose coupling. However DTOs pattern should not be used in local system calls as it's just a simple feature that should not be over-designed.

package com.springboot.rest.example.dto;

import java.time.LocalDateTime;

public class StudentDto {

    private Integer id;
    private String firstName;
    private String lastName;
    private String email;
    private String contactNumber;
    private String courseName;
    private LocalDateTime created;
    private LocalDateTime modified;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    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 getContactNumber() {
        return contactNumber;
    }

    public void setContactNumber(String contactNumber) {
        this.contactNumber = contactNumber;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }

    public LocalDateTime getCreated() {
        return created;
    }

    public void setCreated(LocalDateTime created) {
        this.created = created;
    }

    public LocalDateTime getModified() {
        return modified;
    }

    public void setModified(LocalDateTime modified) {
        this.modified = modified;
    }

}
 

package com.springboot.rest.example.dto;

import java.time.LocalDateTime;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;

public class StudentNewDto {

    private Integer id;
    @NotNull(message = "firstName cannot be empty")
    @Size(min = 2, max = 30,
            message = "firstName must be atleast of 2 letters and not more then 100 letters")
    private String firstName;
    @NotNull(message = "lastName cannot be empty")
    @Size(min = 2, max = 30,
            message = "lastName must be atleast of 2 letters and not more then 100 letters")
    private String lastName;
    @NotEmpty(message = "email cannot be empty")
    @Email
    private String email;
    private String contactNumber;
    private String courseName;
    private LocalDateTime created;
    private LocalDateTime modified;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    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 getContactNumber() {
        return contactNumber;
    }

    public void setContactNumber(String contactNumber) {
        this.contactNumber = contactNumber;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }

    public LocalDateTime getCreated() {
        return created;
    }

    public void setCreated(LocalDateTime created) {
        this.created = created;
    }

    public LocalDateTime getModified() {
        return modified;
    }

    public void setModified(LocalDateTime modified) {
        this.modified = modified;
    }

}
 

package com.springboot.rest.example.dto;

import java.time.LocalDateTime;
import javax.validation.constraints.Email;
import javax.validation.constraints.NotEmpty;
import javax.validation.constraints.NotNull;
import javax.validation.constraints.Size;

public class StudentExistingDto {

    @NotNull(message = "id cannot be empty")
    private Integer id;
    @NotNull(message = "firstName cannot be empty")
    @Size(min = 2, max = 30,
            message = "firstName must be atleast of 2 letters and not more then 100 letters")
    private String firstName;
    @NotNull(message = "lastName cannot be empty")
    @Size(min = 2, max = 30,
            message = "lastName must be atleast of 2 letters and not more then 100 letters")
    private String lastName;
    @NotEmpty(message = "email cannot be empty")
    @Email
    private String email;
    private String contactNumber;
    private String courseName;
    private LocalDateTime created;
    private LocalDateTime modified;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    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 getContactNumber() {
        return contactNumber;
    }

    public void setContactNumber(String contactNumber) {
        this.contactNumber = contactNumber;
    }

    public String getCourseName() {
        return courseName;
    }

    public void setCourseName(String courseName) {
        this.courseName = courseName;
    }

    public LocalDateTime getCreated() {
        return created;
    }

    public void setCreated(LocalDateTime created) {
        this.created = created;
    }

    public LocalDateTime getModified() {
        return modified;
    }

    public void setModified(LocalDateTime modified) {
        this.modified = modified;
    }

}
 

Create Custom Exception Handling Classes

Lets create three classes to handle custom exceptions for this example application.

Create ResourceNotFoundException.class that should be a subclass of RuntimeException class. Any class that extends the RuntimeException makes the class unchecked which means no mandatory exception handling is required for them.

package com.springboot.rest.example.exception;

public class ResourceNotFoundException extends RuntimeException {

    private static final long serialVersionUID = 1L;

    public ResourceNotFoundException(String message) {
        super(message);
    }
}
 

Create Error.class Java class with a simple getter/setter class.

package com.springboot.rest.example.exception;

public class Error {
    private String message;
    private int status;
    private Long timestamp;

    public String getMessage() {
        return message;
    }

    public void setMessage(String message) {
        this.message = message;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public Long getTimestamp() {
        return timestamp;
    }

    public void setTimestamp(Long timestamp) {
        this.timestamp = timestamp;
    }

}
 

Create GlobalExceptionHandlerController.class that should be annotated with @ControllerAdvice. Any class annotated with @ControllerAdvice makes it a controller-advice. A controller advice allows to handle exception across the whole application instead of just to a single controller. Exception handling methods should be annotated with @ExceptionHandler.

package com.springboot.rest.example.exception;

import java.util.Date;
import javax.servlet.http.HttpServletRequest;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.MethodArgumentNotValidException;
import org.springframework.web.bind.annotation.ControllerAdvice;
import org.springframework.web.bind.annotation.ExceptionHandler;

@ControllerAdvice
public class GlobalExceptionHandlerController {

    @ExceptionHandler(ResourceNotFoundException.class)
    public ResponseEntity<Object> resourceNotFound(ResourceNotFoundException ex,
            HttpServletRequest request) {
        Error error = new Error();
        error.setMessage(ex.getMessage());
        error.setTimestamp(new Date().getTime());
        error.setStatus(HttpStatus.NOT_FOUND.value());
        return new ResponseEntity<>(error, null, HttpStatus.NOT_FOUND);
    }

    @ExceptionHandler(MethodArgumentNotValidException.class)
    public ResponseEntity<Object> badRequest(MethodArgumentNotValidException ex, HttpServletRequest request) {
        Error error = new Error();
        error.setMessage(ex.getMessage());
        error.setTimestamp(new Date().getTime());
        error.setStatus(HttpStatus.BAD_REQUEST.value());
        return new ResponseEntity<>(error, null, HttpStatus.BAD_REQUEST);
    }
}
 

Create Repository Interface

To perform CRUD (Create Read Update Delete) operations on the table, create an interface and extend it with CrudRepository interface. The CrudRepository interface provides generic CRUD operations on a repository for a specific type. For this example, we will create StudentRepository.class interface.

package com.springboot.rest.example.repository;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;
import com.springboot.rest.example.model.Student;

@Repository
public interface StudentRepository extends CrudRepository<Student, Integer> {

    public Page<Student> findAll(Pageable pageable);
}

Create Service Interface

Let's create an interface and define some abstract methods which we will implement in another class.

package com.springboot.rest.example.service;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import com.springboot.rest.example.dto.StudentDto;
import com.springboot.rest.example.dto.StudentExistingDto;
import com.springboot.rest.example.dto.StudentNewDto;

public interface StudentService {

    StudentDto addStudent(StudentNewDto studentNewDto);

    StudentDto updateStudent(StudentExistingDto studentExistingDto);

    void deleteStudent(Integer studentId);

    StudentDto getStudentById(Integer studentId);

    Page<StudentDto> getAllStudents(Pageable pageable);
}

Create Service Implementation Class

Next, we will create a class to implement the methods of the interface we just created. This implementation class should be annotated with @Service. The @Service annotation indicates that the annotated class is a service class where methods with your business logic should be implemented.

package com.springboot.rest.example.service.impl;

import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.stereotype.Service;
import com.springboot.rest.example.dto.StudentDto;
import com.springboot.rest.example.dto.StudentExistingDto;
import com.springboot.rest.example.dto.StudentNewDto;
import com.springboot.rest.example.exception.ResourceNotFoundException;
import com.springboot.rest.example.model.Student;
import com.springboot.rest.example.repository.StudentRepository;
import com.springboot.rest.example.service.StudentService;

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentRepository studentRepository;

    @Override
    public StudentDto addStudent(StudentNewDto studentNewDto) {
        Student student = new Student();
        student.setId(studentNewDto.getId());
        student.setFirstName(studentNewDto.getFirstName());
        student.setLastName(studentNewDto.getLastName());
        student.setEmail(studentNewDto.getEmail());
        student.setContactNumber(studentNewDto.getContactNumber());
        student.setCourseName(studentNewDto.getCourseName());

        student = studentRepository.save(student);

        StudentDto studentDto = new StudentDto();
        studentDto.setId(student.getId());
        studentDto.setFirstName(student.getFirstName());
        studentDto.setLastName(student.getLastName());
        studentDto.setEmail(student.getEmail());
        studentDto.setContactNumber(student.getContactNumber());
        studentDto.setCourseName(student.getCourseName());
        studentDto.setCreated(student.getCreated());
        studentDto.setModified(student.getModified());
        return studentDto;

    }

    @Override
    public StudentDto updateStudent(StudentExistingDto studentExistingDto) {
        Optional<Student> studentOpt = studentRepository.findById(studentExistingDto.getId());
        if (!studentOpt.isPresent()) {
            throw new ResourceNotFoundException("student not found");
        }
        Student student = studentOpt.get();
        student.setFirstName(studentExistingDto.getFirstName());
        student.setLastName(studentExistingDto.getLastName());
        student.setEmail(studentExistingDto.getEmail());
        student.setContactNumber(studentExistingDto.getContactNumber());
        student.setCourseName(studentExistingDto.getCourseName());

        student = studentRepository.save(student);

        StudentDto studentDto = new StudentDto();
        studentDto.setId(student.getId());
        studentDto.setFirstName(student.getFirstName());
        studentDto.setLastName(student.getLastName());
        studentDto.setEmail(student.getEmail());
        studentDto.setContactNumber(student.getContactNumber());
        studentDto.setCourseName(student.getCourseName());
        studentDto.setCreated(student.getCreated());
        studentDto.setModified(student.getModified());
        return studentDto;
    }

    @Override
    public void deleteStudent(Integer studentId) {
        if (studentId == null) {
            throw new IllegalArgumentException("studentId must not be null");
        }
        Optional<Student> studentOpt = studentRepository.findById(studentId);
        if (!studentOpt.isPresent()) {
            throw new ResourceNotFoundException("student not found");
        }
        studentRepository.deleteById(studentId);
    }

    @Override
    public StudentDto getStudentById(Integer studentId) {
        if (studentId == null) {
            throw new IllegalArgumentException("studentId must not be null");
        }

        Optional<Student> studentOpt = studentRepository.findById(studentId);
        if (!studentOpt.isPresent()) {
            throw new ResourceNotFoundException("student not found");
        }
        Student student = studentOpt.get();

        StudentDto studentDto = new StudentDto();
        studentDto.setId(student.getId());
        studentDto.setFirstName(student.getFirstName());
        studentDto.setLastName(student.getLastName());
        studentDto.setEmail(student.getEmail());
        studentDto.setContactNumber(student.getContactNumber());
        studentDto.setCourseName(student.getCourseName());
        studentDto.setCreated(student.getCreated());
        studentDto.setModified(student.getModified());
        return studentDto;
    }

    @Override
    public Page<StudentDto> getAllStudents(Pageable pageable) {
        Page<Student> studentsPage = studentRepository.findAll(pageable);

        List<StudentDto> studentsDto = new ArrayList<>();
        Page<StudentDto> studentsDtoPage = new PageImpl<>(studentsDto, pageable, 0);

        if (studentsPage != null && !studentsPage.isEmpty()) {

            studentsPage.getContent().forEach(student -> {
                StudentDto studentDto = new StudentDto();
                studentDto.setId(student.getId());
                studentDto.setFirstName(student.getFirstName());
                studentDto.setLastName(student.getLastName());
                studentDto.setEmail(student.getEmail());
                studentDto.setContactNumber(student.getContactNumber());
                studentDto.setCourseName(student.getCourseName());
                studentDto.setCreated(student.getCreated());
                studentDto.setModified(student.getModified());

                studentsDto.add(studentDto);
            });
            studentsDtoPage =
                    new PageImpl<>(studentsDto, pageable, studentsPage.getTotalElements());
        }
        return studentsDtoPage;
    }

}

Create a Resource Controller

Create a controller class StudentController.java. A Controller is an entry point to consume requests from frontend applications and other services. To create REST APIs in Spring Boot, a controller class should be annotated with @RestController.

package com.springboot.rest.example.controller;

import javax.validation.Valid;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.data.web.PageableDefault;
import org.springframework.data.web.SortDefault;
import org.springframework.http.ResponseEntity;
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.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.springboot.rest.example.dto.StudentDto;
import com.springboot.rest.example.dto.StudentExistingDto;
import com.springboot.rest.example.dto.StudentNewDto;
import com.springboot.rest.example.service.StudentService;

@RestController
@RequestMapping(path = "/students")
public class StudentController {

    @Autowired
    private StudentService studentService;

    @PostMapping(path = "/add")
    public ResponseEntity<StudentDto> addStudent(@RequestBody  @Valid  StudentNewDto studentNewDto) {
        StudentDto student = studentService.addStudent(studentNewDto);
        return ResponseEntity.ok(student);
    }

    @PostMapping(path = "/update")
    public ResponseEntity<StudentDto> updateStudent(
            @RequestBody  @Valid  StudentExistingDto studentExistingDto) {
        StudentDto student = studentService.updateStudent(studentExistingDto);
        return ResponseEntity.ok(student);
    }

    @DeleteMapping(path = "/{studentId}/delete")
    public void deleteStudent(@PathVariable(name = "studentId") Integer studentId) {
        studentService.deleteStudent(studentId);
    }

    @GetMapping(path = "/{studentId}")
    public ResponseEntity<StudentDto> getStudent(
            @PathVariable(name = "studentId") Integer studentId) {
        StudentDto student = studentService.getStudentById(studentId);
        return ResponseEntity.ok(student);
    }

    @GetMapping(path = "/all")
    public ResponseEntity<Page<StudentDto>> getStudents(@PageableDefault(page = 0,
            size = 30) @SortDefault.SortDefaults({@SortDefault(sort = "modified",
                    direction = Sort.Direction.DESC)}) Pageable pageable) {
        Page<StudentDto> students = studentService.getAllStudents(pageable);
        return ResponseEntity.ok(students);
    }

}

Enable JPA Auditing

To enable Auditing, the main Spring Boot Application class should be annotated with @EnableJpaAuditing.

package com.springboot.rest.example;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;

@EnableJpaAuditing
@SpringBootApplication
public class SpringBootRestExampleApplication {

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

}

Summary

Congratulations! You have learned how to build a Spring Boot application with MySQL.