Library Management System

A console-based Library Management System built with Java, JDBC, and MySQL.

The project was built to practice developing a database-backed Java application while keeping responsibilities separated between the application, service, DAO, model, connection, and utility layers.

Overview

The Library Management System provides a menu-driven console interface for managing books, library members, and book issue transactions.

The application supports:

• Adding books
• Viewing all books
• Searching for a book by ID
• Deleting books
• Adding members
• Viewing members
• Issuing books
• Returning books
• Viewing book issue records
• Tracking available and total book copies
• Validating return dates
• Applying a fixed overdue fine
• Retrieving detailed issue information using SQL joins

The application stores its data in a MySQL database named library_db.

---

Features

Book Management

The book management functionality is implemented through BookService and BookDao.

A book contains:

• ID
• Title
• Category
• Publication year
• Total copies
• Available copies

Available operations include:

• Add a book
• View all books
• Search for a book by ID
• Delete a book

When a new book is added, its available-copy count is initially set to its total-copy count.

Member Management

Members are represented by the Member model and managed through MemberService and MemberDao.

A member contains:

• ID
• Name
• Email
• Phone
• Join date

The application supports:

• Creating a member
• Viewing all members

Book Transactions

The transaction functionality handles issuing and returning books.

A transaction stores:

• Transaction ID
• Book ID
• Member ID
• Issue date
• Return date
• Fine

When issuing a book, the application:

1. Displays the available members.
2. Accepts a member ID.
3. Displays the books.
4. Accepts a book ID.
5. Gets the current date.
6. Accepts a return date.
7. Validates the return date.
8. Creates the transaction.
9. Updates the book's available-copy count.

The application can also retrieve detailed information about a transaction, including the member name, phone number, book title, issue date, and return date.

Date and Fine Handling

The DateAndTime utility class uses Java's LocalDate and DateTimeFormatter.

Dates use the following format:

yyyy-MM-dd

The application validates that a requested return date is later than the current date.

The transaction DAO also checks existing transactions for overdue return dates. An overdue transaction is assigned a fixed fine of 200.

---

Technology Stack

The project uses the following technologies and Java features:

Technology / Concept	Usage
Java	Main programming language
JDK 19	Project SDK
JDBC	Database connectivity
MySQL	Persistent database
MySQL Connector/J 9.4.0	JDBC driver
PreparedStatement	Parameterized SQL queries
ResultSet	Reading database results
ArrayList / List	Storing retrieved records
Iterator	Processing transaction records
LocalDate	Date handling
DateTimeFormatter	Date formatting and parsing
Try-with-resources	JDBC resource management
IntelliJ IDEA project configuration	Project development environment

The repository does not use Spring Boot, Hibernate, JPA, REST APIs, Maven, or Gradle.

---

Project Architecture

The source code is organized into separate packages:

src/
├── app/
│   └── LibraryManagementSystem.java
│
├── connection/
│   └── DBConnection.java
│
├── dao/
│   ├── BookDao.java
│   ├── MemberDao.java
│   └── TransactionDao.java
│
├── model/
│   ├── Book.java
│   ├── BookIssue.java
│   ├── Member.java
│   └── Transactions.java
│
├── services/
│   ├── BookService.java
│   ├── MemberService.java
│   └── TransactionService.java
│
└── util/
    └── DateAndTime.java

app

LibraryManagementSystem.java is the application's entry point.

It creates the console menu and directs the selected operation to the appropriate service.

connection

DBConnection.java contains the database connection logic.

It uses:

jdbc:mysql://localhost:3306/library_db

and DriverManager.getConnection() to establish a JDBC connection.

dao

The DAO classes contain database access logic.

BookDao

Responsible for book-related database operations such as:

• Retrieving books
• Adding books
• Searching for books
• Deleting books
• Updating available book quantities

MemberDao

Responsible for:

• Retrieving members
• Adding members
• Searching for members

TransactionDao

Responsible for:

• Creating transactions
• Retrieving transactions
• Retrieving detailed issue records
• Updating overdue fines
• Removing returned-book transactions
• Updating book quantities during issue/return operations

model

The model classes represent data used by the application.

Book

Represents a library book.

Member

Represents a library member.

Transactions

Represents a book issue transaction.

BookIssue

Represents the combined information displayed for a detailed issue record.

