DEV Community: HAMAD ULLAH

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

HAMAD ULLAH — Sun, 27 Sep 2026 05:58:44 +0000

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

How I Built a Student Course Registration System to Learn Java OOP

HAMAD ULLAH — Sun, 27 Sep 2026 05:46:29 +0000

How I Built a Student Course Registration System to Learn Java OOP

When I was learning Java, I reached a point where simply knowing how to create classes and objects was no longer enough. I wanted to understand how the different concepts of object-oriented programming fit together when building an actual application.

At the time, two areas I was particularly interested in were abstraction and exception handling. Abstraction was especially confusing for me. I understood the syntax of an abstract class and an abstract method, but I was still asking myself a basic question: Why do I actually need abstraction?

Instead of trying to understand the concept only through small examples, I decided to build a project around it.

That project became a Student Course Registration System, a console-based Java application that allows students to be registered, courses to be assigned to students, and administrators to access student and course information.

The application is relatively small, but it gave me a practical environment to work with inheritance, encapsulation, abstraction, polymorphism, and exception handling together.

More importantly, it helped me understand that knowing the syntax of an OOP concept and knowing when to use it are two different things.

Why I Built This Project

I built this project while I was learning Java OOP.

My main goal was to stop treating OOP concepts as separate topics. I wanted to see what would happen when I had to use several of them in the same application.

For example, I could create a simple example of inheritance and understand that one class can extend another. I could create an abstract class and understand how abstract methods work. But those examples did not always answer the more practical question of why I would design an application that way.

The Student Course Registration System gave me an opportunity to experiment with that.

I wanted students to have their own information and enrollment behavior. I also wanted administrators to have their own authentication behavior. That naturally gave me different types of objects and relationships to work with.

I was especially interested in abstraction because it was the concept I was having the most difficulty understanding.

I knew what an abstract class looked like in Java, but I didn't initially understand why I would create a class that could not be instantiated directly.

Building this project helped me start answering that question.

The Application Structure

The application is a console program with two main areas: a student portal and an admin portal.

The student side handles operations such as registering students and enrolling them in courses. The admin side provides authentication and access to student and course information.

The application's main menu is handled by the Main class.

SystemManager manager = new SystemManager();

boolean run = true;

while(run) {
    // display menu

    // read selection

    switch (select) {
        case 1:
            manager.enrollStudent();
            break;

        case 2:
            manager.addCourse();
            break;

        // ...
    }
}

I created a separate SystemManager rather than putting all of the application logic inside Main.

The main relationships between the classes can be simplified like this:

Main
  |
  v
SystemManager
  |
  +---- Student[]
  |
  +---- AdminSystem
  |
  +---- Course

User (abstract)
  |
  +---- Student

Admin (abstract)
  |
  +---- AdminSystem

This was not intended to be a large enterprise architecture. I was mainly trying to learn how to divide responsibilities between objects.

Dividing Responsibilities Between Classes

The project contains several classes, and each one has a particular responsibility.

Main is responsible primarily for starting the application, displaying the menu, and receiving the user's selections.

SystemManager coordinates operations such as registering students, searching for students, displaying information, and enrolling students in courses.

Student represents a student and contains student-specific information and behavior.

User contains information common to users and defines an abstract displayDetails() method.

Admin provides the structure for an administrator, while AdminSystem implements the administrator's login behavior.

Course represents the course-related part of the application.

This separation was important to me because I was trying to move away from writing one large class that handled everything.

I wanted the classes to have responsibilities of their own.

Encapsulation: Keeping Data and Behavior Together

The Student class was one of the clearest places where I practiced encapsulation.

Some of the common student information comes from the User class:

protected String name;
protected String fathername;
protected int rollno;
protected int age;

The Student class provides getters and setters for accessing and modifying this information:

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

public String getName(){
    return name;
}

public void setAge(int age) {
    this.age = age;
}

public int getAge(){
    return age;
}

The student's courses are stored inside the Student object:

private String[] courses = new String[2];
int coursecount = 0;

The courses array is private, so other parts of the application don't directly modify it.

Instead, the Student class provides an enrollCourse() method:

public boolean enrollCourse(String course){
    if(coursecount == 2){
        return false;
    }
    else {
        courses[coursecount] = course;
        coursecount++;
        return true;
    }
}

