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.

                  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.


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.

<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">
  <name>SqlQueryTag Maven Webapp</name>


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

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

    <Resource name="jdbc/testdb" 

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.

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

<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
         xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_3_1.xsd"

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

<%@ 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>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

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

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

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

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}">
        <td><c:out value="${car.Id}" /></td>
        <td><c:out value="${car.Name}" /></td>
        <td><c:out value="${car.Price}" /></td>

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.