This object is populated from a SQL query joining the transactions, book, and member tables.

services

The service classes sit between the console application and DAO classes.

BookService

Handles book-related application operations and user input.

MemberService

Handles member creation and member display.

TransactionService

Coordinates book issue, return, transaction display, and transaction detail operations.

util

DateAndTime.java provides reusable date functionality.

It contains methods for:

• Getting the current date
• Comparing dates
• Checking whether a return date has passed

---

Application Flow

The application starts in:

public static void main(String[] args)

inside LibraryManagementSystem.

A Scanner reads the user's menu selection.

The main menu provides:

1. Add Book
2. View Books
3. Issue Book
4. Return Book
5. Delete Book
6. Search Book
7. Book Issue Records

8. Add Member
9. View Members

10. Exit

The main class does not directly execute SQL.

Instead, it creates the appropriate service object:

case 1:
    new BookService().addBook(input);
    break;

case 3:
    new TransactionService().issueBook(input);
    break;

case 8:
    new MemberService().createMember(input);
    break;

The service then performs the required application logic and calls the appropriate DAO.

This gives the application a simple flow:

Console Application
        |
        v
   Service Layer
        |
        v
     DAO Layer
        |
        v
    JDBC / SQL
        |
        v
      MySQL

---

Database

The Java source connects to a MySQL database called:

library_db

The source code expects the following tables.

book

The code accesses these columns:

id
title
pub_year
category
total_copies
available_copies

member

The code accesses:

id
name
email
phone
join_date

transactions

The code accesses:

id
book_id
member_id
return_date
issue_date
fine

The repository currently does not contain a separate SQL schema file. Therefore, the database tables need to be created in MySQL before running the application.

The exact SQL types and constraints should be configured according to the database environment being used.

---

JDBC Database Access

The project uses JDBC directly rather than an ORM.

For example, BookDao.addBook() uses a parameterized SQL statement:

String query =
    "INSERT INTO book " +
    "(title,pub_year,category,total_copies,available_copies) " +
    "VALUES (?, ?, ?, ?, ?)";

The values are then supplied through PreparedStatement:

ps.setString(1, book.getName());
ps.setInt(2, book.getPublish_year());
ps.setString(3, book.getCategory());
ps.setInt(4, book.getTotal_copies());
ps.setInt(5, book.getAvailable_copies());

rowsAffected = ps.executeUpdate();

This keeps the SQL statement and its values separate and allows the DAO to work directly with the Book model.

---

Resource Management

The DAO classes use Java's try-with-resources when working with JDBC resources.

For example:

try(Connection con = DBConnection.getConnection()){
    try(PreparedStatement ps = con.prepareStatement(query)){
        try(ResultSet rs = ps.executeQuery()){
            // Process results
        }
    }
}

This allows the Connection, PreparedStatement, and ResultSet resources to be closed automatically after the operation.

---

SQL Joins for Issue Details

The transaction system uses a SQL join when detailed information about a book issue is requested.

TransactionDao.getBookRecord() combines information from three tables:

SELECT t.id, m.name, m.phone, b.title,
       t.issue_date, t.return_date
FROM transactions t
JOIN book b ON t.book_id = b.id
JOIN member m ON t.member_id = m.id
WHERE t.id = ?;

This allows the application to display information such as:

• Transaction ID
• Member name
• Member phone
• Book title
• Issue date
• Return date

The result is stored in a BookIssue object.

---

Collections

The project uses Java collections to hold records retrieved from the database.

For example, BookDao.getAllBooks() creates:

List<Book> books = new ArrayList<>();

Each row returned by the database is converted into a Book object and added to the list.

The same approach is used for members and transactions.

The transaction fine-validation logic also uses an Iterator to process the transaction list.

---

Exception Handling

The application handles exceptions at different levels.

The main application catches invalid numeric input:

catch(NumberFormatException e){
    System.out.println("Input Error :"+ e.getMessage());
}

It also has a general exception handler:

catch (Exception e){
    System.out.println("Error: "  + e.getMessage());
}

The DAO classes catch SQLException when database operations fail.

Date parsing uses DateTimeParseException to handle invalid date input.

This allows database, input, and date-related errors to be handled separately.

---

Requirements

Before running the application, install:

