Skip to content
Tutorials Center
My learning

Topics

Lesson 46 of 119 ยท Servlets and JSP

Servlet CRUD Web App with Pagination and File Download

Build a complete list, add, edit and delete web app with servlets, a JDBC DAO, paged lists and a CSV download using Content-Disposition, with runnable HttpServer and H2 versions.

Almost every business web application is built from four screens: a list of records, a form to add one, a form to edit one, and a button to delete one. That set of operations is called CRUD (create, read, update, delete). Once you can build it with plain servlets, you understand what every framework generates for you.

In this lesson you will build a book catalog with a Book class, a JDBC DAO, a servlet that routes list/add/edit/delete, a paged list, and a CSV download that uses the Content-Disposition header. A servlet container can't run on this page, so the real Jakarta Servlet code is shown read-only, and two runnable programs rebuild it on the JDK's HttpServer and an H2 database. A real HTTP client calls the endpoints, so you see the actual status codes, redirects and headers. You should know servlets, servlet requests and forms, the DAO pattern and JDBC CRUD.

The shape of the app

flowchart LR
    B["Browser"] -->|"GET /books?page=2"| S["BookServlet"]
    B -->|"POST /books (add, edit, delete)"| S
    S --> D["BookDao (JDBC)"]
    D --> DB[("books table")]
    S -->|"forward"| J["books.jsp / form.jsp"]
    S -->|"redirect after POST"| B
    B -->|"GET /books/export"| X["ExportServlet (CSV)"]

One servlet per resource, one DAO for the SQL, JSP files for the HTML. Every POST ends with a redirect.

A common mapping for one resource uses these URLs:

Action Method and URL Result
List (paged) GET /books?page=0 List page
New form GET /books?action=new Empty form
Edit form GET /books?action=edit&id=7 Form filled with book 7
Create POST /books (no id) Insert, then redirect to the list
Update POST /books (with id) Update, then redirect to the list
Delete POST /books/delete (with id) Delete, then redirect to the list
Export GET /books/export CSV file download

Deleting uses POST, never GET: a link that deletes can be triggered by a search-engine crawler, a browser prefetch, or an attacker's image tag.

The model and the DAO

Java
public record Book(long id, String title, String author, int year) { }
Java
public class BookDao {
    private final DataSource dataSource;

    public BookDao(DataSource dataSource) { this.dataSource = dataSource; }

    public List<Book> findPage(int offset, int size) throws SQLException {
        String sql = "SELECT id, title, author, pub_year FROM book ORDER BY title, id LIMIT ? OFFSET ?";
        try (Connection c = dataSource.getConnection(); PreparedStatement ps = c.prepareStatement(sql)) {
            ps.setInt(1, size);
            ps.setInt(2, offset);
            try (ResultSet rs = ps.executeQuery()) {
                List<Book> books = new ArrayList<>();
                while (rs.next()) {
                    books.add(new Book(rs.getLong(1), rs.getString(2), rs.getString(3), rs.getInt(4)));
                }
                return books;
            }
        }
    }

    public int count() throws SQLException { /* SELECT COUNT(*) FROM book */ }
    public Optional<Book> findById(long id) throws SQLException { /* ... WHERE id = ? */ }
    public long insert(Book book) throws SQLException { /* INSERT ..., RETURN_GENERATED_KEYS */ }
    public boolean update(Book book) throws SQLException { /* UPDATE ... WHERE id = ? */ }
    public boolean delete(long id) throws SQLException { /* DELETE ... WHERE id = ? */ }
}

Every statement is a PreparedStatement, so user input is always a parameter and never part of the SQL text (see SQL injection). The ORDER BY title, id gives the paged list a stable order.

The servlet

Java
@WebServlet(urlPatterns = {"/books", "/books/delete"})
public class BookServlet extends HttpServlet {

    private static final int PAGE_SIZE = 10;
    private BookDao dao;

