Dashboard Library Management System Project in Java | JSP, Servlet, MySQL

 

1. Created one servlate for the controller .

Set the route:-

1 Created the controller to add the HTML page:-

/libraryManagementSystem/src/main/java/com/lms/controller/DashboardController.java

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

String action = request.getParameter("action");

if("viewDashboard".equalsIgnoreCase(action)) {

RequestDispatcher dispacher = request.getRequestDispatcher("jsp/dashboard.jsp");

dispacher.forward(request, response);

}else {

System.out.println("No action found");

}

}

2 Set the HTML inside the JSP using Dashboard,jsp

/libraryManagementSystem/src/main/webapp/jsp/dashboard.jsp

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

pageEncoding="UTF-8"%>

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

<%@ include file ="include/header.jsp" %>

<div class="container-fluid">

<div class="row">

<%@ include file ="include/sidebar.jsp" %>

<main class="col-md-9 ms-sm-auto col-lg-10 px-md-4">

<div class="page-header">

<h1 class="h2"><i class="bi bi-speedometer2 me-2"></i>Dashboard</h1>

</div>

<!-- Statistics Cards -->

<div class="row mb-4">

<div class="col-md-3">

<div class="card text-white stat-card primary mb-3">

<div class="card-body">

<i class="bi bi-book-fill stat-icon"></i>

<h5 class="card-title">Total Books</h5>

<p class="card-text">120</p>

</div>

</div>

</div>

<div class="col-md-3">

<div class="card text-white stat-card warning mb-3">

<div class="card-body">

<i class="bi bi-arrow-right-circle-fill stat-icon"></i>

<h5 class="card-title">Books Assigned</h5>

<p class="card-text">45</p>

</div>

</div>

</div>

<div class="col-md-3">

<div class="card text-white stat-card success mb-3">

<div class="card-body">

<i class="bi bi-arrow-left-circle-fill stat-icon"></i>

<h5 class="card-title">Books Returned</h5>

<p class="card-text">40</p>

</div>

</div>

</div>

<div class="col-md-3">

<div class="card text-white stat-card info mb-3">

<div class="card-body">

<i class="bi bi-people-fill stat-icon"></i>

<h5 class="card-title">Users</h5>

<p class="card-text">20</p>

</div>

</div>

</div>

</div>

<!-- Recent Books Table -->

<h4 class="section-title">Currently Issued Books</h4>

<div class="table-container">

<div class="table-responsive">

<table class="table table-hover align-middle">

<thead>

<tr>

<th>#</th>

<th>Title</th>

<th>Author</th>

<th>Category</th>

<th>ISBN</th>

<th>Due Date</th>

</tr>

</thead>

<tbody>

<tr>

<td><span class="badge bg-primary">1</span></td>

<td><strong>The Great Gatsby</strong></td>

<td>F. Scott Fitzgerald</td>

<td><span class="badge bg-info">Fiction</span></td>

<td><code>9780743273565</code></td>

<td><span class="badge bg-warning">12-08-2025</span></td>

</tr>

<tr>

<td><span class="badge bg-primary">2</span></td>

<td><strong>Clean Code</strong></td>

<td>Robert C. Martin</td>

<td><span class="badge bg-success">Technology</span></td>

<td><code>9780132350884</code></td>

<td><span class="badge bg-warning">15-08-2025</span></td>

</tr>

</tbody>

</table>

</div>

</div>

</main>

</div>

</div>

Get Dynamic Data:-

1. Create the pogo.

/libraryManagementSystem/src/main/java/com/lms/pojo/DashboardStats.java

public class DashboardStats {

private int totalBook;

private int booksAssigned;

private int booksReturned;

private int totalUsers;

public int getTotalBook() {

return totalBook;

}

public void setTotalBook(int totalBook) {

this.totalBook = totalBook;

}

public int getBooksAssigned() {

return booksAssigned;

}

public void setBooksAssigned(int booksAssigned) {

this.booksAssigned = booksAssigned;

}

public int getBooksReturned() {

return booksReturned;

}

public void setBooksReturned(int booksReturned) {

this.booksReturned = booksReturned;

}

public int getTotalUsers() {

return totalUsers;

}

public void setTotalUsers(int totalUsers) {

this.totalUsers = totalUsers;

}

@Override

public String toString() {

return "DashboardStats [totalBook=" + totalBook + ", booksAssigned=" + booksAssigned + ", booksReturned="

+ booksReturned + ", totalUsers=" + totalUsers + "]";

}

}

