SQL query tag in JSTL

last modified July 6, 2020

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

JSTL

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.

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.