create database mydb;
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 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');
<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.
<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.
<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.
<%@ 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>
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).