Java(TM) Boutique - Accessing a Database with the JSTL 1.1 SQL Tag Library

Tutorials : Accessing a Database with the JSTL 1.1 SQL Tag Library :

Updating a Database Table

Follow these steps to update a database table row:

As in the previous section, you need to add the taglib directives for the JSTL SQL tag library and the JSTL Core tag library.

Set the datasource for the JSP page with the setDataSource tag.
Add a sql:update tag to run a UPDATE SQL statement. Add the UPDATE SQL statement in the sql attribute of the sql:update tag.
```
              <sql:update  sql="UPDATE CATALOG SET Title=?,  Author=? 
                 WHERE CatalogId=?">

               </sql:update>
```
The SQL statement has placeholders for specifying IN parameters for the UPDATE statement.

Specify the values for the IN parameters in the SQL statementwith the sql:param tags. Title, Author, and CatalogId are IN parameters in the UPDATE statement. Set a parameter value with the value attribute in the sql:param tag.
```
<sql:param value="Hibernate:The Definitive Guide"/>
<sql:param value="Elliott, James"/>
<sql:param value="catalog2"/>
```
The catalogUpdate.jsp is listed in Listing 2.

To run catalogUpdate.jsp, copy it to the <weblogic91>\samples\server\examples\build\mainWebApp directory and access it with the URL http://localhost:7001/catalogUpdate.jsp. Now the table is updated.

Querying a Database Table

Now you'll see how to query the database table and display the results in an HTML table.

Create a JSP, catalogQuey.jsp.

As in the previous sections, add taglib directives for the JSTL, SQL, and JSTL Core tag libraries.

Also add a setDataSource tag for obtaining a connection with the MySQL database.
Add a sql:query tag to query the database.

In the sql:query tag specify the var attribute, which is a required attribute. The var attribute is returned by the Query tag. Specify the SQL query in the sql attribute.
This adds the sql:query tag to the JSP page.
```
<sql:query   var="catalog" sql="SELECT * FROM CATALOG">
</sql:query>
```

Add a <table> to display the result set of the Query tag. Add the table headers. A row shall be added corresponding to each row in the result set. Add the JSTL 1.1 Core tag c:forEach to the <table> element.The var attribute of the c:forEach tag specifies the variable corresponding to a row in the result set. The items attribute specifies the EL binding to iterate over the rows in the result set returned by the Query tag. The c:forEach tag in the catalogQuery.jsp JSP is:
```
<c:forEach var="journal" items="${catalog.rows}">

</c:forEach>
```

Output the values corresponding to each column in a row to the JSP table. The JSP table values are added with the c:out tag. Add a c:out tag for each column. Specify the EL expression to bind a column value in the value attribute. The journal is the variable corresponding to a row in the result set. You obtain the column values binding with the column names in the result set. For example, the EL expression to bind the CatalogId column is ${journal.CatalogId}.
```
<tr>
<td><c:out value="${journal.CatalogId}"/></td>
</tr>
```

Similarly add c:out tags for the other columns in the result set. The catalogQuery.jsp is shown in Listing 3.

To run catalogQuery.jsp, copy it to the C:\BEA\weblogic91\samples\server\examples\build\mainWebApp directory and access it with the URL http://localhost:7001/catalogQuery.jsp. Now, the result set you obtained with the Query tag is displayed in a HTML table.

Good for Simple Applications

JSTL's SQL tags are suited for prototyping and simple applications. For complex applications, the Model-View-Controller pattern is recommended.

Home / Articles / Accessing a Database with the JSTL 1.1 SQL Tag Library / 1 / 2 / 3 /

Applet Index
(sorted alphabetically)
A B C D E F G H I J K
L M N O P Q R S T U
V W X Y Z #s

The Java Source
(applets w/source code)
A B C D E F G H I J K
L M N O P Q R S T U
V W X Y Z

How to Add Java Applets to Your Site

New on the Java Boutique:

New Review:

Time Management Made Easy with the Quartz Enterprise Job Scheduler
Why not just use the Java timer API? This open source scheduling API boasts simplicity, ease-of-integration, a well-rounded feature set, and it's free!

New Applet:

Reverse Complement
Reverse Complement is a simple applet that converts DNA or RNA sequences into three useful formats.