| Let's look at one more SQL example. In every query that we've examined so far, we always knew exactly what columns were going to be returned. It is also possible to write your JSTL so that the results of a query will be displayed, regardless of what the names of the columns are. Our next example implements a general query form. Using this form, users can enter any SQL query that they like and see the results of the query displayed. Figure 7.8 shows this program executing a query. 
This program works in the same way as many of the other pages we've examined. It presents a form that posts back to the general.jsp page. Listing 7.8 shows the source code. Listing 7.8 A General Query (general.jsp)<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c" %> <%@ taglib uri="http://java.sun.com/jstl/core-rt" prefix="c-rt" %> <%@ taglib uri="http://java.sun.com/jstl/sql" prefix="sql" %> <sql:setDataSource var="dataSource" driver="org.gjt.mm.mysql.Driver" url="jdbc:mysql://localhost/forum?user=forumuser" /> <html> <head> <title>General Query</title> </head> <body> <c:choose> <c:when test="${param.cmd!=null}"> <c:set var="str" value="${param.cmd}" /> </c:when> <c:otherwise> <c:set var="str" value="select * from t_users" /> </c:otherwise> </c:choose> Please enter a query: <br /> <form method="post"> <textarea name="cmd" cols="40" rows="5"> <c:out value="${str}" /> </textarea> <br /> <input type="submit" /> </form> <c:if test="${pageContext.request.method=='POST'}"> <c:catch var="e"> <sql:query var="users" dataSource="${dataSource}" sql="${param.cmd}" /> <table border="1"> <c:forEach var="row" items="${users.rows}" varStatus="status"> <jsp:useBean type="javax.servlet.jsp.jstl.core.LoopTagStatus" /> <c-rt:if test="<%=status.getCount()==1%>"> <tr> <c:forEach var="col" items="${row}"> <th> <c:out value="${col.key}" /> </th> </c:forEach> </tr> </c-rt:if> <tr> <c:forEach var="col" items="${row}"> <td> <c:out value="${col.value}" /> </td> </c:forEach> </tr> </c:forEach> </table> </c:catch> <c:if test="${e!=null}"> <h3>Error</h3> <c:out value="${e}" /> </c:if> </c:if> </body> </html> Like our previous examples, this program first checks to see whether it is being called with a POST. If so, it prepares to execute the query that the user specified: <c:if test="${pageContext.request.method=='POST'}"> It is quite possible that the user might enter an invalid SQL command. To account for this, we use a <c:catch> tag that will catch these exceptions and allow us to handle them: <c:catch var="e"> We now execute a query based on what the user entered. For this query, we use a bodyless <sql:query> tag. For the sql attribute, we specify the variable param.cmd, which holds the SQL command entered by the user: <sql:query var="users" dataSource="${dataSource}" sql="${param.cmd}" /> The first thing that we must do is obtain the names of the columns. These names will be used as the column headers for the table columns when the results are displayed. To do this, we first begin iterating through the returned rows: <table border="1"> <c:forEach var="row" items="${users.rows}" varStatus="status"> <jsp:useBean type="javax.servlet.jsp.jstl.core.LoopTagStatus" /> We now use the row status to determine which row number we are on. If we are on the first row, we display the column heads. The column heads are displayed by iterating through the row variable returned from the query. Then, we simply display the key attribute in each column: <c-rt:if test="<%=status.getCount()==1%>"> <tr> <c:forEach var="col" items="${row}"> <th> <c:out value="${col.key}" /> </th> </c:forEach> </tr> </c-rt:if> The key attribute is not the only attribute available to iterate through in a row; we can also display all the values. For the other rows, we do that by using this code: <tr> <c:forEach var="col" items="${row}"> <td> <c:out value="${col.value}" /> </td> </c:forEach> As you can see, the returned rows are collections themselves. By iterating through these rows, we are able to gain specific information about the structure of the query that we requested. | 
Print 
E-Mail 
Add Note 
Add Bookmark
