Using Spring Boot and Spring Data JPA to Access MySQL Data

In my previous post, we saw how to setup MySQL, add some test data, and run some basic SQL queries. Now, let’s create an Java application that can access our data using the Spring Data JPA library. Spring does have a decent tutorial on using Spring Data JPA, but I felt it lacked any context to the technologies being used and implements an H2 in-memory database. H2 is a great tool for certain use cases, testing, and demos, but does not help us if we want to build an application that actually persists our data between application restarts. You’ll find the code for this tutorial located on GitHub.

A Note on Having Opinions

We will be using Spring Boot to build our application, because Spring Boot takes an opinionated approach to building software. That is to say, Spring has noticed particular patterns in the industry, and has decided to create tools that standardize these patterns into an easy to use framework. The downside to this approach is that you usually have to work within the limits of the tools designed around the approach. Additionally, this adds another layer of complexity to our application, while at the same time obscuring it’s behavior. This can be troublesome because a new developer may not fully understand what the framework is doing ‘under the hood’, leading to confusion, lack of understanding, and creating difficult to debug problems. This post hopes to demystify some of the magic that happens so we can quickly get to what we want to do: Build awesome applications!

A Look at Our Tools

JPA known as the Java Persistence API was designed to be a specification of how developers can build tools that represent values in a relational database. It provides an interface that defines these specifications as a standardized way to implement this functionality. What that means for developers is that there is some familiarity and interchangeability between tools that implement these standards. Developers can choose to write their own implementation, or use existing solutions like Hibernate, EclipseLink, or what we’ll use in this post, Spring Data JPA. Implementing interfaces is a valuable tool in developing object oriented code, check out this tutorial for an introduction into understanding interfaces work and how to implement your own.

Hibernate is a library designed to provide ORM, or Object Relational Mapping. Mapping is the process of taking Java objects and serializing them to be sent and stored to a database, and deserializing them from the database to reconstruct the object. Hibernate also manages our JDBC connections to our database, and then uses it’s ORM libraries to get and fetch Java entities.

Spring Data is actually a collection of libraries designed to provide a “familiar and consistent” experience for developers when accessing data from a number of different types of  data stores. In this example we are working with a relational database, but if our needs change, switching to a different data store becomes less difficult.

Spring Data JPA is Spring’s implementation of the JPA specification. Built on top of Hibernate, Spring Data JPA also implements easy to use repositories to access and manipulate our data.

JUnit is a testing framework designed to easily create tests for our code. Typically used for unit testing, or tests designed around isolated blocks of code, JUnit helps developers write tests quickly, create assertions to validate data, and easily run tests to get fast feedback. In this tutorial, we’ll be using it more for integration testing to test overarching functionality, instead of tying ourselves to any particular way of implementing our new skills.

Getting Started

Since we’re using Spring Boot, we can use the Spring Initializr to create our application. You can generate a project from their site, which creates a ZIP file you can open in your IDE of choice, or you can use generators built into IDEs such as IntelliJ or the Spring Tool Suite plugin for Eclipse. Be sure to add the ‘JPA’ and ‘MySQL’ dependencies as you create your project. To create a new project in IntelliJ, select ‘Create New Project’ from the main menu, or from an existing project navigate to ‘File’>’New’>’Project…’ Use the ‘Spring Initializr’ to get started.

Creating a new Spring Boot project in IntelliJ

Creating a new Spring Boot project in IntelliJ

dependencies

Select your dependencies

If you have an existing Spring Boot project, add the following dependencies to <dependencies> block of your Maven pom.xml.

<dependency>
 <groupId>org.springframework.boot</groupId>
 <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>

<dependency>
 <groupId>mysql</groupId>
 <artifactId>mysql-connector-java</artifactId>
 <scope>runtime</scope>
</dependency>

These two dependencies tell Maven to import the necessary libraries we need to use Spring Data JPA, as well as the required drivers to access MySQL. Once you’ve created your project, you can use Maven to build and test your application by running ‘mvn test’ to ensure everything is configured correctly.

Entities to Represent Our Data

Our first step will be to create an entity that represent the objects in the database we created in the last post. First create a package named ‘domain’ under our src/main/java/com/appname folder. In this example, we can see the directory is at the same level as the main class of our application ‘MysqlJpaDemoApplication’. In this package, create a class called ‘Game’, which we can see below.

