Connecting to a MySQL Database with Play Framework 2.6 and Java

In this tutorial you will learn how to connect a Play application with a MySQL server instance.

This tutorial builds on the previous one about setting up simple REST Endpoints with Play 2.6 and Java. If you would like to go over it click here. The full project from that tutorial can be found here.

Before you begin this tutorial make sure you have MySQL installed on your local host and that you can connect to it with either HeidiSQL(Windows), SQLPro(Mac) or terminal MySQL app(Linux).

Steps:
1. Setup Database structure
2. Add JDBC as a dependency
3. Set up DB connection
4. Update BookRepository code
5. Update BookController code
6. Testing the endpoints

Step 1 - Setup Database structure

In this part of the tutorial, we will be working with a MySQL database. Before we can write data into the database we need to set up its structure. First, we need to create a database and call it "bookstore". Second, we need to create a database table for storing a Book Object that will be called "books".

To create the database you can use a GUI tool (HeidiSQL or SQLPro) or you can execute the code below:

The easiest way to create the "books" table is to execute the code below:

Step 2 - Add JDBC as a dependency

To add JDBC as a dependency we have to open build.sbt file in our root folder and add the following lines to it.

 libraryDependencies += javaJdbc
 libraryDependencies += "mysql" % "mysql-connector-java" % "5.1.39"

The final code for our build.sbt should look like following.

Step 3 - Setup DB connection

To set up the DB connection we have to edit the file application.conf located in the conf directory.


The first thing we will do in this file is change enable module play.api.db.DBModule.


The second thing is updating play.db settings located on line 312. We will do this by uncommenting the default settings on line 315, 316, 322, 323.

The second thing we will do in this file is change db part at line 339. These are the settings for db connection that we want.

The changes made should look like following:

That's it! Your DB connection setup is done, in the next section we will look at how to use it from the Repository.

Step 4 - Update BookRepository code

You can probably remember from the previous tutorial that our diagram was:

Our goal is to replace ArrayList completely and use MySQL relational DB. Our diagram at the end of the tutorial will look like this:

Our main goal is to remove ArrayList and go to the database for fetching our data. The logic for accessing the ArrayList is encapsulated within BookRepository so we will be working on the BookRepositories code the most.


We need to inject play.db.Database object into our BookRepository. Injected objects are automatically set at runtime so we don't have to worry about creating them. Injecting an object is done by using the @Inject annotation. Here is how our BookRepository should look like at this point

Now we need to remove the ArrayList from our class. The first method we are changing is findAll. Our new findAll code will look like this:

To obtain a ResultSet for accessing our data we will have to create a PreparedStatement. We need to pass a regular SQL query in order to create the PreparedStatement. After executing the PreparedStatement we can get our ResultSet, iterate through it and read our books from it. Each book we are storing into a new List that our method will eventually return.


When dealing with the DB a lot of things can go wrong for that reason our method has a declaration for throwing a SQLException which we will be handling in our BookController.


After findAll we should change findById since they are very similar. The code for findById should now look like following:

The main difference between findAll and findById is that it is passing the parameter id to the prepared statement and returning an Optional instead of a List.

The method delete will be executing the delete SQL statement:

For inserting the book into the database we need to pass all required fields into the prepared statement and get the id of the inserted book. Your add method code should look like this:

For updating the data we only need to pass all required fields into the prepared statement. Your update method should look like this:

Step 5 - Update BookController Code

BookController is in charge of loading the data. We will be handling our transaction level data in it. We are going to inject Database into our controller as well. We have to wrap our calls to the repository into database.withConnection() calls.

After injecting the Database and wrapping the repository calls the final controller should look like this:

Step 6 - Testing the endpoints

For testing we will execute the same tests like in the previous part of this tutorial.

Testing POST /book/{id} show
Testing GET /books show
Testing GET /book/{id} show
Testing PUT /book/{id} show
Making sure PUT worked show
Testing DELETE /book/{id} show
Making sure DELETE worked show

Conclusion

It is super easy to create CRUD application backend with using JDBC and Play 2.8.

Thanks for following this through!

If you have any comments or suggestions please leave a comment or contact me at dusan.stanojevic.cs@gmail.com

The final code for this project can be found here.

Author

Dusan Stanojevic Dux

Hi, I'm a full stack developer. I've been programming for over 5 years. I use Javascript, Java, Objective C and C++ on a daily. I love driving cars and playing the guitar.