Part 2 :
We will develop the University Database & Fee Management System one page at a time. Each page will have two separate files:
.html– frontend design only..jsp– backend processing using JSP, JDBC and Oracle Database.
We will use simple HTML, minimal CSS and beginner-friendly JavaScript.
University Management System – Dashboard Frontend
This is File 1 of the University Database and Fee Management System project. In this tutorial, we will create a simple dashboard using HTML and CSS.
1. Dashboard Features
- Display total students.
- Display total courses.
- Display course registrations.
- Display collected fees.
- Display outstanding fees.
- Navigate to different system modules.
2. Create the HTML File
Create a file named index.html inside your project folder.
3. Complete HTML Code
<!DOCTYPE html>
<html>
<head>
<title>University Management System</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
body {
font-family: Arial;
background: #f2f4f8;
margin: 0;
}
header {
background: #173b70;
color: white;
padding: 18px;
text-align: center;
}
nav {
background: #244f85;
padding: 12px;
text-align: center;
}
nav a {
color: white;
text-decoration: none;
margin: 10px;
display: inline-block;
}
.container {
padding: 25px;
}
.cards {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
gap: 15px;
}
.card {
background: white;
padding: 20px;
border-radius: 6px;
text-align: center;
}
.card h3 {
color: #173b70;
}
.card p {
font-size: 25px;
font-weight: bold;
}
footer {
text-align: center;
padding: 15px;
color: #555;
}
</style>
</head>
<body>
<header>
<h2>University Management System</h2>
<p>Student, Course and Fee Management</p>
</header>
<nav>
<a href="index.html">Dashboard</a>
<a href="students.html">Students</a>
<a href="courses.html">Courses</a>
<a href="registrations.html">Registrations</a>
<a href="fees.html">Student Fees</a>
<a href="payments.html">Payments</a>
<a href="reports.html">Reports</a>
</nav>
<div class="container">
<h2>Dashboard</h2>
<p>Welcome to the University Management System.</p>
<div class="cards">
<div class="card">
<h3>Total Students</h3>
<p>150</p>
</div>
<div class="card">
<h3>Total Courses</h3>
<p>25</p>
</div>
<div class="card">
<h3>Registrations</h3>
<p>320</p>
</div>
<div class="card">
<h3>Fees Collected</h3>
<p>৳5,00,000</p>
</div>
<div class="card">
<h3>Outstanding Fees</h3>
<p>৳1,50,000</p>
</div>
</div>
<h3>System Modules</h3>
<ul>
<li>Student Information Management</li>
<li>Course Management</li>
<li>Course Registration</li>
<li>Student Fee Management</li>
<li>Fee Payment Management</li>
<li>Financial Reports</li>
</ul>
</div>
<footer>
<p>University Management System | CSE Department</p>
</footer>
</body>
</html>
4. How to Run
- Create the project folder named UniversityManagementSystem.
- Save the code as index.html.
- Open the file using Google Chrome.
- Click the navigation links to explore the modules after creating their respective HTML files.
5. Important Note
The dashboard values are static sample data. In the next stage, we will create index.jsp to retrieve actual student, course, registration and fee information from Oracle Database using JDBC.
Next Tutorial: File 2 – index.jsp (Dashboard Backend).
University Management System – Dashboard Backend Using JSP
This is File 2 of the University Database and Fee Management System project. In this tutorial, we will create a JSP dashboard that retrieves student, course, registration and fee information from Oracle Database using JDBC.
1. Dashboard Features
- Count total students.
- Count total courses.
- Count total course registrations.
- Calculate total fees collected.
- Calculate outstanding fees.
2. Create the JSP File
Create a file named index.jsp in the main project folder.
3. Complete JSP Code
<%@ page import="java.sql.*" %>
<%
String url = "jdbc:oracle:thin:@//localhost:1521/XEPDB1";
String dbUser = "attendance_user";
String dbPassword = "attendance123";
int totalStudents = 0;
int totalCourses = 0;
int totalRegistrations = 0;
double totalPaid = 0;
double totalDue = 0;
Connection con = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager.getConnection(
url, dbUser, dbPassword
);
Statement st = con.createStatement();
ResultSet rs;
// Total Students
rs = st.executeQuery(
"SELECT COUNT(*) FROM STUDENTS"
);
if (rs.next()) {
totalStudents = rs.getInt(1);
}
rs.close();
// Total Courses
rs = st.executeQuery(
"SELECT COUNT(*) FROM COURSES"
);
if (rs.next()) {
totalCourses = rs.getInt(1);
}
rs.close();
// Total Registrations
rs = st.executeQuery(
"SELECT COUNT(*) FROM COURSE_REGISTRATIONS"
);
if (rs.next()) {
totalRegistrations = rs.getInt(1);
}
rs.close();
// Total Fee Collected
rs = st.executeQuery(
"SELECT NVL(SUM(PAID_AMOUNT),0) FROM STUDENT_FEES"
);
if (rs.next()) {
totalPaid = rs.getDouble(1);
}
rs.close();
// Outstanding Fees
rs = st.executeQuery(
"SELECT NVL(SUM(TOTAL_FEE - PAID_AMOUNT),0) FROM STUDENT_FEES"
);
if (rs.next()) {
totalDue = rs.getDouble(1);
}
rs.close();
st.close();
} catch (Exception e) {
out.println("Database Error: " + e.getMessage());
}
finally {
if (con != null) {
try {
con.close();
} catch (SQLException e) {
// Connection closing error
}
}
}
%>
<!DOCTYPE html>
<html>
<head>
<title>University Management System</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
body {
font-family: Arial;
background: #f2f4f8;
margin: 0;
}
header {
background: #173b70;
color: white;
padding: 18px;
text-align: center;
}
nav {
background: #244f85;
padding: 12px;
text-align: center;
}
nav a {
color: white;
text-decoration: none;
margin: 10px;
display: inline-block;
}
.container {
padding: 25px;
}
.cards {
display: grid;
grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
gap: 15px;
}
.card {
background: white;
padding: 20px;
border-radius: 6px;
text-align: center;
}
.card h3 {
color: #173b70;
}
.card p {
font-size: 25px;
font-weight: bold;
}
footer {
text-align: center;
padding: 15px;
color: #555;
}
</style>
</head>
<body>
<header>
<h2>University Management System</h2>
<p>Student, Course and Fee Management</p>
</header>
<nav>
<a href="index.jsp">Dashboard</a>
<a href="students.html">Students</a>
<a href="courses.html">Courses</a>
<a href="registrations.html">Registrations</a>
<a href="fees.html">Student Fees</a>
<a href="payments.html">Payments</a>
<a href="reports.html">Reports</a>
</nav>
<div class="container">
<h2>Dashboard</h2>
<p>Welcome to the University Management System.</p>
<div class="cards">
<div class="card">
<h3>Total Students</h3>
<p><%= totalStudents %></p>
</div>
<div class="card">
<h3>Total Courses</h3>
<p><%= totalCourses %></p>
</div>
<div class="card">
<h3>Registrations</h3>
<p><%= totalRegistrations %></p>
</div>
<div class="card">
<h3>Fees Collected</h3>
<p>৳<%= String.format("%,.2f", totalPaid) %></p>
</div>
<div class="card">
<h3>Outstanding Fees</h3>
<p>৳<%= String.format("%,.2f", totalDue) %></p>
</div>
</div>
<h3>System Modules</h3>
<ul>
<li>Student Information Management</li>
<li>Course Management</li>
<li>Course Registration</li>
<li>Student Fee Management</li>
<li>Fee Payment Management</li>
<li>Financial Reports</li>
</ul>
</div>
<footer>
<p>University Management System | CSE Department</p>
</footer>
</body>
</html>
4. Important Database Note
This JSP assumes that your Oracle database contains the following tables and columns:
- STUDENTS – student information.
- COURSES – course information.
- COURSE_REGISTRATIONS – student course registrations.
- STUDENT_FEES – TOTAL_FEE and PAID_AMOUNT.
Important: These table names and column names must match your actual Oracle schema. If your database uses different names, update the SQL queries accordingly. The database driver JAR must also be placed inside WEB-INF/lib.
5. How to Run
- Place index.jsp in the project root folder.
- Configure Oracle Database connection details.
- Copy the Oracle JDBC driver into WEB-INF/lib.
- Deploy the project on Apache Tomcat 9.
- Open http://localhost:8080/UniversityManagementSystem/index.jsp.
6. Learning Outcome
Students will understand how JSP connects to Oracle Database using JDBC, executes SQL queries and displays database results dynamically on a web page.
Next Tutorial: File 3 – students.html (Student Management Frontend).
Student Management – HTML Frontend
This is File 3 of the University Database and Fee Management System project. We will create a simple HTML page for managing student information.
Complete HTML Code
<!DOCTYPE html>
<html>
<head>
<title>Student Management</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
body {
font-family: Arial;
background: #f2f4f8;
margin: 0;
}
header {
background: #173b70;
color: white;
padding: 18px;
text-align: center;
}
nav {
background: #244f85;
padding: 12px;
text-align: center;
}
nav a {
color: white;
text-decoration: none;
margin: 10px;
}
.container {
padding: 25px;
}
.panel {
background: white;
padding: 20px;
margin-bottom: 20px;
border-radius: 6px;
}
input {
padding: 10px;
margin: 5px;
border: 1px solid #ccc;
border-radius: 4px;
}
button {
background: #2563eb;
color: white;
padding: 10px 18px;
border: none;
border-radius: 4px;
cursor: pointer;
}
table {
width: 100%;
border-collapse: collapse;
}
th, td {
padding: 12px;
border: 1px solid #ddd;
text-align: left;
}
th {
background: #e8eef8;
}
footer {
text-align: center;
padding: 15px;
}
</style>
</head>
<body>
<header>
<h2>University Management System</h2>
<p>Student Management</p>
</header>
<nav>
<a href="index.html">Dashboard</a>
<a href="students.html">Students</a>
<a href="courses.html">Courses</a>
<a href="registrations.html">Registrations</a>
<a href="fees.html">Student Fees</a>
<a href="payments.html">Payments</a>
<a href="reports.html">Reports</a>
</nav>
<div class="container">
<div class="panel">
<h3>Add New Student</h3>
<form action="students.jsp" method="post">
<input type="text" name="studentId"
placeholder="Student ID" required>
<input type="text" name="studentName"
placeholder="Student Name" required>
<input type="email" name="email"
placeholder="Email Address" required>
<input type="text" name="department"
placeholder="Department" required>
<input type="text" name="phone"
placeholder="Contact Number">
<input type="number" name="cgpa"
placeholder="CGPA" min="0" max="4"
step="0.01">
<br><br>
<button type="submit">Save Student</button>
</form>
</div>
<div class="panel">
<h3>Student List</h3>
<form action="students.jsp" method="get">
<input type="text" name="search"
placeholder="Search by ID or Name">
<button type="submit">Search</button>
</form>
<br>
<table>
<thead>
<tr>
<th>Student ID</th>
<th>Student Name</th>
<th>Email</th>
<th>Department</th>
<th>Phone</th>
<th>CGPA</th>
</tr>
</thead>
<tbody>
<tr>
<td>101</td>
<td>Rahim Ahmed</td>
<td>rahim@gmail.com</td>
<td>CSE</td>
<td>01711111111</td>
<td>3.75</td>
</tr>
<tr>
<td>102</td>
<td>Karim Hasan</td>
<td>karim@gmail.com</td>
<td>CSE</td>
<td>01722222222</td>
<td>3.60</td>
</tr>
</tbody>
</table>
</div>
</div>
<footer>
<p>University Management System | CSE Department</p>
</footer>
</body>
</html>
3. How It Works
- The administrator enters the student's information.
- When the Save Student button is clicked, the form sends the data to students.jsp using the POST method.
- The Search form sends a search keyword to the same JSP file using the GET method.
- The JSP backend will process the information and communicate with Oracle Database.
4. Important Note
The student records shown in the table are sample data for frontend demonstration. The actual student list will be retrieved from Oracle Database in the next JSP implementation.
Next Tutorial: File 4 – students.jsp (Student Management Backend Using JDBC and Oracle).
Student Management Backend Using JSP and JDBC
This is File 4 of the University Database and Fee Management System project. We will use JSP, JDBC and Oracle Database to save and retrieve student information.
1. Database Table
For this example, the STUDENTS table should have the following columns:
CREATE TABLE STUDENTS (
STUDENT_ID NUMBER PRIMARY KEY,
STUDENT_NAME VARCHAR2(100) NOT NULL,
EMAIL VARCHAR2(100) NOT NULL,
DEPARTMENT VARCHAR2(50) NOT NULL,
PHONE VARCHAR2(20),
CGPA NUMBER(3,2)
);
Use this structure if you have not already created the STUDENTS table. If your existing table has a different structure, adjust the SQL queries accordingly.
2. Complete JSP Code
<%@ page import="java.sql.*" %>
<%@ page contentType="text/html; charset=UTF-8" %>
<%
String url = "jdbc:oracle:thin:@//localhost:1521/XEPDB1";
String dbUser = "attendance_user";
String dbPassword = "attendance123";
String message = "";
String studentId = request.getParameter("studentId");
String studentName = request.getParameter("studentName");
String email = request.getParameter("email");
String department = request.getParameter("department");
String phone = request.getParameter("phone");
String cgpaValue = request.getParameter("cgpa");
String search = request.getParameter("search");
// INSERT STUDENT
if ("POST".equalsIgnoreCase(request.getMethod())) {
try {
Class.forName("oracle.jdbc.OracleDriver");
try (Connection con = DriverManager.getConnection(
url, dbUser, dbPassword)) {
String sql = "INSERT INTO STUDENTS " +
"(STUDENT_ID, STUDENT_NAME, EMAIL, " +
"DEPARTMENT, PHONE, CGPA) " +
"VALUES (?, ?, ?, ?, ?, ?)";
try (PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, Integer.parseInt(studentId));
ps.setString(2, studentName);
ps.setString(3, email);
ps.setString(4, department);
ps.setString(5, phone);
if (cgpaValue == null || cgpaValue.trim().isEmpty()) {
ps.setNull(6, Types.NUMERIC);
} else {
ps.setDouble(6, Double.parseDouble(cgpaValue));
}
ps.executeUpdate();
message = "Student added successfully!";
}
}
} catch (SQLIntegrityConstraintViolationException e) {
message = "Student ID already exists or a database constraint was violated.";
} catch (Exception e) {
message = "Error: " + e.getMessage();
}
}
// DISPLAY AND SEARCH STUDENTS
String searchSql =
"SELECT STUDENT_ID, STUDENT_NAME, EMAIL, " +
"DEPARTMENT, PHONE, CGPA FROM STUDENTS";
boolean hasSearch =
search != null && !search.trim().isEmpty();
if (hasSearch) {
searchSql +=
" WHERE TO_CHAR(STUDENT_ID) LIKE ? " +
"OR LOWER(STUDENT_NAME) LIKE ?";
}
searchSql += " ORDER BY STUDENT_ID";
try {
Class.forName("oracle.jdbc.OracleDriver");
} catch (Exception e) {
message = "JDBC Driver Error: " + e.getMessage();
}
// HTML OUTPUT HELPER
StringBuilder rows = new StringBuilder();
try (Connection con = DriverManager.getConnection(
url, dbUser, dbPassword);
PreparedStatement ps = con.prepareStatement(searchSql)) {
if (hasSearch) {
ps.setString(1, "%" + search.trim() + "%");
ps.setString(2, "%" + search.trim().toLowerCase() + "%");
}
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
rows.append("<tr>");
rows.append("<td>")
.append(rs.getInt("STUDENT_ID"))
.append("</td>");
rows.append("<td>")
.append(escape(rs.getString("STUDENT_NAME")))
.append("</td>");
rows.append("<td>")
.append(escape(rs.getString("EMAIL")))
.append("</td>");
rows.append("<td>")
.append(escape(rs.getString("DEPARTMENT")))
.append("</td>");
rows.append("<td>")
.append(escape(rs.getString("PHONE")))
.append("</td>");
rows.append("<td>")
.append(rs.getString("CGPA") == null
? "" : rs.getString("CGPA"))
.append("</td>");
rows.append("</tr>");
}
}
} catch (Exception e) {
message = "Database Error: " + e.getMessage();
}
if (rows.length() == 0) {
rows.append("<tr><td colspan='6'>")
.append("No student records found.")
.append("</td></tr>");
}
// Escape special HTML characters
String escape(String value) {
if (value == null) return "";
return value.replace("&", "&")
.replace("<", "<")
.replace(">", ">")
.replace("\"", """)
.replace("'", "'");
}
%>
<!DOCTYPE html>
<html>
<head>
<title>Student Management</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
body {
font-family: Arial;
background: #f2f4f8;
margin: 0;
}
header {
background: #173b70;
color: white;
padding: 18px;
text-align: center;
}
nav {
background: #244f85;
padding: 12px;
text-align: center;
}
nav a {
color: white;
text-decoration: none;
margin: 10px;
}
.container {
padding: 25px;
}
.panel {
background: white;
padding: 20px;
margin-bottom: 20px;
border-radius: 6px;
}
input {
padding: 10px;
margin: 5px;
border: 1px solid #ccc;
border-radius: 4px;
}
button {
background: #2563eb;
color: white;
padding: 10px 18px;
border: none;
border-radius: 4px;
cursor: pointer;
}
table {
width: 100%;
border-collapse: collapse;
}
th, td {
padding: 12px;
border: 1px solid #ddd;
text-align: left;
}
th {
background: #e8eef8;
}
.message {
background: #e0f2fe;
padding: 12px;
margin-bottom: 15px;
border-radius: 4px;
}
footer {
text-align: center;
padding: 15px;
}
</style>
</head>
<body>
<header>
<h2>University Management System</h2>
<p>Student Management</p>
</header>
<nav>
<a href="index.jsp">Dashboard</a>
<a href="students.jsp">Students</a>
<a href="courses.html">Courses</a>
<a href="registrations.html">Registrations</a>
<a href="fees.html">Student Fees</a>
<a href="payments.html">Payments</a>
<a href="reports.html">Reports</a>
</nav>
<div class="container">
<% if (!message.isEmpty()) { %>
<div class="message">
<%= escape(message) %>
</div>
<% } %>
<div class="panel">
<h3>Add New Student</h3>
<form action="students.jsp" method="post">
<input type="number" name="studentId"
placeholder="Student ID" required>
<input type="text" name="studentName"
placeholder="Student Name" required>
<input type="email" name="email"
placeholder="Email Address" required>
<input type="text" name="department"
placeholder="Department" required>
<input type="text" name="phone"
placeholder="Contact Number">
<input type="number" name="cgpa"
placeholder="CGPA" min="0" max="4"
step="0.01">
<br><br>
<button type="submit">Save Student</button>
</form>
</div>
<div class="panel">
<h3>Student List</h3>
<form action="students.jsp" method="get">
<input type="text" name="search"
placeholder="Search by ID or Name">
<button type="submit">Search</button>
<a href="students.jsp">Show All</a>
</form>
<br>
<table>
<thead>
<tr>
<th>Student ID</th>
<th>Student Name</th>
<th>Email</th>
<th>Department</th>
<th>Phone</th>
<th>CGPA</th>
</tr>
</thead>
<tbody>
<%= rows.toString() %>
</tbody>
</table>
</div>
</div>
<footer>
<p>University Management System | CSE Department</p>
</footer>
</body>
</html>
3. How the JSP File Works
- Class.forName() loads the Oracle JDBC driver.
- DriverManager.getConnection() connects JSP to Oracle Database.
- PreparedStatement inserts student information safely.
- executeUpdate() saves the new student record.
- executeQuery() retrieves student information.
- ResultSet reads the returned database records.
- The JSP page displays the student records in an HTML table.
4. Required Configuration
- Oracle Database must be running.
- Oracle JDBC driver must be inside WEB-INF/lib.
- Database name, username and password must match your configuration.
- The STUDENTS table must have the columns used in this tutorial.
5. Run the Application
Deploy the project on Apache Tomcat 9 and open:
http://localhost:8080/UniversityManagementSystem/students.jsp
6. Learning Outcome
Students will learn how to insert, search and retrieve student information using JSP, JDBC, PreparedStatement and Oracle Database.
Next Tutorial: File 5 – courses.html (Course Management Frontend).
Course Management – HTML Frontend
This is File 5 of the University Database and Fee Management System project. We will create a simple frontend for adding and viewing university courses.
1. Complete HTML Code
<!DOCTYPE html>
<html>
<head>
<title>Course Management</title>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
body {
font-family: Arial;
background: #f2f4f8;
margin: 0;
}
header {
background: #173b70;
color: white;
padding: 18px;
text-align: center;
}
nav {
background: #244f85;
padding: 12px;
text-align: center;
}
nav a {
color: white;
text-decoration: none;
margin: 10px;
}
.container {
padding: 25px;
}
.panel {
background: white;
padding: 20px;
margin-bottom: 20px;
border-radius: 6px;
}
input {
padding: 10px;
margin: 5px;
border: 1px solid #ccc;
border-radius: 4px;
}
button {
background: #2563eb;
color: white;
padding: 10px 18px;
border: none;
border-radius: 4px;
cursor: pointer;
}
table {
width: 100%;
border-collapse: collapse;
}
th, td {
padding: 12px;
border: 1px solid #ddd;
text-align: left;
}
th {
background: #e8eef8;
}
footer {
text-align: center;
padding: 15px;
}
</style>
</head>
<body>
<header>
<h2>University Management System</h2>
<p>Course Management</p>
</header>
<nav>
<a href="index.html">Dashboard</a>
<a href="students.html">Students</a>
<a href="courses.html">Courses</a>
<a href="registrations.html">Registrations</a>
<a href="fees.html">Student Fees</a>
<a href="payments.html">Payments</a>
<a href="reports.html">Reports</a>
</nav>
<div class="container">
<div class="panel">
<h3>Add New Course</h3>
<form action="courses.jsp" method="post">
<input type="number" name="courseId"
placeholder="Course ID" required>
<input type="text" name="courseName"
placeholder="Course Name" required>
<input type="number" name="credit"
placeholder="Course Credit"
min="1" max="6" required>
<br><br>
<button type="submit">Save Course</button>
</form>
</div>
<div class="panel">
<h3>Course List</h3>
<form action="courses.jsp" method="get">
<input type="text" name="search"
placeholder="Search Course ID or Name">
<button type="submit">Search</button>
</form>
<br>
<table>
<thead>
<tr>
<th>Course ID</th>
<th>Course Name</th>
<th>Credit</th>
</tr>
</thead>
<tbody>
<tr>
<td>201</td>
<td>Database Systems</td>
<td>3</td>
</tr>
<tr>
<td>202</td>
<td>Data Structures</td>
<td>3</td>
</tr>
<tr>
<td>203</td>
<td>Software Engineering</td>
<td>3</td>
</tr>
<tr>
<td>204</td>
<td>Computer Networks</td>
<td>3</td>
</tr>
</tbody>
</table>
</div>
</div>
<footer>
<p>University Management System | CSE Department</p>
</footer>
</body>
</html>
3. How It Works
- The administrator enters the course ID, course name and credit.
- The Save Course button sends the information to courses.jsp using POST.
- The Search button sends a keyword to courses.jsp using GET.
- The JSP backend will insert and retrieve course information from Oracle Database.
4. Sample Course Information
| Course ID | Course Name | Credit |
|---|---|---|
| 201 | Database Systems | 3 |
| 202 | Data Structures | 3 |
| 203 | Software Engineering | 3 |
| 204 | Computer Networks | 3 |
5. Important Note
The course records in the HTML table are sample data. The actual course information will be retrieved from Oracle Database through the JSP backend.
Next Tutorial: File 6 – courses.jsp (Course Management Backend Using JDBC and Oracle).
University Management System – Course Management Backend Using JSP and JDBC
File name: courses.jsp
Purpose: Insert, search, and display course information using JSP and Oracle Database.
1. Oracle Database Table
First, create the COURSES table in Oracle:
CREATE TABLE COURSES (
COURSE_ID NUMBER PRIMARY KEY,
COURSE_NAME VARCHAR2(100) NOT NULL,
CREDIT NUMBER(2) NOT NULL
);
2. JSP Code
Create a file named courses.jsp and add the following code:
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%!
public String escapeHtml(String value) {
if (value == null) return "";
return value.replace("&", "&")
.replace("<", "<")
.replace(">", ">")
.replace("\"", """)
.replace("'", "'");
}
%>
<%
String message = "";
String url = "jdbc:oracle:thin:@//localhost:1521/XEPDB1";
String username = "attendance_user";
String password = "attendance123";
List<String[]> courses = new ArrayList<String[]>();
try {
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection(
url, username, password
);
// Insert a new course
if ("POST".equalsIgnoreCase(request.getMethod())) {
int courseId = Integer.parseInt(
request.getParameter("courseId")
);
String courseName = request.getParameter("courseName");
int credit = Integer.parseInt(
request.getParameter("credit")
);
String sql = "INSERT INTO COURSES " +
"(COURSE_ID, COURSE_NAME, CREDIT) " +
"VALUES (?, ?, ?)";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, courseId);
ps.setString(2, courseName);
ps.setInt(3, credit);
ps.executeUpdate();
message = "Course added successfully.";
ps.close();
}
// Search courses
String search = request.getParameter("search");
String sql = "SELECT COURSE_ID, COURSE_NAME, CREDIT " +
"FROM COURSES";
PreparedStatement ps;
if (search != null && !search.trim().isEmpty()) {
sql += " WHERE TO_CHAR(COURSE_ID) LIKE ? " +
"OR LOWER(COURSE_NAME) LIKE ?";
ps = con.prepareStatement(sql);
ps.setString(1, "%" + search.trim() + "%");
ps.setString(2, "%" + search.trim().toLowerCase() + "%");
} else {
ps = con.prepareStatement(sql);
}
ResultSet rs = ps.executeQuery();
while (rs.next()) {
courses.add(new String[] {
rs.getString("COURSE_ID"),
rs.getString("COURSE_NAME"),
rs.getString("CREDIT")
});
}
rs.close();
ps.close();
con.close();
} catch (NumberFormatException e) {
message = "Please enter valid numeric values for Course ID and Credit.";
} catch (SQLIntegrityConstraintViolationException e) {
message = "This Course ID already exists.";
} catch (Exception e) {
message = "Unable to process the request. Please check the database connection and table.";
}
%>
<!DOCTYPE html>
<html>
<head>
<title>Course Management</title>
<meta charset="UTF-8">
</head>
<body>
<h2>Course Management System</h2>
<p>
<a href="index.html">Dashboard</a> |
<a href="students.html">Students</a> |
<a href="courses.html">Courses</a> |
<a href="registrations.html">Registrations</a> |
<a href="fees.html">Fees</a> |
<a href="payments.html">Payments</a> |
<a href="reports.html">Reports</a>
</p>
<hr>
<h3>Course List</h3>
<form method="get" action="courses.jsp">
<input type="text" name="search"
placeholder="Search by ID or course name">
<button type="submit">Search</button>
<a href="courses.jsp">Show All</a>
</form>
<p><%= escapeHtml(message) %></p>
<table border="1" cellpadding="8">
<tr>
<th>Course ID</th>
<th>Course Name</th>
<th>Credit</th>
</tr>
<%
for (String[] course : courses) {
%>
<tr>
<td><%= escapeHtml(course[0]) %></td>
<td><%= escapeHtml(course[1]) %></td>
<td><%= escapeHtml(course[2]) %></td>
</tr>
<%
}
%>
</table>
<p>Total Courses: <%= courses.size() %></p>
<p><a href="courses.html">Add Another Course</a></p>
</body>
</html>
3. How It Works
- The user enters Course ID, Course Name, and Credit in courses.html.
- The form sends the information to courses.jsp using POST.
- JSP connects to Oracle Database using JDBC.
- A PreparedStatement inserts the course into the COURSES table.
- The JSP retrieves and displays the course list.
- The search option finds courses by ID or course name.
4. Important Notes
- Make sure the Oracle JDBC driver is available in Tomcat's WEB-INF/lib folder.
- Update the database username and password if your configuration is different.
- The database table columns must match the JSP code.
- This is a beginner demonstration. A production system should also include input validation, authentication, and CSRF protection.
5. Next File
registrations.html – Course Registration Frontend Using HTML.
University Management System – Course Registration Frontend Using HTML
File name: registrations.html
Purpose: Create a course registration form for students.
1. HTML Code
Create a file named registrations.html and add the following code:
<!DOCTYPE html>
<html>
<head>
<title>Course Registration</title>
<meta charset="UTF-8">
</head>
<body>
<h2>University Management System</h2>
<p>
<a href="index.html">Dashboard</a> |
<a href="students.html">Students</a> |
<a href="courses.html">Courses</a> |
<a href="registrations.html">Registrations</a> |
<a href="fees.html">Fees</a> |
<a href="payments.html">Payments</a> |
<a href="reports.html">Reports</a>
</p>
<hr>
<h3>Register Student in a Course</h3>
<form action="registrations.jsp" method="post">
<label>Registration ID:</label><br>
<input type="number" name="registrationId" required>
<br><br>
<label>Student ID:</label><br>
<input type="number" name="studentId" required>
<br><br>
<label>Course ID:</label><br>
<input type="number" name="courseId" required>
<br><br>
<label>Registration Date:</label><br>
<input type="date" name="registrationDate" required>
<br><br>
<button type="submit">Register Course</button>
</form>
<hr>
<h3>Registration Information</h3>
<p>
Enter a valid Student ID and Course ID from the database.
The registration will be saved in the course registration table.
</p>
<p>
<a href="registrations.jsp">View All Registrations</a>
</p>
</body>
</html>
2. Form Field Explanation
- Registration ID: Unique ID for each registration.
- Student ID: Identifies the student.
- Course ID: Identifies the selected course.
- Registration Date: Date when the student registers.
3. How It Works
- The user enters the registration details.
- The form sends the data to registrations.jsp using POST.
- The JSP file will validate the Student ID and Course ID.
- Valid registration information will be saved in Oracle Database.
4. Important Notes
- The Student ID must exist in the STUDENTS table.
- The Course ID must exist in the COURSES table.
- Registration ID must be unique.
- The actual database operation will be handled by the next JSP file.
5. Next File
registrations.jsp – Course Registration Backend Using JSP and JDBC.
University Management System – Course Registration Backend Using JSP and JDBC
File name: registrations.jsp
Purpose: Save, validate, search, and display student course registrations.
1. Oracle Database Table
Create the COURSE_REGISTRATIONS table:
CREATE TABLE COURSE_REGISTRATIONS (
REGISTRATION_ID NUMBER PRIMARY KEY,
STUDENT_ID NUMBER NOT NULL,
COURSE_ID NUMBER NOT NULL,
REGISTRATION_DATE DATE NOT NULL,
CONSTRAINT FK_REG_STUDENT
FOREIGN KEY (STUDENT_ID)
REFERENCES STUDENTS(STUDENT_ID),
CONSTRAINT FK_REG_COURSE
FOREIGN KEY (COURSE_ID)
REFERENCES COURSES(COURSE_ID)
);
2. JSP Code
Create registrations.jsp and add this code:
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%!
public String escapeHtml(String value) {
if (value == null) return "";
return value.replace("&", "&")
.replace("<", "<")
.replace(">", ">")
.replace("\"", """)
.replace("'", "'");
}
%>
<%
String message = "";
String url = "jdbc:oracle:thin:@//localhost:1521/XEPDB1";
String username = "attendance_user";
String password = "attendance123";
List<String[]> registrations = new ArrayList<String[]>();
try {
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection(
url, username, password
);
// Insert registration
if ("POST".equalsIgnoreCase(request.getMethod())) {
int registrationId = Integer.parseInt(
request.getParameter("registrationId")
);
int studentId = Integer.parseInt(
request.getParameter("studentId")
);
int courseId = Integer.parseInt(
request.getParameter("courseId")
);
String registrationDate =
request.getParameter("registrationDate");
String sql = "INSERT INTO COURSE_REGISTRATIONS " +
"(REGISTRATION_ID, STUDENT_ID, COURSE_ID, REGISTRATION_DATE) " +
"VALUES (?, ?, ?, TO_DATE(?, 'YYYY-MM-DD'))";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, registrationId);
ps.setInt(2, studentId);
ps.setInt(3, courseId);
ps.setString(4, registrationDate);
ps.executeUpdate();
message = "Course registration completed successfully.";
ps.close();
}
// Retrieve registrations
String search = request.getParameter("search");
String sql = "SELECT R.REGISTRATION_ID, R.STUDENT_ID, " +
"S.STUDENT_NAME, R.COURSE_ID, C.COURSE_NAME, " +
"TO_CHAR(R.REGISTRATION_DATE, 'YYYY-MM-DD') AS REG_DATE " +
"FROM COURSE_REGISTRATIONS R " +
"JOIN STUDENTS S ON R.STUDENT_ID = S.STUDENT_ID " +
"JOIN COURSES C ON R.COURSE_ID = C.COURSE_ID";
PreparedStatement ps;
if (search != null && !search.trim().isEmpty()) {
sql += " WHERE TO_CHAR(R.REGISTRATION_ID) LIKE ? " +
"OR TO_CHAR(R.STUDENT_ID) LIKE ? " +
"OR LOWER(S.STUDENT_NAME) LIKE ? " +
"OR TO_CHAR(R.COURSE_ID) LIKE ? " +
"OR LOWER(C.COURSE_NAME) LIKE ?";
ps = con.prepareStatement(sql);
String keyword = "%" + search.trim().toLowerCase() + "%";
ps.setString(1, "%" + search.trim() + "%");
ps.setString(2, "%" + search.trim() + "%");
ps.setString(3, keyword);
ps.setString(4, "%" + search.trim() + "%");
ps.setString(5, keyword);
} else {
ps = con.prepareStatement(sql);
}
ResultSet rs = ps.executeQuery();
while (rs.next()) {
registrations.add(new String[] {
rs.getString("REGISTRATION_ID"),
rs.getString("STUDENT_ID"),
rs.getString("STUDENT_NAME"),
rs.getString("COURSE_ID"),
rs.getString("COURSE_NAME"),
rs.getString("REG_DATE")
});
}
rs.close();
ps.close();
con.close();
} catch (NumberFormatException e) {
message = "Please enter valid numeric IDs.";
} catch (SQLIntegrityConstraintViolationException e) {
message = "Registration failed. Check the IDs or duplicate Registration ID.";
} catch (Exception e) {
message = "Unable to process registration. Please check the database and input information.";
}
%>
<!DOCTYPE html>
<html>
<head>
<title>Course Registrations</title>
<meta charset="UTF-8">
</head>
<body>
<h2>Course Registration Management</h2>
<p>
<a href="index.html">Dashboard</a> |
<a href="students.html">Students</a> |
<a href="courses.html">Courses</a> |
<a href="registrations.html">Registrations</a> |
<a href="fees.html">Fees</a> |
<a href="payments.html">Payments</a> |
<a href="reports.html">Reports</a>
</p>
<hr>
<p><%= escapeHtml(message) %></p>
<h3>Search Registrations</h3>
<form method="get" action="registrations.jsp">
<input type="text" name="search"
placeholder="Search registration, student, or course">
<button type="submit">Search</button>
<a href="registrations.jsp">Show All</a>
</form>
<h3>Registration List</h3>
<table border="1" cellpadding="8">
<tr>
<th>Registration ID</th>
<th>Student ID</th>
<th>Student Name</th>
<th>Course ID</th>
<th>Course Name</th>
<th>Registration Date</th>
</tr>
<%
for (String[] r : registrations) {
%>
<tr>
<td><%= escapeHtml(r[0]) %></td>
<td><%= escapeHtml(r[1]) %></td>
<td><%= escapeHtml(r[2]) %></td>
<td><%= escapeHtml(r[3]) %></td>
<td><%= escapeHtml(r[4]) %></td>
<td><%= escapeHtml(r[5]) %></td>
</tr>
<%
}
%>
</table>
<p>Total Registrations: <%= registrations.size() %></p>
<p><a href="registrations.html">Register Another Course</a></p>
</body>
</html>
3. How It Works
- The user submits the registration form from registrations.html.
- JSP receives the Registration ID, Student ID, Course ID, and Date.
- Oracle checks the foreign key relationships with STUDENTS and COURSES.
- If the information is valid, the registration is inserted.
- The JSP displays registration details with student and course names.
4. Important Notes
- Create the STUDENTS and COURSES tables before creating this table.
- Use valid Student IDs and Course IDs.
- Registration IDs must be unique.
- This example does not yet prevent a student from registering for the same course more than once. That rule can be added with a unique constraint.
5. Next File
fees.html – Student Fee Management Frontend Using HTML.
University Management System – Student Fee Management Frontend Using HTML
File name: fees.html
Purpose: Create and manage student fee records.
1. HTML Code
Create a file named fees.html and add the following code:
<!DOCTYPE html>
<html>
<head>
<title>Student Fee Management</title>
<meta charset="UTF-8">
</head>
<body>
<h2>University Management System</h2>
<p>
<a href="index.html">Dashboard</a> |
<a href="students.html">Students</a> |
<a href="courses.html">Courses</a> |
<a href="registrations.html">Registrations</a> |
<a href="fees.html">Fees</a> |
<a href="payments.html">Payments</a> |
<a href="reports.html">Reports</a>
</p>
<hr>
<h3>Create Student Fee Record</h3>
<form action="fees.jsp" method="post">
<label>Fee ID:</label><br>
<input type="number" name="feeId" required>
<br><br>
<label>Student ID:</label><br>
<input type="number" name="studentId" required>
<br><br>
<label>Total Fee (BDT):</label><br>
<input type="number" name="totalFee"
min="0" step="0.01" required>
<br><br>
<label>Due Date:</label><br>
<input type="date" name="dueDate" required>
<br><br>
<button type="submit">Save Fee Record</button>
</form>
<hr>
<h3>Search Student Fee Records</h3>
<form action="fees.jsp" method="get">
<input type="text" name="search"
placeholder="Enter Fee ID or Student ID">
<button type="submit">Search</button>
</form>
<p>
<a href="fees.jsp">View All Fee Records</a>
</p>
<h3>Fee Information</h3>
<p>
The total fee is the amount assigned to a student.
Payments will be recorded separately through the
Fee Payments module.
</p>
</body>
</html>
2. Form Field Explanation
- Fee ID: Unique identifier for a fee record.
- Student ID: Identifies the student who must pay the fee.
- Total Fee: Total amount assigned to the student in BDT.
- Due Date: Last date for paying the assigned fee.
3. How It Works
- The administrator enters the student's fee information.
- The form sends the information to fees.jsp.
- The JSP file validates the Student ID.
- Valid fee information is saved in Oracle Database.
- Actual payments will be recorded separately in the payments module.
4. Important Notes
- The Student ID must already exist in the STUDENTS table.
- Each Fee ID must be unique.
- The initial paid amount will be zero.
- Outstanding dues will be calculated from total fees minus recorded payments.
5. Next File
fees.jsp – Student Fee Management Backend Using JSP and JDBC.
University Management System – Student Fee Management Backend Using JSP and JDBC
File name: fees.jsp
Purpose: Insert, search, and display student fee records.
1. Oracle Database Table
Create the STUDENT_FEES table:
CREATE TABLE STUDENT_FEES (
FEE_ID NUMBER PRIMARY KEY,
STUDENT_ID NUMBER NOT NULL,
TOTAL_FEE NUMBER(10,2) NOT NULL,
PAID_AMOUNT NUMBER(10,2) DEFAULT 0 NOT NULL,
DUE_DATE DATE NOT NULL,
CONSTRAINT FK_FEE_STUDENT
FOREIGN KEY (STUDENT_ID)
REFERENCES STUDENTS(STUDENT_ID),
CONSTRAINT CHK_TOTAL_FEE
CHECK (TOTAL_FEE >= 0),
CONSTRAINT CHK_PAID_AMOUNT
CHECK (PAID_AMOUNT >= 0 AND PAID_AMOUNT <= TOTAL_FEE)
);
2. JSP Code
Create fees.jsp and add the following code:
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%!
public String escapeHtml(String value) {
if (value == null) return "";
return value.replace("&", "&")
.replace("<", "<")
.replace(">", ">")
.replace("\"", """)
.replace("'", "'");
}
%>
<%
String message = "";
String url = "jdbc:oracle:thin:@//localhost:1521/XEPDB1";
String username = "attendance_user";
String password = "attendance123";
List<String[]> feeRecords = new ArrayList<String[]>();
try {
Class.forName("oracle.jdbc.OracleDriver");
Connection con = DriverManager.getConnection(
url, username, password
);
// Insert a new fee record
if ("POST".equalsIgnoreCase(request.getMethod())) {
int feeId = Integer.parseInt(
request.getParameter("feeId")
);
int studentId = Integer.parseInt(
request.getParameter("studentId")
);
double totalFee = Double.parseDouble(
request.getParameter("totalFee")
);
String dueDate = request.getParameter("dueDate");
if (totalFee < 0) {
throw new IllegalArgumentException("Invalid fee amount");
}
String sql = "INSERT INTO STUDENT_FEES " +
"(FEE_ID, STUDENT_ID, TOTAL_FEE, PAID_AMOUNT, DUE_DATE) " +
"VALUES (?, ?, ?, 0, TO_DATE(?, 'YYYY-MM-DD'))";
PreparedStatement ps = con.prepareStatement(sql);
ps.setInt(1, feeId);
ps.setInt(2, studentId);
ps.setDouble(3, totalFee);
ps.setString(4, dueDate);
ps.executeUpdate();
message = "Student fee record saved successfully.";
ps.close();
}
// Search fee records
String search = request.getParameter("search");
String sql = "SELECT F.FEE_ID, F.STUDENT_ID, " +
"S.STUDENT_NAME, F.TOTAL_FEE, F.PAID_AMOUNT, " +
"(F.TOTAL_FEE - F.PAID_AMOUNT) AS DUE_AMOUNT, " +
"TO_CHAR(F.DUE_DATE, 'YYYY-MM-DD') AS DUE_DATE " +
"FROM STUDENT_FEES F " +
"JOIN STUDENTS S ON F.STUDENT_ID = S.STUDENT_ID";
PreparedStatement ps;
if (search != null && !search.trim().isEmpty()) {
sql += " WHERE TO_CHAR(F.FEE_ID) LIKE ? " +
"OR TO_CHAR(F.STUDENT_ID) LIKE ? " +
"OR LOWER(S.STUDENT_NAME) LIKE ?";
ps = con.prepareStatement(sql);
String keyword = "%" + search.trim().toLowerCase() + "%";
ps.setString(1, "%" + search.trim() + "%");
ps.setString(2, "%" + search.trim() + "%");
ps.setString(3, keyword);
} else {
ps = con.prepareStatement(sql);
}
ResultSet rs = ps.executeQuery();
while (rs.next()) {
feeRecords.add(new String[] {
rs.getString("FEE_ID"),
rs.getString("STUDENT_ID"),
rs.getString("STUDENT_NAME"),
rs.getString("TOTAL_FEE"),
rs.getString("PAID_AMOUNT"),
rs.getString("DUE_AMOUNT"),
rs.getString("DUE_DATE")
});
}
rs.close();
ps.close();
con.close();
} catch (NumberFormatException e) {
message = "Please enter valid numeric values.";
} catch (SQLIntegrityConstraintViolationException e) {
message = "Fee record could not be saved. Check the Student ID or Fee ID.";
} catch (IllegalArgumentException e) {
message = "Please enter a valid fee amount.";
} catch (Exception e) {
message = "Unable to process the fee record. Check the database connection and input information.";
}
%>
<!DOCTYPE html>
<html>
<head>
<title>Student Fee Management</title>
<meta charset="UTF-8">
</head>
<body>
<h2>Student Fee Management</h2>
<p>
<a href="index.html">Dashboard</a> |
<a href="students.html">Students</a> |
<a href="courses.html">Courses</a> |
<a href="registrations.html">Registrations</a> |
<a href="fees.html">Fees</a> |
<a href="payments.html">Payments</a> |
<a href="reports.html">Reports</a>
</p>
<hr>
<p><%= escapeHtml(message) %></p>
<h3>Search Fee Records</h3>
<form method="get" action="fees.jsp">
<input type="text" name="search"
placeholder="Search by Fee ID, Student ID or Name">
<button type="submit">Search</button>
<a href="fees.jsp">Show All</a>
</form>
<h3>Student Fee List</h3>
<table border="1" cellpadding="8">
<tr>
<th>Fee ID</th>
<th>Student ID</th>
<th>Student Name</th>
<th>Total Fee (BDT)</th>
<th>Paid Amount (BDT)</th>
<th>Due Amount (BDT)</th>
<th>Due Date</th>
</tr>
<%
for (String[] fee : feeRecords) {
%>
<tr>
<td><%= escapeHtml(fee[0]) %></td>
<td><%= escapeHtml(fee[1]) %></td>
<td><%= escapeHtml(fee[2]) %></td>
<td><%= escapeHtml(fee[3]) %></td>
<td><%= escapeHtml(fee[4]) %></td>
<td><%= escapeHtml(fee[5]) %></td>
<td><%= escapeHtml(fee[6]) %></td>
</tr>
<%
}
%>
</table>
<p>Total Fee Records: <%= feeRecords.size() %></p>
<p><a href="fees.html">Create Another Fee Record</a></p>
</body>
</html>
3. How It Works
- The administrator submits a fee record from fees.html.
- JSP receives the Fee ID, Student ID, Total Fee, and Due Date.
- Oracle checks whether the Student ID exists.
- The new fee record is inserted with an initial PAID_AMOUNT of zero.
- The JSP calculates the outstanding amount using Total Fee minus Paid Amount.
- The fee list displays the student name and financial details.
4. Important Notes
- Create the STUDENTS table before creating STUDENT_FEES.
- Payments will update PAID_AMOUNT through the payments module.
- Do not manually change PAID_AMOUNT when recording a payment.
- For a production system, use transactions and stronger validation to protect financial records.
5. Next File
payments.html – Student Fee Payment Frontend Using HTML.
University Management System – Fee Payment Frontend Using HTML
File name: payments.html
Purpose: Create a form for recording student fee payments.
1. HTML Code
Create a file named payments.html and add the following code:
<!DOCTYPE html>
<html>
<head>
<title>Fee Payment Management</title>
<meta charset="UTF-8">
</head>
<body>
<h2>University Management System</h2>
<p>
<a href="index.html">Dashboard</a> |
<a href="students.html">Students</a> |
<a href="courses.html">Courses</a> |
<a href="registrations.html">Registrations</a> |
<a href="fees.html">Fees</a> |
<a href="payments.html">Payments</a> |
<a href="reports.html">Reports</a>
</p>
<hr>
<h3>Record Student Payment</h3>
<form action="payments.jsp" method="post">
<label>Payment ID:</label><br>
<input type="number" name="paymentId" required>
<br><br>
<label>Fee ID:</label><br>
<input type="number" name="feeId" required>
<br><br>
<label>Payment Amount (BDT):</label><br>
<input type="number" name="paymentAmount"
min="0.01" step="0.01" required>
<br><br>
<label>Payment Date:</label><br>
<input type="date" name="paymentDate" required>
<br><br>
<label>Payment Method:</label><br>
<select name="paymentMethod" required>
<option value="">Select Method</option>
<option value="Cash">Cash</option>
<option value="Bank">Bank</option>
<option value="Mobile Banking">Mobile Banking</option>
<option value="Online Transfer">Online Transfer</option>
</select>
<br><br>
<button type="submit">Save Payment</button>
</form>
<hr>
<h3>Search Payment Records</h3>
<form action="payments.jsp" method="get">
<input type="text" name="search"
placeholder="Enter Payment ID or Fee ID">
<button type="submit">Search</button>
</form>
<p>
<a href="payments.jsp">View All Payments</a>
</p>
<h3>Payment Information</h3>
<p>
Every payment must be recorded against an existing Fee ID.
The system will update the student's paid amount and
outstanding balance after a successful payment.
</p>
</body>
</html>
2. Form Field Explanation
- Payment ID: Unique identifier for each payment.
- Fee ID: Identifies the student's fee record.
- Payment Amount: Amount received in BDT.
- Payment Date: Date when the payment is received.
- Payment Method: Cash, bank, mobile banking, or online transfer.
3. How It Works
- The administrator enters the payment details.
- The form sends the information to payments.jsp.
- The JSP checks the Fee ID and validates the payment amount.
- The payment is saved in the database.
- The corresponding student's PAID_AMOUNT is updated.
- The outstanding balance is recalculated.
4. Important Notes
- The Fee ID must exist in the STUDENT_FEES table.
- Payment ID must be unique.
- A payment must not exceed the remaining outstanding balance.
- The backend should use a database transaction so that payment insertion and fee balance updating succeed or fail together.
5. Next File
payments.jsp – Fee Payment Backend Using JSP and JDBC.
University Management System – Fee Payment Backend Using JSP and JDBC
File name: payments.jsp
Purpose: Save payments, update paid amounts, and display payment history.
1. Oracle Database Table
Create the PAYMENTS table:
CREATE TABLE PAYMENTS (
PAYMENT_ID NUMBER PRIMARY KEY,
FEE_ID NUMBER NOT NULL,
PAYMENT_AMOUNT NUMBER(10,2) NOT NULL,
PAYMENT_DATE DATE NOT NULL,
PAYMENT_METHOD VARCHAR2(30) NOT NULL,
CONSTRAINT FK_PAYMENT_FEE
FOREIGN KEY (FEE_ID)
REFERENCES STUDENT_FEES(FEE_ID),
CONSTRAINT CHK_PAYMENT_AMOUNT
CHECK (PAYMENT_AMOUNT > 0)
);
2. JSP Code
Create payments.jsp and add the following code:
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%@ page import="java.math.BigDecimal" %>
<%!
public String escapeHtml(String value) {
if (value == null) return "";
return value.replace("&", "&")
.replace("<", "<")
.replace(">", ">")
.replace("\"", """)
.replace("'", "'");
}
%>
<%
String message = "";
String url = "jdbc:oracle:thin:@//localhost:1521/XEPDB1";
String username = "attendance_user";
String password = "attendance123";
List<String[]> payments = new ArrayList<String[]>();
Connection con = null;
try {
Class.forName("oracle.jdbc.OracleDriver");
con = DriverManager.getConnection(url, username, password);
// Record a new payment
if ("POST".equalsIgnoreCase(request.getMethod())) {
int paymentId = Integer.parseInt(
request.getParameter("paymentId")
);
int feeId = Integer.parseInt(
request.getParameter("feeId")
);
BigDecimal paymentAmount = new BigDecimal(
request.getParameter("paymentAmount")
);
String paymentDate = request.getParameter("paymentDate");
String paymentMethod = request.getParameter("paymentMethod");
if (paymentAmount.compareTo(BigDecimal.ZERO) <= 0) {
throw new IllegalArgumentException();
}
String[] allowedMethods = {
"Cash", "Bank", "Mobile Banking", "Online Transfer"
};
boolean validMethod = false;
for (String method : allowedMethods) {
if (method.equals(paymentMethod)) {
validMethod = true;
break;
}
}
if (!validMethod) {
throw new IllegalArgumentException();
}
con.setAutoCommit(false);
// Lock the fee record before checking its balance
String checkSql =
"SELECT TOTAL_FEE, PAID_AMOUNT " +
"FROM STUDENT_FEES WHERE FEE_ID = ? FOR UPDATE";
PreparedStatement checkPs = con.prepareStatement(checkSql);
checkPs.setInt(1, feeId);
ResultSet checkRs = checkPs.executeQuery();
if (!checkRs.next()) {
throw new IllegalArgumentException();
}
BigDecimal totalFee = checkRs.getBigDecimal("TOTAL_FEE");
BigDecimal paidAmount = checkRs.getBigDecimal("PAID_AMOUNT");
checkRs.close();
checkPs.close();
BigDecimal dueAmount = totalFee.subtract(paidAmount);
if (paymentAmount.compareTo(dueAmount) > 0) {
throw new IllegalArgumentException();
}
// Insert payment
String insertSql =
"INSERT INTO PAYMENTS " +
"(PAYMENT_ID, FEE_ID, PAYMENT_AMOUNT, PAYMENT_DATE, PAYMENT_METHOD) " +
"VALUES (?, ?, ?, TO_DATE(?, 'YYYY-MM-DD'), ?)";
PreparedStatement insertPs = con.prepareStatement(insertSql);
insertPs.setInt(1, paymentId);
insertPs.setInt(2, feeId);
insertPs.setBigDecimal(3, paymentAmount);
insertPs.setString(4, paymentDate);
insertPs.setString(5, paymentMethod);
insertPs.executeUpdate();
insertPs.close();
// Update paid amount
String updateSql =
"UPDATE STUDENT_FEES " +
"SET PAID_AMOUNT = PAID_AMOUNT + ? " +
"WHERE FEE_ID = ?";
PreparedStatement updatePs = con.prepareStatement(updateSql);
updatePs.setBigDecimal(1, paymentAmount);
updatePs.setInt(2, feeId);
updatePs.executeUpdate();
updatePs.close();
con.commit();
con.setAutoCommit(true);
message = "Payment recorded successfully.";
}
// Display payment history
String search = request.getParameter("search");
String sql =
"SELECT P.PAYMENT_ID, P.FEE_ID, F.STUDENT_ID, " +
"S.STUDENT_NAME, P.PAYMENT_AMOUNT, " +
"TO_CHAR(P.PAYMENT_DATE, 'YYYY-MM-DD') AS PAY_DATE, " +
"P.PAYMENT_METHOD " +
"FROM PAYMENTS P " +
"JOIN STUDENT_FEES F ON P.FEE_ID = F.FEE_ID " +
"JOIN STUDENTS S ON F.STUDENT_ID = S.STUDENT_ID";
PreparedStatement ps;
if (search != null && !search.trim().isEmpty()) {
sql += " WHERE TO_CHAR(P.PAYMENT_ID) LIKE ? " +
"OR TO_CHAR(P.FEE_ID) LIKE ? " +
"OR TO_CHAR(F.STUDENT_ID) LIKE ? " +
"OR LOWER(S.STUDENT_NAME) LIKE ?";
ps = con.prepareStatement(sql);
String keyword = "%" + search.trim().toLowerCase() + "%";
ps.setString(1, "%" + search.trim() + "%");
ps.setString(2, "%" + search.trim() + "%");
ps.setString(3, "%" + search.trim() + "%");
ps.setString(4, keyword);
} else {
ps = con.prepareStatement(sql);
}
ResultSet rs = ps.executeQuery();
while (rs.next()) {
payments.add(new String[] {
rs.getString("PAYMENT_ID"),
rs.getString("FEE_ID"),
rs.getString("STUDENT_ID"),
rs.getString("STUDENT_NAME"),
rs.getString("PAYMENT_AMOUNT"),
rs.getString("PAY_DATE"),
rs.getString("PAYMENT_METHOD")
});
}
rs.close();
ps.close();
} catch (IllegalArgumentException e) {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex) {
// Rollback failed
}
}
message = "Invalid payment. Check the Fee ID, payment amount, date, and payment method. The payment must not exceed the outstanding balance.";
} catch (SQLIntegrityConstraintViolationException e) {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex) {
// Rollback failed
}
}
message = "Payment could not be saved. The Payment ID may already exist.";
} catch (Exception e) {
if (con != null) {
try {
con.rollback();
} catch (SQLException ex) {
// Rollback failed
}
}
message = "Unable to process payment. Please check the database connection and input information.";
} finally {
if (con != null) {
try {
con.setAutoCommit(true);
con.close();
} catch (SQLException e) {
// Connection closing failed
}
}
}
%>
<!DOCTYPE html>
<html>
<head>
<title>Fee Payment Management</title>
<meta charset="UTF-8">
</head>
<body>
<h2>Fee Payment Management</h2>
<p>
<a href="index.html">Dashboard</a> |
<a href="students.html">Students</a> |
<a href="courses.html">Courses</a> |
<a href="registrations.html">Registrations</a> |
<a href="fees.html">Fees</a> |
<a href="payments.html">Payments</a> |
<a href="reports.html">Reports</a>
</p>
<hr>
<p><%= escapeHtml(message) %></p>
<h3>Search Payment History</h3>
<form method="get" action="payments.jsp">
<input type="text" name="search"
placeholder="Search Payment ID, Fee ID or Student">
<button type="submit">Search</button>
<a href="payments.jsp">Show All</a>
</form>
<h3>Payment History</h3>
<table border="1" cellpadding="8">
<tr>
<th>Payment ID</th>
<th>Fee ID</th>
<th>Student ID</th>
<th>Student Name</th>
<th>Amount (BDT)</th>
<th>Payment Date</th>
<th>Payment Method</th>
</tr>
<%
for (String[] p : payments) {
%>
<tr>
<td><%= escapeHtml(p[0]) %></td>
<td><%= escapeHtml(p[1]) %></td>
<td><%= escapeHtml(p[2]) %></td>
<td><%= escapeHtml(p[3]) %></td>
<td><%= escapeHtml(p[4]) %></td>
<td><%= escapeHtml(p[5]) %></td>
<td><%= escapeHtml(p[6]) %></td>
</tr>
<%
}
%>
</table>
<p>Total Payments: <%= payments.size() %></p>
<p><a href="payments.html">Record Another Payment</a></p>
</body>
</html>
3. How It Works
- The administrator submits payment details from payments.html.
- JSP locks the corresponding fee record and checks the outstanding balance.
- If the payment is valid, a new record is inserted into PAYMENTS.
- The PAID_AMOUNT in STUDENT_FEES is updated.
- Both database changes are committed together.
- The payment history is displayed with student information.
4. Important Notes
- Create STUDENT_FEES before creating PAYMENTS.
- The Fee ID must exist.
- A payment cannot exceed the outstanding balance.
- Use a database transaction to avoid inconsistent financial records.
- For a real university system, add authentication, authorization, audit logs, and payment receipt generation.
5. Next File
reports.html – Student and Financial Reports Frontend Using HTML.
University Management System – Reports Frontend Using HTML
File name: reports.html
Purpose: Create a simple report dashboard for university administration.
1. HTML Code
Create a file named reports.html and add the following code:
<!DOCTYPE html>
<html>
<head>
<title>University Management Reports</title>
<meta charset="UTF-8">
</head>
<body>
<h2>University Management System</h2>
<p>
<a href="index.html">Dashboard</a> |
<a href="students.html">Students</a> |
<a href="courses.html">Courses</a> |
<a href="registrations.html">Registrations</a> |
<a href="fees.html">Fees</a> |
<a href="payments.html">Payments</a> |
<a href="reports.html">Reports</a>
</p>
<hr>
<h3>Report Management</h3>
<p>Select a report to view the corresponding information.</p>
<h3>1. Student Report</h3>
<p>View all registered students and their academic information.</p>
<a href="reports.jsp?type=students">View Student Report</a>
<hr>
<h3>2. Course Report</h3>
<p>View available courses and their credit information.</p>
<a href="reports.jsp?type=courses">View Course Report</a>
<hr>
<h3>3. Course Registration Report</h3>
<p>View students registered in different courses.</p>
<a href="reports.jsp?type=registrations">View Registration Report</a>
<hr>
<h3>4. Student Fee Report</h3>
<p>View total fees, paid amounts, and outstanding balances.</p>
<a href="reports.jsp?type=fees">View Fee Report</a>
<hr>
<h3>5. Payment Report</h3>
<p>View student payment history and payment methods.</p>
<a href="reports.jsp?type=payments">View Payment Report</a>
<hr>
<h3>6. Financial Summary</h3>
<p>View total assigned fees, collected payments, and outstanding dues.</p>
<a href="reports.jsp?type=summary">View Financial Summary</a>
</body>
</html>
2. Available Reports
- Student Report: Student ID, name, department, email, and CGPA.
- Course Report: Course ID, course name, and credit.
- Registration Report: Student and course registration details.
- Fee Report: Total fee, paid amount, and due amount.
- Payment Report: Payment ID, student, amount, date, and method.
- Financial Summary: Overall financial position of the university.
3. How It Works
- The administrator opens the Reports page.
- The administrator selects a report category.
- The selected report type is sent to reports.jsp.
- The JSP file retrieves the relevant records from Oracle Database.
- The report is displayed in a table.
4. Important Notes
- This is a static HTML frontend.
- Actual report data will come from Oracle Database through JSP.
- The report links use query parameters to select the report category.
- The next file will implement all six report types.
5. Next File
reports.jsp – Reports Backend Using JSP and JDBC.
University Management System – Reports Backend Using JSP and JDBC
File: reports.jsp
Project: University Management System
This page generates six types of reports using JSP, JDBC, and Oracle Database.
1. Create reports.jsp
Create a file named reports.jsp in your UniversityManagementSystem project folder.
2. Complete JSP Code
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>
<%
request.setCharacterEncoding("UTF-8");
String reportType = request.getParameter("type");
if (reportType == null || reportType.trim().isEmpty()) {
reportType = "students";
}
String[] headers = new String[0];
List<String[]> rows = new ArrayList<String[]>();
String message = "";
String url = "jdbc:oracle:thin:@//localhost:1521/XEPDB1";
String dbUser = "attendance_user";
String dbPassword = "attendance123";
String sql = "";
try {
Class.forName("oracle.jdbc.OracleDriver");
try (Connection con = DriverManager.getConnection(
url, dbUser, dbPassword)) {
if ("students".equals(reportType)) {
headers = new String[]{
"Student ID", "Student Name", "Department",
"Email", "Phone", "CGPA"
};
sql = "SELECT STUDENT_ID, STUDENT_NAME, DEPARTMENT, " +
"EMAIL, PHONE, CGPA FROM STUDENTS ORDER BY STUDENT_ID";
} else if ("courses".equals(reportType)) {
headers = new String[]{
"Course ID", "Course Name", "Credit"
};
sql = "SELECT COURSE_ID, COURSE_NAME, CREDIT " +
"FROM COURSES ORDER BY COURSE_ID";
} else if ("registrations".equals(reportType)) {
headers = new String[]{
"Registration ID", "Student ID", "Student Name",
"Course ID", "Course Name", "Registration Date"
};
sql = "SELECT R.REGISTRATION_ID, R.STUDENT_ID, " +
"S.STUDENT_NAME, R.COURSE_ID, C.COURSE_NAME, " +
"TO_CHAR(R.REGISTRATION_DATE, 'YYYY-MM-DD') " +
"FROM COURSE_REGISTRATIONS R " +
"JOIN STUDENTS S ON R.STUDENT_ID = S.STUDENT_ID " +
"JOIN COURSES C ON R.COURSE_ID = C.COURSE_ID " +
"ORDER BY R.REGISTRATION_ID";
} else if ("fees".equals(reportType)) {
headers = new String[]{
"Fee ID", "Student ID", "Student Name",
"Total Fee", "Paid Amount", "Due Amount", "Due Date"
};
sql = "SELECT F.FEE_ID, F.STUDENT_ID, S.STUDENT_NAME, " +
"F.TOTAL_FEE, F.PAID_AMOUNT, " +
"(F.TOTAL_FEE - F.PAID_AMOUNT), " +
"TO_CHAR(F.DUE_DATE, 'YYYY-MM-DD') " +
"FROM STUDENT_FEES F " +
"JOIN STUDENTS S ON F.STUDENT_ID = S.STUDENT_ID " +
"ORDER BY F.FEE_ID";
} else if ("payments".equals(reportType)) {
headers = new String[]{
"Payment ID", "Fee ID", "Student ID",
"Student Name", "Payment Amount",
"Payment Date", "Payment Method"
};
sql = "SELECT P.PAYMENT_ID, P.FEE_ID, F.STUDENT_ID, " +
"S.STUDENT_NAME, P.PAYMENT_AMOUNT, " +
"TO_CHAR(P.PAYMENT_DATE, 'YYYY-MM-DD'), " +
"P.PAYMENT_METHOD " +
"FROM PAYMENTS P " +
"JOIN STUDENT_FEES F ON P.FEE_ID = F.FEE_ID " +
"JOIN STUDENTS S ON F.STUDENT_ID = S.STUDENT_ID " +
"ORDER BY P.PAYMENT_ID";
} else if ("summary".equals(reportType)) {
headers = new String[]{
"Total Students", "Total Courses",
"Total Registrations", "Total Fees",
"Total Paid", "Total Due"
};
sql = "SELECT " +
"(SELECT COUNT(*) FROM STUDENTS), " +
"(SELECT COUNT(*) FROM COURSES), " +
"(SELECT COUNT(*) FROM COURSE_REGISTRATIONS), " +
"NVL((SELECT SUM(TOTAL_FEE) FROM STUDENT_FEES), 0), " +
"NVL((SELECT SUM(PAID_AMOUNT) FROM STUDENT_FEES), 0), " +
"NVL((SELECT SUM(TOTAL_FEE - PAID_AMOUNT) " +
"FROM STUDENT_FEES), 0) FROM DUAL";
} else {
message = "Invalid report type.";
}
if (message.isEmpty()) {
try (PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
ResultSetMetaData meta = rs.getMetaData();
int columnCount = meta.getColumnCount();
while (rs.next()) {
String[] row = new String[columnCount];
for (int i = 0; i < columnCount; i++) {
Object value = rs.getObject(i + 1);
row[i] = (value == null) ? "" : value.toString();
}
rows.add(row);
}
}
}
}
} catch (Exception e) {
message = "Unable to generate the report. Check database connection and tables.";
application.log("Reports JSP error", e);
}
%>
<%!
public String escapeHtml(String value) {
if (value == null) {
return "";
}
return value.replace("&", "&")
.replace("<", "<")
.replace(">", ">")
.replace("\"", """)
.replace("'", "'");
}
%>
<!DOCTYPE html>
<html>
<head>
<title>University Management System - Reports</title>
<style>
body {
font-family: Arial, sans-serif;
margin: 20px;
}
h2 {
color: #1d3557;
}
a {
margin-right: 10px;
}
table {
width: 100%;
border-collapse: collapse;
margin-top: 20px;
}
th, td {
border: 1px solid #ccc;
padding: 9px;
text-align: left;
}
th {
background: #eeeeee;
}
.message {
color: red;
margin-top: 15px;
}
</style>
</head>
<body>
<h2>University Management System - Reports</h2>
<p>
<a href="index.jsp">Dashboard</a>
<a href="students.jsp">Students</a>
<a href="courses.jsp">Courses</a>
<a href="registrations.jsp">Registrations</a>
<a href="fees.jsp">Fees</a>
<a href="payments.jsp">Payments</a>
</p>
<hr>
<h3>Select a Report</h3>
<p>
<a href="reports.jsp?type=students">Student Report</a> |
<a href="reports.jsp?type=courses">Course Report</a> |
<a href="reports.jsp?type=registrations">Registration Report</a> |
<a href="reports.jsp?type=fees">Fee Report</a> |
<a href="reports.jsp?type=payments">Payment Report</a> |
<a href="reports.jsp?type=summary">Summary Report</a>
</p>
<h3>
<%= reportType.substring(0, 1).toUpperCase() +
reportType.substring(1) %> Report
</h3>
<%
if (!message.isEmpty()) {
%>
<p class="message"><%= message %></p>
<%
} else if (rows.isEmpty()) {
%>
<p>No records found.</p>
<%
} else {
%>
<table>
<tr>
<%
for (String header : headers) {
%>
<th><%= header %></th>
<%
}
%>
</tr>
<%
for (String[] row : rows) {
%>
<tr>
<%
for (String value : row) {
%>
<td><%= escapeHtml(value) %></td>
<%
}
%>
</tr>
<%
}
%>
</table>
<%
}
%>
</body>
</html>
3. Available Reports
- Student Report: Displays all student information.
- Course Report: Displays available courses.
- Registration Report: Shows student-course registrations.
- Fee Report: Displays total fees, paid amounts, and outstanding dues.
- Payment Report: Displays individual payment records.
- Summary Report: Displays overall university statistics.
4. How to Run
- Make sure all required Oracle database tables are created.
- Place the Oracle JDBC driver JAR file in the Tomcat lib folder.
- Update the database username and password in the JSP file.
- Start the Tomcat server.
- Open reports.jsp through your project URL.
5. Important Notes
This is an educational project example. A production system should include login-based access control, pagination, stronger error handling, and secure database credential management.
Next Step: Test all six report types after inserting sample records into the database.
কোন মন্তব্য নেই:
একটি মন্তব্য পোস্ট করুন