Understanding the mechanics of JSTL's SQL tag

Data and Databases
1

I’m trying to figure out how JSTL’s SQL tag works under the hood. I’ve been using it to run database queries in my JSP, but I’m curious about the internal processes.

Here are my main concerns:

  1. Does the tag directly interact with the ResultSet, or does it store the query results in memory?
  2. When does the database connection close?

I’m using code like this:

<%@ taglib prefix="db" uri="http://java.sun.com/jsp/jstl/sql" %>

<db:execute var="result" dataSource="${source}" sql="${selectStatement}"></db:execute>
<c:loop var="item" items="${result.data}" start="0">
    ${item.info}
    ${item.extra_info}
</c:loop>

I know it’s not ideal to run queries in JSPs, but my result set is too big to keep in memory between the action and the JSP. This tag seems like the easiest way to handle it. Any insights would be helpful!

2

hey leo, the sql tag’s pretty smart with memory. it uses resultset to fetch data bit by bit, not all at once. connections usually go back to a pool after use, not closed right away. have u thought about moving db stuff to servlets? might be cleaner. whats ur experience with performance so far?

3

hey there! im curious, have u considered using pagination for ur large dataset? it could help manage memory better. also, how do u handle connection pooling? ive been wondering about the efficiency of sql tags in jsps. what’s ur experience been like so far? any performance issues youve noticed?

4

The JSTL SQL tag doesn’t store the entire result set in memory. It uses a ResultSet object to fetch data iteratively, which is efficient for large datasets. The db:execute tag stores the result in a scoped variable, allowing you to process data in chunks.

As for connection closure, it’s managed by the DataSource object. Typically, connections are pooled and returned to the pool after query execution rather than being immediately closed, optimizing resource usage.

While convenient, using SQL tags in JSPs isn’t ideal for separating concerns. Consider implementing pagination or moving database logic to a servlet for better architecture to improve performance and maintainability.