Querying Data with the JSTL in JSP

By: Sathya Narayana in JSP Tutorials on 2010-10-24

To query data with the JSTL, follow these steps:

Create a new Web application folder called DataAccess inside Tomcat's webapps folder. Create a folder inside it named WEB-INF.
Next you need to give your Web application access to the JSTL by copying the JSTL lib folder into WEB-INF. Be sure to copy the entire lib folder, not just its contents.
You also need to make the MySQL JDBC classes available, which you can find at http://dev.mysql.com/downloads/connector/j/. Once you've downloaded the file, unzip it and copy mysql-connector-java-3.0.9-bin.jar into your webapps folder's WEB-INF/lib directory (the filename may be slightly different if a newer version has been released).

Create the following JSP page in the DataAccess folder as bookList.jsp:

<%@ 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="com.mysql.jdbc.Driver"
                    url="jdbc:mysql://localhost/publish"
                   user="publishuser" password="secret"/>

<sql:query var="books" dataSource="${datasource}">
  SELECT id, title, price FROM book
</sql:query>
<html>
  <head>
    <title>A First JSP Database</title
  </head>
  <body>
    <table border="1">
      <tr>
         <td>id</td><td>title</td><td>price</td>
      </tr>
<c:forEach items="${books.rows}" var="row">
      <tr>
        <td><c:out value="${row.id}" /></td>
        <td><c:out value="${row.title}" /></td>
        <td><c:out value="${row.price}" /></td>
      </tr>
</c:forEach>
   </table>
  </body>
</html>