Connecting Spring Boot With MySQL and Oracle Databases

This guide will help you understand how to connect Spring Boot with databases like MySQL and Oracle.

You Will Learn

• How to connect a Spring Boot, JPA application with MySQL and Oracle.
• What should you configure in application.properties.
• How to set up the database schema.

Updating the Spring Boot Project Step by Step

Let's look at the 5 steps involved in connecting a Spring Boot application to a database.

You can use the example we created earlier for connecting to H2 in a memory database as the starting point for this article.

Step 1 - Add a Dependency for Your Database Connector to pom.xml

An example for a MySQL database is shown below.

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

If you would want to connect to an Oracle database, you can use a dependency similar to the one shown below.

<dependency>
    <groupId>com.oracle</groupId>
    <artifactId>ojdbc7</artifactId>
    <version>12.1.0.1</version>
</dependency>

Step 2 - Remove H2 Dependency From pom.xml

Or at least make its scope as test

<!--
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>test</scope>
</dependency>
-->

Step 3 - Setup Your MySQL Database

We would need to set up a database with a schema and the tables.

For an example, check out my GitHub repo.

Step 4 - Configure Your Connection to Your Database

Configure application.properties to connect to your database.

An example for a MySQL database is shown below:

spring.jpa.hibernate.ddl-auto=none
spring.datasource.url=jdbc:mysql://localhost:3306/todo_example
spring.datasource.username=todouser
spring.datasource.password=YOUR_PASSWORD

spring.jpa.hibernate.ddl-auto

Spring Boot chooses a default value for this based on whether you are connecting to an embedded database or not.

• Embedded Databases - default create-drop
• Other Databases - default none

Here is a quick guide to all the options

• none: No action will be performed.
• create-only: Database creation will be generated from entities.
• drop: Database dropping will be generated from entities.
• create: Database dropping will be generated followed by database creation.
• validate: Validate entities with the database schema.
• update: Update the database schema based on the entities.

Step 5 - Restart and You Are Ready!