Configuring Database Connectivity in Spring Boot

Course: Advanced Java Training

Topic: Configuring Database Connectivity in Spring Boot

Course Link: Advanced Java Training Course

Demo Link: Register for a Free Course Demo

Website: JustAcademy

1. Introduction to Database Connectivity in Spring Boot

Database connectivity is an essential part of modern Java applications. Most applications need to store, retrieve, update, and delete information such as customer details, product records, employee data, orders, and user accounts. Spring Boot simplifies database integration by providing auto-configuration, starter dependencies, connection pooling, and support for several database technologies.

Spring Boot applications can connect to relational databases such as MySQL, PostgreSQL, Oracle Database, SQL Server, and H2. They can access these databases using JDBC, Spring Data JPA, Hibernate, or other supported persistence technologies.

Database connectivity configuration defines how an application identifies the database, authenticates with it, establishes connections, and performs database operations.

Learning Objectives

• Understand how database connectivity works in Spring Boot.
• Configure database dependencies and connection properties.
• Connect a Spring Boot application to MySQL and H2.
• Understand JDBC drivers, connection pooling, and DataSource configuration.
• Use Spring Data JPA and Hibernate for database operations.
• Configure database connections using application.properties and application.yml.
• Manage transactions, credentials, and connection errors.
• Apply database configuration best practices in real-world projects.

2. How Database Connectivity Works

Spring Boot provides a convenient way to establish communication between a Java application and a database. Once the correct dependencies and connection properties are configured, Spring Boot can automatically create a DataSource and configure the required database infrastructure.

Basic Architecture

Client / User
     |
     v
Spring Boot Controller
     |
     v
Service Layer
     |
     v
Repository Layer
     |
     v
Spring Data JPA / JDBC
     |
     v
DataSource and Connection Pool
     |
     v
JDBC Driver
     |
     v
Database Server

Explanation of components:

• Controller: Receives HTTP requests and returns responses.
• Service: Contains business logic and coordinates application operations.
• Repository: Provides methods to interact with stored data.
• JPA/Hibernate or JDBC: Converts application operations into database interactions.
• DataSource: Supplies database connections to the application.
• Connection pool: Reuses database connections to reduce connection-creation overhead.
• JDBC driver: Enables Java code to communicate with a specific database.
• Database server: Stores and processes persistent application data.

3. Technologies Used for Database Connectivity

4. Prerequisites

Before configuring database connectivity, ensure the development environment includes:

• Java Development Kit compatible with the selected Spring Boot version.
• Maven or Gradle build tool.
• A Spring Boot project created using Spring Initializr or an existing project.
• A supported database server, such as MySQL, or an embedded database such as H2.
• A database account with the required permissions.
• An IDE such as IntelliJ IDEA, Eclipse, or Spring Tool Suite.

For a MySQL connection, ensure the database server is running and the database schema has been created before starting the application.

5. Adding Database Dependencies

Spring Boot uses starter dependencies to simplify dependency management. The required dependencies depend on the database access method selected for the project.

5.1 MySQL with Spring Data JPA

For a Maven project, add the following dependencies to the pom.xml file. The Spring Boot parent or dependency-management configuration should manage compatible dependency versions.

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

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

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

Dependency explanation:

• spring-boot-starter-data-jpa provides Spring Data JPA and the default Hibernate integration.
• mysql-connector-j provides the JDBC driver for MySQL.
• spring-boot-starter-web supports building REST APIs and web applications.

If the project uses JDBC directly rather than JPA, use spring-boot-starter-jdbc instead of the JPA starter where appropriate. Avoid adding unnecessary persistence dependencies.

5.2 Gradle Dependencies

For a Gradle project using the Spring Boot dependency-management setup, the equivalent dependencies are:

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    runtimeOnly 'com.mysql:mysql-connector-j'
    implementation 'org.springframework.boot:spring-boot-starter-web'
}

6. Creating a MySQL Database

Before configuring the application, create a database in MySQL. Open MySQL Workbench or a MySQL command-line client and execute:

CREATE DATABASE springboot_demo;

To verify that the database exists, execute:

SHOW DATABASES;

Choose a database account that has access only to the required schema and operations. For development, a dedicated application account is preferable to using the database root account.

7. Configuring Database Connectivity Using application.properties

The application.properties file is commonly located inside src/main/resources. It stores application configuration, including the database URL, username, password, driver class, and JPA settings.

7.1 MySQL Configuration

spring.datasource.url=jdbc:mysql://localhost:3306/springboot_demo
spring.datasource.username=${DB_USERNAME}
spring.datasource.password=${DB_PASSWORD}
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true

