JDBC & the DAO Pattern

JDBC in a Web Application

18 min Lesson 1 of 13

JDBC in a Web Application

You already know how to write a main() method that opens a JDBC connection, runs a query, and prints the result. A web application is a different beast entirely. Dozens of requests arrive simultaneously, each one expecting a response in milliseconds. The database call that felt instant in a single-threaded demo can silently kill throughput when it is repeated hundreds of times per second across competing threads.

This lesson examines exactly what changes when JDBC moves from a standalone program into the web tier — and why isolating your database code into its own layer is not optional if you want to maintain the application.

The Web Tier Is Multi-Threaded by Default

A Jakarta EE servlet container (Tomcat, Jetty, WildFly) assigns a dedicated thread to each incoming HTTP request. Two users hitting the same endpoint at the same moment each get their own thread, and both threads may try to use the database at the same instant.

A java.sql.Connection is not thread-safe. If two threads share a single Connection instance they will corrupt each other's statements. The naive solution — a single static Connection field on a servlet — is a classic interview trap that actually reaches production far too often.

Never store a Connection as a servlet field. Servlets are singletons: one instance serves all threads. A shared connection means interleaved SQL, garbled result sets, and exceptions that are nearly impossible to reproduce in testing.

Connection Per Request vs. Connection Pooling

The correct model is: one connection per unit of work, obtained at the start of the request and closed (returned to the pool) before the response is sent. Two approaches:

Connection-per-request with DriverManager: open a fresh TCP socket for every request. Works, but establishing a connection costs 20–100 ms, which dominates your response time budget at any meaningful scale.
Connection pool (DataSource): a fixed set of pre-warmed connections is shared across all request threads. Borrowing from the pool takes microseconds. This is the only viable production approach.

The standard pool in the Java ecosystem is HikariCP. In a plain servlet application you initialise it once in a ServletContextListener and expose it through the ServletContext:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import jakarta.servlet.ServletContext;
import jakarta.servlet.ServletContextEvent;
import jakarta.servlet.ServletContextListener;
import jakarta.servlet.annotation.WebListener;
import javax.sql.DataSource;

@WebListener
public class AppContextListener implements ServletContextListener {

    private HikariDataSource pool;

    @Override
    public void contextInitialized(ServletContextEvent sce) {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl(System.getenv("DB_URL"));         // e.g. jdbc:mysql://db:3306/shop
        cfg.setUsername(System.getenv("DB_USER"));
        cfg.setPassword(System.getenv("DB_PASS"));
        cfg.setMaximumPoolSize(10);
        cfg.setConnectionTimeout(30_000);
        cfg.setMaxLifetime(1_800_000);                   // 30 min — must be < DB wait_timeout

        pool = new HikariDataSource(cfg);

        ServletContext ctx = sce.getServletContext();
        ctx.setAttribute("dataSource", pool);            // make it available to servlets
    }

    @Override
    public void contextDestroyed(ServletContextEvent sce) {
        if (pool != null) pool.close();                  // graceful shutdown
    }
}

Why a ServletContextListener? It runs once when the application starts (and once more when it stops). That is the right lifecycle for a resource as expensive as a database connection pool — not every time a servlet handles a request.

Borrowing a Connection inside a Servlet

Once the DataSource is in the ServletContext, any servlet retrieves it and borrows a connection for the duration of the request:

import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

@WebServlet("/products")
public class ProductListServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws IOException {

        DataSource ds = (DataSource) getServletContext().getAttribute("dataSource");

        try (Connection conn = ds.getConnection();
             PreparedStatement ps = conn.prepareStatement(
                     "SELECT id, name, price FROM products WHERE active = ?")) {

            ps.setBoolean(1, true);

            try (ResultSet rs = ps.executeQuery()) {
                // build response ...
            }

        } catch (SQLException e) {
            resp.sendError(500, "Database error");
        }
    }
}

The try-with-resources block guarantees the connection is returned to the pool even when an exception occurs — an absolute requirement in multi-threaded code.

Why Putting SQL Directly in Servlets Is a Problem

The servlet above works, but it is already doing too much: receiving HTTP input, executing SQL, and building a response. That is three distinct responsibilities in one class. As the application grows you will feel the pain:

Duplication: the same SELECT * FROM products query appears in five servlets. Changing the table name means editing five files.
Untestability: to test any business logic you must spin up a servlet container and a database. Unit tests become integration tests by accident.
Coupling: switching from MySQL to PostgreSQL means hunting SQL dialects through every controller class.

The solution is a separate data access layer — a group of classes whose only job is translating between Java objects and database rows. Every other part of the application talks to these classes, never to JDBC directly.

The Separation of Concerns Model

In a well-structured web application, responsibilities are divided across layers:

Web / Controller layer — Servlets or framework controllers. Parse HTTP input, call the service or DAO, forward to a view. Zero SQL here.
Service layer (optional at small scale) — Business rules, orchestration, transaction boundaries. Calls one or more DAOs.
Data Access Object (DAO) layer — JDBC statements, result set mapping. Returns domain objects or primitives. No HTTP knowledge here.
Domain model — Plain Java objects (POJOs) like Product, Order. No framework or JDBC dependencies.

With this structure your servlet becomes trivially thin:

@WebServlet("/products")
public class ProductListServlet extends HttpServlet {

    private final ProductDao productDao = new ProductDao();   // injected in real apps

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws IOException, ServletException {

        List<Product> products = productDao.findAllActive();  // no SQL in the servlet
        req.setAttribute("products", products);
        req.getRequestDispatcher("/WEB-INF/views/products.jsp").forward(req, resp);
    }
}

Program to interfaces. Declare your DAO field as ProductDao (or better, an interface IProductDao) so you can swap in a fake/stub for unit tests without touching the servlet. This is the single biggest testability win you can make to a JDBC application.

What JDBC Still Does — and Does Not Do — in the Web Tier

JDBC remains the low-level plumbing: SQL strings, parameter binding, result sets, transactions. What changes in a web context is how you manage the connection lifecycle and where you keep the SQL. The specific mechanics — PreparedStatement, row mapping, exception handling — are covered in the following lessons. What matters now is that you understand the architecture before you write the first query:

The pool is initialised once and lives for the application lifetime.
Each request borrows a connection, does its work, and returns it.
SQL belongs in dedicated DAO classes, not in servlets or JSPs.

Summary

Deploying JDBC in a web application requires two fundamental changes from standalone code: replace DriverManager with a pooled DataSource to survive concurrent load, and move all database code out of the web layer into a dedicated data access layer. These two decisions — pooling and layering — are the foundation on which every subsequent lesson builds. With the architecture clear, the next lesson formalises the data access layer into the DAO pattern.