How I Structured a Java Library Management System with DAO and Service Layers

When I started building a Library Management System in Java, I wanted to do more than connect a few SQL queries to a console menu. I wanted to understand how a database-backed Java application could be organized when it starts having multiple responsibilities.

I built a console-based Library Management System using Java, JDBC, and MySQL. Instead of putting everything into one class, I separated the application into model, DAO, service, connection, and utility packages.

The project gave me practical experience with something I had been learning conceptually: separating application responsibilities so that each part of the program has a clear purpose.

It also gave me a chance to work with a more realistic workflow than simple CRUD, particularly when issuing and returning books affects both transaction records and book availability.

Why I Built the Project

I built this project as a way to practice Java in a larger application rather than working with isolated classes and small exercises.

A library system has several related operations. Books need to be stored and searched, members need to be registered, and transactions need to connect members with books. When a book is issued, its available-copy count also needs to change. Returning a book reverses part of that process.

That made the project useful for practicing database operations while also thinking about application structure.

I also wanted to get more comfortable separating responsibilities. Instead of having the main class handle user input, SQL queries, business logic, and database connections, I wanted different parts of the application to handle different jobs.

What the Application Does

The application runs through a console menu.

Users can:

• Add books
• View books
• Search for books
• Delete books
• Add members
• View members
• Issue books
• Return books
• View book issue records
• Exit the application

The application uses a MySQL database called library_db.

The project is a plain Java application using JDBC directly rather than a framework such as Spring Boot or Hibernate. The IntelliJ project configuration uses JDK 19 and references MySQL Connector/J.

That made the project particularly useful for me because I had to work directly with JDBC instead of having a framework hide the database interaction.

Organizing the Project

The source code is divided into several 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

I divided the application into five main responsibilities:

• Model — represents application data.
• DAO — communicates with the database.
• Service — coordinates application operations and business logic.
• Connection — provides database connectivity.
• Utility — contains reusable functionality such as date handling.

The main application brings these parts together.

This structure helped me understand that organizing code is not only about creating more classes. The important question is why each class exists and what responsibility belongs there.

Models: Representing Application Data

The model classes represent the data used by the application.

For example, the Book class contains information such as the book ID, title, category, publication year, total copies, and available copies:

public class Book {

    private int id;
    private String name;
    private String category;
    private int publish_year;
    private int total_copies;
    private int available_copies;

    // getters and setters
}

There are also separate models for members and transactions.

One interesting part of the project is BookIssue. A transaction stored in the database contains IDs connecting a member and a book, but when displaying an issue record, I wanted information such as the member's name, phone number, and book title.

That is where a separate representation becomes useful. The application does not have to expose the raw database structure directly to the user.

The DAO Layer

The DAO classes are responsible for database operations.

There are separate DAOs for books, members, and transactions:

BookDao
MemberDao
TransactionDao

For example, BookDao handles operations such as adding books, retrieving books, searching by ID, deleting books, and changing the number of available copies.

One of the important things I learned while working with JDBC was to use PreparedStatement rather than building SQL statements by concatenating user input.

A simplified example from the book insertion code is:

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

try (Connection con = DBConnection.getConnection()) {
    try (PreparedStatement ps = con.prepareStatement(query)) {

        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();
    }
}

The DAO knows how to communicate with MySQL, but it does not need to know how the console application collects the user's input.

That separation was one of the clearest benefits of the architecture.

Why the Service Layer Exists

The service classes sit between the main application and the DAOs.

For example, BookService handles the process of collecting book information, creating a Book object, and sending it to the DAO.

Book book = new Book();

book.setName(title);
book.setCategory(category);
book.setPublish_year(publish_year);
book.setTotal_copies(total_copies);
book.setAvailable_copies(total_copies);

boolean success = BookDao.addBook(book);

There is a small but important piece of logic here:

book.setAvailable_copies(total_copies);

When a new book is added, all of its copies are initially available.

The service layer is therefore not just passing data between classes. It can make application-level decisions before the data reaches the database.

This helped me understand the difference between business logic and database access.

The DAO answers a question such as:

How do I insert this book into MySQL?

The service layer deals more with:

What should happen when a user adds a new book?

Keeping those responsibilities separate makes the code easier to reason about.

The Most Interesting Workflow: Issuing a Book

The issuing process was one of the most useful parts of the project because it involves several components working together.

When TransactionService.issueBook() runs, the application asks the user to select a member and a book. It then asks for a return date.

Before creating the transaction, the return date is validated:

String current_date = DateAndTime.getDate();

boolean validDate =
    DateAndTime.compareDate(current_date, return_date);

if (validDate) {
    issueBookHelper(
        member_id,
        book_id,
        current_date,
        return_date
    );
}
else {
    System.out.println("Invalid Return Date Entered");
}

If the date is valid, a transaction object is created containing information such as:

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

The transaction is then sent to TransactionDao.

At the same time, the available quantity of the book needs to be updated.

So one user action involves several parts of the application:

User
  ↓
TransactionService
  ↓
