How to get data from database to servlet jdbc JSP

Here we will get data from the database to servlet JDBC and show data on the JSP page using Jakarta Server Pages and the JSTL library.

Technology used

apache-tomcat-10.1.1
JavaSE-17
eclipse IDE for Enterprise Java and Web Developers

Steps to develop programs

Create MySql Table and insert data
Create A Dynamic Web Project
Create package structure
Create java files
create jsp files

1 Create MySQL Table

Create a MySql table and insert a few data that will be retrieved from the database using our servlet program.

The script for creating the table is as below. Insert data as per need.

create table student(
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT, 
  roll_no bigint(20), 
  name varchar(100), 
  course varchar(100), 
  session varchar(80), 
  semester varchar(80), 
  mobile_no varchar(30), 
  email_id varchar(100), 
  address varchar(200), 
  add_date timestamp DEFAULT CURRENT_TIMESTAMP, 
  primary key(id), 
  unique key(roll_no), 
  unique key(mobile_no), 
  unique key(email_id)
) ENGINE = InnoDB DEFAULT CHARSET = utf8;

create table student(

id bigint(20) unsigned NOT NULL AUTO_INCREMENT,

roll_no bigint(20),

name varchar(100),

course varchar(100),

session varchar(80),

semester varchar(80),

mobile_no varchar(30),

email_id varchar(100),

address varchar(200),

add_date timestamp DEFAULT CURRENT_TIMESTAMP,

primary key(id),

unique key(roll_no),

unique key(mobile_no),

unique key(email_id)

) ENGINE = InnoDB DEFAULT CHARSET = utf8;

Creating Java Dynamic Web Project

Open Eclipse IDE and follow the below steps

Click on File->New-> Dynamic Web Project.
Give project Name
Select Target runtime (here apache-tomcat-10.1.1)
then next again next then finish.

This will create a new project.

Creating packages and files

Create packages com.ebhor.controller, com.ebhor.dao and com.ebhor.model inside src/main/java
Create GetStudent.java servlet inside com.ebhor.controller
Create ConnectionFactory and StudentDAO.java inside com.ebhor.dao
Create Student.java inside com.ebhor.model
Create a index.jsp and students.jsp inside webapp folder.

Project Explorer

External Jar

The following External jars are included in the apache tomcat lib folder

mysql-connector-java-8.0.30
jakarta.servlet.jsp.jstl-3.0.0
jakarta.servlet.jsp.jstl-api-3.0.0

Servlet class to get data from DAO (GetStudents.java)

Create a Dao object and access fetchAll(). This will return a List of students.
Set the student list as an attribute in the request scope.
Send the request and response to students.jsp using RequestDispatcher.

package com.ebhor.controller;
import java.io.IOException;
import java.util.List;
import com.ebhor.dao.StudentDAO;
import com.ebhor.model.Student;
import jakarta.servlet.RequestDispatcher;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
@WebServlet("/show-students")
public class GetStudents extends HttpServlet {
	private static final long serialVersionUID = 1L;
	StudentDAO dao = new StudentDAO();
	protected void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		List<Student> studentList = dao.fetchAll();
		request.setAttribute("students", studentList);
		RequestDispatcher requestDispatcher = request.getRequestDispatcher("/students.jsp");
		requestDispatcher.forward(request, response);
	}
}

package com.ebhor.controller;

import java.io.IOException;

import java.util.List;

import com.ebhor.dao.StudentDAO;

import com.ebhor.model.Student;

import jakarta.servlet.RequestDispatcher;

import jakarta.servlet.ServletException;

import jakarta.servlet.annotation.WebServlet;

import jakarta.servlet.http.HttpServlet;

import jakarta.servlet.http.HttpServletRequest;

import jakarta.servlet.http.HttpServletResponse;

@WebServlet("/show-students")

public class GetStudents extends HttpServlet {

private static final long serialVersionUID = 1L;

StudentDAO dao = new StudentDAO();

protected void doGet(HttpServletRequest request, HttpServletResponse response)

throws ServletException, IOException {

List<Student> studentList = dao.fetchAll();

request.setAttribute("students", studentList);

RequestDispatcher requestDispatcher = request.getRequestDispatcher("/students.jsp");

requestDispatcher.forward(request, response);

}

Student Model class Student.java

Create a student model class to hold values of student objects and provide a getter, setter, constructor, and toString method.

package com.ebhor.model;

import java.io.Serializable;

public class Student implements Serializable {
	private static final long serialVersionUID = 7935940591376293207L;
	private long id;
	private long rollNo;
	private String name;
	private String course;
	private String session;
	private String semester;
	private String mailId;
	private String mobileNo;
	private String address;

	public Student() {

	}

	public Student(long id, long rollNo, String name, String course, String session, String semester, String mailId,
			String mobileNo, String address) {
		super();
		this.id = id;
		this.rollNo = rollNo;
		this.name = name;
		this.course = course;
		this.session = session;
		this.semester = semester;
		this.mailId = mailId;
		this.mobileNo = mobileNo;
		this.address = address;
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", rollNo=" + rollNo + ", name=" + name + ", course=" + course + ", session="
				+ session + ", semester=" + semester + ", mailId=" + mailId + ", mobileNo=" + mobileNo + ", address="
				+ address + "]";
	}

	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public long getRollNo() {
		return rollNo;
	}