This was a useful example for me because it showed that encapsulation is not simply about making variables private.

The object can also contain the behavior that operates on its data.

In this case, the Student object is responsible for managing its own course enrollment and enforcing the two-course limit.

Using Inheritance

I used inheritance in two parts of the application.

The first relationship is between User and Student:

abstract class User {
    // common user information
}

public class Student extends User {
    // student-specific information
}

The second is between Admin and AdminSystem:

public abstract class Admin {
    // administrator structure
}

public class AdminSystem extends Admin {
    // administrator implementation
}

The User class contains information that can be shared by different types of users:

protected String name;
protected String fathername;
protected int rollno;
protected int age;

Instead of repeating those fields in every user-related class, they can be defined in the parent class.

Student can then add behavior that is specific to students.

This was where inheritance started to make more sense to me.

Previously, I mostly thought of inheritance as a way to reuse code. Working on the project helped me see that it can also represent a relationship between classes.

A student is a type of user, so having Student extend User made sense within the design I was experimenting with.

The OOP Concept I Struggled With Most: Abstraction

Abstraction was probably the most interesting part of this project for me because it was the concept I was struggling to understand before I started.

I knew how to write an abstract class, but knowing the syntax wasn't enough.

My User class contains common user information and an abstract method:

abstract class User {

    protected String name;
    protected String fathername;
    protected int rollno;
    protected int age;

    public abstract void displayDetails();
}

The important part here is that User does not provide the implementation of displayDetails().

The subclass provides it.

For example, Student implements the method:

public void displayDetails(){
    System.out.println("Student Name : " + getName());
    System.out.println("Father Name : " + getFatherName());
    System.out.println("Age : " + getAge());
    System.out.println("Roll No :" + getRollno());

    // display enrolled courses
}

I used a similar idea for the administrator side:

public abstract class Admin {

    protected String username;
    protected int pin;

    public abstract boolean login(String username, int pin);
}

AdminSystem then provides the implementation:

@Override
public boolean login(String user, int pass) {
    if(username.equals(user) && pin == pass){
        return true;
    }
    else {
        return false;
    }
}

This was the point where abstraction became more practical for me.

Before building the project, I was mostly thinking about abstraction as a Java feature. After using it, I started to understand it as a way of defining common structure while leaving certain behavior for subclasses to implement.

I wouldn't say this project made me an expert in abstraction, but it changed the way I thought about it.

Instead of asking only "How do I create an abstract class?", I started asking "What should be common, and what should be implemented differently by each subclass?"

That was a much more useful question.

Seeing Polymorphism in the Project

Polymorphism was another concept I practiced through the abstract classes.

User declares:

public abstract void displayDetails();

while Student provides the implementation.

Similarly, Admin declares:

public abstract boolean login(String username, int pin);

and AdminSystem overrides the method.

The @Override annotation makes that relationship clear:

@Override
public boolean login(String user, int pass) {
    // implementation
}

What helped me was seeing that the parent class can define a behavior that subclasses are expected to provide, while each subclass can implement that behavior according to its own needs.

This made polymorphism easier to understand than when I was learning it only from definitions.

Handling Invalid Input With Exceptions

Exception handling was another important part of the project.

Because the application is console-based, it receives a lot of input from the user. Some of that input needs to be converted from strings into integers.

For example:

int select = Integer.parseInt(scan.nextLine());

If a user enters something that cannot be converted into an integer, a NumberFormatException can occur.

I handle this with a try-catch block:

try {
    int select = Integer.parseInt(scan.nextLine());

    // menu processing
}
catch (NumberFormatException e){
    System.out.println("Selection cannot be non-numeric");
}

I also use exception handling when registering a student:

try {
    String name = scan.nextLine();

    String fathername = scan.nextLine();

    int age = Integer.parseInt(scan.nextLine());

    int rollno = Integer.parseInt(scan.nextLine());

    // register student
}
catch (NumberFormatException e){
    System.out.println(
        "---- (Age - rollno ) cannot be non numeric ----"
    );
}
catch (Exception e){
    System.out.println(
        "---- Unexpected error occurred ----"
    );
}