2. Create the Dao interface.

/libraryManagementSystem/src/main/java/com/lms/dao/DashboardDao.java

import com.lms.pojo.DashboardStats;

public interface DashboardDao {

public DashboardStats fetchDashboardStats();

}

3. Create the Dao implementations.

import java.sql.Connection;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.util.ArrayList;

import com.lms.dao.DashboardDao;

import com.lms.pojo.Book;

import com.lms.pojo.DashboardStats;

import com.lms.util.DbUtil;

public class DashboardDaoImpl implements DashboardDao{

private final String TOTAL_BOOK = "Select COUNT(*) from books";

private final String TOTAL_USER = "Select COUNT(*) from users where role = 'USER'";

// private final String BOOK_ASSIGNED = "Select COUNT(*) from book_issued WHERE status = 'ISSUED'";

// private final String TOTAL_RETURN = "Select COUNT(*) from book_issued WHERE status = 'RETURN'";

private final String BOOK_STATUS = "Select COUNT(*) from book_issued WHERE status = ? ";

public int fetchCount(String sql) {

return fetchCount(sql, null);

}

private int fetchCount(String sql, String status) {

Connection con = null ;

PreparedStatement prepareStatement = null;

ResultSet rs = null;

int count = 0;

try {

con = DbUtil.createConnection();

prepareStatement = con.prepareStatement(sql);

if(status != null) {

prepareStatement.setString(1, status);

}

rs = prepareStatement.executeQuery();

if(rs.next()) {

count = rs.getInt(1);

}

} catch (Exception e) {

e.printStackTrace();

}finally {

try {

if(rs != null) {

rs.close();

}

if(prepareStatement != null) {

prepareStatement.close();

}

if(con != null) {

con.close();

}

}catch (SQLException e) {

e.printStackTrace();

}

}

return count;

}

public DashboardStats fetchDashboardStats() {

int totalBook = fetchCount(TOTAL_BOOK);

int totalUser = fetchCount(TOTAL_USER);

int bookAssigned = fetchCount(BOOK_STATUS, "ISSUED");

int bookReturned = fetchCount(BOOK_STATUS, "RETURN");

DashboardStats dashbordStates = new DashboardStats();

dashbordStates.setTotalBook(totalBook);

dashbordStates.setTotalUsers(totalUser);

dashbordStates.setBooksAssigned(bookAssigned);

dashbordStates.setBooksReturned(bookReturned);

return dashbordStates;

}

}

4. Create the Dao Service interface.

/libraryManagementSystem/src/main/java/com/lms/service/DashboardService.java

import com.lms.pojo.DashboardStats;

public interface DashboardService {

public DashboardStats fetchDashboardStats();

}

5. Create the Dao Service call to the controller.

/libraryManagementSystem/src/main/java/com/lms/servicelmpl/DashboardServiceImpl.java

import com.lms.dao.DashboardDao;

import com.lms.daoImpl.DashboardDaoImpl;

import com.lms.pojo.DashboardStats;

import com.lms.service.DashboardService;

public class DashboardServiceImpl implements DashboardService {

private DashboardDao dashboardDao = new DashboardDaoImpl();

@Override

public DashboardStats fetchDashboardStats() {

DashboardStats stats = dashboardDao.fetchDashboardStats();

return stats != null ? stats : new DashboardStats();

}

}

5. Create the controller to pass the data inside the JSP.

/libraryManagementSystem/src/main/java/com/lms/controller/DashboardController.java

/**

* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)

*/

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {

String action = request.getParameter("action");

if("viewDashboard".equalsIgnoreCase(action)) {

DashboardService dashboardService = new DashboardServiceImpl();

DashboardStats dashboardStats = dashboardService.fetchDashboardStats();

System.out.println(dashboardStats);

request.setAttribute("dashboardStats", dashboardStats);

RequestDispatcher dispacher = request.getRequestDispatcher("jsp/dashboard.jsp");

dispacher.forward(request, response);

}else {

System.out.println("No action found");

}

}