    @Override
    public void init() {
        dao = new BookDao((DataSource) getServletContext().getAttribute("dataSource"));
    }

    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        try {
            String action = req.getParameter("action");
            if ("new".equals(action)) {
                req.setAttribute("book", new Book(0, "", "", 2000));
                req.getRequestDispatcher("/WEB-INF/views/form.jsp").forward(req, resp);
            } else if ("edit".equals(action)) {
                long id = Long.parseLong(req.getParameter("id"));
                Book book = dao.findById(id).orElse(null);
                if (book == null) { resp.sendError(404); return; }
                req.setAttribute("book", book);
                req.getRequestDispatcher("/WEB-INF/views/form.jsp").forward(req, resp);
            } else {
                showPage(req, resp);
            }
        } catch (SQLException e) {
            throw new ServletException(e);
        } catch (NumberFormatException e) {
            resp.sendError(400, "id must be a number");
        }
    }

    private void showPage(HttpServletRequest req, HttpServletResponse resp)
            throws SQLException, ServletException, IOException {
        int total = dao.count();
        int totalPages = Math.max(1, (int) Math.ceil(total / (double) PAGE_SIZE));
        int page = parse(req.getParameter("page"), 0);
        page = Math.min(Math.max(page, 0), totalPages - 1);             // clamp into range

        req.setAttribute("books", dao.findPage(page * PAGE_SIZE, PAGE_SIZE));
        req.setAttribute("page", page);
        req.setAttribute("totalPages", totalPages);
        req.getRequestDispatcher("/WEB-INF/views/books.jsp").forward(req, resp);
    }

    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        req.setCharacterEncoding("UTF-8");
        try {
            if (req.getServletPath().equals("/books/delete")) {
                dao.delete(Long.parseLong(req.getParameter("id")));
            } else {
                String title = req.getParameter("title");
                String author = req.getParameter("author");
                int year = Integer.parseInt(req.getParameter("year"));
                if (title == null || title.isBlank() || year < 1450 || year > 2100) {
                    resp.sendError(400, "Invalid book");
                    return;
                }
                String id = req.getParameter("id");
                if (id == null || id.isBlank()) {
                    dao.insert(new Book(0, title.trim(), author.trim(), year));
                } else {
                    dao.update(new Book(Long.parseLong(id), title.trim(), author.trim(), year));
                }
            }
            resp.sendRedirect(req.getContextPath() + "/books");                // Post/Redirect/Get
        } catch (SQLException e) {
            throw new ServletException(e);
        } catch (NumberFormatException e) {
            resp.sendError(400, "Invalid number");
        }
    }

    private static int parse(String text, int fallback) {
        try { return Integer.parseInt(text); } catch (NumberFormatException e) { return fallback; }
    }
}

Three habits make this code safe and pleasant: it validates on the server even if the form has validation too, it clamps the page number so ?page=999 shows the last page instead of an empty table, and it redirects after every POST so refreshing the browser does not repeat the change.

The list view uses JSTL to draw rows and the page links (see JSTL and Expression Language). <c:out> escapes the data, which prevents cross-site scripting:

XML
<table>
  <c:forEach var="b" items="${books}">
    <tr>
      <td><c:out value="${b.title}"/></td>
      <td><c:out value="${b.author}"/></td>
      <td>${b.year}</td>
      <td>
        <a href="books?action=edit&id=${b.id}">Edit</a>
        <form method="post" action="books/delete" style="display:inline">
          <input type="hidden" name="id" value="${b.id}">
          <button>Delete</button>
        </form>
      </td>
    </tr>
  </c:forEach>
</table>
<c:forEach begin="0" end="${totalPages - 1}" var="i">
  <a href="books?page=${i}">${i + 1}</a>
</c:forEach>

Run it: a CRUD app on HttpServer and H2

The program below is the same application without a servlet container. BookDao is real JDBC against an in-memory H2 database. The HTTP handler plays the role of the servlet: it routes by path and method, pages the list, and redirects after POST. A client then creates, lists, edits and deletes books over real HTTP.

Java A CRUD application with a JDBC DAO and paging over HTTP

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.