This was useful because exception handling was no longer just something I had to memorize for an exam.

I could see exactly why it was necessary.

A console application depends on user input, and users don't always enter what the program expects. Handling the exception allows the program to respond with a message instead of immediately terminating.

How the Objects Communicate

The different classes work together through their objects.

Main creates a SystemManager:

SystemManager manager = new SystemManager();

The manager maintains the student collection:

Student[] student = new Student[100];
int studentcount = 0;

When a student is registered, the manager creates a Student object and assigns its information:

student[studentcount] = new Student();

student[studentcount].setName(name);
student[studentcount].setRollno(rollno);
student[studentcount].setAge(age);
student[studentcount].setFatherName(fathername);

studentcount++;

When a course needs to be assigned, the manager finds the relevant student and calls the student's enrollment method:

boolean enrolled = s.enrollCourse(c);

The Student object then handles the enrollment itself:

if(coursecount == 2){
    return false;
}

I liked this part of the design because the manager coordinates the operation, while the Student object handles behavior that belongs specifically to the student.

The administrator side works in a similar way.

The application creates an AdminSystem object:

AdminSystem admin = new AdminSystem();

and delegates authentication to it:

boolean Checklogin = admin.login(name, pin);

Working with these relationships helped me understand that OOP is not only about creating classes. The more important part is deciding how those objects should interact.

The Challenges I Faced

The biggest challenge wasn't writing individual Java statements. It was trying to design the application using OOP concepts while I was still learning what good OOP design actually meant.

Abstraction was the clearest example.

I knew that I could create an abstract class, but I didn't initially understand why I should.

Building User and Admin as abstract classes forced me to think about what should be common and what should be implemented by subclasses.

I also had to think about where particular behavior should live.

For example, course enrollment is performed through:

s.enrollCourse(c);

The operation belongs to the Student class rather than being implemented entirely inside SystemManager.

That decision helped me understand the idea of giving an object responsibility for behavior related to its own data.

I also learned that getting a program to work and designing it well are not always the same thing.

The project works as a learning application, but there are several parts I would design differently if I built it again.

What I Would Improve

The current project uses fixed-size arrays:

Student[] student = new Student[100];

and the student has a fixed course capacity:

private String[] courses = new String[2];

These choices were enough for the project I was building at the time, but they are restrictive.

If I rebuilt the application, I would consider using ArrayList for the students and courses.

That would make the application more flexible and remove the fixed limits.

I would also revisit the Course design.

The current implementation uses static arrays for course names and codes. If I were redesigning the application today, I would think more carefully about representing individual courses as objects and how those objects should relate to students.

The input validation could also be improved so that invalid input is handled consistently throughout the application.

Most importantly, I would spend more time refining the class responsibilities.

That is one of the things I understand better now than when I first built the project. OOP design isn't just about using inheritance, abstract classes, and getters and setters because they are available in Java. The real challenge is deciding whether those tools actually make the application easier to understand and maintain.

What I Learned From the Project

The biggest lesson from this project was that knowing the definition of an OOP concept is different from knowing how to use it.

I could read that encapsulation means bundling data and behavior, that inheritance allows one class to inherit from another, or that abstraction hides implementation details.

But implementing those concepts in a real application forced me to think about them differently.

Abstraction was particularly valuable because it was the concept I found confusing at the beginning.

After working with User and Admin as abstract classes, I started to understand why a parent class might define common structure while leaving specific behavior to subclasses.

I also became more comfortable with exception handling because I could see a real reason for catching exceptions instead of simply learning the syntax of try and catch.

Another important lesson was about application design.

When you build a small program, it is tempting to put everything in one place because that can be faster initially. But as soon as the application contains several different responsibilities, separating those responsibilities becomes much more useful.

This project helped me start thinking in terms of objects, responsibilities, and relationships instead of only thinking about individual lines of code.

Conclusion

I built the Student Course Registration System while I was learning Java OOP because I wanted to practice the concepts in an actual application rather than learning them only through small examples.

The project gave me a practical way to work with inheritance, encapsulation, abstraction, polymorphism, and exception handling.

The most important part for me was abstraction.