package com.recursivechaos.gamemanager.domain;

import javax.persistence.*;

@Entity
public class Game {

 @Id
 @GeneratedValue(strategy = GenerationType.AUTO)
 private Integer id;

 private String name;

 private String description;

 public Game() {}

 public Game(String name, String description) {
   this.name = name;
   this.description = description;
 }

 public Integer getId() {
   return id;
 }

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

 public String getName() {
   return name;
 }

 public void setName(String name) {
   this.name = name;
 }

 public String getDescription() {
   return description;
 }

 public void setDescription(String description) {
   this.description = description;
 }
}

On line 5 we can see a JPA interface annotation called @Entity. This will signify to Spring to treat this object as a database entity. We see some more annotations on lines 8 and 9. The @Id annotation indicates that the ‘id’ field will be used as the unique primary key we defined in the earlier tutorial. Note that it is type Integer, which matches what we created in the database. The @GeneratedValue tells Spring that MySQL will automatically generate this value for us.

The next two fields, ‘name’ and ‘description’ match our database table, and Spring is smart enough to map these two String values to the varchar values in the database.

We’ll need a no-args constructor for Spring to use under the hood, and we’ll create a second constructor that takes our two fields in order to easily create new objects to store to the database.

You’ll notice that our fields are all private, so we will create getter and setter methods to change/access their values. This enforces encapsulation, as described here. This allows us to restrict access to fields if need be and modify their getting/setting behavior easily.

Repositories to Access Our Data

Next, we’ll create a repository to access our data. First, create a new package next to our domain package called ‘repository’. We’ll create a new Interface called GameRepository. Remember all that rambling at the beginning of the post about opinionated implementation, and Spring Data JPA doing all sorts of magic for us? Here’s where it happens, so follow along! We’re creating an interface, instead of a class, because we’re not actually implementing any of this code, we’re merely defining a contract to be used elsewhere in the code. We’ll use the extends keyword to inherit the behavior from Spring’s CrudRepository class. If you examine the CrudRepository class, you’ll see that Spring has already implemented some useful looking features, which are called CRUD functions. We can Create using the save methods, Read data with the find methods, Update using the same save methods, and Delete with the delete methods. Typically, these will cover the bulk of your needs, but later we’ll see how we can implement our own find methods to give us more control over what we’re trying to retrieve. We do need to provide the CrudRepository with our Entity type, as well as it’s index’s type. Below we can see our new Repository.

package com.recursivechaos.gamemanager.repository;

import com.recursivechaos.gamemanager.domain.Game;
import org.springframework.data.repository.CrudRepository;

public interface GameRepository extends CrudRepository<Game, Integer> {

}

And that’s all the code we need in order to implement JPA using Spring Data!

Accessing Our MySQL Database

The last step before we can start accessing our data is to point our application to our MySQL database. With Spring Boot, this is as simple is locating our application.properties file located in src/main/resources and adding the following properties

spring.datasource.url=jdbc:mysql://localhost/game_manager
spring.datasource.username=dbuser
spring.datasource.password=dbpass
spring.datasource.driver-class-name=com.mysql.jdbc.Driver

We define 4 properties. The first is the connection string to our database. In our case, it is running locally, so we point to localhost. We’ll also define the path to our schema ‘game_manager’. Next we provide the username and password to access this database. Finally, we provide the name of the driver that will be used to access the database. Once Spring starts, it will read our properties by default, and use them to connect to the database.

Double Check our Application Structure

By now, your project’s application structure should look the same as below.

Our application structure

Our application structure

Creating JUnit Tests

Alright, we can almost see the light at the end of the tunnel. Let’s see how we can use our new repository to access our data! Spring provides a number of tools to help us create tests that leverage the magic of Spring. Let’s open up the MysqlJpaDemoApplicationTests class that was automatically generated. We’ll see a basic test called ‘contextLoads’. We can go ahead and run this test to see if our Spring application is configured correctly. In most IDEs this is done by right clicking the class name and selecting ‘Run tests’, or something to that effect. If everything is good, we should see our test pass.

Let’s go ahead and delete the contextLoads() test, and implement our own. Lets take a look at a basic test to see if we can load our games