	public void setRollNo(long rollNo) {
		this.rollNo = rollNo;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getCourse() {
		return course;
	}

	public void setCourse(String course) {
		this.course = course;
	}

	public String getSession() {
		return session;
	}

	public void setSession(String session) {
		this.session = session;
	}

	public String getSemester() {
		return semester;
	}

	public void setSemester(String semester) {
		this.semester = semester;
	}

	public String getMailId() {
		return mailId;
	}

	public void setMailId(String mailId) {
		this.mailId = mailId;
	}

	public String getMobileNo() {
		return mobileNo;
	}

	public void setMobileNo(String mobileNo) {
		this.mobileNo = mobileNo;
	}

	public String getAddress() {
		return address;
	}

	public void setAddress(String address) {
		this.address = address;
	}

}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

package com.ebhor.model;

import java.io.Serializable;

public class Student implements Serializable {

private static final long serialVersionUID = 7935940591376293207L;

private long id;

private long rollNo;

private String name;

private String course;

private String session;

private String semester;

private String mailId;

private String mobileNo;

private String address;

public Student() {

}

public Student(long id, long rollNo, String name, String course, String session, String semester, String mailId,

String mobileNo, String address) {

super();

this.id = id;

this.rollNo = rollNo;

this.name = name;

this.course = course;

this.session = session;

this.semester = semester;

this.mailId = mailId;

this.mobileNo = mobileNo;

this.address = address;

}

@Override

public String toString() {

return "Student [id=" + id + ", rollNo=" + rollNo + ", name=" + name + ", course=" + course + ", session="

+ session + ", semester=" + semester + ", mailId=" + mailId + ", mobileNo=" + mobileNo + ", address="

+ address + "]";

}

public long getId() {

return id;

}

public void setId(long id) {

this.id = id;

}

public long getRollNo() {

return rollNo;

}

public void setRollNo(long rollNo) {

this.rollNo = rollNo;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getCourse() {

return course;

}

public void setCourse(String course) {

this.course = course;

}

public String getSession() {

return session;

}

public void setSession(String session) {

this.session = session;

}

public String getSemester() {

return semester;

}

public void setSemester(String semester) {

this.semester = semester;

}

public String getMailId() {

return mailId;

}

public void setMailId(String mailId) {

this.mailId = mailId;

}

public String getMobileNo() {

return mobileNo;

}

public void setMobileNo(String mobileNo) {

this.mobileNo = mobileNo;

}

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

Connection with MySql and Getting Data from MySQL

ConnectionFactory.java

Instead of specifying a database connection to each JDBC call a class is created to return the Connection object.

package com.ebhor.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
 
public class ConnectionFactory {
 
 public static Connection getConnection() {
 Connection c = null;
 try {
 Class.forName("com.mysql.cj.jdbc.Driver");
 c = DriverManager.getConnection("jdbc:mysql://localhost:3306/ebhor","root", "");
 } catch (ClassNotFoundException e) {
 System.out.println("ClassNotFoundException " + e);
 } catch (SQLException e) {
 System.out.println("SQLException " + e);
 }
 return c;
 }
}

package com.ebhor.dao;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class ConnectionFactory {

public static Connection getConnection() {

Connection c = null;

try {

Class.forName("com.mysql.cj.jdbc.Driver");

c = DriverManager.getConnection("jdbc:mysql://localhost:3306/ebhor","root", "");

} catch (ClassNotFoundException e) {

System.out.println("ClassNotFoundException " + e);

} catch (SQLException e) {

System.out.println("SQLException " + e);

}

return c;

}

StudentDAO.java

To access student’s data from MySQL table JDBC Prepared statement is used.

Steps to read data from MySQL

Get the connection object
Prepare the PreparedStatement
Execute the query and get ResultSet
Iterate ResultSet object
Prepare student objects and assign to student ArrayList.
Close the connection
Return students

package com.ebhor.dao;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.ebhor.model.Student;
public class StudentDAO {
	Connection con = null;
	PreparedStatement ps = null;
	ResultSet rs = null;
	public List<Student> fetchAll() {
		List<Student> studentList = new ArrayList<Student>();
		con = ConnectionFactory.getConnection();
		try {
			String query = "select * from student order by id";
			ps = con.prepareStatement(query);
			rs = ps.executeQuery();
			while (rs.next()) {
				Student student = new Student();
				student.setId(rs.getLong("id"));
				student.setRollNo(rs.getLong("roll_no"));
				student.setName(rs.getString("name"));
				student.setCourse(rs.getString("course"));
				student.setSession(rs.getString("session"));
				student.setSemester(rs.getString("semester"));
				student.setMobileNo(rs.getString("mobile_no"));
				student.setMailId(rs.getString("email_id"));
				student.setAddress(rs.getString("address"));
			    studentList.add(student);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				con.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		return studentList;
	}
}

package com.ebhor.dao;

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import java.util.List;

import com.ebhor.model.Student;

public class StudentDAO {

Connection con = null;

PreparedStatement ps = null;

ResultSet rs = null;

public List<Student> fetchAll() {

List<Student> studentList = new ArrayList<Student>();

con = ConnectionFactory.getConnection();

try {

String query = "select * from student order by id";

ps = con.prepareStatement(query);

rs = ps.executeQuery();

while (rs.next()) {

Student student = new Student();

student.setId(rs.getLong("id"));

student.setRollNo(rs.getLong("roll_no"));

student.setName(rs.getString("name"));

student.setCourse(rs.getString("course"));

student.setSession(rs.getString("session"));

student.setSemester(rs.getString("semester"));

student.setMobileNo(rs.getString("mobile_no"));

student.setMailId(rs.getString("email_id"));

student.setAddress(rs.getString("address"));

studentList.add(student);

}

} catch (Exception e) {

e.printStackTrace();

} finally {

try {

con.close();

} catch (SQLException ex) {

ex.printStackTrace();

}

return studentList;

}

Index Page (index.jsp)

This page contains a link called Servlet.

<%@ page language="java" contentType="text/html; charset=utf-8"
	pageEncoding="utf-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<title>Index Page</title>
</head>
<body>
	<h2>Student Details</h2>
	<a href="show-students">Show Students</a>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=utf-8"

pageEncoding="utf-8"%>

<!DOCTYPE html>

<html>

<head>

<title>Index Page</title>

</head>

<body>

<h2>Student Details</h2>

<a href="show-students">Show Students</a>

</body>

</html>

List of students (students.jsp)

After fetching data from the database servlet send a request response to this page.

This page iterates request scope object students using c:forEach. JSTL core tag library is used here.

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="utf-8"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<!DOCTYPE html>
<html>
<head>
<title>Student Details</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link
	href="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/css/bootstrap.min.css"
	rel="stylesheet">
<script
	src="https://cdn.jsdelivr.net/npm/bootstrap@5.2.3/dist/js/bootstrap.bundle.min.js"></script>
</head>
<body>
	<div class="container mt-3">
		<div class="row">
			<div class="col-2"></div>
			<div class="col-8">
				<h2>List of Students</h2>
				<table class="table table-bordered">
					<thead>
						<tr>
							<th>#</th>
							<th>Roll No</th>
							<th>Name</th>
							<th>Course</th>
							<th>Session</th>
							<th>Semester</th>
							<th>Mobile No</th>
							<th>Email Id</th>
							<th>Address</th>
						</tr>
					</thead>
					<tbody>
						<c:forEach items="${students}" var="student" varStatus="loop">
							<tr>
								<td>${loop.index+1}</td>
								<td>${student.rollNo}</td>
								<td>${student.name}</td>
								<td>${student.course}</td>
								<td>${student.session}</td>
								<td>${student.semester}</td>
								<td>${student.mobileNo}</td>
								<td>${student.mailId}</td>
								<td>${student.address}</td>
							</tr>
						</c:forEach>
					</tbody>
				</table>
			</div>
			<div class="col-2"></div>
		</div>
	</div>
</body>
</html>

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"

pageEncoding="utf-8"%>

<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<!DOCTYPE html>

<html>

<head>

<title>Student Details</title>

<link

href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css"

rel="stylesheet">

<script

src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>

</head>

<body>

<h2>List of Students</h2>

<thead>

<tr>

<th>Course</th>

<th>Session</th>

<th>Semester</th>

<th>Mobile No</th>

<th>Email Id</th>

<th>Address</th>

</tr>

</thead>

<tbody>

<c:forEach items="${students}" var="student" varStatus="loop">

<tr>

<td>${loop.index+1}</td>

<td>${student.rollNo}</td>

<td>${student.name}</td>

<td>${student.course}</td>

<td>${student.session}</td>

<td>${student.semester}</td>

<td>${student.mobileNo}</td>

<td>${student.mailId}</td>

<td>${student.address}</td>

</tr>

</c:forEach>

</tbody>

</table>

</div>

</div>

</body>

</html>

Result

How To Get Data From Database To Servlet Jdbc

Q: jakarta.servlet.ServletException: java.lang.NoClassDefFoundError: jakarta/servlet/jsp/jstl/core/LoopTag

Along with jakarta.servlet.jsp.jstl-3.0.0 include jakarta.servlet.jsp.jstl-api-3.0.0

Q: org.apache.jasper.JasperException: The absolute uri: [http://java.sun.com/jsp/jstl/core] cannot be resolved in either web.xml or the jar files deployed with this application

Answer: Include jakarta.servlet.jsp.jstl-3.0.0 and supporting jar jakarta.servlet.jsp.jstl-api-3.0.0 to work it correctly.

Servlet url and class mapping using web.xml

Servlet Annotation WebServlet Example

ServletConfig to access initial parameter value

Servlet Maven Configuration Example

ServletContext getting parameter

Getting parameter values in Servlet getParameterNames

ServletContext getting multiple parameters

ServletConfig To Access Multiple Initial Parameter Value

getting all request parameters in servlet

Request dispatcher in Servlet User Login Example

Sending data to servlet using http get method