Monday, October 16, 2006

Creating a simple JSTL dynamic query?

Ok, maybe this seems to be very simple, but when anyone tries to make a query using JSTL figures out the following:

"As a rule, you should avoid using SELECT * FROM except for testing or debugging purposes, unless you really do need every column from the table. Performance will be enhanced if you request only those fields you actually intend to use. Additionally, SELECT * offers no control over the order of the returned fields because they're returned in the order in which they were declared in the CREATE TABLE statement."

The solution to this problem is following:

<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core_rt" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jstl/sql_rt" %>

<sql:setDataSource var="datasource"
driver="oracle.jdbc.driver.OracleDriver"
url="jdbc:oracle:thin:@192.168.1.11:1521:dacorp"
user="scott" password="tiger"/>
<sql:query var="emp" dataSource="${datasource}">
select * from VIEW_PERSONAL_EMS
</sql:query>
<html>
<head>
<title>Simple test</title
</head>
<body>
<br>
<br>
<table border="1" >
<%-- Get the column names for the header of the table --%>
<c:forEach var="columnName" items="${emp.columnNames}">
<th><c:out value="${columnName}"/></th>
</c:forEach>
<%-- Get the value of each column while iterating over rows --%>
<c:forEach var="row" items="${emp.rowsByIndex}">
<tr>
<c:forEach var="column" items="${row}">
<td><c:out value="${column}"/></td>
</c:forEach>
</tr>
</c:forEach>
</table>
</body>
</html>


This snippet will guide to some other who has this problem. When using this snippet, you'll see that the fetching of the results will be such as one desires.

I wish this could be helpful...

mikevegap@gmail.com

No comments: