SQL Data Access: JPA, JDBC, jOOQ, and Schema Migrations

Spring Boot provides extensive support for relational databases through multiple abstraction layers, from direct JDBC access using JdbcTemplate or JdbcClient up to full object-relational mapping with Hibernate and Spring Data JPA. Auto-configuration handles DataSource setup, connection pooling, schema migration, and repository scanning so you can focus on your domain model rather than infrastructure plumbing.

DataSource auto-configuration

DataSource configuration is controlled by external configuration properties in spring.datasource.*. At a minimum, provide the JDBC URL:

application.yaml

spring:
  datasource:
    url: "jdbc:mysql://localhost/test"
    username: "dbuser"
    password: "dbpass"

Spring Boot can deduce the JDBC driver class for most databases from the URL. Spring Boot uses the following algorithm for choosing a connection pool:

HikariCP — preferred for its performance and concurrency
Tomcat pooling DataSource — used if HikariCP is not available
Commons DBCP2 — used if Tomcat pool is not available
Oracle UCP — used if none of the above are available

If you use spring-boot-starter-jdbc or spring-boot-starter-data-jpa, you automatically get HikariCP as a dependency.

Embedded databases

For development, Spring Boot can auto-configure embedded H2, HSQL, or Derby databases with no connection URL required:

pom.xml

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

HikariCP configuration

Fine-tune the connection pool with HikariCP-specific properties:

application.yaml

spring:
  datasource:
    hikari:
      maximum-pool-size: 20
      minimum-idle: 5
      connection-timeout: 30000
      idle-timeout: 600000
      max-lifetime: 1800000

Lazy connections

Wrap the pooled DataSource in a proxy that fetches JDBC connections as late as possible:

application.yaml

spring:
  datasource:
    connection-fetch: "lazy"

JPA and Spring Data JPA

JPA
JDBC

The spring-boot-starter-data-jpa starter pulls in Hibernate, Spring Data JPA, and Spring ORM. Define your entity classes and repositories, and Spring Boot handles the rest.Entity class:

City.java

@Entity
public class City {

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

    @Column(nullable = false)
    private String name;

    @Column(nullable = false)
    private String state;

    protected City() {}

    public City(String name, String state) {
        this.name = name;
        this.state = state;
    }

    // getters...
}

Repository interface:

CityRepository.java

public interface CityRepository extends JpaRepository<City, Long> {

    List<City> findAllByState(String state);

    @Query("SELECT c FROM City c WHERE c.name LIKE :name%")
    List<City> findByNameStartingWith(@Param("name") String name);
}

Spring Data JPA generates the SQL for standard finder methods from the method name. For complex queries, use the @Query annotation.Configure DDL behavior:

application.yaml

spring:
  jpa:
    hibernate:
      ddl-auto: "validate"   # validate | update | create | create-drop | none
    show-sql: true

Set spring.jpa.open-in-view to false to disable the Open EntityManager in View interceptor. This is recommended for applications that care about clear transaction boundaries.

Spring’s JdbcTemplate and NamedParameterJdbcTemplate are auto-configured and can be autowired directly into your beans:

MyBean.java

@Component
public class MyBean {

    private final JdbcTemplate jdbcTemplate;

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

    public List<City> findCitiesInState(String state) {
        return jdbcTemplate.query(
            "SELECT id, name, state FROM city WHERE state = ?",
            (rs, rowNum) -> new City(rs.getLong("id"), rs.getString("name"), rs.getString("state")),
            state
        );
    }
}

Or use the newer JdbcClient for a more fluent API:

MyBean.java

@Component
public class MyBean {

    private final JdbcClient jdbcClient;

    public MyBean(JdbcClient jdbcClient) {
        this.jdbcClient = jdbcClient;
    }

    public Optional<City> findById(Long id) {
        return jdbcClient.sql("SELECT * FROM city WHERE id = :id")
            .param("id", id)
            .query(City.class)
            .optional();
    }
}

Customize template properties:

application.yaml

spring:
  jdbc:
    template:
      max-rows: 500

Spring Data JDBC provides repository support with automatic SQL generation. Add spring-boot-starter-data-jdbc and define repositories extending CrudRepository:

CityRepository.java

public interface CityRepository extends CrudRepository<City, Long> {

    @Query("SELECT * FROM city WHERE state = :state")
    List<City> findByState(@Param("state") String state);
}

Schema migration

Flyway

Flyway manages database schema migrations through versioned SQL scripts. Add spring-boot-starter-data-jpa (or spring-jdbc) and the flyway-core dependency:

pom.xml

<dependency>
    <groupId>org.flywaydb</groupId>
    <artifactId>flyway-core</artifactId>
</dependency>

Place migration scripts in src/main/resources/db/migration/ following the naming convention V{version}__{description}.sql:

V1__Create_city_table.sql
V2__Add_population_column.sql

Flyway auto-configures when it detects the dependency on the classpath and runs migrations on startup.

Liquibase

Liquibase manages schema changes through XML, YAML, JSON, or SQL changelogs. Add the dependency:

pom.xml

<dependency>
    <groupId>org.liquibase</groupId>
    <artifactId>liquibase-core</artifactId>
</dependency>

By default, Liquibase reads classpath:/db/changelog/db.changelog-master.yaml. Configure a custom path:

application.yaml

spring:
  liquibase:
    change-log: "classpath:db/changelog/changes.xml"
    enabled: true

jOOQ

jOOQ Object Oriented Querying generates Java code from your database schema and lets you build type-safe SQL queries through its fluent API. Spring Boot auto-configures a DSLContext bean and connects it to your DataSource:

MyBean.java

@Component
public class MyBean {

    private final DSLContext create;

    public MyBean(DSLContext dslContext) {
        this.create = dslContext;
    }

    public List<String> getCityNames(String state) {
        return create
            .select(CITY.NAME)
            .from(CITY)
            .where(CITY.STATE.eq(state))
            .fetch(CITY.NAME);
    }
}

Unless spring.jooq.sql-dialect has been configured, Spring Boot determines the SQL dialect to use for your datasource automatically.

R2DBC (reactive SQL)

For reactive applications, use R2DBC to connect to relational databases without blocking:

application.yaml

spring:
  r2dbc:
    url: "r2dbc:postgresql://localhost/test"
    username: "dbuser"
    password: "dbpass"

Spring Data R2DBC repositories extend ReactiveCrudRepository:

CityRepository.java

public interface CityRepository extends ReactiveCrudRepository<City, Long> {

    Flux<City> findAllByState(String state);
}

H2 web console

The H2 database provides a browser-based console that Spring Boot can auto-configure for you when using Spring Boot DevTools. Enable it manually with:

application.yaml

spring:
  h2:
    console:
      enabled: true
      path: "/h2-console"

The H2 console is only intended for use during development. Ensure spring.h2.console.enabled is not set to true in production.

Get Started

Core Concepts

Building Applications

Production & Observability

Packaging & Deployment

Testing

SQL Data Access: JPA, JDBC, jOOQ, and Schema Migrations

DataSource auto-configuration

Embedded databases

HikariCP configuration

Lazy connections

JPA and Spring Data JPA

Schema migration

jOOQ

R2DBC (reactive SQL)

H2 web console

Build docs developers (and LLMs) love

Get Started

Core Concepts

Building Applications

Production & Observability

Packaging & Deployment

Testing

Documentation Index

​DataSource auto-configuration

​Embedded databases

​HikariCP configuration

​Lazy connections

​JPA and Spring Data JPA

​Schema migration

​jOOQ

​R2DBC (reactive SQL)

​H2 web console

Build docs developers (and LLMs) love

DataSource auto-configuration

Embedded databases

HikariCP configuration

Lazy connections

JPA and Spring Data JPA

Schema migration

jOOQ

R2DBC (reactive SQL)

H2 web console