Display Multiple Records from Database in JSP
This example about, How to Display Multiple Records from Database in JSP, Servlet and JDBC
Project Structure
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
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>
Output
Download Source Code
References
1. Servlet API JavaDoc
2. JDBC API JavaDoc
Hello, folks, I am a founder of idineshkrishnan.com. I love open source technologies, If you find my tutorials are useful, please consider making donations to these charities.
No Responses