Property explanations:

• spring.datasource.url: Specifies the database connection URL, including the host, port, and schema name.
• spring.datasource.username: Reads the database username from an environment variable.
• spring.datasource.password: Reads the database password from an environment variable.
• spring.datasource.driver-class-name: Specifies the MySQL JDBC driver. Spring Boot can often infer this from the URL and driver dependency.
• spring.jpa.hibernate.ddl-auto: Controls Hibernate's schema-management behavior.
• spring.jpa.show-sql: Enables SQL statement logging.
• spring.jpa.properties.hibernate.format_sql: Formats logged SQL for readability.

Set the environment variables DB_USERNAME and DB_PASSWORD in the environment where the application runs. Do not commit real database passwords to source control.

7.2 Understanding the JDBC URL

jdbc:mysql://localhost:3306/springboot_demo

• jdbc:mysql identifies the JDBC protocol and MySQL driver.
• localhost identifies the database host.
• 3306 is the commonly used default MySQL port.
• springboot_demo is the database name.

If the database runs on a different machine or port, update the URL accordingly. Additional connection parameters may be required for some environments, but they should be added only when needed and configured according to the database driver's documentation.

8. Configuring Database Connectivity Using application.yml

Spring Boot also supports YAML configuration. The same settings can be expressed in application.yml as follows:

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springboot_demo
    username: ${DB_USERNAME}
    password: ${DB_PASSWORD}
    driver-class-name: com.mysql.cj.jdbc.Driver

  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        format_sql: true

Use either application.properties or application.yml for the same configuration unless there is a specific reason to divide settings across files. Keeping related settings together makes the application easier to maintain.

9. Understanding Hibernate DDL Settings

The spring.jpa.hibernate.ddl-auto property controls how Hibernate handles database schema changes when the application starts.

Important: Avoid relying on update, create, or create-drop for production schema management. They may cause unexpected schema changes or data loss. Production applications commonly use migration tools such as Flyway or Liquibase and set Hibernate to validate or none.

10. Creating a JPA Entity

A JPA entity is a Java class mapped to a database table. Each entity instance represents a record that can be persisted through JPA.

Example: Student Entity

package com.example.demo.entity;

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

@Entity
@Table(name = "students")
public class Student {

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

    private String name;
    private String email;
    private String course;

    public Student() {
    }

    public Student(String name, String email, String course) {
        this.name = name;
        this.email = email;
        this.course = course;
    }

    public Long getId() {
        return 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;
    }

    public String getCourse() {
        return course;
    }

    public void setCourse(String course) {
        this.course = course;
    }
}

Annotations explained:

• @Entity marks the class as a persistent JPA entity.
• @Table(name = "students") specifies the database table name.
• @Id marks the primary-key field.
• @GeneratedValue configures primary-key generation.
• GenerationType.IDENTITY uses identity-based key generation supported by the database.

In modern Spring Boot applications using Spring Boot 3 or later, JPA annotations are imported from jakarta.persistence. Older Spring Boot 2 applications generally use javax.persistence.

11. Creating a Repository Interface

Spring Data JPA provides repository interfaces that reduce the amount of code required for common database operations.

package com.example.demo.repository;

import com.example.demo.entity.Student;
import org.springframework.data.jpa.repository.JpaRepository;

public interface StudentRepository
        extends JpaRepository<Student, Long> {
}

In this example, StudentRepository inherits common operations from JpaRepository, including saving, retrieving, updating, and deleting records.

12. Creating a Service Layer

The service layer contains application logic and separates business operations from web request handling. It can also define transaction boundaries.

package com.example.demo.service;

import com.example.demo.entity.Student;
import com.example.demo.repository.StudentRepository;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Optional;

@Service
public class StudentService {

    private final StudentRepository repository;

    public StudentService(StudentRepository repository) {
        this.repository = repository;
    }

    @Transactional
    public Student createStudent(Student student) {
        return repository.save(student);
    }

    @Transactional(readOnly = true)
    public List<Student> getAllStudents() {
        return repository.findAll();
    }

    @Transactional(readOnly = true)
    public Optional<Student> getStudentById(Long id) {
        return repository.findById(id);
    }

    @Transactional
    public void deleteStudent(Long id) {
        repository.deleteById(id);
    }
}

Explanation:

• @Service registers the class as a Spring-managed service component.
• Constructor injection supplies the repository dependency.
• @Transactional establishes a transaction boundary for a method.
• readOnly = true communicates that a method is intended to perform read operations. It is generally a hint to the transaction infrastructure rather than a universal guarantee that writes are impossible.

