Question

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.

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 .