Initially, I understood the syntax of abstract classes, but I wasn't sure why I needed them. Building the User and Admin classes helped me understand abstraction as a design concept: common structure can be defined in a parent class while specific behavior is left for subclasses.

The project also showed me that OOP design takes practice. Some of the decisions I made were appropriate for a small learning project, while others are areas I would change today, such as replacing fixed-size arrays with ArrayList and reconsidering how courses are represented.

I don't consider that a weakness of the project. For me, it is part of the learning process.

Building something once gives me the opportunity to look back at the code later and understand what I would do differently.

That is one of the main reasons I enjoy building projects while learning a technology: the code becomes more than an exercise. It becomes a record of how my understanding developed.

Project Repository

The complete source code for this project is available on GitHub:

Student Course Registration System — Java OOP

https://github.com/Hamadullah-Odho/StudentCourseRegistrationSystem-JAVA-OOP

How I Built a Java Hotel Reservation System with JDBC and MySQL

HAMAD ULLAH — Sun, 27 Sep 2026 05:30:19 +0000

How I Built a Java Hotel Reservation System with JDBC and MySQL

When I first started learning JDBC, one of the things I struggled with was understanding how a Java application actually communicates with a database.

I already understood the basics of Java and SQL separately, but connecting them together felt different. I wanted to move beyond simple Java exercises and build something where I had to store information, retrieve it, update it, and delete it from a real database.

That led me to build a console-based Hotel Reservation System using Java, JDBC, and MySQL.

The project started as a learning exercise, but I also wanted to use it to practice writing Java code in a more organized way. Instead of putting all the database logic inside one class, I separated the application into different components for the model, service logic, database operations, and connection handling.

In this article, I'll walk through how the project is structured, how Java communicates with MySQL through JDBC, how I implemented CRUD operations, and some of the problems I encountered while building it.

Why I Built This Project

At the time I built this project, I was relatively new to JDBC.

I wanted to practice database connectivity with a Java application, so I decided to give myself a practical challenge instead of only reading about JDBC concepts.

One of my biggest questions was simple:

How does JDBC actually talk to the database?

I understood that Java could execute SQL queries, but I wanted to understand the complete process. A Java program needs to establish a connection, send a SQL statement to the database, receive the result, and then work with that result inside the application.

Building a reservation system gave me a reason to go through that process repeatedly.

I chose a hotel reservation system because it resembles a real-world application. A reservation system needs to store customer information and allow that information to be created, viewed, modified, and removed.

That made it a good project for practicing CRUD operations while learning JDBC.

Project Overview

The application is a console-based Java program.

A user can interact with the application through the console and perform operations such as:

Creating a hotel reservation
Viewing existing reservations
Updating reservation information
Deleting a reservation
Working with reservation dates and times

The application stores the reservation information in a MySQL database.

The main technologies I used were:

Java
JDBC
MySQL
SQL
Java OOP
Java Date and Time API

The database contains a reservations table with information such as the customer's name, room number, email, phone number, reservation date, and reservation time.

Project Structure

One thing I wanted to improve while building the project was the organization of the code.

Instead of putting everything into Main.java, I separated different responsibilities into different packages and classes.

The project has this general structure:

src/
├── app/
│   └── Main.java
│
├── model/
│   └── Reservation.java
│
├── services/
│   └── HotelReservationService.java
│
├── dao/
│   └── HotelReservationDB.java
│
├── connection/
│   └── DBConnection.java
│
└── util/
    └── DateAndTime.java

Each component has a specific responsibility.

`Main`

Main.java is the entry point of the application. It handles the application flow and user interaction.

I did not want this class to contain all of the database code because that would make it difficult to maintain as the project grew.

`Reservation`

The Reservation class represents a reservation in the application.

It contains information such as:

Customer name
Phone number
Room number
Email
Reservation date
Reservation time

This gives the database record a corresponding Java object that the rest of the application can work with.

`HotelReservationService`

The service class handles the application's reservation-related operations.

This creates a separation between the user interface and the lower-level database operations.

For example, the application can request that a reservation be created without needing the main application flow to know exactly how the SQL INSERT statement is constructed.

`HotelReservationDB`

This class is responsible for database operations.

It contains the JDBC code used to execute SQL queries against the reservations table.