13. Creating a REST Controller

A REST controller exposes HTTP endpoints that clients can use to interact with the database-backed application.

package com.example.demo.controller;

import com.example.demo.entity.Student;
import com.example.demo.service.StudentService;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/students")
public class StudentController {

    private final StudentService service;

    public StudentController(StudentService service) {
        this.service = service;
    }

    @PostMapping
    public Student createStudent(@RequestBody Student student) {
        return service.createStudent(student);
    }

    @GetMapping
    public List<Student> getAllStudents() {
        return service.getAllStudents();
    }

    @GetMapping("/{id}")
    public ResponseEntity<Student> getStudentById(
            @PathVariable Long id) {
        return service.getStudentById(id)
                .map(ResponseEntity::ok)
                .orElseGet(() -> ResponseEntity.notFound().build());
    }

    @DeleteMapping("/{id}")
    public ResponseEntity<Void> deleteStudent(
            @PathVariable Long id) {
        service.deleteStudent(id);
        return ResponseEntity.noContent().build();
    }
}

This example demonstrates the basic flow from a REST endpoint to a service and repository. For production applications, add request validation, consistent error handling, appropriate update endpoints, and DTOs rather than exposing persistence entities directly.

14. Testing the Database Connection and API

After configuring the dependencies and connection properties, start the Spring Boot application. Watch the startup logs for database connection errors, driver issues, and schema-validation failures.

14.1 Add a Student

Send a POST request to http://localhost:8080/api/students with the following JSON body:

{
  "name": "Aarav Sharma",
  "email": "[email protected]",
  "course": "Advanced Java"
}

Example using curl:

curl -X POST http://localhost:8080/api/students \
  -H "Content-Type: application/json" \
  -d '{"name":"Aarav Sharma","email":"[email protected]","course":"Advanced Java"}'

14.2 Retrieve All Students

GET http://localhost:8080/api/students

The response should contain the saved student records. Exact IDs and response fields depend on the generated schema and application configuration.

14.3 Retrieve a Student by ID

GET http://localhost:8080/api/students/1

If the record exists, the API returns the student. If it does not exist, the example controller returns HTTP 404.

14.4 Delete a Student

DELETE http://localhost:8080/api/students/1

The example returns HTTP 204 after the delete operation. For a production API, consider checking whether the record exists first and returning a suitable response when it does not.

15. Configuring an H2 In-Memory Database

H2 is a lightweight database often used for learning, development, and automated tests. It can run in memory, which means its data is generally lost when the database instance shuts down.

15.1 Add the H2 Dependency

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

15.2 Configure H2

spring.datasource.url=jdbc:h2:mem:trainingdb
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver

spring.jpa.hibernate.ddl-auto=create-drop
spring.h2.console.enabled=true
spring.h2.console.path=/h2-console

Start the application and, when the H2 console is enabled and accessible in the environment, visit http://localhost:8080/h2-console. Enter the JDBC URL jdbc:h2:mem:trainingdb along with the configured credentials.

Security note: The H2 console should be enabled only in appropriate development or test environments. Do not expose it publicly in production. If Spring Security is enabled, additional access and CSRF configuration may be required.

16. Understanding DataSource and Connection Pooling

A DataSource is a standard Java interface used to obtain database connections. Spring Boot typically configures a connection pool automatically when a supported pool implementation is available on the classpath.

Spring Boot commonly uses HikariCP as its default connection pool when available. Instead of opening a new physical database connection for every request, the pool keeps a controlled set of connections available for reuse.

Benefits of Connection Pooling

• Reduces the cost of repeatedly establishing database connections.
• Improves performance for applications that make frequent database requests.
• Controls the maximum number of simultaneous connections.
• Allows the application to reuse existing connections efficiently.
• Provides configurable connection acquisition and lifecycle settings.

16.1 Configuring HikariCP

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=2
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.idle-timeout=600000
spring.datasource.hikari.max-lifetime=1800000

These values are examples, not universal production defaults. Tune pool settings based on database capacity, application concurrency, query duration, and deployment architecture.

17. Using Spring JDBC Instead of JPA

Not every application needs object-relational mapping. Spring JDBC is useful when developers prefer writing SQL statements directly while avoiding repetitive JDBC boilerplate.

17.1 Add the JDBC Starter

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

Keep the appropriate JDBC driver dependency as well. For MySQL, use the MySQL Connector/J dependency shown earlier.

17.2 Example Using JdbcTemplate

package com.example.demo.repository;