Downloading a file: Content-Disposition

A browser decides whether to show a response or save it from the Content-Type and the Content-Disposition header. For a CSV export you want a file save, with a helpful name:

Java
@WebServlet("/books/export")
public class ExportServlet extends HttpServlet {
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws IOException {
        resp.setContentType("text/csv");
        resp.setCharacterEncoding("UTF-8");
        resp.setHeader("Content-Disposition", "attachment; filename=\"books.csv\"");
        try (PrintWriter out = resp.getWriter()) {
            out.println("id,title,author,year");
            for (Book b : dao.findAll()) {                       // stream rows, do not build one huge string
                out.printf("%d,%s,%s,%d%n", b.id(), csv(b.title()), csv(b.author()), b.year());
            }
        }
    }

    private static String csv(String value) {
        return "\"" + value.replace("\"", "\"\"") + "\"";        // quote, and double embedded quotes
    }
}

attachment forces a save dialog; inline asks the browser to display the content if it can (PDFs and images). For non-ASCII file names, add the RFC 6266 form filename*=UTF-8''... with percent-encoding. To send an existing file of any size, copy it in chunks with Files.copy(path, resp.getOutputStream()) and set Content-Length first. Never build the path from a request parameter without checking it stays inside the download folder, as explained in Spring MVC: File Upload and Pagination.

Also remember CSV injection: a cell that starts with =, +, - or @ can run as a formula when someone opens the file in a spreadsheet. If the data is user supplied, prefix those cells with a single quote or a space.

Java A CSV download with Content-Disposition and safe quoting

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 first row of the body is the header, the quote inside book 2's title is doubled, and book 3's malicious formula is defused with a leading quote.

Common mistakes

  • Deleting with GET. Use POST (and a CSRF token in a real site, see Web Security Basics).
  • No redirect after POST. A page refresh repeats the insert and creates duplicates.
  • Trusting form validation. Browsers validate for convenience; the server must validate for safety.
  • Counting rows with list.size(). Fetching all rows to count them kills performance. Use COUNT(*).
  • Unstable paging. Always sort by a column with a unique tie-breaker.
  • Hard-coded connections in the servlet. Use a DataSource with connection pooling.
  • Missing character encoding. Call req.setCharacterEncoding("UTF-8") before reading parameters in a POST.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does the servlet answer a successful POST with a redirect instead of rendering the list directly?

    This is Post/Redirect/Get. After the redirect the browser shows a normal GET page, so a refresh just reloads the list.
  2. 2. Which header makes a browser save a response as a file named books.csv instead of showing it?

    Content-Disposition with the attachment type asks the browser to download. The filename parameter suggests the saved name.
  3. 3. A request arrives with ?page=999 but there are only 5 pages. What should the servlet do?

    A page outside the range returns no rows. Clamping to the last page (or a 404) gives users a useful response.
  4. 4. Why is deleting a record done with a POST form rather than a link to a GET URL?

    GET must be safe, meaning it must not change data. A state-changing action belongs behind POST, with CSRF protection in a real application.
  5. 5. What protects the CSV file from "formula injection" when opened in a spreadsheet?

    Spreadsheets evaluate cells that start with those characters as formulas. A leading quote or space turns them into plain text.

Summary

  • A CRUD app has list, add, edit and delete screens. One servlet routes them, a DAO runs the SQL, and JSP views show the data.
  • Use PreparedStatement in the DAO, validate on the server, and escape output with <c:out>.
  • Delete and update with POST and finish every POST with a redirect (Post/Redirect/Get).
  • Paging needs COUNT(*), LIMIT/OFFSET, a stable ORDER BY with a tie-breaker, and a clamped page number.
  • A download sets Content-Type and Content-Disposition: attachment; filename="...", streams rows, quotes CSV values and guards against formula injection.
  • The runnable versions use HttpServer, H2 and a real HTTP client, so you can see statuses, redirects and headers.

Next: Struts overview shows the framework many CRUD apps like this one were built with before Spring MVC took over.