Loading published curriculum…

08 · SQL and JDBC / LESSON 36 OF 102

DAO boundaries and connection pools

A DAO translates between application operations and SQL while a pool manages reusable connections.

Before this lesson: Manual transactions, rollback, and batches

Understand the topic

Depend on javax.sql.DataSource so the DAO can borrow a connection without knowing pool implementation details. close on a pooled connection normally returns it to the pool. Keep SQL and row mapping inside the persistence adapter, not in controllers.

Pool size must reflect database capacity and request concurrency. A small pool can queue requests; a huge pool can overload the database. Configure acquisition timeouts and measure active, idle, and waiting connections before tuning.

EXAMPLE · JAVA

TaskCounter.java

import javax.sql.DataSource;
import java.sql.SQLException;
public final class TaskCounter {
 private final DataSource source;
 public TaskCounter(DataSource source) { this.source = source; }
 public long count() throws SQLException {
  try (var c = source.getConnection();
       var ps = c.prepareStatement("SELECT count(*) FROM work_items");
       var rs = ps.executeQuery()) {
   rs.next(); return rs.getLong(1);
  }
 }
}

What to expect

Construct with a configured DataSource. Each call borrows and returns its connection even when the query fails.

YOUR TURN

Put it into practice

  1. 01Refactor direct DriverManager access into a DataSource-backed DAO.
  2. 02Run repeated reads with a deliberately small pool and verify resource return.
  3. 03Verify: The pool does not exhaust under sequential calls and controllers contain no SQL.

Pass a quiz or complete a lab to check today’s streak. Each lesson counts once per day, including when you review it later.

UNDER THE HOOD

Build the mental model

A DAO translates between application operations and SQL while a pool manages reusable connections.

  1. 01Depend on javax.sql.DataSource so the DAO can borrow a connection without knowing pool implementation details. close on a pooled connection normally returns it to the pool. Keep SQL and row mapping inside the persistence adapter, not in controllers.
  2. 02Pool size must reflect database capacity and request concurrency. A small pool can queue requests; a huge pool can overload the database. Configure acquisition timeouts and measure active, idle, and waiting connections before tuning.
  3. 03Trace the example: Construct with a configured DataSource. Each call borrows and returns its connection even when the query fails.

A mistake worth avoiding

Requests eventually time out waiting for a connection.

DEBUGGING ROOM

Something broke. Where do you look?

Requests eventually time out waiting for a connection.

Before revealing the diagnosis, write down your first investigation step.

CHECK YOUR UNDERSTANDING

What does close usually do to a borrowed pooled connection?

Choose one answer

BUILD IT YOURSELF

Refactor direct DriverManager access into a DataSource-backed DAO.

  1. 1.Refactor direct DriverManager access into a DataSource-backed DAO.
  2. 2.Run repeated reads with a deliberately small pool and verify resource return.
  3. 3.Reproduce this failure, then repair it: Requests eventually time out waiting for a connection.

Acceptance checklist

Lab completion is your self-report. Knowledge checks do not certify project quality.

RETRIEVE IT FROM MEMORY

What does close usually do to a borrowed pooled connection?

Revisit this lesson in three days after a successful check or lab.

Ready for the next step?

The pool does not exhaust under sequential calls and controllers contain no SQL.

Reading is separate from passing a knowledge check or completing a lab.

Go deeper · official references