Spring Data provides several unified ways to work with any relational databases. The most common one of them is Spring Data JPA. In this topic, we will demonstrate how to start using this approach step by step and then consider a few use cases of the three popular databases: H2, PostgreSQL, and MySQL. Following the same approach, you can start working with any other relational databases (e.g. with Oracle). You don’t need to install all the mentioned databases, but we highly encourage you to try out what you’ll learn from this topic with at least one relational database of your choice.

## General Approach

Imagine you have a Spring Boot project that uses a build tool (Gradle or Maven). To start working with a relational database using Spring Data JPA, you need to follow the next four steps.

1) Choose the database you need depending on your project.

2) Add the JDBC driver for the chosen database to your project via a build tool.

3) Configure your application data source to access the target database (e.g. locally or remotely). You can do it using the configuration file (application.properties) or programmatically. You will have to adjust the following properties:

• spring.datasource.driverClassName that sets the class of the data source driver (H2, MySQL, PostgreSQL, etc). This property is optional in new versions of Spring Boot because the driver can be determined automatically. However, it is common practice to configure it explicitly, which we will do for learning purposes.
• spring.datasource.url that sets the JDBC URL of the database to connect and interact with;
• spring.datasource.username that sets the user working with the database;
• spring.datasource.password that sets the password of the user to connect to the database (it may be optional in some cases).

These properties are the same for all databases, but you might need to configure several database-specific properties as well.

When developers work on real projects, they usually have several configuration files to access their locally installed database during the development and work with a production database after deploying the application.

4) Add a dependency for JPA, which doesn’t depend on the relational database you will use, and configure the dialect.

If you use Gradle, add the following dependency in the build.gradle file:

implementation 'org.springframework.boot:spring-boot-starter-data-jpa'


If you use Maven, add the following dependency in the pom.xml file:

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


To set the dialect, you need to use the spring.jpa.database-platform property depending on the SQL dialect of your database. It is not always required because there are default dialects for some databases.

It is also worth pointing out that the first three steps are required not only for JPA. You can also use them to start working with Spring JDBC Templates and interact with relational databases in other ways. However, the last step is very JPA-specific. In addition to it, it is also possible to specify JPA-related properties when required. We will discuss them in a separate topic.

In the following examples, we don’t specify any versions for project dependencies: we rely on the default versions available from the org.springframework.boot dependency. However, you can change the versions of all the dependencies if needed.

## Using H2

H2 is an open-source lightweight relational database for the Java platform. It is provided by Spring Boot by default, and it is easy and quick to set up in comparison with other databases. Using H2 in training projects can help you concentrate on learning Spring Boot and programming language features instead of the database-specific setup. However, it also requires some minimal configuration. You can reuse the config you will create in this topic in other studying projects with minor changes.

There are two main ways to store data in the H2 database.

• You can use it as an in-memory database and keep your data in memory while H2 is running. After you stop using H2 or the application, all data will be lost. This mode is especially convenient for fast prototyping, learning, and testing purposes.
• The other way is to store the data on your drive in the file system and reuse it when needed. We will use this type of database here and learn the options available for it.

If you want to work with this database, you need to have a Spring Boot project and add the H2 JDBC driver as a dependency.

For Gradle (in the build.gradle file):

runtimeOnly 'com.h2database:h2'


For Maven (in the pom.xml file):

<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<scope>runtime</scope>
</dependency>


Now we are ready to set up the H2 database properties in the configuration file. The H2 parameters are configured in the application.properties file. First, we set the data source parameters, which define where our database is stored, what type it is, and how the application can connect to it.

Let’s start with the following string in the application.properties file:

spring.datasource.driverClassName=org.h2.Driver
spring.datasource.url=jdbc:h2:file:~/testdb


It means that your application will use the H2 driver to communicate with this database, and the database will be named testdb. By default, there is no password for H2, so you don’t have to write it. However, having a password is good practice.

If you are going to use H2 as an in-memory database, you need to specify the spring.datasource.url property as jdbc:h2:mem:testdb.

You also need to add spring-boot-starter-data-jpa as a dependency of your project.

If you run your Spring application after configuring all the properties and updating the project dependencies, you will see some log lines related to JPA, Hibernate, and connection pools. It means that your application can access the database properly.

Bootstrapping Spring Data JPA repositories in DEFAULT mode.
Finished Spring Data repository scanning in 3 ms. Found 0 JPA repository interfaces.


Besides this, you can also find the automatically created file for the database.

Unlike many other databases, H2 provides an embedded console to manage your database. You can use it to access tables, modify data, and so on. Let’s take a look at how to enable this console. Since the console is a kind of web application, you need to add a dependency to the Spring Web module.

For Gradle (in the build.gradle file):

implementation 'org.springframework.boot:spring-boot-starter-web'


