SQL Tags Example

This page is designed to show how easy it is to list data from a database using JSTL's SQL Tags. The following is the query that is used to expose a users variable to the pageContext.

<sql:query var="users" dataSource="jdbc/appfuse">
    select username, first_name as firstName, last_name as lastName
    from app_user order by upper(username);
</sql:query>

Using the "View Table Source" link below this table, you can see the JSP/HTML code that's used to render this table.

View Table Source

<table class="list">
<tr>
    <th><fmt:message key="userForm.username"/></th>
    <th><fmt:message key="userForm.firstName"/></th>
    <th><fmt:message key="userForm.lastName"/></th>
</tr>

<c:forEach var="row" items="${users.rows}" varStatus="status">
<c:choose>
<c:when test="${status.count % 2 == 0}"><tr class="even"></c:when>
<c:otherwise><tr class="odd"></c:otherwise>
</c:choose>
    <td><c:out value="${row.username}"/></td>
    <td><c:out value="${row.firstName}"/></td>
    <td><c:out value="${row.lastName}"/></td>
</tr>
</c:forEach>
</table>

So that's cool, right? But how about something even better?! The display tag now supports iterating this set of results. All you need to do is reference the ${users.row} in the name attribute when using the EL tag. Now you can render this same data set, but this time you get column sorting.

View Table Source

<display-el:table name="${users.rows}" id="user" class="list">
    <display-el:column property="username" sortable="true" titleKey="userForm.username"/>
    <display-el:column property="firstName" sortable="true" titleKey="userForm.firstName"/>
    <display-el:column property="lastName" sortable="true" titleKey="userForm.lastName"/>
</display-el:table>

Heck, it even supports displaying all columns returned. If you use <display-el:table name="${users.rows}" class="list"/> - it'll render what you see below.