6. Get the data inside the JSP file.

${dashboardStats}

7. Dynamic HTML.

/libraryManagementSystem/src/main/webapp/jsp/dashboard.jsp

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

pageEncoding="UTF-8"%>

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

<%@ include file ="include/header.jsp" %>

<div class="container-fluid">

<div class="row">

<%@ include file ="include/sidebar.jsp" %>

<!-- ${dashboardStats} -->

<main class="col-md-9 ms-sm-auto col-lg-10 px-md-4">

<div class="page-header">

<h1 class="h2"><i class="bi bi-speedometer2 me-2"></i>Dashboard</h1>

</div>

<!-- Statistics Cards -->

<div class="row mb-4">

<div class="col-md-3">

<div class="card text-white stat-card primary mb-3">

<div class="card-body">

<i class="bi bi-book-fill stat-icon"></i>

<h5 class="card-title">Total Books</h5>

<p class="card-text">${dashboardStats.totalBook}</p>

</div>

</div>

</div>

<div class="col-md-3">

<div class="card text-white stat-card warning mb-3">

<div class="card-body">

<i class="bi bi-arrow-right-circle-fill stat-icon"></i>

<h5 class="card-title">Books Assigned</h5>

<p class="card-text">${dashboardStats.booksAssigned}</p>

</div>

</div>

</div>

<div class="col-md-3">

<div class="card text-white stat-card success mb-3">

<div class="card-body">

<i class="bi bi-arrow-left-circle-fill stat-icon"></i>

<h5 class="card-title">Books Returned</h5>

<p class="card-text">${dashboardStats.booksReturned}</p>

</div>

</div>

</div>

<div class="col-md-3">

<div class="card text-white stat-card info mb-3">

<div class="card-body">

<i class="bi bi-people-fill stat-icon"></i>

<h5 class="card-title">Users</h5>

<p class="card-text">${dashboardStats.totalUsers}</p>

</div>

</div>

</div>

</div>

<!-- Recent Books Table -->

<h4 class="section-title">Currently Issued Books</h4>

<div class="table-container">

<div class="table-responsive">

<table class="table table-hover align-middle">

<thead>

<tr>

<th>#</th>

<th>Title</th>

<th>Author</th>

<th>Category</th>

<th>ISBN</th>

<th>Due Date</th>

</tr>

</thead>

<tbody>

<tr>

<td><span class="badge bg-primary">1</span></td>

<td><strong>The Great Gatsby</strong></td>

<td>F. Scott Fitzgerald</td>

<td><span class="badge bg-info">Fiction</span></td>

<td><code>9780743273565</code></td>

<td><span class="badge bg-warning">12-08-2025</span></td>

</tr>

<tr>

<td><span class="badge bg-primary">2</span></td>

<td><strong>Clean Code</strong></td>

<td>Robert C. Martin</td>

<td><span class="badge bg-success">Technology</span></td>

<td><code>9780132350884</code></td>

<td><span class="badge bg-warning">15-08-2025</span></td>

</tr>

</tbody>

</table>

</div>

</div>

</main>

</div>

</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>

</body>

</html>

Currently Issued Books table Data:-

Step 1:- Created the interface Bookdao.

public interface BookDao {

public List<BookIssued> getIssuedBookListForDashboard();

}

Step 2:- Bookdao implementation.

@Override

public List<BookIssued> getIssuedBookListForDashboard() {

Connection con = null ;

PreparedStatement prepareStatement = null;

ResultSet rs = null;

List<BookIssued> issuedList = new ArrayList<>();

try {

String sql = "SELECT bi.issue_id, bi.book_id, bi.user_id, bi.issue_date, bi.due_date, bi.return_date, bi.status, "

+ "bi.book_condition, bi.assignment_notes, bi.return_notes, "

+ "u.first_name, u.last_name, u.email, u.address, u.phone_no, "

+ "b.title, b.author, b.category, b.isbn, b.publisher, b.total_copies, b.available_copies "

+ "FROM book_issued bi "

+ "JOIN books b ON bi.book_id = b.book_id "

+ "JOIN users u ON bi.user_id = u.user_id "

+ " WHERE bi.status = 'ISSUED' "

+ "LIMIT 10";

con = DbUtil.createConnection();

prepareStatement = con.prepareStatement(sql);

rs = prepareStatement.executeQuery();

while(rs.next()) {

issuedList.add(mapIssuedRecord(rs));

}

}catch(Exception e) {

e.printStackTrace();

}

return issuedList;

}

