Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 66 ยท Data Access

DataSource, Connection Pools and JdbcClient

Connect Spring Boot to a database: DataSource auto-configuration, HikariCP pool settings, JdbcClient queries, schema.sql and data.sql, and the H2 console.

Almost every real application keeps its data in a database, and Spring Boot makes the first connection almost effortless: add a driver, set a URL, and a working connection pool appears. This lesson explains what sits behind that convenience. You will meet the DataSource, the connection pool that Boot configures for you (HikariCP), and JdbcClient, a small fluent API for running SQL without the boilerplate of raw JDBC.

You do not need an object-relational mapper for everything. Plain SQL through JdbcClient is fast, transparent and perfect for reports, bulk work and small services. The next lessons build JPA on top of the same foundation. For raw JDBC itself, see JDBC Introduction, and for the older JdbcTemplate in a non-Boot setting, see Spring JDBC.

Spring Boot and Maven cannot run on this site, so the Spring code is read-only. The runnable examples use real H2 through plain JDBC, which is exactly what JdbcClient calls underneath, plus a hand-written mini connection pool.

What auto-configuration gives you

With spring-boot-starter-jdbc (or JPA, which includes it) and a database driver on the classpath, Boot creates a DataSource bean. A DataSource is a factory for connections. Boot picks the implementation in this order: HikariCP if present (it is, in the starter), then Tomcat's pool, then Commons DBCP2.

You describe the database in application.yml:

YAML
spring:
  datasource:
    url: jdbc:postgresql://localhost:5432/shop
    username: shop
    password: ${DB_PASSWORD}

Boot works out the driver class from the URL. Notice that the password is read from an environment variable, never written in the file (see Profiles and Environment-Specific Config).

If you set no URL at all and an embedded database such as H2 is on the classpath, Boot starts an in-memory database for you. That is wonderful for tutorials and tests, and a trap in production: if the real URL is missing, the app starts happily with an empty throwaway database. Fail fast instead by adding the real driver only for production profiles or by checking your configuration in a startup test.

From URL to query

Driver Found from the JDBC URL.
Pool HikariCP keeps connections open.
DataSource The bean that hands them out.
JdbcClient Runs your SQL.
1 / 2

Why a connection pool

Opening a database connection involves a network round trip, authentication and often TLS. Doing that for every request would waste more time than the query itself. A connection pool opens a fixed number of connections once and lends them out. Your code borrows a connection, runs its statements and gives it back; closing a pooled connection does not close the socket, it returns the connection to the pool.

The pool also acts as a safety valve. If 500 requests arrive at once and the pool has 10 connections, 490 requests wait in line instead of 500 simultaneous queries overwhelming the database. The wait is bounded: when a request waits longer than connection-timeout, it fails with an exception instead of hanging forever.

The mini pool below makes this behavior visible using real H2 connections. It hands out two connections, shows that a third caller times out, and shows that returning one connection frees a place.

Java A mini connection pool over real H2

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The failure message is almost word for word what HikariCP reports when the pool is exhausted. The usual cause in real applications is a connection leak: code that borrows a connection and forgets to give it back, which JdbcClient and @Transactional prevent by always releasing it for you.

Tuning HikariCP

Boot exposes Hikari's settings under spring.datasource.hikari. The few that matter:

YAML
spring:
  datasource:
    hikari:
      pool-name: shop-pool
      maximum-pool-size: 10        # default 10
      minimum-idle: 10             # defaults to maximum-pool-size
      connection-timeout: 30000    # ms to wait for a connection, default 30 s
      max-lifetime: 1800000        # ms before a connection is retired, default 30 min
      leak-detection-threshold: 20000   # log a warning if held longer than 20 s
  • maximum-pool-size: a bigger pool is not a faster pool. A database can only work on as many queries at once as it has CPU cores and disks, and extra connections just add contention. Ten is plenty for most services; measure before raising it. Remember that every application instance has its own pool, so ten instances with ten connections means one hundred connections at the database.
  • connection-timeout: lower it (for example to 3 seconds) in APIs where a slow answer is worse than a quick error.
  • max-lifetime: keep it a few minutes shorter than any limit your database or firewall places on idle connections.
  • leak-detection-threshold: switch it on in staging; it logs the stack trace of the code that held a connection too long.

Running SQL with JdbcClient

JdbcClient arrived in Spring Framework 6.1 and is the recommended entry point for plain SQL. You inject it like any other bean (Boot configures it from the DataSource), write SQL with named parameters, and map rows straight to records.

