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 id, first_name as firstName, last_name as lastName
    from app_user order by id limit 10; 
</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.

Id	First Name	Last Name
5	Dion	Almaer
6	Calvin	Austin
7	Doug	Bateman
8	Clinton	Begin
9	Cedric	Beust
10	Geert	Bevin
11	Naren	Chawla
12	Eugene	Ciurana
13	Doug	Clark
14	Dan	Diephouse

View Table Source

<table class="list">
<tr>
    <th><fmt:message key="userForm.id"/></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="true"><tr class="even"></c:when>
<c:otherwise><tr class="odd"></c:otherwise>
</c:choose>
    <td><c:out value=""/></td>
    <td><c:out value=""/></td>
    <td><c:out value=""/></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.rows} in the name attribute when using the EL tag. Now you can render this same data set, but this time you get column sorting.

Id	First Name	Last Name
5	Dion	Almaer
6	Calvin	Austin
7	Doug	Bateman
8	Clinton	Begin
9	Cedric	Beust
10	Geert	Bevin
11	Naren	Chawla
12	Eugene	Ciurana
13	Doug	Clark
14	Dan	Diephouse

View Table Source

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

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

FirstName	Id	LastName
Dion	5	Almaer
Calvin	6	Austin
Doug	7	Bateman
Clinton	8	Begin
Cedric	9	Beust
Geert	10	Bevin
Naren	11	Chawla
Eugene	12	Ciurana
Doug	13	Clark
Dan	14	Diephouse

Now that you're into it - checkout the other examples I put together: