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.
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
.
<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.
<?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.
<?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.
<%@ 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.
List all Java tutorials.