• JDK 19
• MySQL Server
• IntelliJ IDEA or another Java IDE
• MySQL Connector/J 9.4.0

The project is currently configured as an IntelliJ IDEA Java project.

---

Configuration

Database connectivity is currently configured in:

src/connection/DBConnection.java

The connection uses:

Host: localhost
Port: 3306
Database: library_db

The current source also contains the MySQL username and password directly in DBConnection.java.

For a public repository or production application, database credentials should not be committed to source control. They should be moved to environment variables or an external configuration mechanism.

---

Running the Project

1. Clone the repository

git clone https://github.com/Hamadullah-Odho/LibraryManagementSystem.git

2. Open the project

Open the project in IntelliJ IDEA.

Make sure the project is configured to use JDK 19.

3. Configure MySQL

Create the database:

CREATE DATABASE library_db;

Create the required book, member, and transactions tables using the column names documented above.

4. Configure the database connection

Open:

src/connection/DBConnection.java

and configure the MySQL username and password for your local MySQL installation.

5. Run the application

Run:

src/app/LibraryManagementSystem.java

The application will start with the console menu.

---

Example Application Flow

A typical book issue operation looks like this:

Library Management System

1. Add Book
2. View Books
3. Issue Book
...

Enter your choice: 3

--- Issuing Book ---

Enter Member Id: 1
Enter Book Id: 4

Current Date: 2026-09-27
Enter Return Date: 2026-10-05

Book issued successfully

The transaction is stored in the database and the book's available-copy value is updated through the DAO layer.

---

Design Approach

The project uses a simple layered structure:

Model

Represents application data.

Book
Member
Transactions
BookIssue

Service

Coordinates user interaction and application operations.

BookService
MemberService
TransactionService

DAO

Handles database operations.

BookDao
MemberDao
TransactionDao

Connection

Provides the JDBC database connection.

DBConnection

Utility

Contains reusable date-related functionality.

DateAndTime

This structure keeps database queries out of the main console application and gives each part of the project a specific responsibility.

---

Current Limitations

This project is a learning-focused console application rather than a production-ready library platform.

Some areas that could be improved include:

• Moving database credentials out of the source code
• Adding a dedicated SQL schema file
• Adding stronger validation for user input
• Preventing issue operations when no copies are available
• Making database updates transactional so related operations succeed or fail together
• Improving consistency in exception handling
• Adding automated tests
• Adding a graphical or web-based interface
• Introducing a build/dependency management tool such as Maven or Gradle

The current implementation also keeps the application intentionally simple by using JDBC directly rather than introducing an ORM or framework.

---

Future Improvements

Possible future improvements include:

• Better book availability validation
• More detailed transaction management
• Improved validation for members and books
• External database configuration
• Automated unit and integration tests
• Maven or Gradle project configuration
• GUI implementation
• Reporting functionality
• Authentication and user roles

These are potential extensions rather than features currently implemented in the repository.

---

What I Practiced

This project provided practical experience with:

• Core Java
• Object-oriented programming
• JDBC
• MySQL
• SQL CRUD operations
• PreparedStatement
• ResultSet
• SQL joins
• Java collections
• ArrayList
• Iterator
• Exception handling
• LocalDate
• DateTimeFormatter
• Try-with-resources
• Layered application organization
• Separating database access from application logic

---

Project Structure

LibraryManagementSystem/
│
├── .idea/
│   └── IntelliJ project configuration
│
├── src/
│   ├── app/
│   │   └── LibraryManagementSystem.java
│   │
│   ├── connection/
│   │   └── DBConnection.java
│   │
│   ├── dao/
│   │   ├── BookDao.java
│   │   ├── MemberDao.java
│   │   └── TransactionDao.java
│   │
│   ├── model/
│   │   ├── Book.java
│   │   ├── BookIssue.java
│   │   ├── Member.java
│   │   └── Transactions.java
│   │
│   ├── services/
│   │   ├── BookService.java
│   │   ├── MemberService.java
│   │   └── TransactionService.java
│   │
│   └── util/
│       └── DateAndTime.java
│
├── .gitignore
├── LibraryManagementSystem.iml
└── README.md

---

Author

Hamadullah Odho

Java Developer & Programming Instructor

---

Repository

The complete source code is available on GitHub:

https://github.com/Hamadullah-Odho/LibraryManagementSystem

---