JSP Tags for SQL to connect to a database
By: Manoj Kumar
The JSTL, includes a set of JSP tags that allow you to access a database via SQL directly from your JSP pages. The obvious advantage of the JSP tags for SQL is simplicity. It’s easy to query the database and to throw the results up on a web page. This is great for simple applications that need only to display database data on a web page and make simple database table updates—JSP tags for SQL work well. The <sql:query> tag executes an SQL query and returns a result set object that you can iterate over and display with other JSTL tags. You can also perform database table updates. The <sql:update> tag executes an SQL update.
For larger projects, there are a couple of disadvantages to keep in mind. One disadvantage is that you must embed SQL queries with table and field names into your JSP pages. If database table and field names change, you’ll have to make the corresponding changes in your JSP files. For a small project, this might not be a big problem, but larger projects should consider other data access options. Other data access options, such as O/R frameworks and EJB, can provide some level of indirection so that changes to the database schema do not have as much of an effect on your application code.
Another disadvantage is updates. The JSP tags for SQL allow you to perform updates on a database, but you have to build the SQL update string yourself. Again, for small projects with simple updating needs, this might not be a problem, but larger projects should consider other data access options. Other data access options can provide infrastructure to make updates much easier to program.
JavaServer Pages Tags for SQL Example
Listing below is an example of JSP tags for SQL. This example illustrates how to execute a query, iterate through the results, and display the results in an HTML table.
jstl-example.jsp
<%@ page language="java" %><%@ taglib uri="/WEB-INF/c.tld" prefix="c"%>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql"%>
<!DOCTYPE HTML PUBLIC "-//w3c//dtd html 4.0 transitional//en">
<html><head><title>jstl-example</title></head>
<body bgcolor="#ffffff">
<sql:query var="items"
dataSource="jdbc:hsqldb:hsql://localhost,org.hsqldb.jdbcDriver,sa">
SELECT TITLE,TIME FROM ITEM ORDER BY TIME
</sql:query>
<h1>News Items</h1>
<table border="1">
<th>Title</th><th>Time</th>
<c:forEach var="row" items="${items.rows}">
<tr>
<td><c:out value="${row.TITLE}"/></td>
<td><c:out value="${row.TIME}"/></td>
</tr>
</c:forEach>
</table>
</body>
</html>
Let’s examine the code more closely. At the top of the page, you see the two taglib directives:
<%@ taglib uri="/WEB-INF/c.tld"
prefix="c"%>
<%@ taglib uri="/WEB-INF/sql.tld" prefix="sql"%>
The first directive declares that this example uses JSTL Core tags and the second directive declares use of the JSTL SQL tags. The corresponding TLD files, c.tld and sql.tld, must be in the example web application’s WEB-INF directory.
The next interesting block of code is the actual query itself, shown here, and expressed using the JSTL <sql:query> tag:
<sql:query var="items" dataSource="jdbc:hsqldb:hsql://localhost,org.hsqldb.jdbcDriver,sa">
SELECT TITLE,TIME FROM ITEM ORDER BY TIME
</sql:query>
The tag has two attributes: var and dataSource. The var attribute specifies the name of an object (of type javax.servlet.jsp.jstl.sql.Result) that will be created by the query to hold the results of the query. The dataSource attribute specifies the database connection string. This is a comma separated string with the format connection URL, the JDBC driver class name, the username, and password. In the example, the password is an empty string and can be omitted from the connection string. The <sql:query> tag will use these parameters to obtain a connection from the JDBC driver manager. Within the body of the <sql:query> tag is the SQL query string to be executed: SELECT TITLE,TIME FROM ITEM ORDER BY TIME.
After the query has been executed and the results are available in the items object, you display the HTML table by using the JSTL <c:forEach> tag to iterate through the rows that are contained in the items object:
<table border="1">
<th>Title</th><th>Time</th>
<c:forEach var="row" items="${items.rows}">
<tr>
<td><c:out value="${row.TITLE}"/></td>
<td><c:out value="${row.TIME}"/></td>
</tr>
</c:forEach>
</table>
For each row of data, you use the <c:out> tag to display each column of data. Listing above gives a brief example of how to use a few of the tags. If you want to learn more about the JSTL, you can find more information at Sun’s JSTL web page (http://java.sun.com/products/jsp/jstl/). The Sun Web Services tutorial (http://java.sun.com/webservices/docs/1.0/tutorial/index.html) also covers JSTL and includes a section on the JSTL SQL tags.