Keeping this code separate made the project easier for me to understand because I could identify exactly where database-related operations belonged.

`DBConnection`

The database connection class is responsible for establishing the connection between the Java application and MySQL.

This is where JDBC becomes the bridge between my Java code and the relational database.

`DateAndTime`

I also separated date and time handling into its own utility class.

This became useful because handling dates and times was one of the areas that initially caused me problems.

How JDBC Connects Java to MySQL

The part I was most interested in learning was the communication between Java and MySQL.

JDBC, or Java Database Connectivity, provides the API that Java applications can use to work with relational databases.

The basic flow in my project is:

Main
  ↓
HotelReservationService
  ↓
HotelReservationDB
  ↓
DBConnection
  ↓
JDBC
  ↓
MySQL

When the application needs to perform a database operation, the request eventually reaches the database layer.

The database connection is established using JDBC, and SQL statements are then executed through JDBC classes such as PreparedStatement.

Conceptually, the process looks like this:

Java Application
       |
       | JDBC
       ↓
Database Connection
       |
       | SQL Query
       ↓
     MySQL
       |
       | Result
       ↓
Java Application

Understanding this flow was one of the main reasons I built the project.

Before working on it, I knew that JDBC was used for database connectivity, but building the application helped me understand what that actually meant in practice.

Designing the Reservation Table

The database side of the project is intentionally simple because the main purpose was to practice Java-to-database communication.

The reservations table contains fields such as:

Column	Purpose
`id`	Identifies the reservation
`name`	Stores the customer's name
`room`	Stores the room number
`email`	Stores the customer's email
`phone`	Stores the customer's phone number
`date`	Stores the reservation date
`time`	Stores the reservation time

This structure gives the Java application enough information to perform the basic operations required by the reservation system.

Creating a Reservation

The first major database operation is creating a reservation.

The application collects the reservation information and sends it to the database using an SQL INSERT statement.

The query follows this pattern:

INSERT INTO reservations
(name, room, email, phone, date, time)
VALUES (?, ?, ?, ?, ?, ?);

The question marks are parameters that are supplied through PreparedStatement.

This approach is different from building an SQL query by concatenating user input directly into the SQL string.

For example, rather than constructing a query by joining strings together, the application creates a prepared statement and assigns values to its parameters.

Conceptually:

PreparedStatement statement =
    connection.prepareStatement(sql);

statement.setString(1, name);
statement.setInt(2, room);
statement.setString(3, email);
statement.setString(4, phone);

The exact parameter types depend on the data being sent to MySQL.

This was one of the JDBC concepts that became much clearer after actually using it in a project.

Reading Reservations

Creating data is only one part of a database application.

The application also needs to retrieve existing reservations.

For this, I used a SQL SELECT query:

SELECT * FROM reservations;

JDBC returns the query results through a ResultSet.

The basic idea is:

Execute SELECT query
        ↓
Receive ResultSet
        ↓
Read each record
        ↓
Display/process the information

The ResultSet allows the Java application to move through the returned records and retrieve individual column values.

This helped me understand an important part of JDBC: the database doesn't simply return a Java object. The application has to read the returned result and work with the data.

Updating Reservation Information

The application also supports updating reservation information.

For example, one of the operations updates a customer's name based on their phone number.

The SQL operation follows this pattern:

UPDATE reservations
SET name = ?
WHERE phone = ?;

Another update operation changes the room associated with a reservation.

This is where having a separate database class became useful. The SQL statements are kept in the database layer instead of being scattered throughout the application's main flow.

Deleting a Reservation

The application also supports deleting reservations.

The delete operation identifies the reservation using information such as the customer's name and phone number.

The SQL follows this general structure:

DELETE FROM reservations
WHERE name = ? AND phone = ?;

Again, I used PreparedStatement to supply the values.

At this point, the application supported the four basic CRUD operations:

Create  → INSERT
Read    → SELECT
Update  → UPDATE
Delete  → DELETE

Practicing all four operations in one application gave me a much better understanding of how Java applications interact with relational databases.

The Date and Time Problem

One of the problems I encountered while building the project involved dates and times.

Sending date information to the database and retrieving it correctly was not immediately obvious to me.

I was still learning JDBC at the time, so I had to look at documentation and experiment with the Java date and time APIs to understand how the values should be represented.

