Editable Table / SQL Tag

This page is designed to demonstrate how you can use the display tag to create editable rows of data. This particular example uses JSTL's <sql:query> tag to select, insert, update and delete rows from an actual database.

Sorting is disabled on the column headings. The reason? There's an issue with sorting when your <display-el:column> tag has a body. I didn't feel like digging in and fixing it. Sorry. ;-)

Motivation for this exercise was gained from this implementation, which is a patch that never made it into the old display tag (version 0.8.5).

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

<%-- Create temporary table --%> DROP TABLE IF EXISTS temp_user CREATE TABLE temp_user ( id BIGINT not null AUTO_INCREMENT, username VARCHAR(50), firstName VARCHAR(100), lastName VARCHAR(100), primary key (id) ) INSERT INTO temp_user (username, firstName, lastName) VALUES ('mraible', 'Matt', 'Raible') INSERT INTO temp_user (username, firstName, lastName) VALUES ('tomcat', 'Tomcat', 'User') <%-- This is a hack - if you use an auto-increment on your table, you won't have to worry about this kind of logic. --%> <% // generate a random number Random generator = new Random(); String id = String.valueOf(generator.nextInt()); pageContext.setAttribute("id", id); %> insert into temp_user (id) values (?) update temp_user set username=?, firstName=?, lastName=? where id=?

updated successfully!

delete from temp_user where id=?

Delete succeeded!

select id, username, firstName, lastName from temp_user order by upper(username);

View JSP Source

NOTE: The table (temp_user) for this list will be dropped and re-created whenever this webapp is restarted.

<c:if test="${param.method == 'Add'}">
    <%-- This is a hack - if you use an auto-increment on your table,
         you won't have to worry about this kind of logic. --%>
    <%
    // generate a random number
    Random generator = new Random();
    String id = String.valueOf(generator.nextInt());
    pageContext.setAttribute("id", id);
    %>
    <sql:update dataSource="jdbc/appfuse">
        insert into temp_user (id) values (?)
        <sql:param value="${id}"/>
    </sql:update>
    <c:redirect url="users-edit-sql.jsp">
        <c:param name="id" value="${id}"/>
        <c:param name="method" value="Edit"/>
    </c:redirect>
</c:if>

<c:if test="${param.method == 'Save'}">
    <sql:update dataSource="jdbc/appfuse">
        update temp_user set username=?, firstName=?, lastName=? where id=?
        <sql:param value="${param.username}"/>
        <sql:param value="${param.firstName}"/>
        <sql:param value="${param.lastName}"/>
        <sql:param value="${param.id}"/>
    </sql:update>
    <div class="message">
        <b><c:out value="${param.firstName} ${param.lastName}"/></b> updated successfully!
    </div>
</c:if>

<c:if test="${param.method == 'Delete'}">
    <sql:update dataSource="jdbc/appfuse">
        delete from temp_user where id=?
        <sql:param value="${param.id}"/>
    </sql:update>
    <div class="message">Delete succeeded!</div>
</c:if>

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

<c:set var="checkAll">
    <input type="checkbox" name="allbox" onclick="checkAll(this.form)" style="margin: 0 0 0 4px" />
</c:set>
<form name="editForm" action="users-edit-sql.jsp">
<c:if test="${not empty param.method}">
    <input type="button" onclick="location.href='users-edit-sql.jsp'" class="button"
            value="Cancel" />
</c:if>
<c:if test="${param.method == 'Edit'}">
    <input type="submit" name="method" value="Save" class="button" />
</c:if>
<input type="submit" name="method" value="Edit" class="button"/>
<input type="button" name="method" value="Add" class="button" onclick="location.href='?method=Add'" />
<input type="submit" name="method" value="Delete" class="button" />
<br /><br />
<display-el:table name="${users.rows}" id="user" class="list">
  <display-el:column style="width: 5%" title="${checkAll}">
    <input type="checkbox" name="id" value="<c:out value="${user.id}"/>"
    <c:if test="${param.id == user.id and param.method != 'Save'}">checked="checked"</c:if>
        style="margin: 0 0 0 4px" onclick="radio(this)" />
  </display-el:column>
  <display-el:column title="Username">
    <c:choose>
        <c:when test="${param.method == 'Edit' and param.id == user.id}">
            <input type="text" name="username" style="padding: 0"
                value="<c:out value="${user.username}"/>" />
        </c:when>
        <c:otherwise><c:out value="${user.username}"/></c:otherwise>
    </c:choose>
  </display-el:column>
  <display-el:column title="First Name">
    <c:choose>
        <c:when test="${param.method == 'Edit' and param.id == user.id}">
            <input type="text" name="firstName" style="padding: 0"
                value="<c:out value="${user.firstName}"/>" />
        </c:when>
        <c:otherwise><c:out value="${user.firstName}"/></c:otherwise>
    </c:choose>
  </display-el:column>
  <display-el:column title="Last Name">
      <c:choose>
        <c:when test="${param.method == 'Edit' and param.id == user.id}">
            <input type="text" name="lastName" style="padding: 0"
                value="<c:out value="${user.lastName}"/>" />
        </c:when>
        <c:otherwise><c:out value="${user.lastName}"/></c:otherwise>
    </c:choose>
  </display-el:column>
</display-el:table>
</form>