Connecting Spring Boot to MySQL/PostgreSQL

Integrating a Spring Boot application with a relational database like MySQL or PostgreSQL is a common requirement for building modern applications. Spring Boot simplifies database connectivity with built-in support for JDBC, JPA, and Hibernate, allowing developers to focus on building business logic rather than complex configurations.

This guide covers key steps for connecting Spring Boot to MySQL or PostgreSQL, including setting up the JDBC driver, configuring datasource properties, leveraging JPA with Hibernate, and testing via a sample entity.

Table of Contents

1. JDBC Driver Setup
2. Configuring Datasource Properties
3. Using JPA and Hibernate
4. Testing with a Sample Entity
5. External Resources for Further Learning
6. Final Thoughts

JDBC Driver Setup

To connect your Spring Boot application to MySQL or PostgreSQL, you need the respective JDBC driver dependencies in your project. These drivers are responsible for enabling your application to communicate with the database server.

Adding the JDBC Driver

MySQL Dependency

For Maven:

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <scope>runtime</scope>
</dependency>

For Gradle:

runtimeOnly 'mysql:mysql-connector-j'

PostgreSQL Dependency

For Maven:

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

For Gradle:

runtimeOnly 'org.postgresql:postgresql'

With these dependencies added, Spring Boot automatically detects and uses the correct driver based on your configured database.

Configuring Datasource Properties

Datasource configuration establishes the connection to the database, specifying properties such as URL, username, password, and driver class.

Example Configuration in application.properties

MySQL Configuration

spring.datasource.url=jdbc:mysql://localhost:3306/mydb
spring.datasource.username=root
spring.datasource.password=secret
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

PostgreSQL Configuration

spring.datasource.url=jdbc:postgresql://localhost:5432/mydb
spring.datasource.username=postgres
spring.datasource.password=secret
spring.datasource.driver-class-name=org.postgresql.Driver

Using application.yml (Alternative Configuration Format)

For PostgreSQL:

spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/mydb
    username: postgres
    password: secret
    driver-class-name: org.postgresql.Driver

Testing the Connection

After configuring the datasource, Spring Boot automatically validates the connection upon application startup. Any issues, such as an incorrect username/password, will throw an exception at runtime, visible in the logs.

Pro Tip: Use the spring.sql.init.mode=always property to initialize your database during testing.

Using JPA and Hibernate

Spring Boot includes default support for JPA (Java Persistence API) and Hibernate, which simplifies database interaction by allowing developers to work with entities rather than raw SQL.

Adding Spring Data JPA Dependency

For Maven:

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

For Gradle:

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

Configuring JPA Properties

Include additional configuration for JPA and Hibernate in your application.properties or application.yml file:

Example Configuration:

# JPA Hibernate Configuration
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect  # For PostgreSQL

Here’s what each property does:

• ddl-auto=update: Automatically updates the database schema to match your entity classes.
• show-sql=true: Enables printing of SQL queries in the logs.
• hibernate.dialect: Specifies the correct dialect for the database (e.g., MySQLDialect or PostgreSQLDialect).

Common Hibernate Dialects

• MySQL: org.hibernate.dialect.MySQLDialect or MySQL8Dialect (for modern versions).
• PostgreSQL: org.hibernate.dialect.PostgreSQLDialect.

Testing with a Sample Entity

To ensure your database connection works as expected, create and test a sample entity, repository, and service.

Step 1 – Define the Entity

User.java

package com.example.demo.model;

import jakarta.persistence.Entity;
import jakarta.persistence.GeneratedValue;
import jakarta.persistence.GenerationType;
import jakarta.persistence.Id;

@Entity
public class User {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String name;
    private String email;

    // Getters and Setters
    public Long getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

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

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

Step 2 – Create the Repository

UserRepository.java

package com.example.demo.repository;

import com.example.demo.model.User;
import org.springframework.data.jpa.repository.JpaRepository;

public interface UserRepository extends JpaRepository<User, Long> {
}

The JpaRepository interface provides built-in methods for CRUD operations.

Step 3 – Build a Service Layer

UserService.java

package com.example.demo.service;

import com.example.demo.model.User;
import com.example.demo.repository.UserRepository;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    public List<User> getAllUsers() {
        return userRepository.findAll();
    }
}

Step 4 – Add a Rest Controller

UserController.java

package com.example.demo.controller;

import com.example.demo.model.User;
import com.example.demo.service.UserService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class UserController {

    private final UserService userService;

    public UserController(UserService userService) {
        this.userService = userService;
    }

    @GetMapping("/users")
    public List<User> getUsers() {
        return userService.getAllUsers();
    }
}

Step 5 – Testing

Start your application and use an API testing tool like Postman to hit the /users endpoint. If your datasource is correctly configured, the endpoint should return all users from the database (an empty array by default if no data is present).

External Resources for Further Learning

• Spring Boot JPA Documentation
• MySQL JDBC Documentation
• PostgreSQL JDBC Documentation
• Wikipedia Database Management Systems

Final Thoughts

Connecting Spring Boot to relational databases like MySQL and PostgreSQL is an essential skill for any Java developer. By combining the flexibility of JDBC with the power of JPA and Hibernate, Spring Boot allows you to build scalable applications with minimal setup. This guide provided the foundational steps for establishing a database connection, configuring datasource properties, and working with JPA entities.

Start integrating your Spring Boot apps with MySQL or PostgreSQL today, and take advantage of the robust support these tools offer for building modern web applications. Bookmark this guide and return to it whenever you need a refresher!