I used Java's LocalDate and LocalTime for handling reservation dates and times.

For example:

LocalDate date;
LocalTime time;

I also used DateTimeFormatter where formatting was necessary.

This was a useful lesson because database applications often require you to think about how a value is represented on both sides of the connection.

The Java application has its own types, while MySQL has its own database types. JDBC provides the mechanism for working between them, but you still need to understand what type of data you are sending and receiving.

Why I Used PreparedStatement

Another important lesson from this project was PreparedStatement.

When I first started learning JDBC, I mainly thought of it as a way to execute SQL queries from Java. I didn't initially understand why parameterized statements were important.

As I worked on the project, I learned that PreparedStatement provides a safer way to pass values into SQL queries and helps protect applications against SQL injection.

Instead of constructing SQL statements by directly concatenating user-provided values, I could use parameters:

SELECT * FROM reservations
WHERE phone = ?;

Then Java supplies the actual value separately.

This separation between the SQL statement and its parameters is an important practice when building database applications.

It was one of the concepts that made much more sense to me after implementing it rather than simply reading about it.

Why I Separated the Application Into Layers

Another goal of this project was to practice writing Java code in a more professional structure.

I could have written the application as one large class containing:

User input
SQL queries
Database connections
Business logic
Output

That would probably have worked for a small exercise, but it would not have been the kind of structure I wanted to practice.

Instead, I separated the application into different responsibilities.

Main
  ↓
Service
  ↓
DAO / Database Layer
  ↓
Database Connection
  ↓
MySQL

This separation makes it easier to identify where a particular piece of functionality belongs.

For example, if I need to change a SQL query, I know that the database layer is the appropriate place to look.

If I need to change how reservations are represented inside the Java application, I can work with the model.

If I need to change the application's reservation-related operations, I can work with the service layer.

This was part of my attempt to apply OOP principles instead of treating the project as only a collection of SQL statements.

What I Learned From Building It

The biggest benefit of this project was not simply getting a working reservation system.

It helped me understand the connection between several concepts that I had previously learned separately.

Before building the project, I could learn Java, SQL, and JDBC individually. While building it, I had to combine them.

I learned:

How a Java application establishes a database connection
How JDBC is used to execute SQL statements
How PreparedStatement works
How to retrieve data using ResultSet
How CRUD operations work from a Java application
How Java date/time values can be handled in a database application
Why separating responsibilities makes a project easier to organize
How database code can be separated from application logic
Why parameterized SQL queries are important for security

I also learned something less technical: documentation becomes much more useful when you have a specific problem to solve.

When I encountered problems with dates or database operations, I did not always know the answer immediately. Reading documentation with a specific question in mind made it easier to understand what I needed to change.

What I Would Improve

This project was built primarily as a learning exercise, so there are several things I would improve if I continued developing it.

The database design could be expanded beyond a single reservations table. For example, a larger hotel management application could have separate tables for rooms, customers, reservations, and payments.

The application could also move from a console interface to a graphical or web interface.

I would also consider adding stronger validation for user input, better error handling, configuration management for database credentials, and more comprehensive testing.

Another natural step would be replacing some of the manual JDBC infrastructure with a framework such as Spring Boot and exploring how database access can be handled using technologies such as Spring Data JPA.

However, I would not want to skip the JDBC experience. Learning what happens underneath higher-level frameworks gave me a better understanding of what those frameworks are abstracting away.

Conclusion

I originally built this hotel reservation system because I wanted to practice connecting a Java application to a database while I was still learning JDBC.

What started as a database connectivity exercise became an opportunity to practice much more: CRUD operations, PreparedStatement, result handling, Java date and time APIs, OOP, and separating application responsibilities.

The biggest lesson for me was that concepts become easier to understand when you have to use them to solve an actual problem. Reading about JDBC explained what it was, but building a project forced me to understand how the pieces worked together.

The project is relatively simple compared with a production hotel management system, but that was not the point. It gave me a practical foundation in Java database development and helped me become more comfortable working with JDBC and relational databases.

You can find the complete project and source code on my GitHub repository:

https://github.com/Hamadullah-Odho/HotelReservationSystem-JDBC.git