TransactionDao
  ↓
MySQL
  ↓
BookDao
  ↓
Update available copies

This was where the layered structure became more than just a way of organizing files. It gave each part of the workflow a specific responsibility.

Updating Book Availability

Book availability is a simple example of application logic that goes beyond basic CRUD.

When a book is issued, the available-copy count is decreased:

String decrement =
    "UPDATE book " +
    "SET available_copies = available_copies - 1 " +
    "WHERE id = ?";

When the book is returned, the count can be increased:

String increment =
    "UPDATE book " +
    "SET available_copies = available_copies + 1 " +
    "WHERE id = ?";

The database therefore stores both the total number of copies and the number currently available.

This made me think about data consistency in a way that simple insert and select operations did not.

An operation such as issuing a book is not just:

INSERT transaction

It also means:

Create transaction
+
Update book availability

That relationship is part of the application's business logic.

Using SQL Joins for Issue Records

Another part of the project that helped me understand database-backed applications better was retrieving information from multiple tables.

The transaction table stores IDs that connect the transaction to a book and member. When displaying a record, however, the user needs more useful information.

TransactionDao.getBookRecord() uses SQL joins:

String query =
    "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 query combines information from the transaction, book, and member tables.

The result is then represented using a BookIssue object.

This was useful because it showed me that the objects used by an application do not always have to correspond exactly to one database table.

A database might store normalized information across several tables, while the application can create an object representing the information needed for a particular operation or screen.

Date Handling and Fines

The project also contains a DateAndTime utility class for working with dates.

It uses Java's LocalDate and a DateTimeFormatter:

private static final DateTimeFormatter formatter =
    DateTimeFormatter.ofPattern("yyyy-MM-dd");

The utility provides operations for retrieving the current date and comparing or validating dates.

The transaction system also includes fine handling. TransactionDao.fineValidate() checks transaction records and updates the fine when a return date has passed.

The project uses an Iterator to process transaction records:

Iterator<Transactions> iterator =
    transactions.iterator();

while (iterator.hasNext()) {
    Transactions t = iterator.next();

    if (DateAndTime.validateDate(t.getReturn_date())) {
        // update fine
    }
}

This gave me practical experience combining Java collections, date handling, and database operations in the same application.

Handling Errors and JDBC Resources

Exception handling is another area I practiced throughout the project.

The main application handles invalid numeric input using NumberFormatException:

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

The DAO classes also handle SQLException when working with the database.

For JDBC resources, I used try-with-resources:

try (Connection con = DBConnection.getConnection()) {
    try (PreparedStatement ps =
             con.prepareStatement(query)) {
        // database operation
    }
}

This was important because JDBC resources such as connections and statements should not be left open unnecessarily.

Using try-with-resources made the cleanup automatic and made the database code easier to manage.

What I Learned from the Project

The biggest lesson from this project was that application structure becomes increasingly important as the number of responsibilities grows.

In smaller Java exercises, it is easy to put everything into one class. As soon as an application has users, database operations, validation, transactions, and multiple types of data, that approach becomes difficult to maintain.

This project made me think about questions such as:

• Which class should contain this SQL query?
• Should this logic belong in the service or DAO?
• What object should represent data returned by a join?
• Where should date validation happen?
• What should happen to available copies when a book is issued?
• How should database resources be managed?

These questions helped me move from thinking only about whether my code works to thinking about how the code is organized.

I also became more comfortable with JDBC concepts such as PreparedStatement, ResultSet, SQL joins, executeQuery(), executeUpdate(), and try-with-resources.

What I Would Improve

The project is a learning application, so there are several things I would improve if I continued developing it.

First, I would move database configuration into external configuration rather than keeping connection details directly in application code. This would make the application easier and safer to configure across different environments.

I would also strengthen the validation around issuing books. For example, the application should explicitly prevent an issue operation when a book has no available copies.

Another improvement would be transaction management. Issuing a book can involve creating a transaction and changing the available-copy count. These related database operations would benefit from proper database transaction handling so they can succeed or fail together.

I would also make exception handling more consistent. Some parts of the application return boolean values, while other parts print errors directly. A more consistent error-handling strategy would make the application easier to maintain.

Finally, I would consider improving the project setup and documentation so that someone cloning the repository could configure the database and run the application with fewer manual steps.

Conclusion

Building this Library Management System helped me understand something that is difficult to learn from small coding exercises alone: good application structure is about assigning responsibilities clearly.

The project uses plain Java, JDBC, and MySQL, which meant I could see the database interaction directly instead of relying on a framework to hide it.

The model classes represent application data, the DAOs handle database operations, the services coordinate application logic, the connection class manages JDBC connectivity, and the utility class provides reusable date functionality.

The book-issue workflow brought all of these pieces together. A single operation can involve user input, validation, object creation, database persistence, and updating book availability.

That experience changed how I think about organizing Java applications. I started the project wanting to practice JDBC and database programming, but I finished with a better understanding of how different layers can work together to keep an application organized.

GitHub Repository

The complete source code is available on GitHub:

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