SQL query tag in JSTL

In this tutorial, we learn how to work with JSTL's SQL query tag.

The JavaServer Pages Standard Tag Library (JSTL) is a collection of useful JSP tags which provide core functionality common to many JSP files. <sql:query> tag executes an SQL SELECT statement and saves the result in a scoped variable.

Generally, it is not recommended to access a database from a JSP page. However, for simple applications and testing it can be useful. In our application, we are going to use the JSTL's SQL query tag to retrieve data from a MySQL database. The project is built with Maven. We deploy the application on Tomcat.

Creating a MySQL database

First, we create a testdb database and a Cars table in MySQL.

cars_mysql.sql
DROP TABLE IF EXISTS Cars;
CREATE TABLE Cars(Id INT PRIMARY KEY AUTO_INCREMENT, 
                  Name TEXT, Price INT) ENGINE=InnoDB;
                  
INSERT INTO Cars(Name, Price) VALUES('Audi', 52642);
INSERT INTO Cars(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO Cars(Name, Price) VALUES('Skoda', 9000);
INSERT INTO Cars(Name, Price) VALUES('Volvo', 29000);
INSERT INTO Cars(Name, Price) VALUES('Bentley', 350000);
INSERT INTO Cars(Name, Price) VALUES('Citroen', 21000);
INSERT INTO Cars(Name, Price) VALUES('Hummer', 41400);
INSERT INTO Cars(Name, Price) VALUES('Volkswagen', 21600);

This is the SQL to create the Cars table in MySQL.

To create the database and the table, we use the mysql monitor tool.

$ sudo service mysql start

MySQL is started with sudo service mysql start command.

$ mysql -u testuser -p 

We connect to the database with the mysql monitor.

mysql> CREATE DATABASE testdb;
Query OK, 1 row affected (0.02 sec)

The CREATE DATABASE statement creates a new database named testdb.

mysql> USE testdb;
mysql> SOURCE cars_mysql.sql

With the source command, we load and execute the cars_mysql.sql file.

mysql> SELECT * FROM Cars;
+----+------------+--------+
| Id | Name       | Price  |
+----+------------+--------+
|  1 | Audi       |  52642 |
|  2 | Mercedes   |  57127 |
|  3 | Skoda      |   9000 |
|  4 | Volvo      |  29000 |
|  5 | Bentley    | 350000 |
|  6 | Citroen    |  21000 |
|  7 | Hummer     |  41400 |
|  8 | Volkswagen |  21600 |
+----+------------+--------+
8 rows in set (0.00 sec)

We verify the data. Refer to MySQL tutorial to learn more about MySQL.

Initiating a project with Maven

Apache Maven is a software project management and comprehension tool.

$ mvn archetype:generate -DgroupId=com.zetcode -DartifactId=SqlQueryTag 
    -DarchetypeArtifactId=maven-archetype-webapp -DinteractiveMode=false

With the maven-archetype-webapp, we create a skeleton of a web application.

$ cd SqlQueryTag/
$ tree
.
├── pom.xml
└── src
    └── main
        ├── resources
        └── webapp
            ├── index.jsp
            └── WEB-INF
                └── web.xml

5 directories, 3 files

Maven created this project structure.

$ mkdir src/main/webapp/META-INF
$ touch src/main/webapp/META-INF/context.xml

We create a META-INF directory and the context.xml file.

Application

The application connects to the Cars table created previously and retrieves all its rows. To connect to the database table, we use the <sql:query> tag.

Maven Project Object Model (POM) file is an XML representation of a Maven project held in a file named pom.xml.

pom.xml
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>com.zetcode</groupId>
  <artifactId>SqlQueryTag</artifactId>
  <packaging>war</packaging>
  <version>1.0-SNAPSHOT</version>
  <name>SqlQueryTag Maven Webapp</name>
  <url>http://maven.apache.org</url>
  <dependencies>
    <dependency>
        <groupId>jstl</groupId>
        <artifactId>jstl</artifactId>
        <version>1.2</version>
    </dependency>    

    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.39</version>
    </dependency>        
            
  </dependencies>
  <build>
         
    <finalName>SqlQueryTag</finalName>
  </build>
</project>

In the pom.xml file, we declare dependencies for the MySQL driver and JSTL library.

context.xml
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/SqlQueryTag" >

    <Resource name="jdbc/testdb" 
              auth="Container"
              type="javax.sql.DataSource" 
              username="testuser" 
              password="test623"              
              driverClassName="com.mysql.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/testdb"
              maxActive="10" 
              maxIdle="4"/>
</Context>

The context.xml file is a Tomcat's configuration file for a web application. In the context.xml file, we define a data source. Refer to Datasource in Java tutorial to learn more about data sources.

web.xml
<?xml version="1.0" encoding="UTF-8"?>

<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"
         version="3.1">
    <session-config>
        <session-timeout>
            30
        </session-timeout>
    </session-config>
</web-app>

We provide a standard deployment descriptor. Note that Maven might create a deployment descriptor which is not compatible with your JSTL JARs.

index.jsp
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Cars</title>
    </head>

    <sql:query var="carsList" dataSource="jdbc/testdb">
        SELECT * FROM Cars
    </sql:query>    

    <body>
        <div align="center">
            <table border="1" cellpadding="2">
                <caption><h2>List of cars</h2></caption>
                <tr>
                    <th>Id</th>
                    <th>Name</th>
                    <th>Price</th>
                </tr>
                <c:forEach var="car" items="${carsList.rows}">
                    <tr>
                        <td><c:out value="${car.Id}" /></td>
                        <td><c:out value="${car.Name}" /></td>
                        <td><c:out value="${car.Price}" /></td>
                    </tr>
                </c:forEach>
            </table>
        </div>
    </body>
</html>

In the index.jsp file, we connect to the database, retrieve the data from the Cars table, and display it in a HTML table.

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

We need to declare the JSTL core and sql modules.

<sql:query var="carsList" dataSource="jdbc/testdb">
    SELECT * FROM Cars
</sql:query> 

With the <sql:query> tag, we execute the SELECT * FROM Cars statement. The data is stored in the carsList variable. The data source is specified with the dataSource parameter.

<c:forEach var="car" items="${carsList.rows}">
    <tr>
        <td><c:out value="${car.Id}" /></td>
        <td><c:out value="${car.Name}" /></td>
        <td><c:out value="${car.Price}" /></td>
    </tr>
</c:forEach>

The <c:forEach> tag goes through the carsList variable and the <c:out> outputs the current value.

Building and deploying

Now, we are going to build and deploy the application.

$ mvn package

We build the project.

$ mysql start/running, process 6030
$ $TOMCAT_HOME/bin/startup.sh

We start the MySQL and Tomcat.

$ cp target/SqlQueryTag.war $TOMCAT_HOME/webapps

We deploy the application.

Displaying cars
Figure: Displaying cars

We navigate to the application in a browser and get the data from the database.

This was the SQL query tag tutorial. We have built a web application using JSTL, JSP, MySQL, Tomcat, and Maven. You might also want to check the Validation filter tutorial, JDBI tutorial, MySQL tutorial, or Apache Derby tutorial.