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