Spring Boot – Spring Data JPA – MySQL Example

By Amit Phaltankar

An end to end guide to building Spring Boot REST service using Spring Data JPA, Hibernate, and MySQL database.

Overview

This is a hands on guide to building a Spring Boot REST API Service from scratch that uses MySQL Database with Spring Data JPA and Hibernate support.

In this Spring Boot – Spring Data JPA – MySQL Example, we will setup a MySQL database instance using Docker and do the required basic configurations. Then, we will follow a few steps to create a Spring Boot Application, add dependencies, add datasource configurations, setup initial data, and add all the essential components like Entity, Repository, and Controller. Lastly, we will launch our application and test the CRUD (POST, GET, PUT, and DELTE) endpoints of our service.

MySQL Setup

As our application will be using MySQL database, we need a locally accessible instance. If you don’t have a MySQL database ready, you can follow these steps to quickly start a database in a docker container. Alternatively, you can skip this section and jump to the next one.

Create a docker-compose.yml file as shown next.

version: "2"

services:
  mysql:
    restart: always
    image: mysql:latest
    container_name: my-sql-latest
    environment:
      - MYSQL_DATABASE=studentsdb
      - MYSQL_ROOT_PASSWORD=password
    ports:
      - 33080:3306Code language: YAML (yaml)

As you can see we have configured the MySQL docker image to have studentsdb and also setup the password for the root user. Also, we have forwarded the default MySQL port (3306) to a different port on our local machine. Thus, we can access the instance on 33080 port on localhost.

Now, start the MySQL docker container from the command prompt. 

~ docker-compose -f path/to/docker-compose.yml up -dCode language: Bash (bash)

We have used -d option to make sure that the container starts up in detached mode. 

Creating network "docker-mysql_default" with the default driver
Pulling mysql (mysql:latest)…
latest: Pulling from library/mysql
b4d181a07f80: Pull complete
a462b60610f5: Pull complete
578fafb77ab8: Pull complete
524046006037: Pull complete
d0cbe54c8855: Pull complete
aa18e05cc46d: Pull complete
32ca814c833f: Pull complete
9ecc8abdb7f5: Pull complete
ad042b682e0f: Pull complete
71d327c6bb78: Pull complete
165d1d10a3fa: Pull complete
2f40c47d0626: Pull complete
Digest: sha256:52b8406e4c32b8cf0557f1b74517e14c5393aff5cf0384eff62d9e81f4985d4b
Status: Downloaded newer image for mysql:latest
Creating my-sql-latest … done

On the command terminal, you should see that the MySQL image is downloaded and my-sql-latest container is now started.

Spring Boot + Spring Data JPA + MySQL Application

Follow the steps to create a Spring Boot Application, install required dependencies, and all the required components like Entity Bean, Repository and Controller

Basic Project Setup

For this tutorial, we will reuse the setup we did for CRUD REST Service With Spring Boot, Hibernate, and JPA. Following all the steps in the tutorial, we will have,

  • A basic Spring Boot Application.
  • All the basic Maven or Gradle Dependencies (spring-boot-starter-data-jpa, spring-boot-starter-web, and lombok). We’ll replace the H2 dependency with MySQL Java driver.
  • An Entity Bean and a Repository Interface
  • Spring Rest Controller that exposes basic GET, POST, PUT, and DELTE endpoints on the given entity and repository.

Additionally, add the MySQL Java Connector (mysql-connector-java) to the dependency section of your pom.xml or build.gradle file.

Maven

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
</dependency>Code language: HTML, XML (xml)

Gradle

runtimeOnly 'mysql:mysql-connector-java'Code language: Gradle (gradle)

MySQL Data Source Configuration

Now, we need to tell Spring Boot about the datasource details of our MySQL database instance. Let’s add the next datasource configurations to the application.yml file

spring:
  datasource:
    username: "root"
    password: "password" ##Enter your root password
    url: "jdbc:mysql://localhost:33080/studentsdb"
    driverClassName: "com.mysql.cj.jdbc.Driver"Code language: YAML (yaml)

Initial Schema

With Spring Boot, we can easily setup and manage the database schema and the initial data. To do so, we have to put schema creation scripts into a schema.sql file and all the initial data scripts into a data.sql file.

schema.sql

CREATE TABLE student
(
    student_id INTEGER     NOT NULL AUTO_INCREMENT,
    first_name  VARCHAR(50) NOT NULL,
    last_name   VARCHAR(50),
    year       INTEGER,
    PRIMARY KEY (student_id)
);Code language: SQL (Structured Query Language) (sql)

Now let’s add a few students into the student table.

data.sql

INSERT INTO student (first_name, last_name, year) VALUES ('Jon', 'S', 2024);
INSERT INTO student (first_name, last_name, year) VALUES ('Khal', 'D', 2025);
INSERT INTO student (first_name, last_name, year) VALUES ('Belwas', 'S', 2029);
INSERT INTO student (first_name, last_name, year) VALUES ('Petyr', 'L', 2023);Code language: SQL (Structured Query Language) (sql)

Place both of these files under src/main/resources directory and we are ready to launch our application.

Launch and Test the Application

When we launch our Spring Boot + Spring Data JPA + MySQL application, we can verify that the respective database and table is correctly created. Now it’s time to execute some REST API calls and test if all the GET, POST, PUT, and DELTE works.

GET Endpoint

~ curl --location --request GET 'localhost:8081/students/
[
   {
      "student_id":1,
      "firstName":"Jon",
      "lastName":"S",
      "year":2024
   },
   {
      "student_id":2,
      "firstName":"Khal",
      "lastName":"D",
      "year":2025
   },
   {
      "student_id":3,
      "firstName":"Belwas",
      "lastName":"S",
      "year":2029
   },
   {
      "student_id":4,
      "firstName":"Petyr",
      "lastName":"L",
      "year":2023
   }
]Code language: Bash (bash)

POST Endpoint

~ curl --location --request POST 'localhost:8081/students' \
--header 'Content-Type: application/json' \
--data-raw '{
    "firstName" : "New",
    "lastName" : "Student",
    "year" :2025
}'Code language: Bash (bash)

PUT Endpoint

curl --location --request PUT 'localhost:8081/students/5' \
--header 'Content-Type: application/json' \
--data-raw '{
    "firstName" : "JORY",
    "lastName" : "C",
    "year" : 2020
}'Code language: Bash (bash)

DELETE Endpoint

curl --location --request DELETE 'localhost:8081/students/5'Code language: Bash (bash)

Summary

This tutorial covered how to create a MySQL and Spring Data JPA backed Spring Boot REST Service. We developed our application completely from scratch by following a step by step approach. Also, we started a MySQL database instance using Docker and used Spring Boot to setup initial data and initial schema. Lastly, we tested all of our CRUD endpoints and ensured they worked.

For full source of the examples used here, please visit our Github Repo – spring-boot-data-jpa-mysql.