JSP Database JDBC, MySQL, JNDI Pooling

Database-free JSP Views

A JSP should not open JDBC connections or execute SQL. Database work belongs in a repository called by a service or controller; the JSP receives only the rows or view model needed to render the response. This keeps credentials, transactions, and failure handling out of the presentation layer.

Do Not Put JDBC in JSP

JSP files become fragile when they mix HTML, SQL, connection code, and error handling. Move SQL into DAO classes and call them from servlets or services.

JSP renders.
Servlet controls the request.
DAO owns SQL.
Service owns business rules.

Prepared Statements and Pools

Prepared statements protect SQL structure from user input. Connection pools avoid creating a new physical connection for every request.

Bind request values as parameters.
Use DataSource instead of DriverManager in JSP.
Close connections so they return to the pool.

Rendering Results Safely

Database values can still contain unsafe text. Escape output in JSP and paginate large result sets.

Use JSTL c:out or equivalent escaping.
Do not display raw SQL exceptions.
Redirect after successful writes.

Query and Render Flow

The controller obtains validated parameters, invokes a repository that uses a pooled DataSource and prepared statements, maps results to plain view data, then forwards. Every JDBC resource closes before rendering begins, so a slow client does not hold a database connection.

For writes, complete the transaction and redirect before rendering the next page. Use JSTL c:forEach and EL for rows, escape text, and paginate large results. A database failure should produce a controlled error view with a correlation ID rather than SQL text.

Servlet DAO JSP Flow

1. User submits search form.
2. Servlet validates filters.
3. DAO runs a prepared SELECT query.
4. Servlet stores results in request scope.
5. JSP renders escaped values in a table.

Keep Query Work in the DAO

The servlet coordinates the request, while the DAO owns JDBC resources and the JSP only renders the result.

Keep Query Work in the DAO

protected void doGet(HttpServletRequest req, HttpServletResponse res)
        throws ServletException, IOException {
    String status = req.getParameter("status");
    List<Order> orders = orderDao.findByStatus(status);
    req.setAttribute("orders", orders);
    req.getRequestDispatcher("/WEB-INF/orders.jsp").forward(req, res);
}

Output

The JSP receives a List<Order>; it never opens a database connection.

The DAO should use a pooled DataSource, prepared statements, and try-with-resources.

Before you move on