How to list records in a database table using JSP and JSTL

Details: Written by Nam Ha Minh; Last Updated on 05 November 2023 | Print Email

In this tutorial you will learn how easy it is to query a list of records in MySQL database and display them in a web page using JSP and JSTL. You need the following:

- Installed software:

- - JDK 7.0 or later (download).
  - Apache Tomcat v7.0 or later (download).
  - MySQL 5.5 or later (download community version).

- Required libraries:

- - MySQL JDBC driver library: MySQL connector/J (download).
  - JSTL libraries (download).

After extracting the downloaded archives, put the following jar files under the WEB-INF\lib directory:

- javax.servlet.jsp.jstl-1.2.1.jar
- javax.servlet.jsp.jstl-api-1.2.1.jar
- mysql-connector-java-5.1.25-bin.jar

1. Creating the database

Supposing we have a table named users in a MySQL database called mydb with the following fields:

Execute the following script to create the database, the table and insert some sample data:

Create the database:
```
create database mydb; 
```

Create the table:

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `profession` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;

Insert some sample records:

INSERT INTO users (name, email, profession) values ('Peter', 'peter@gmail.com', 'Programmer');
INSERT INTO users (name, email, profession) values ('Tom', 'tom@hotmail.com', 'Developer');
INSERT INTO users (name, email, profession) values ('Sam', 'sam@yahoo.com', 'Consultant');
INSERT INTO users (name, email, profession) values ('David', 'david@gmail.com', 'Designer');

2. Writing code to connect to the database

Use the <sql:setDataSource> tag to create a data source to our database like this:

<sql:setDataSource
	var="myDS"
	driver="com.mysql.jdbc.Driver"
	url="jdbc:mysql://localhost:3306/mydb"
	user="root" password="secret"
/>

Remember to change the user and password attributes according to your MySQL settings. Note that the data source is assigned to a variable called myDS for reference later.

3. Writing code to query the records

Use the <sql:query> tag to create a query to the database as follows:

<sql:query var="list_users" dataSource="${myDS}">
	SELECT * FROM users;
</sql:query>

Note that the dataSource attribute refers to the data source myDS created in the previous step, and result of this query is assigned to a variable called listUsers for reference later.

4. Writing code to display the records

Use the <c:forEach> tag to iterate over the records returned by the <sql:query> tag. And for each record, use the <c:out> tag to print value of a column in the table, like this:

<c:forEach var="user" items="${listUsers.rows}">

	<c:out value="${user.name}" />

	<c:out value="${user.email}" />

	<c:out value="${user.profession}" />

</c:forEach>

Note that the items attribute of the <c:forEach> tag refers to the listUsers variable assigned by the <sql:query> tag.

5. The complete JSP code

Now we wire the above pieces together to form a complete JSP page with taglib directives to import JSTL tags and HTML code to display the users list in tabular format. Code of the complete JSP page is as follows (ListUsers.jsp):

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>    
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" 
	"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>JSP List Users Records</title>
</head>
<body>
	<sql:setDataSource
		var="myDS"
		driver="com.mysql.jdbc.Driver"
		url="jdbc:mysql://localhost:3306/mydb"
		user="root" password="secret"
	/>
	
	<sql:query var="listUsers"	dataSource="${myDS}">
		SELECT * FROM users;
	</sql:query>
	
	<div align="center">
		<table border="1" cellpadding="5">
			<caption><h2>List of users</h2></caption>
			<tr>
				<th>ID</th>
				<th>Name</th>
				<th>Email</th>
				<th>Profession</th>
			</tr>
			<c:forEach var="user" items="${listUsers.rows}">
				<tr>
					<td><c:out value="${user.id}" /></td>
					<td><c:out value="${user.name}" /></td>
					<td><c:out value="${user.email}" /></td>
					<td><c:out value="${user.profession}" /></td>
				</tr>
			</c:forEach>
		</table>
	</div>
</body>
</html>

6. Testing the application

Supposing we put the ListUsers.jsp file inside the web application called JSPListRecords on localhost Tomcat, type the following URL to run the list users JSP page:

http://localhost:8080/JSPListRecords/ListUsers.jsp

The following page should appear:

I recommend you take this course to learn how to use Java Servlet, JSP, Hibernate framework to build a fully functional eCommerce Website (with PayPal and credit card payment).

About the Author:

Nam Ha Minh is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.

Attachments:
JSPListRecords.war	[Deployable WAR file (Remember to update user and password according to your database)]	1176 kB
JSPSListRecords.zip	[Eclipse project]	1180 kB