JDBC & the DAO Pattern

Project: A Database-Backed CRUD Web App

18 min Lesson 10 of 13

Project: A Database-Backed CRUD Web App

This capstone lesson ties together everything covered in the tutorial: a pooled DataSource, a DAO interface with a PreparedStatement-based implementation, a thin service layer that owns transactions, and JSP views that drive the full Create / Read / Update / Delete cycle. You will build a minimal Product Catalogue application — realistic enough to show every moving part, small enough to fit in a single lesson.

Project Layout

The Maven WAR project follows a clean layered structure. Every layer has a single responsibility and depends only on the layer below it.

product-catalogue/
├── pom.xml
└── src/main/
    ├── java/com/example/catalogue/
    │   ├── db/           DataSourceProvider.java
    │   ├── model/        Product.java
    │   ├── dao/          ProductDao.java  (interface)
    │   │                 ProductDaoImpl.java
    │   ├── service/      ProductService.java
    │   └── servlet/      ProductServlet.java
    └── webapp/
        ├── WEB-INF/
        │   ├── web.xml
        │   └── views/
        │       ├── list.jsp
        │       ├── form.jsp
        │       └── error.jsp
        └── index.jsp   (redirects to /products)

Step 1 — The Model

Product is a plain Java object. Keep it free of any persistence logic.

package com.example.catalogue.model;

public class Product {
    private int    id;
    private String name;
    private String sku;
    private double price;
    private int    stock;

    public Product() {}

    public Product(int id, String name, String sku, double price, int stock) {
        this.id = id; this.name = name; this.sku = sku;
        this.price = price; this.stock = stock;
    }

    // standard getters and setters omitted for brevity
    public int    getId()    { return id; }
    public void   setId(int id)         { this.id = id; }
    public String getName()  { return name; }
    public void   setName(String n)     { this.name = n; }
    public String getSku()   { return sku; }
    public void   setSku(String s)      { this.sku = s; }
    public double getPrice() { return price; }
    public void   setPrice(double p)    { this.price = p; }
    public int    getStock() { return stock; }
    public void   setStock(int s)       { this.stock = s; }
}

Step 2 — The DAO Interface and Implementation

Define the contract first; the implementation is the only class that touches SQL.

package com.example.catalogue.dao;

import com.example.catalogue.model.Product;
import java.sql.SQLException;
import java.util.List;

public interface ProductDao {
    List<Product> findAll()               throws SQLException;
    Product        findById(int id)        throws SQLException;
    void           insert(Product p)       throws SQLException;
    void           update(Product p)       throws SQLException;
    void           delete(int id)          throws SQLException;
}

The implementation injects a DataSource through its constructor — this makes it trivially testable.

package com.example.catalogue.dao;

import com.example.catalogue.model.Product;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {

    private final DataSource ds;

    public ProductDaoImpl(DataSource ds) { this.ds = ds; }

    // ---- helpers ----------------------------------------

    private Product map(ResultSet rs) throws SQLException {
        return new Product(
            rs.getInt("id"),
            rs.getString("name"),
            rs.getString("sku"),
            rs.getDouble("price"),
            rs.getInt("stock")
        );
    }

    // ---- CRUD -------------------------------------------

    @Override
    public List<Product> findAll() throws SQLException {
        String sql = "SELECT id, name, sku, price, stock FROM products ORDER BY name";
        List<Product> list = new ArrayList<>();
        try (Connection c = ds.getConnection();
             PreparedStatement ps = c.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) list.add(map(rs));
        }
        return list;
    }

    @Override
    public Product findById(int id) throws SQLException {
        String sql = "SELECT id, name, sku, price, stock FROM products WHERE id = ?";
        try (Connection c = ds.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? map(rs) : null;
            }
        }
    }

    @Override
    public void insert(Product p) throws SQLException {
        String sql = "INSERT INTO products (name, sku, price, stock) VALUES (?, ?, ?, ?)";
        try (Connection c = ds.getConnection();
             PreparedStatement ps = c.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            ps.setString(1, p.getName());
            ps.setString(2, p.getSku());
            ps.setDouble(3, p.getPrice());
            ps.setInt(4, p.getStock());
            ps.executeUpdate();
            try (ResultSet keys = ps.getGeneratedKeys()) {
                if (keys.next()) p.setId(keys.getInt(1));
            }
        }
    }

    @Override
    public void update(Product p) throws SQLException {
        String sql = "UPDATE products SET name=?, sku=?, price=?, stock=? WHERE id=?";
        try (Connection c = ds.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setString(1, p.getName());
            ps.setString(2, p.getSku());
            ps.setDouble(3, p.getPrice());
            ps.setInt(4, p.getStock());
            ps.setInt(5, p.getId());
            ps.executeUpdate();
        }
    }

    @Override
    public void delete(int id) throws SQLException {
        String sql = "DELETE FROM products WHERE id = ?";
        try (Connection c = ds.getConnection();
             PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, id);
            ps.executeUpdate();
        }
    }
}

Why pass DataSource through the constructor instead of looking it up? Constructor injection keeps the DAO decoupled from JNDI and from any specific pool library. In a unit test you can pass an in-memory H2 DataSource; in production the servlet container supplies HikariCP. Same DAO, zero changes.

Step 3 — The Service Layer

The service owns validation and transaction boundaries. For simple single-table CRUD the service is thin, but it is the right place to add business rules later (e.g., "do not delete a product that has open orders").

package com.example.catalogue.service;

import com.example.catalogue.dao.ProductDao;
import com.example.catalogue.model.Product;
import java.sql.SQLException;
import java.util.List;

public class ProductService {

    private final ProductDao dao;

    public ProductService(ProductDao dao) { this.dao = dao; }

    public List<Product> listAll() throws SQLException {
        return dao.findAll();
    }

    public Product getById(int id) throws SQLException {
        Product p = dao.findById(id);
        if (p == null) throw new IllegalArgumentException("Product not found: " + id);
        return p;
    }

    public void create(Product p) throws SQLException {
        validate(p);
        dao.insert(p);
    }

    public void edit(Product p) throws SQLException {
        validate(p);
        dao.update(p);
    }

    public void remove(int id) throws SQLException {
        dao.delete(id);
    }

    private void validate(Product p) {
        if (p.getName() == null || p.getName().isBlank())
            throw new IllegalArgumentException("Name is required.");
        if (p.getSku() == null || p.getSku().isBlank())
            throw new IllegalArgumentException("SKU is required.");
        if (p.getPrice() < 0)
            throw new IllegalArgumentException("Price cannot be negative.");
    }
}

Step 4 — The Servlet (Front Controller)

A single ProductServlet maps to /products and dispatches on an action query parameter. It initialises the DAO and service once at startup using @WebServlet with loadOnStartup = 1.

package com.example.catalogue.servlet;

import com.example.catalogue.dao.ProductDaoImpl;
import com.example.catalogue.db.DataSourceProvider;
import com.example.catalogue.model.Product;
import com.example.catalogue.service.ProductService;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;

@WebServlet(urlPatterns = "/products", loadOnStartup = 1)
public class ProductServlet extends HttpServlet {

    private ProductService service;

    @Override
    public void init() throws ServletException {
        try {
            service = new ProductService(
                new ProductDaoImpl(DataSourceProvider.get())
            );
        } catch (Exception e) {
            throw new ServletException("Failed to initialise service", e);
        }
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        String action = req.getParameter("action");
        if (action == null) action = "list";
        try {
            switch (action) {
                case "new"    -> showForm(req, resp, new Product());
                case "edit"   -> showEditForm(req, resp);
                case "delete" -> handleDelete(req, resp);
                default       -> showList(req, resp);
            }
        } catch (SQLException | IllegalArgumentException e) {
            req.setAttribute("error", e.getMessage());
            req.getRequestDispatcher("/WEB-INF/views/error.jsp").forward(req, resp);
        }
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        String action = req.getParameter("action");
        try {
            if ("create".equals(action))       handleCreate(req, resp);
            else if ("update".equals(action))  handleUpdate(req, resp);
        } catch (SQLException | IllegalArgumentException e) {
            req.setAttribute("error", e.getMessage());
            req.getRequestDispatcher("/WEB-INF/views/error.jsp").forward(req, resp);
        }
    }

    // ---- private helpers --------------------------------