package com.recursivechaos.gamemanager;

import com.recursivechaos.gamemanager.domain.Game;
import com.recursivechaos.gamemanager.repository.GameRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.SpringApplicationConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import java.util.ArrayList;
import java.util.List;

import static org.junit.Assert.assertEquals;

@RunWith(SpringJUnit4ClassRunner.class)
@SpringApplicationConfiguration(classes = MysqlJpaDemoApplication.class)
public class MysqlJpaDemoApplicationTests {

 @Autowired
 GameRepository gameRepository;

 @Test
 public void testLoadGames() {
     List<Game> games = (ArrayList<Game>) gameRepository.findAll();
     assertEquals("Did not get all games", 3, games.size());
 }

}

Lets break down what’s happening here:

@RunWith(SpringJUnit4ClassRunner.class) tells our class that we want to use JUnit with Spring’s enhancements

@SpringApplicationConfiguration(classes = MysqlJpaDemoApplication.class) tells our test that we want to use setup our test class the same way we set up our application. There is a lot of things that happen when Spring Boot starts, but for now understand that our test class will operate the same way a Spring Boot application works. In our situation, it is loading our application.properties and configuring our database for us.

@Autowired This annotation tells Spring to create a GameRepository for us. In a nutshell, it sees that GameRepository is an interface, will assign an implementation to our variable that has been injected with our credentials through a process called dependency injection. If this is new to you, I recommend reading up on how Spring does dependency injection. For now, understand that this allows us to easily create our repository and connect to our database.

@Test annotation is an indicator to JUnit that this is a test inside a suite of other tests

List<Game> games = (ArrayList<Game>) gameRepository.findAll();

Here’s the actual meat and potatoes of our functionality. Reading from right to left, we call the findAll() method on our repository. This will get a connection to the database, and perform a SELECT * FROM games on our database. The findAll method is provided by the CrudRepository we extended in our class. It returns an Iterable, which we will to cast into an ArrayList,  a subtype of Iterable, that we can manipulate in a familiar fashion. We assign this to our List of type Game, called games.

assertEquals("Did not get all games", 3, games.size());

Finally, we assert what we expect (that there are 3 elements in our database) against how many elements is in our list (using the size() method). Assertions allow us to test that the desired behavior has happened as expected in our test. If you happened to have a different number of elements in your test, your assertion will fail, throwing an exception, and failing your test.

Go ahead and run your test like before and see if your assertion is correct. If it is off, confirm the number of rows in your database, and update your expected number of games to the correct amount.

Building a Better Test

We’ve already come across one problem with our test. It’s dependent on the current state of our data. This will not work going forward, as we’re going to want to demonstrate additional functionality like adding/deleting. We don’t want to have to constantly be updating our tests to do so. Let’s see how we can use JUnit’s @Before annotation to run some operations before each test ensuring our database is in a consistent state. Lets add the following code to our test class.

@Before
public void setUp() throws Exception {
 gameRepository.deleteAll();
 Game pandemic = new Game("Pandemic", "Co-op game for wannabe disease control specailists");
 Game werewolf = new Game("Werewolf", "You must find out who's secretly the werewolf, before it's too late");
 Game camelUp = new Game("Camel Up", "A high stakes game of gambling and camel racing");
 gameRepository.save(pandemic);
 gameRepository.save(werewolf);
 gameRepository.save(camelUp);
}

Here we can see we delete all existing data in our table, and add 3 games. The @Before annotation tells JUnit to execute our setUp method before each test is run. Note that this isn’t the wisest idea if you are using these tables for anything other than testing, as it will delete any data you’ve created. But, for this tutorial, it makes for good demonstration. Run your tests again, and we should always see success!

Adding Query Functionality

Lets add some extra functionality to our repository. It’s likely that we’ll want to search for our games by name, so lets add a method to our GameRepository class. Spring Data JPA lets us create query methods by simply following naming conventions described in their documentation. Add the following line to our GameRepository class.

List<Game> findByName(String name);

This tells our repository to query our database using a WHERE name="param" clause to it’s statement. Since we cannot guarantee that only one value is returned, we need to return a List of Game objects. Now lets write a test to assert the desired behavior. Add the following method to our test.

