Spring Boot MySQL Configuration

By Daniel Vladimirov / a few months ago

Setting up a MySQL connection with Spring Boot is fairly easy and very straightforward. This tutorial will set up a basic MySQL connection in Spring Boot, using an application.yml configuration file.

Most of the work for getting a MySQL connection is done behind the scenes. What is left for you is to just configure your connection, by providing a few properties like host, port, username and password.

If you are not familiar with how to quickly set up a basic Spring Boot application, or you just don’t have any code to work on, you can use this tutorial


Spring Boot

Check out this tutorial on how to setup a basic Spring Boot application if you need to


If you are familiar with how to set up a Spring Boot application and want to just get the base source code for this tutorial, you can do so from GitHub

Web Application Skeleton – Tomcat (GitHub)


Dependencies

To set up MySQL with Spring Boot you need the following dependencies:

The spring-boot-starter-data-jpa dependency is going to provide some other useful functionality as well like JPA, transaction management and Spring repositories – those will be topics of other tutorials in this series.

So, add those dependencies to your pom.xml file. You now have everything you need to start setting up your connection.

On my machine I have a MySQL instance running on the default port 3306.

If you need a quick, easy and non-intrusive guide how to set up a MySQL instance, without polluting your environment, you can check out this tutorial:

The next thing you need is the application.yml/application.properties configuration file. If you don’t have one, create it under /src/main/resources of your application, or any other location in which case you need to supply this location so Spring can pick up the file and use it.

You can also check this extensive tutorial on configuring Spring Boot externally

The contents of the application.yml file are the following

What this basically does is create a datasource object that can be used throughout your application. The datasource object represents the connection to a MySQL instance, so naturally you can have multiple datasources, even multiple connections to the same database and multiple databases as well.

The name property is not mandatory. It’s there just to give the datasource some identity in case that there are other datasources. It’s good practice to have it there.

The url property is self-explanatory. This is your connection string, if you are familiar with JDBC. The useSSL=false flag means that we don’t want to encrypt our connection to this database. This is not mandatory and will help remove some of the security warnings when you start up the application.

The username and password properties are also pretty clear. Supply your MySQL database user and the user password.

The driverClassName property is also not mandatory and will suppress some warnings that appear as of writing this article. Basically it tells Spring which driver class to use, otherwise Spring will figure this out from the connection string automatically, but for the version I’m using, they are loading an older class. It may be due to the mysql-connector-java being a bit ahead at this time.

This is all the configuration you need to create a MySQL connection from your Spring application. If you start the application now and the database you have supplied doesn’t actually exist yet, you will get an error –

I’ve set up my database name in the connection string as ‘simplyprogram’ and I haven’t actually created the database yet. Open up a command window or MySQL Workbench or any other SQL tool and create the database

For the sake of example, let’s create an “initializer” class that will populate our database with some test data, so that we can test our MySQL connection. The test data will be removed and repopulated on each application startup.

Create the following class and annotate it with @Component

What this class does is basically create the database ‘simplyprogram’ if it doesn’t exist and also create a single table and insert two records in it. This operation will be done each time you start your application, which is coming from the @PostConstruct method. This method will be executed once an instance of the component class has been created.

The queries are going to be executed by the single dependency in this class, the JdbcTemplate. JdbcTemplate is a class, provided by Spring that eases JDBC operations and processing of result sets.

Now if you start the application, there should be no errors, since the database now exists.

The DatabaseInitializer should have done its work, so you should have a table named “test_table” in your database and two records in it.

Let’s create a very small controller class that will fetch the test data and display it in the browser. Create the following class:

This class is going to respond on a GET request to the /test_table URL. It will fetch the data from the database and return it as a JSON.

Re-start your application after adding the controller.

Open up a browser or any other tool you might be using for REST requests and visit (or make a request to)

http://localhost:8080/test_table

And the response should be an array of the test values, we’re inserting on application startup

The controller is again using the JdbcTemplate to query the database and return the result.

This is all for this tutorial on setting up a basic connection to MySQL with Spring Boot. If you need to brush up on some of the REST principles and how to apply them with Spring Boot, you can check out some of the other tutorials on the site.


About the author

Daniel Vladimirov

Software engineer and founder of simplyprogram.com

Click here to add a comment

Leave a comment: