How to Display Multiple Records from Database in JSP

by Dinesh Krishnan

on December 3, 2016

Post Views: 3,240

Display Multiple Records from Database in JSP

This example about, How to Display Multiple Records from Database in JSP, Servlet and JDBC

Project Structure

display multiple records from database in jsp

Table Information

Table Query

CREATE TABLE student_details(firstName varchar(30), lastName varchar(30), age int, emailId varchar(50), contactNumber varchar(12), address varchar(100));

INSERT INTO student_details VALUES(‘Dinesh’, ‘Krishnan’, 25, ‘dinesh@idineshkrishnan.com’, ‘9941937705’, ‘India’);
INSERT INTO student_details VALUES(‘Johanthan’, ‘James’, 26, ‘john@idineshkrishnan.com’, ‘8989898989’, ‘US’);
INSERT INTO student_details VALUES(‘Will’, ‘Smith’, 26, ‘smith@idineshkrishnan.com’, ‘8989898989’, ‘US’);
INSERT INTO student_details VALUES(‘Ram’, ‘Ganesh’, 26, ‘ram@idineshkrishnan.com’, ‘7338792929’, ‘India’);
INSERT INTO student_details VALUES(‘David’, ‘John’, 26, ‘david@idineshkrishnan.com’, ‘8787878787’, ‘UK’);

Table Structure

display multiple records from database in jsp

Application

DisplayRecordsController.java

package com.dineshkrish.controller;
import java.io.IOException;
import java.util.List;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.dineshkrish.dao.StudentDAO;
import com.dineshkrish.dto.Student;
/**
* 
* @author Dinesh Krishnan
*
*/
public class DisplayRecordsController extends HttpServlet {
@Override
public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
StudentDAO dao = new StudentDAO();
// Getting Student List from Database
List<Student> studentList = dao.getStudentList();
RequestDispatcher dispatcher = req.getRequestDispatcher("studentList.jsp");
req.setAttribute("studentList", studentList);
dispatcher.forward(req, resp);
}
}

Student.java

<pre class="prettyprint" >package com.dineshkrish.dto;
/**
* 
* @author Dinesh Krishnan
*
*/
public class Student {
private String firstName;
private String secondName;
private int age;
private String emailId;
private String contactNumber;
private String address;
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getSecondName() {
return secondName;
}
public void setSecondName(String secondName) {
this.secondName = secondName;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public String getEmailId() {
return emailId;
}
public void setEmailId(String emailId) {
this.emailId = emailId;
}
public String getContactNumber() {
return contactNumber;
}
public void setContactNumber(String contactNumber) {
this.contactNumber = contactNumber;
}
public String getAddress() {
return address;
}
public void setAddress(String address) {
this.address = address;
}
}
</pre>

StudentDAO.java

package com.dineshkrish.dao;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import com.dineshkrish.dto.Student;
/**
* 
* @author Dinesh Krishnan
*
*/
public class StudentDAO {
public List<Student> getStudentList() {
List<Student> studentList = new ArrayList<Student>();
Connection connection = null;
try {
// Loading Driver Class
Class.forName("com.mysql.jdbc.Driver");
// Getting the Connection
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/student", "root", "root");
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT * FROM student_details");
while (rs.next()) {
// Defining Student Object
Student student = new Student();
student.setFirstName(rs.getString("firstName"));
student.setSecondName(rs.getString("lastName"));
student.setAge(rs.getInt("age"));
student.setEmailId(rs.getString("emailId"));
student.setContactNumber(rs.getString("contactNumber"));
student.setAddress(rs.getString("address"));
// Adding the Student Object to List
studentList.add(student);
}
// Closing the Resources
rs.close();
statement.close();
connection.close();
} catch (SQLException e) {
System.out.println(e.getMessage());
e.printStackTrace();
} catch (ClassNotFoundException e) {
System.out.println(e.getMessage());
e.printStackTrace();
}
return studentList;
}
}

index.jsp

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!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=ISO-8859-1">
<title>Student Application</title>
</head>
<body>
<h1>Simple Student Management System</h1>
<a href="getStudentList">Click here to get All Student List</a>
</body>
</html>

studentList.jsp

<%@ page language="java" import="java.util.*, com.dineshkrish.dto.*"
contentType="text/html; charset=ISO-8859-1" pageEncoding="ISO-8859-1"%>
<!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=ISO-8859-1">
<title>Student List</title>
</head>
<body>
<h1>Student List</h1>
<%! List<Student> studentList; %>
<table cellpadding="5" cellspacing="5" border="1">
<tr>
<th>S.no</th>
<th>First Name</th>
<th>Last Name</th>
<th>Age</th>
<th>Email ID</th>
<th>Contact Number</th>
<th>Address</th>
</tr>
<%
studentList = (List<Student>)request.getAttribute("studentList");
if(studentList != null && !studentList.isEmpty()) {
for(int i=0;i<studentList.size();i++) {		
%>
<tr>
<td><%=(i+1) %></td>
<td><%=studentList.get(i).getFirstName() %></td>
<td><%=studentList.get(i).getSecondName() %></td>
<td><%=studentList.get(i).getAge() %></td>
<td><%=studentList.get(i).getEmailId() %></td>
<td><%=studentList.get(i).getContactNumber() %></td>
<td><%=studentList.get(i).getAddress() %></td>
</tr>
<%
}
} else {
%>
<tr>
<td colspan="9" align="center">No Records Available</td>
</tr>
<%
}
%>
</table>
<br>
<br>
<a href="index.jsp">Back to Home..</a>
</body>
</html>

web.xml

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
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"
id="WebApp_ID" version="3.1">
<display-name>DisplayRecord</display-name>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>DisplayRecord</servlet-name>
<servlet-class>com.dineshkrish.controller.DisplayRecordsController</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>DisplayRecord</servlet-name>
<url-pattern>/getStudentList</url-pattern>
</servlet-mapping>
</web-app>