@Test
public void testFindGame() throws Exception {
 List<Game> camelUpList = gameRepository.findByName("Camel Up");
 assertEquals("Found wrong number of Camel Ups", 1, camelUpList.size());
 assertEquals("Found wrong name", "Camel Up", camelUpList.get(0).getName());
}

If we run our tests once more, we should now see two passing tests.

Now that we can effectively find a game in particular, lets see how editing and deleting entities work. Using the CrudRepository’s methods, let’s try finding a game, updating it’s name, and then deleting it. After each step, we will use assertions to ensure the desired behavior. Add the following test to see the rest of our functionality.

@Test
public void testCRUD() throws Exception {
 // Create a new game
 Game munchkin = new Game("Munchkin", "A wild game for wild people.");
 gameRepository.save(munchkin);

 // Assert it was created
 List<Game> foundGame = gameRepository.findByName(munchkin.getName());
 assertEquals("Did not find Munchkin", munchkin.getName(), foundGame.get(0).getName());

 // Edit it's description
 String newDescription = "If you love rules that change, you'll love Munchkin!";
 foundGame.get(0).setDescription(newDescription);

 // Save to the database (note that we can save not just single Entities, but collections of them as well)
 gameRepository.save(foundGame);

 // Assert it updated
 List<Game> updatedGame = gameRepository.findByName(munchkin.getName());
 assertEquals("Did not update description", newDescription, updatedGame.get(0).getDescription());

 // Delete game
 gameRepository.delete(updatedGame);

 // Assert not found
 List<Game> emptyGame = gameRepository.findByName(munchkin.getName());
 assertEquals("Should have returned no games", 0, emptyGame.size());
}

Here we can see the full range of CRUD operations on our database. Congratulations, you now can easily access and manipulate data in your very own database, and learned some new tools along the way!

Peeking Under the Hood

Add the following properties to your application.properties to see what is actually happening to your database

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
spring.jpa.properties.hibernate.format_sql=true

These logs can be rather noisy and difficult to read, but will display the commands issued against our database. This can be useful for troubleshooting or gaining an understanding of how our queries are formed into SQL statements.

Wrap Up

In this tutorial we’ve gone from an empty project, to one that has a full toolbox of options to manipulate data in our database. We’ve seen how Spring Boot’s opinionated approach to using Spring Data JPA means we can easily configure a repository to manage our entities. Below are links to Spring Data JPA’s documentation that can take you even further by writing custom queries and creating relationships between other entities in our database.

Troubleshooting

Here are some common issues you may run into along the way

WARN: Establishing SSL connection without server’s identity verification is not recommended.

You’ll likely see this, since we didn’t establish a secure connection to our MySQL database. In a test environment this is fine, but understand that this is warning us that we are transmitting potentially sensitive data encrypted across the wire. You can ignore this error for now.

java.sql.SQLException: Access denied for user ‘badusernamepassword’@’localhost’ (using password: YES)

Double check your username and password. If necessary, you may need to create a new user and password to connect.

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown database ‘gamemanager’

Make sure the schema you created in MySQL matches the end of the url provided in your properties.

Other Resources

This Tutorial’s Source Code

Spring’s Documentation on working with SQL

Spring Data JPA Documentation

Posted in Tutorial Tagged with: , , , , ,
12 comments on “Using Spring Boot and Spring Data JPA to Access MySQL Data
  1. Vikas Reddy Katta says:

    Superb! It was very clear. Thank you!

  2. Yuval says:

    Thank you! it was very clear and with much more added value then spring’s tutorial, looking forward for more tutorials

  3. Jian wang says:

    thank you very much!

  4. Ashok says:

    Thanks this is very informative for a beginner like me.

  5. Ashok says:

    Do you have any blog regarding how to add _link in spring boot jpa ? I want the response to show the query with Href links to the particular entity.

  6. Jon says:

    Hello, any screenshots? I am going to go through your tutorial today.

  7. Surendra says:

    Hi in this example suppose I need a native query then where I can write that.

  8. Govin says:

    I’m new to Spring Boot and appreciate this excellent explanation.

    I’m looking for help to access an existing legacy MySQL database. I want to avoid typing the entities. These are over 40 tables.

Leave a Reply

Your email address will not be published. Required fields are marked *

*