Java
record Book(long id, String title, String author, int price) {}

@Repository
class BookRepository {

    private final JdbcClient jdbc;

    BookRepository(JdbcClient jdbc) {
        this.jdbc = jdbc;
    }

    List<Book> findByAuthor(String author) {
        return jdbc.sql("select id, title, author, price from books where author = :author order by title")
            .param("author", author)
            .query(Book.class)
            .list();
    }

    Optional<Book> findById(long id) {
        return jdbc.sql("select id, title, author, price from books where id = :id")
            .param("id", id)
            .query(Book.class)
            .optional();
    }

    long insert(String title, String author, int price) {
        KeyHolder keys = new GeneratedKeyHolder();
        jdbc.sql("insert into books(title, author, price) values (:t, :a, :p)")
            .param("t", title).param("a", author).param("p", price)
            .update(keys);
        return keys.getKey().longValue();
    }

    int count() {
        return jdbc.sql("select count(*) from books").query(Integer.class).single();
    }
}

Points worth knowing:

  • Parameters are always bound, never concatenated into the SQL text. That is what blocks SQL injection. Never build a query by joining strings with user input.
  • query(Book.class) matches columns to record components by name and understands snake_case columns for camelCase components.
  • .list(), .optional() and .single() say what you expect: many rows, zero or one, exactly one. single() throws when the result is empty or has several rows.
  • .update() returns the number of affected rows, which you should check in updates and deletes that must hit a row.
  • Checked SQLExceptions become Spring's unchecked DataAccessException hierarchy, so you do not need try/catch everywhere. JdbcTemplate offers the same features with an older, callback-based style, and you will meet it in existing code.

schema.sql and data.sql

Boot can run SQL scripts at startup. Put schema.sql (tables) and data.sql (starter rows) in src/main/resources:

SQL
-- schema.sql
create table books (
    id     bigint auto_increment primary key,
    title  varchar(100) not null,
    author varchar(60)  not null,
    price  int          not null
);

By default the scripts run only for embedded databases. To control this, set spring.sql.init.mode to always or never. This is handy for demos and tests, but for a real database you should use a migration tool such as Flyway (see Database Migrations with Flyway): it tracks which changes have been applied, while schema.sql blindly reruns on every start.

Here is the same flow in runnable form. The stand-in runs a schema script and a data script against H2, then maps rows to records by reflection, the way query(Book.class) does.

Java schema, data and row mapping with H2

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The two Herbert books come back as Book records, the injection attempt matches nothing because it was treated as a plain string, and the update reports how many rows it changed.

The H2 console and other tools

H2 ships a small browser console that is handy during development. Enable it with spring.h2.console.enabled=true; it appears at /h2-console, and the JDBC URL to type is printed in the startup log when you use an in-memory database. Treat it as a development-only tool: never enable it in production, and if Spring Security is on, you must explicitly allow its path and frames, which is another reason to leave it out of anything public.

For a real database such as PostgreSQL or MySQL, use a proper client (psql, DBeaver, your IDE) and keep the credentials out of the repository.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does Spring Boot do when it finds H2 on the classpath and no spring.datasource.url?

    Boot auto-configures an embedded in-memory database when an embedded driver is available and no URL is given. That is convenient for demos and risky if you forgot the production URL.
  2. 2. Why does a connection pool improve performance?

    Opening a connection is expensive. The pool opens connections once and lends them out, and it also limits how many queries hit the database at once.
  3. 3. In jdbc.sql("... where author = :author").param("author", value), why is the value safe from SQL injection?

    Bound parameters travel separately from the statement, so the database always treats them as data. The danger is in building SQL by concatenating strings.
  4. 4. Which JdbcClient terminal call fits "I expect exactly one row, and anything else is a bug"?

    single() throws when there are zero or several rows. optional() allows zero or one, and list() returns any number.

Summary

  • Boot builds a DataSource from the driver and spring.datasource.*; HikariCP is the default pool.
  • A pool reuses connections and limits load; exhausted pools time out, usually because of leaks or oversized workloads.
  • Keep maximum-pool-size modest, set a sensible connection-timeout and use leak-detection-threshold in staging.
  • JdbcClient runs SQL with named, bound parameters and maps rows to records; use list(), optional() or single() to state the expected shape.
  • schema.sql and data.sql run by default only on embedded databases; use a migration tool for real ones.
  • The H2 console is a development tool only; never enable it in production.

Next: Spring Data JPA Repositories.