For Maven (in the pom.xml file):

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


You can also set the database type you are planning to use. Otherwise, it will be set automatically.

spring.jpa.database-platform=org.hibernate.dialect.H2Dialect


Now we enable the H2 console so it can run our application and use the console functionality:

spring.h2.console.enabled=true
spring.h2.console.settings.trace=false


The trace parameter value above hides all the H2 console traces in the application log. You can set it to true and see how the console actions are traced.

Now if you run the application, you will see how the testdb is created and the console is enabled:

H2 console available at '/h2-console'. Database available at 'jdbc:h2:file:~/testdb'


As the log says, the database is available in the user’s directory. You can open it and find the testdb.mv file there. It also says that now you can get to the H2 console at the localhost:8080/h2-console URL using your browser.

Replace the JDBC URL value with the one you set at the spring.datasource.url parameter and connect with the username and password you set at the spring.datasource.username and spring.datasource.password parameters.

The main console page should look like the one in this picture:

You can see the window for your SQL queries and the testdb schema. Now you can create the table in this schema and then access it from your application.

By the way, you can use the spring.h2.console.path property in the application.properties file to change the default path of the H2 console.

spring.h2.console.path=/h2


Although H2 is easy to start using, it is not enough for real projects when your database should be stored outside your own computer and multiple applications may access it (it’s called a standalone mode). In this case, you need to use one of persistent production-ready RDBS. The main difference here is that instead of managing the data source properties in our Spring application, we need to get and apply them from the database settings (host, port, user, etc.).

## Using MySQL

As you already know, MySQL is one of the most popular RDBMS in the world. Let’s consider how to add this database to your Spring Boot project. If you want to work with MySQL locally, you may install it following these instructions. Before connecting a Spring Boot application to a database, you need to have:

• a database created for your application, e.g. testdb;
• a user with a password to access the database, e.g. testuser with privileges to edit tables in that database.

If you don’t have any of them, just google some guides on how to do it and then try to access your database via the terminal / mysql workbench / IntelliJ IDEA DB Navigator.

Now let’s configure our project to work with the database. First of all, you need to add a dependency for the database driver.

implementation 'mysql:mysql-connector-java'


For Maven:

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


You also need to configure access to the target database. If you have installed MySQL locally, configure the data source to work with a MySQL location, database name, and user details.

spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/testdb


Now you need to add a dependency to the JPA (spring-boot-starter-data-jpa) and set up the dialect of MySQL depending on the SQL version you are going to use. For example, you can set this one:

spring.jpa.database-platform=org.hibernate.dialect.MySQL5Dialect


If the properties are filled correctly, your application will start without any errors and connect to the database. Here is a short version of a successful starting log:

Finished Spring Data repository scanning in 3 ms. Found 0 JPA repository interfaces.
…
Using dialect: org.hibernate.dialect.MySQL5Dialect
…
Started DbdemoApplication in 6.338 seconds (JVM running for 6.648)


## Using PostgreSQL

Postgres is another popular RDBMS. If you want to work with Postgres locally, you can install it following these instructions. Adding PostgreSQL to your project isn’t that different from adding MySQL. Before doing it you also need to create a database and a user to access it. If you don’t have any of them, just google some guides on how to do it and then try to access your database via the terminal / pgadmin / IntelliJ IDEA DB Navigator.

To have the PostgreSQL driver in your project, add the required dependency.

runtimeOnly 'org.postgresql:postgresql'


For Maven:

<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<scope>runtime</scope>
</dependency>


You also need to configure access to the target database. If you have installed PostgreSQL locally, configure the data source to work with a PostgreSQL location, database name, and user details.

spring.datasource.driverClassName=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://localhost:5432/testdb


Now you need to add a dependency to the JPA (spring-boot-starter-data-jpa) and set up the dialect of Postgres depending on the SQL version you are going to use. For example, you can set this one:

spring.jpa.database-platform=org.hibernate.dialect.PostgreSQL10Dialect


If the properties are filled correctly, your application will start without any errors and connect to the database. Here is a short version of a successful starting log:

Finished Spring Data repository scanning in 4 ms. Found 0 JPA repository interfaces.
...
Using dialect: org.hibernate.dialect.PostgreSQL10Dialect
...
Started DbdemoApplication in 6.786 seconds (JVM running for 7.223)


## Conclusion

In this topic, we have considered the general approach to working with any relational database in a Spring Boot application. The key parts of this approach are adding the required dependencies (driver, JPA) and configuring the properties file. We mostly focused on H2 because this database is easy to access from a Spring Boot application and is often used for educational purposes. However, we also looked at some examples with MySQL and PostgreSQL. We strongly encourage you to apply the knowledge from this topic to at least one other relational database to make sure that you will be able to move on to the next topics and projects!