Step 3:- Interface for Book the DAO service.

public interface BookService {

public List<BookIssued> getIssuedBookListForDashboard();

}

Step 4:- Book the DAO service for the call controller.

@Override

public List<BookIssued> getIssuedBookListForDashboard() {

return bookDao.getIssuedBookListForDashboard();

}

Step 5:- Controller code for show list.

BookService bookService = new BookServiceImpl();

List<BookIssued> issuedList = bookService.getAllIssuedBookList();

//System.out.println(issuedList);

if(issuedList != null && issuedList.size() > 0) {

LocalDate today = LocalDate.now();

for(BookIssued bookIssued : issuedList ) {

LocalDate dueDate = bookIssued.getDueDate();

if(dueDate.isBefore(today)) {

bookIssued.setDueDayStatus("Overdue");

}

else if(dueDate.isEqual(today)) {

bookIssued.setDueDayStatus("Due Today");

}

else {

bookIssued.setDueDayStatus("On Time");

}

}

request.setAttribute("issuedList", issuedList);

}

Step 6:- Pass the book-issued data in html or jsp file and show the dynamic table.

/libraryManagementSystem/src/main/webapp/jsp/dashboard.jsp

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

<%@ include file ="include/header.jsp" %>

<div class="container-fluid">

<div class="row">

<%@ include file ="include/sidebar.jsp" %>

<!-- ${dashboardStats} -->

<main class="col-md-9 ms-sm-auto col-lg-10 px-md-4">

<div class="page-header">

<h1 class="h2"><i class="bi bi-speedometer2 me-2"></i>Dashboard</h1>

</div>

<!-- Statistics Cards -->

<div class="row mb-4">

<div class="col-md-3">

<div class="card text-white stat-card primary mb-3">

<div class="card-body">

<i class="bi bi-book-fill stat-icon"></i>

<h5 class="card-title">Total Books</h5>

<p class="card-text"><c:out value="${dashboardStats.totalBook}" default="0" /></p>

</div>

</div>

</div>

<div class="col-md-3">

<div class="card text-white stat-card warning mb-3">

<div class="card-body">

<i class="bi bi-arrow-right-circle-fill stat-icon"></i>

<h5 class="card-title">Books Assigned</h5>

<p class="card-text"><c:out value="${dashboardStats.booksAssigned}" default="0" /></p>

</div>

</div>

</div>

<div class="col-md-3">

<div class="card text-white stat-card success mb-3">

<div class="card-body">

<i class="bi bi-arrow-left-circle-fill stat-icon"></i>

<h5 class="card-title">Books Returned</h5>

<p class="card-text"><c:out value="${dashboardStats.booksReturned}" default="0" /></p>

</div>

</div>

</div>

<div class="col-md-3">

<div class="card text-white stat-card info mb-3">

<div class="card-body">

<i class="bi bi-people-fill stat-icon"></i>

<h5 class="card-title">Users</h5>

<p class="card-text"><c:out value="${dashboardStats.totalUsers}" default="0" /></p>

</div>

</div>

</div>

</div>

<!-- Recent Books Table -->

<c:choose>

<c:when test="${not empty issuedList}">

<h4 class="section-title">Currently Issued Books</h4>

<div class="table-container">

<div class="table-responsive">

<table class="table table-hover align-middle">

<thead>

<tr>

<th>#</th>

<th>Title</th>

<th>Author</th>

<th>Category</th>

<th>ISBN</th>

<th>Due Date</th>

</tr>

</thead>

<tbody>

<tr>

<td><span class="badge bg-primary">1</span></td>

<td><strong>The Great Gatsby</strong></td>

<td>F. Scott Fitzgerald</td>

<td><span class="badge bg-info">Fiction</span></td>

<td><code>9780743273565</code></td>

<td><span class="badge bg-warning">12-08-2025</span></td>

</tr>

<tr>

<td><span class="badge bg-primary">2</span></td>

<td><strong>Clean Code</strong></td>

<td>Robert C. Martin</td>