import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class StudentJdbcRepository {

    private final JdbcTemplate jdbcTemplate;

    public StudentJdbcRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public int countStudents() {
        return jdbcTemplate.queryForObject(
                "SELECT COUNT(*) FROM students",
                Integer.class
        );
    }
}

Spring Boot can automatically configure JdbcTemplate when the JDBC starter and a valid DataSource are present. Use parameterized SQL for queries that include user-supplied values. Avoid building SQL statements by concatenating untrusted input because it can introduce SQL injection vulnerabilities.

18. Configuring Multiple Database Environments

Applications often use different databases or credentials for local development, testing, staging, and production. Spring Boot profiles allow environment-specific configuration without changing application code.

18.1 Define a Default Configuration

# application.properties
spring.application.name=student-management

18.2 Define Development Configuration

# application-dev.properties
spring.datasource.url=jdbc:mysql://localhost:3306/student_dev
spring.datasource.username=${DEV_DB_USERNAME}
spring.datasource.password=${DEV_DB_PASSWORD}
spring.jpa.hibernate.ddl-auto=update

18.3 Define Production Configuration

# application-prod.properties
spring.datasource.url=${PROD_DB_URL}
spring.datasource.username=${PROD_DB_USERNAME}
spring.datasource.password=${PROD_DB_PASSWORD}
spring.jpa.hibernate.ddl-auto=validate

Activate the desired profile through an environment variable or deployment configuration, for example:

SPRING_PROFILES_ACTIVE=dev

Production credentials should be supplied by a secure secrets-management mechanism or protected deployment environment. Do not place sensitive credentials in a public repository or expose them in logs.

19. Database Transactions in Spring Boot

A transaction groups database operations into a logical unit of work. Transactions help maintain consistency when multiple related operations must succeed or fail together.

Spring provides transaction management through annotations such as @Transactional. With JPA, a transaction typically coordinates entity changes and commits them to the database when the transaction completes successfully.

Example

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class EnrollmentService {

    private final StudentRepository studentRepository;

    public EnrollmentService(StudentRepository studentRepository) {
        this.studentRepository = studentRepository;
    }

    @Transactional
    public void performEnrollment(Student student) {
        studentRepository.save(student);
        // Additional related database operations can be
        // performed within the same transaction.
    }
}

Important considerations:

• Keep transaction boundaries around logically related database operations.
• Understand Spring's proxy-based transaction behavior and the configured rollback rules.
• Do not assume that every checked exception triggers rollback by default.
• Avoid holding database transactions open during slow external API calls.
• Use the correct transaction manager when working with multiple data sources.

20. Database Connectivity Security Best Practices

• Protect credentials: Use environment variables, a secrets manager, or a protected configuration system.
• Use least privilege: Give the application database account only the permissions it requires.
• Use encrypted connections: Configure and verify TLS where supported and required by the deployment environment.
• Validate input: Validate incoming data before storing it.
• Prevent SQL injection: Use parameterized queries, prepared statements, or safe repository methods.
• Manage schema changes: Use versioned migration scripts for production databases.
• Limit connection pools: Configure pool size according to database and application capacity.
• Protect diagnostic endpoints: Restrict database consoles and detailed diagnostics to trusted environments.
• Use appropriate logging: Avoid logging passwords, sensitive data, or full connection URLs containing secrets.
• Back up important data: Establish and test a database backup and recovery plan.

21. Common Database Connectivity Errors and Solutions

22. Troubleshooting Checklist

When a Spring Boot application cannot connect to its database, follow this systematic checklist:

1. Confirm the database server is running.
2. Verify the hostname, port, and schema name in the JDBC URL.
3. Check that the correct JDBC driver is included in the build.
4. Verify that the database username and password are available to the application.
5. Test the credentials using a database client.
6. Confirm that the database account has the required privileges.
7. Review the application startup logs for the first underlying exception.
8. Check whether the application is using the intended Spring profile.
9. Verify that entity mappings and the schema are compatible.
10. Inspect connection-pool settings if connections time out or become exhausted.
11. Remove secrets from logs and configuration before sharing error details.

23. Best Practices for Production Applications

• Keep database configuration separate from application business logic.
• Use Spring Boot's externalized configuration features.
• Prefer constructor injection for repository and service dependencies.
• Use DTOs and validation for incoming REST requests.
• Use pagination for large result sets instead of loading all records unnecessarily.
• Monitor connection-pool utilization, query latency, and database errors.
• Use database migrations to apply reviewed, version-controlled schema changes.
• Set realistic connection and query timeouts.
• Use transactions where consistency across multiple database operations is required.
• Test repository and service logic using an appropriate test database.
• Choose JPA or JDBC based on project requirements rather than using both without a clear reason.
• Never expose database credentials or management consoles to unauthorized users.

24. Practical Exercise: Student Management Database Application

Build a simple Student Management application that stores student information in MySQL using Spring Boot and Spring Data JPA.

Project Requirements

• Create a MySQL database named springboot_demo.
• Configure the JDBC URL and credentials in application configuration.
• Create a Student entity with ID, name, email, and course fields.
• Create a StudentRepository extending JpaRepository.
• Create a service layer for student operations.
• Create REST endpoints to add, list, retrieve, update, and delete students.
• Validate email and required fields.
• Test the endpoints with Postman or curl.
• Verify that records persist in the database after restarting the application.
• Handle missing records and database errors with appropriate responses.

Expected Learning Outcome

After completing the exercise, learners should be able to configure a database connection, map a Java entity to a relational table, implement CRUD operations, test REST endpoints, and troubleshoot common connectivity problems.

25. Interview Questions and Answers

Q1. What is database connectivity in Spring Boot?

Database connectivity is the configuration and infrastructure that allows a Spring Boot application to communicate with a database, execute queries, and persist or retrieve data.

Q2. What is a DataSource?

A DataSource is a Java interface used to obtain database connections. In Spring Boot, it is commonly auto-configured from database dependencies and connection properties.

Q3. What is JDBC?

JDBC stands for Java Database Connectivity. It provides APIs for connecting to relational databases, executing SQL statements, and processing query results.

Q4. What is the purpose of a JDBC driver?

A JDBC driver implements the communication required between Java database APIs and a specific database system.

Q5. What is the purpose of application.properties?

It stores externalized application configuration, such as database URLs, credentials, logging options, and JPA settings.

Q6. What is the difference between JPA and Hibernate?

JPA is a persistence specification, while Hibernate is an ORM framework that implements JPA and provides additional persistence features.

Q7. What is Spring Data JPA?

Spring Data JPA is a Spring project that simplifies repository implementation and common database operations using JPA.

Q8. Why is a connection pool needed?

A connection pool reuses database connections, reducing the overhead of repeatedly creating physical connections and helping control concurrent database access.

Q9. What is HikariCP?

HikariCP is a high-performance JDBC connection pool commonly used by Spring Boot applications.

Q10. What does spring.jpa.hibernate.ddl-auto do?

It controls Hibernate schema-management behavior, with options such as none, validate, update, create, and create-drop.

Q11. Why should ddl-auto=update be used cautiously?

Automatic schema updates may not provide controlled, reviewable production migrations and may cause unexpected schema changes. Versioned migration tools are generally preferred for production.

Q12. How can database credentials be protected?

Store them in environment variables, a secrets manager, or protected deployment configuration rather than committing passwords to source control.

Q13. What is the difference between JDBC and JPA?

JDBC provides direct SQL execution and low-level result handling. JPA provides an object-relational persistence model that maps Java entities to database tables.

Q14. What is @Transactional?

@Transactional declares a transaction boundary managed by Spring's transaction infrastructure, helping coordinate related database operations.

Q15. How do you troubleshoot a database connection failure?

Check the database service, network, hostname, port, JDBC URL, driver dependency, credentials, privileges, active profile, and the complete underlying exception in the application logs.

26. Quick Revision Checklist

• Understand the role of JDBC, JPA, Hibernate, and Spring Data JPA.
• Add the correct database driver and persistence dependencies.
• Configure the JDBC URL, username, and password securely.
• Understand DataSource configuration and connection pooling.
• Create entity classes and repository interfaces.
• Use service and controller layers to structure the application.
• Test create, read, update, and delete operations.
• Understand database profiles, transactions, and schema migrations.
• Diagnose connection errors using application logs and database tools.
• Follow production security and performance best practices.

27. Conclusion

Configuring database connectivity is a fundamental skill for building Spring Boot applications that work with persistent data. Spring Boot simplifies the process through starter dependencies, externalized configuration, automatic DataSource setup, and integration with JDBC, JPA, Hibernate, and Spring Data JPA.

By configuring the correct driver and connection properties, creating entity and repository classes, managing transactions, and testing database operations, developers can build maintainable database-backed applications. Secure credential handling, controlled schema migrations, appropriate connection-pool settings, and systematic troubleshooting are essential for reliable production deployments.

Explore more learning opportunities through the Advanced Java Training course at JustAcademy, visit the course demo registration page, or browse the JustAcademy website.