    private void showList(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException, SQLException {
        req.setAttribute("products", service.listAll());
        req.getRequestDispatcher("/WEB-INF/views/list.jsp").forward(req, resp);
    }

    private void showForm(HttpServletRequest req, HttpServletResponse resp, Product p)
            throws ServletException, IOException {
        req.setAttribute("product", p);
        req.getRequestDispatcher("/WEB-INF/views/form.jsp").forward(req, resp);
    }

    private void showEditForm(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException, SQLException {
        int id = Integer.parseInt(req.getParameter("id"));
        showForm(req, resp, service.getById(id));
    }

    private void handleDelete(HttpServletRequest req, HttpServletResponse resp)
            throws IOException, SQLException {
        service.remove(Integer.parseInt(req.getParameter("id")));
        resp.sendRedirect(req.getContextPath() + "/products");
    }

    private void handleCreate(HttpServletRequest req, HttpServletResponse resp)
            throws IOException, SQLException {
        service.create(buildFromRequest(req, 0));
        resp.sendRedirect(req.getContextPath() + "/products");
    }

    private void handleUpdate(HttpServletRequest req, HttpServletResponse resp)
            throws IOException, SQLException {
        int id = Integer.parseInt(req.getParameter("id"));
        service.edit(buildFromRequest(req, id));
        resp.sendRedirect(req.getContextPath() + "/products");
    }

    private Product buildFromRequest(HttpServletRequest req, int id) {
        Product p = new Product();
        p.setId(id);
        p.setName(req.getParameter("name"));
        p.setSku(req.getParameter("sku"));
        p.setPrice(Double.parseDouble(req.getParameter("price")));
        p.setStock(Integer.parseInt(req.getParameter("stock")));
        return p;
    }
}

Post-Redirect-Get (PRG) pattern: After every successful POST (create or update), the servlet issues a sendRedirect rather than a forward. This prevents the browser from re-submitting the form when the user refreshes, which would create duplicate records.

Step 5 — The JSP Views

list.jsp — iterates the product list and provides action links.

<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<html><body>
<h1>Products</h1>
<a href="${pageContext.request.contextPath}/products?action=new">+ New Product</a>
<table>
  <tr><th>Name</th><th>SKU</th><th>Price</th><th>Stock</th><th>Actions</th></tr>
  <c:forEach var="p" items="${products}">
  <tr>
    <td><c:out value="${p.name}"/></td>
    <td><c:out value="${p.sku}"/></td>
    <td><c:out value="${p.price}"/></td>
    <td><c:out value="${p.stock}"/></td>
    <td>
      <a href="products?action=edit&amp;id=${p.id}">Edit</a>
      <a href="products?action=delete&amp;id=${p.id}"
         onclick="return confirm('Delete this product?')">Delete</a>
    </td>
  </tr>
  </c:forEach>
</table>
</body></html>

form.jsp — shared for both create and edit. When product.id is non-zero the form is in edit mode.

<%@ taglib prefix="c" uri="jakarta.tags.core" %>
<html><body>
<h1><c:choose>
  <c:when test="${product.id == 0}">New Product</c:when>
  <c:otherwise>Edit Product</c:otherwise>
</c:choose></h1>

<form method="post" action="${pageContext.request.contextPath}/products">
  <input type="hidden" name="action" value="${product.id == 0 ? 'create' : 'update'}"/>
  <c:if test="${product.id != 0}">
    <input type="hidden" name="id" value="${product.id}"/>
  </c:if>

  <label>Name: <input name="name" value="<c:out value='${product.name}'/>" required/></label><br/>
  <label>SKU:  <input name="sku"  value="<c:out value='${product.sku}'/>"  required/></label><br/>
  <label>Price:<input name="price" type="number" step="0.01" value="${product.price}" required/></label><br/>
  <label>Stock:<input name="stock" type="number" value="${product.stock}" required/></label><br/>
  <button type="submit">Save</button>
  <a href="${pageContext.request.contextPath}/products">Cancel</a>
</form>
</body></html>

Always use <c:out> when rendering user-supplied text in JSP. Without it, a product named <script>alert(1)</script> would execute in the browser. <c:out> HTML-escapes the value automatically, blocking reflected XSS.

Step 6 — Wiring Everything Together

The DataSourceProvider initialises HikariCP once and hands the same pool to every DAO. Reading credentials from environment variables keeps secrets out of source control.

package com.example.catalogue.db;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;

public class DataSourceProvider {
    private static final HikariDataSource DS;
    static {
        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl(System.getenv("DB_URL"));       // e.g. jdbc:mysql://localhost/catalogue
        cfg.setUsername(System.getenv("DB_USER"));
        cfg.setPassword(System.getenv("DB_PASS"));
        cfg.setMaximumPoolSize(10);
        DS = new HikariDataSource(cfg);
    }
    public static DataSource get() { return DS; }
}

The DDL for the products table is straightforward:

CREATE TABLE products (
    id    INT          AUTO_INCREMENT PRIMARY KEY,
    name  VARCHAR(120) NOT NULL,
    sku   VARCHAR(60)  NOT NULL UNIQUE,
    price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    stock INT          NOT NULL DEFAULT 0
);

Key Design Decisions

One servlet, action dispatch: keeps URL mapping simple. For larger apps, move to a framework (Spring MVC) or a JAX-RS resource class.
DAO per entity: ProductDao owns all SQL for products. No SQL leaks into the servlet or service.
Service owns validation: the DAO trusts data; the service enforces rules. This boundary makes unit-testing business logic without a database straightforward.
PRG after POST: prevents duplicate submissions on refresh.
Constructor injection throughout: every class receives its dependencies; no static look-ups in business logic. The object graph is assembled in init().

Summary

You now have a complete, layered CRUD web application: DataSource (pooling) → DAO (SQL, row mapping) → Service (validation, business rules) → Servlet (HTTP dispatch, PRG) → JSP (display, XSS-safe output). Each layer is independently testable and replaceable. This architecture scales cleanly — swap the DAO implementation for one backed by Spring Data JPA tomorrow, and neither the service nor the servlet changes at all.