<td><span class="badge bg-success">Technology</span></td>

<td><code>9780132350884</code></td>

<td><span class="badge bg-warning">15-08-2025</span></td>

</tr>

<c:forEach var="issueBook" items="${issuedList}" varStatus="status">

<tr>

<td><span class="badge bg-primary">${status.index + 1 }</span></td>

<td><strong><c:out value="${issueBook.book.title}" /></strong></td>

<td><c:out value="${issueBook.book.author}" /></td>

<td><span class="badge ${issueBook.book.category == 'technology' ? 'bg-success' : issueBook.book.category == 'science' ? 'bg-danger' : 'bg-info'}" style="text-transform: capitalize"><c:out value="${issueBook.book.category}" /></span></td>

<td><code><c:out value="${issueBook.book.isbn}" /></code></td>

<td><span class="badge bg-warning">${issueBook.dueDate}</span></td>

</tr>

</c:forEach>

</tbody>

</table>

</div>

</div>

</c:when>

<c:otherwise>

<h6>No Data Found</h6>

</c:otherwise>

</c:choose>

</main>

</div>

</div>

<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/js/bootstrap.bundle.min.js"></script>

</body>

</html>

OUTPUT:-

Login & logout functionality:-

Login:- 

Step 1:- Put the username and password or click the sign-up button

<form action="AuthenticationController" method="post">      

Session Start:- 

Step 2:- Check the username and password and start the Session

if("checklogin".equalsIgnoreCase(action)) {

String username = request.getParameter("username");

String password = request.getParameter("password");

UserService userService = new UserServiceImpl();

User user = userService.checkLogin(username, password);

if(user != null) {

HttpSession session = request.getSession();

session.setAttribute("user", user);

response.sendRedirect("DashboardController?action=viewDashboard");

}else {

// System.out.println("No User Found");

request.setAttribute("errorMessage", "Invalid username or password");

RequestDispatcher dispacher = request.getRequestDispatcher("jsp/login.jsp");

dispacher.forward(request, response);

}

}

Step 3:- Show the username first name which is Session.

<span class="text-white me-3">

Welcome, ${user.firstName}

</span>

OutPut:-

Destroy the Session:- 

Step 4:- Now we can click on the Sign out button.

<div class="nav-item text-nowrap">

<a class="nav-link px-3" href="AuthenticationController?action=signOut">Sign out</a>

</div>

Step 5:- Check if the session is not null the Destroy the session and redirect to login page.

else if("signOut".equalsIgnoreCase(action)) {

HttpSession session = request.getSession();

if(session != null) {

session.invalidate();

}

RequestDispatcher dispacher = request.getRequestDispatcher("jsp/login.jsp");

dispacher.forward(request, response);

}

Output:-

Maintain the Session:- 

Step 6:- The problem is that without a login, we are able to access all pages.

http://localhost:8080/libraryManagementSystem/UserController?action=allUserList http://localhost:8080/libraryManagementSystem/UserController?action=showAddUser http://localhost:8080/libraryManagementSystem/BookController?action=allBookList http://localhost:8080/libraryManagementSystem/BookController?action=showAddBook

                                 Made filter for Sessions page:- 

Step 1:- Create the new package for the filter

Step 2:- Create the Class. The name of the AuthFilter

Missing code:-
src/main/java/com/lms/filter/AuthFilter.java

package com.lms.filter;

import java.io.IOException;

import javax.servlet.Filter;

import javax.servlet.FilterChain;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.ServletRequest;

import javax.servlet.ServletResponse;

import javax.servlet.annotation.WebFilter;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpSession;

public class AuthFilter implements Filter {

@Override

public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)

throws IOException, ServletException {

HttpServletRequest httpServletReqquest = (HttpServletRequest) request;

String url = httpServletReqquest.getRequestURI();

HttpSession session = httpServletReqquest.getSession();

boolean loggedIn = (session != null && session.getAttribute("user") != null);

boolean allowedUrl = url.equals(httpServletReqquest.getContextPath() + "/");

if(loggedIn || allowedUrl) {

chain.doFilter(request, response);

}

else {

RequestDispatcher dispacher = request.getRequestDispatcher("jsp/login.jsp");

dispacher.forward(request, response);

}

}

} Step 3:- Now, the class is also not called, please add the annotation.

@WebFilter("/*") //that annotation denoted the class will run first.

src/main/java/com/lms/filter/AuthFilter.java

Final code:-

package com.lms.filter;

import java.io.IOException;

import javax.servlet.Filter;

import javax.servlet.FilterChain;

import javax.servlet.RequestDispatcher;

import javax.servlet.ServletException;

import javax.servlet.ServletRequest;

import javax.servlet.ServletResponse;

import javax.servlet.annotation.WebFilter;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpSession;

@WebFilter("/*")

public class AuthFilter implements Filter {

@Override

public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)

throws IOException, ServletException {

HttpServletRequest httpServletReqquest = (HttpServletRequest) request;

String url = httpServletReqquest.getRequestURI();

HttpSession session = httpServletReqquest.getSession();

boolean loggedIn = (session != null && session.getAttribute("user") != null);

boolean allowedUrl = url.equals(httpServletReqquest.getContextPath() + "/");

if(loggedIn || allowedUrl) {

chain.doFilter(request, response);

}

else {

RequestDispatcher dispacher = request.getRequestDispatcher("jsp/login.jsp");

dispacher.forward(request, response);

}

}

}

Step 3:- Session is working, it will redirect to the login page when the session not created.

Step 3:- Session block all pages so we need to allow the required more URL "/AuthenticationController".

src/main/java/com/lms/filter/AuthFilter.java

@WebFilter("/*")

public class AuthFilter implements Filter {

@Override

public void doFilter(ServletRequest request, ServletResponse response, FilterChain chain)

throws IOException, ServletException {

HttpServletRequest httpServletReqquest = (HttpServletRequest) request;

String url = httpServletReqquest.getRequestURI();

HttpSession session = httpServletReqquest.getSession();

boolean loggedIn = (session != null && session.getAttribute("user") != null);

boolean allowedUrl = url.equals(httpServletReqquest.getContextPath() + "/") || url.equals(httpServletReqquest.getContextPath() + "/AuthenticationController");

if(loggedIn || allowedUrl) {

chain.doFilter(request, response);

}

else {

RequestDispatcher dispacher = request.getRequestDispatcher("jsp/login.jsp");

dispacher.forward(request, response);

}

}

} Output:-

Full Database Query:-

CREATE DATABASE IF NOT EXISTS library_db DEFAULT CHARACTER SET utf8mb4; USE library_db; CREATE TABLE IF NOT EXISTS users ( user_id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, email VARCHAR(120) NOT NULL UNIQUE, password VARCHAR(120), role VARCHAR(120) NOT NULL DEFAULT 'USER', phone_no VARCHAR(120) NOT NULL, address text(120), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS books ( book_id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(250) NOT NULL, author VARCHAR(250) NOT NULL, category VARCHAR(120), isbn VARCHAR(30), publisher VARCHAR(250), total_copies INT NOT NULL, available_copies INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); CREATE TABLE IF NOT EXISTS book_issued ( issue_id INT AUTO_INCREMENT PRIMARY KEY, book_id INT NOT NULL, user_id INT NOT NULL, issue_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, due_date TIMESTAMP NULL, return_date TIMESTAMP NULL, status VARCHAR(20) NOT NULL, book_condition VARCHAR(1024), assignment_notes VARCHAR(1024), return_notes VARCHAR(1024), CONSTRAINT fk_book FOREIGN KEY (book_id) REFERENCES books(book_id), CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(user_id) ); -- Seed an admin user (password should be re-hashed in production) INSERT INTO users (first_name, last_name, email, password, role, phone_no) VALUES ('Admin', 'test', 'admin@library.com', 'admin123', 'ADMIN', "84848454545") ON DUPLICATE KEY UPDATE email = email; SELECT * FROM books; SELECT * FROM users; SELECT * FROM book_issued; select b.title, b.author, bi.issue_date, bi.due_date, u.first_name, u.last_name, u.email from book_Issued bi join books b ON b.book_id = bi.book_id join users u ON u.user_id = bi.user_id where bi.issue_id = 2; UPDATE book_issued SET return_date = '2026-04-03', book_condiion = 'fair', return_notes = 'test', status = 'RETURN' WHERE issue_id = 1