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).
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
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.
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 email@example.com
The final code for this project can be found here.