Creating an API with Spring Boot and MySQL: Part 1

Bootstrapping your API is easy with Spring Initializr!

4 min readJul 17, 2018

Spring Initializr is a handy tool that generates a Spring Boot skeleton project, and with a little extra configuration and coding, it is easy to complete the remaining pieces and create a functional API serving JSON data from MySQL.

Audience

Anyone who wants to learn how to create and run an API using Spring Boot with MySQL and Flyway.

Experience with Java programming and relational databases is helpful but not required.

Requirements

Let’s get started!

To create a Spring Boot skeleton project, go to Spring Initializr and enter options similar to below.

Group should be a unique path for your project. It is often a company or personal web address. For myself, I used my Github page
Artifact is the name of the actual project. I matched the name of my Github repository for this demo
The dependencies are added one at a time. Each one will be explained when introduced in these tutorials

Then, click Generate Project.

MySQL setup

After unzipping, building, and running SpringBootMysqlDemoApplication with IntelliJ without any modifications, I get this error:

***************************
APPLICATION FAILED TO START
***************************Description:Failed to auto-configure a DataSource: 'spring.datasource.url' is not specified and no embedded datasource could be auto-configured.Reason: Failed to determine a suitable driver class

The MySQL properties are missing from application.properties.

MySQL is a popular open-source relational database which the API will use to store and retrieve data. One-time setup is needed for Spring Boot to connect to MySQL. First, create a new database using tool like MySQL Workbench:

create database MYSQL_DEMO;

Also, make sure to create a user account that has permission to modify the database.

Then, tell Spring Boot to look for the database by adding these lines to application.properties, filling in username and password where left blank:

# MySQL properties
spring.datasource.url=jdbc:mysql://localhost:3306/MYSQL_DEMO
spring.datasource.username=
spring.datasource.password=
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

Flyway setup

The second attempt to run the application gives a different error about Flyway:

java.lang.IllegalStateException: Cannot find migrations location in: [classpath:db/migration] (please add migrations or check your Flyway configuration)

Flyway is a database versioning tool that can work with many brands of relational databases, including MySQL. SQL scripts are written during development, and then they are applied to create or update tables required during startup of the Spring Boot application. However, if the scripts are not found, the error above is thrown.

Flyway requires database migration scripts to be located in a single folder. If there are new scripts that have not been applied to the database, then it will run them and log the script in a special flyway_schema_history table.

The default location of the folder can be configured as a property. For example, I want to have a specific MySQL migration folder in case I want to support another database in the future. The MySQL-specific folder goes under src/main/resources and the custom folder structure can be created as db/mysql/migration.

In order for Flyway to find this folder, add the following lines to application.properties:

# Flyway properties
spring.flyway.locations=classpath:db/mysql/migration

Also, Flyway requires at least one migration script to initialize the database. Let’s create a USER table that we can use for our API.

Add a script called V1_0__USER.sql :

CREATE TABLE IF NOT EXISTS MYSQL_DEMO.USER (
  ID INT NOT NULL AUTO_INCREMENT,
  FIRST_NAME VARCHAR(50) NOT NULL,
  LAST_NAME VARCHAR(50) NOT NULL,
  ADD_TS DATETIME(6) NOT NULL,
  PRIMARY KEY (ID))
ENGINE = InnoDB;

Subsequent scripts can increment the version number like V1_1, V1_2, etc. The original script should always be kept as V1_0 to keep a full record of the database history for initializing a new database to work with the Spring Boot application.

Started SpringBootMysqlDemoApplication

Run the application one more time, and it finally starts!

2018-04-22 23:33:55.010  INFO 8508 --- [           main] o.s.b.w.embedded.tomcat.TomcatWebServer  : Tomcat started on port(s): 8080 (http) with context path ''
2018-04-22 23:33:55.010  INFO 8508 --- [           main] c.g.a.s.SpringBootMysqlDemoApplication   : Started SpringBootMysqlDemoApplication in 14.208 seconds (JVM running for 15.343)

The Spring Boot application is running at http://localhost:8080/

Opening the URL in a web browser may look like below:

The application doesn’t do much aside from the default 404 page, but now Flyway is working for us. The Spring Boot startup log shows the migration being applied:

2018-04-22 23:33:50.633  INFO 8508 --- [           main] o.f.c.i.s.JdbcTableSchemaHistory         : Creating Schema History table: `MYSQL_DEMO`.`flyway_schema_history`
2018-04-22 23:33:51.420  INFO 8508 --- [           main] o.f.core.internal.command.DbMigrate      : Current version of schema `MYSQL_DEMO`: << Empty Schema >>
2018-04-22 23:33:51.426  INFO 8508 --- [           main] o.f.core.internal.command.DbMigrate      : Migrating schema `MYSQL_DEMO` to version 1.0 - USER
2018-04-22 23:33:51.488  INFO 8508 --- [           main] o.f.core.internal.command.DbMigrate      : Successfully applied 1 migration to schema `MYSQL_DEMO` (execution time 00:00.856s)

Additionally, MySQL Workbench shows the script migration logged in flyway_schema_history: