A hotel reservation app in Java needs three things: a JDBC connection to MySQL, parameterized SQL for every guest-supplied value, and a booking write that stays correct when two people reserve the same room at the same moment. The first two are routine. The third is where most tutorials fail, because a plain availability SELECT followed later by an INSERT does not stop competing bookings. This walkthrough builds the whole thing as an illustrative design. The schema, statuses and date rules below are choices made for this article, not a reconstruction of any particular project.
What you need and which versions
JDBC is Java’s database API. MySQL Connector/J is the JDBC driver that talks to MySQL. Oracle’s JDBC tutorial names the driver class com.mysql.cj.jdbc.Driver and shows the URL form jdbc:mysql://host:port/database. The tutorial says DataSource is the preferred way to obtain connections, though its simpler examples use DriverManager. The tutorial was written for JDK 8 and does not cover later Java improvements. Treat it as a source for stable JDBC concepts, and check current product docs for setup details.
As an Amazon Associate I earn from qualifying purchases.
The official Connector/J guide, in a revision dated 2026-08-31, describes Connector/J 26.7. It recommends that version for production and says it targets MySQL Server 8.0 and up. Pin whichever driver and server versions you actually use, and record them in your build file.
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
- A current JDK (the examples use text blocks, so Java 15 or later)
- MySQL Server 8.0 or later with InnoDB tables
- Connector/J added as a Maven or Gradle dependency
Step 1: Design a small schema
The design needs room inventory, guest details and reservations. This version tracks individual rooms, which makes locking straightforward. It uses a half-open stay convention: a guest occupies the nights from check_in up to, but not including, check_out. Someone leaving on the 10th and someone arriving on the 10th therefore do not conflict.
CREATE TABLE rooms (
id INT AUTO_INCREMENT PRIMARY KEY,
room_number VARCHAR(10) NOT NULL UNIQUE,
room_type VARCHAR(30) NOT NULL,
nightly_rate DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB;
CREATE TABLE guests (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) NOT NULL UNIQUE
) ENGINE=InnoDB;
CREATE TABLE reservations (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
room_id INT NOT NULL,
guest_id INT NOT NULL,
check_in DATE NOT NULL,
check_out DATE NOT NULL,
status ENUM('CONFIRMED','CANCELLED') NOT NULL DEFAULT 'CONFIRMED',
CONSTRAINT fk_res_room FOREIGN KEY (room_id) REFERENCES rooms(id),
CONSTRAINT fk_res_guest FOREIGN KEY (guest_id) REFERENCES guests(id),
CONSTRAINT chk_dates CHECK (check_out > check_in),
INDEX idx_room_dates (room_id, check_in, check_out)
) ENGINE=InnoDB;Two decisions here are yours to make for any real system. The first is which statuses consume inventory. Here only CONFIRMED does, and cancelled rows are ignored. If you add pending holds, include them in the overlap check. The second is the index on (room_id, check_in, check_out). MySQL advises indexing the columns used by locking reads and updates, and this index serves the overlap check below.
Step 2: Connect with a DataSource
DriverManager is fine for a throwaway script. A DataSource gives you centralized configuration and a place to plug in connection pooling, so it is the better default for an application. Keep credentials out of source. Oracle states plainly that its sample code does not use deployed password-management techniques, so do not copy embedded sample passwords into anything you deploy.
import com.mysql.cj.jdbc.MysqlDataSource;
import javax.sql.DataSource;
public final class Db {
public static DataSource create() {
MysqlDataSource ds = new MysqlDataSource();
ds.setUrl(System.getenv("HOTEL_DB_URL")); // e.g. jdbc:mysql://localhost:3306/hotel
ds.setUser(System.getenv("HOTEL_DB_USER"));
ds.setPassword(System.getenv("HOTEL_DB_PASSWORD"));
return ds;
}
}Put the database name in the URL. If you must switch databases on an open connection, Connector/J’s URL documentation says to use Connection.setCatalog() instead of the SQL USE statement. For production traffic, wrap or replace this with a pooled DataSource so each request does not open a fresh connection.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Rank #2
Step 3: Use prepared statements for every user value
Guest names, emails, room identifiers and dates all arrive from outside your code. Oracle’s tutorial puts the reason in one sentence: “Prepared statements always treat client-supplied data as content of a parameter and never as a part of an SQL statement.” Never build SQL by concatenating these values.
public int findOrCreateGuest(Connection c, String name, String email) throws SQLException {
try (PreparedStatement ps = c.prepareStatement(
"SELECT id FROM guests WHERE email = ?")) {
ps.setString(1, email);
try (ResultSet rs = ps.executeQuery()) {
if (rs.next()) return rs.getInt(1);
}
}
try (PreparedStatement ps = c.prepareStatement(
"INSERT INTO guests (name, email) VALUES (?, ?)",
Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, name);
ps.setString(2, email);
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
keys.next();
return keys.getInt(1);
}
}
}Pass dates with setObject(i, localDate) or setDate, and keep them as java.time.LocalDate in your own code. Note the lookup-then-insert above can itself race: two requests with the same new email could both miss and both insert. The UNIQUE constraint on email catches that, and you handle the duplicate-key error in Step 6.
Step 4: Search availability (a read, not a promise)
With the half-open convention, two stays overlap when the existing check-in is before the requested check-out and the existing check-out is after the requested check-in. A room is available when it has no overlapping confirmed reservation.
String sql = """
SELECT r.id, r.room_number, r.nightly_rate
FROM rooms r
WHERE r.room_type = ?
AND NOT EXISTS (
SELECT 1 FROM reservations x
WHERE x.room_id = r.id
AND x.status = 'CONFIRMED'
AND x.check_in < ? -- requested check_out
AND x.check_out > ? -- requested check_in
)
ORDER BY r.room_number
""";The result is only a snapshot. By the time the guest clicks Book, another session may have taken that room. Treat search results as suggestions and make the booking step re-verify under a lock.
Step 5: Confirm the booking inside a transaction with a lock
MySQL InnoDB’s default isolation level is REPEATABLE READ. Ordinary reads there are consistent snapshot reads, so two sessions can each see the room as free and each insert a reservation. Changing the isolation level alone does not fix this. MySQL’s documentation notes that under READ COMMITTED, gap locking for ordinary searches is disabled except for foreign-key and duplicate-key checks, so phantom rows can appear.
The fix is to give competing bookings something to queue on. MySQL documents SELECT ... FOR UPDATE as a locking read: it locks the index records it scans, and the locks are released at commit or rollback. In a per-room design the natural lock target is the room row. Every booking attempt for room 12 first locks room 12, then checks for overlap, then inserts. The second session waits at the lock and, once the first commits, sees the new reservation.
Rank #4
public long book(DataSource ds, int roomId, String name, String email,
LocalDate in, LocalDate out) throws SQLException, RoomUnavailableException {
if (!out.isAfter(in)) throw new IllegalArgumentException("check-out must be after check-in");
try (Connection c = ds.getConnection()) {
c.setAutoCommit(false);
try {
// 1. Serialize bookings for this room
try (PreparedStatement lock = c.prepareStatement(
"SELECT id FROM rooms WHERE id = ? FOR UPDATE")) {
lock.setInt(1, roomId);
try (ResultSet rs = lock.executeQuery()) {
if (!rs.next()) throw new IllegalArgumentException("No such room");
}
}
// 2. Re-check overlap now that we hold the lock
try (PreparedStatement chk = c.prepareStatement("""
SELECT 1 FROM reservations
WHERE room_id = ? AND status = 'CONFIRMED'
AND check_in < ? AND check_out > ?
LIMIT 1""")) {
chk.setInt(1, roomId);
chk.setObject(2, out);
chk.setObject(3, in);
try (ResultSet rs = chk.executeQuery()) {
if (rs.next()) throw new RoomUnavailableException();
}
}
// 3. Guest + reservation in the same transaction
int guestId = findOrCreateGuest(c, name, email);
long reservationId;
try (PreparedStatement ins = c.prepareStatement("""
INSERT INTO reservations (room_id, guest_id, check_in, check_out)
VALUES (?, ?, ?, ?)""", Statement.RETURN_GENERATED_KEYS)) {
ins.setInt(1, roomId);
ins.setInt(2, guestId);
ins.setObject(3, in);
ins.setObject(4, out);
ins.executeUpdate();
try (ResultSet k = ins.getGeneratedKeys()) { k.next(); reservationId = k.getLong(1); }
}
c.commit();
return reservationId;
} catch (Exception e) {
c.rollback();
throw e;
}
}
}Keep this transaction short. Do validation of user input, price calculation and any email sending outside it, so the room lock is held for as little time as possible. If a single method touches several tables, always touch them in the same order. MySQL recommends this to reduce deadlocks.
Choosing the lock target for your inventory model
The room-row lock works because the thing being sold is a specific, lockable row. Other models need a different coordination point.
| Inventory model | What must be coordinated | Approach |
|---|---|---|
| Individual rooms (this article) | One room’s date range | Lock the room row with FOR UPDATE, re-check overlap, insert |
| Room-type capacity (“3 doubles left”) | The count per type per night | Keep a per-type, per-night inventory row and lock or decrement it for every night in the stay, in date order |
The MySQL sources establish the locking mechanics, not a hotel schema, so which model fits depends on whether guests are assigned a specific room at booking or only at check-in. A room-type design with no lockable row for the type and night would reproduce the same race you are trying to remove.
Best Value
Why not just use isolation levels or a unique constraint?
A unique key can stop two reservations that share exactly the same room and start date, but it cannot express “these date ranges overlap”. Raising isolation to SERIALIZABLE is possible, but it changes locking behavior for every query and tends to produce more lock waits and deadlocks. An explicit, narrow lock on the row you are allocating is easier to reason about.
Step 6: Handle errors visibly
Translate database failures into messages a guest can act on, and retry only what is safe to retry.
- Room taken (your own
RoomUnavailableException): show “That room was just booked. Here are the other available rooms for those dates” and rerun the search. - Duplicate key (SQLState
23000, MySQL error 1062): typically a guest email inserted by a concurrent request. Re-run the lookup once, or report that the email is already registered. - Deadlock (SQLState
40001, MySQL error 1213): InnoDB chose your transaction as the victim and rolled it back. MySQL’s guidance is that deadlocks can still happen even with good design, so the application must be prepared. Retry the whole transaction a small, bounded number of times. - Lock wait timeout (error 1205): another session held the room lock too long. Fail with a “try again” message instead of retrying forever.
for (int attempt = 1; ; attempt++) {
try {
return book(ds, roomId, name, email, in, out);
} catch (SQLException e) {
boolean deadlock = "40001".equals(e.getSQLState());
if (!deadlock || attempt >= 3) throw e;
}
}Never swallow exceptions after a rollback. Log the SQLState and error code so you can tell a deadlock from a constraint failure later.
Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minutePC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Step 7: Cancellation and verification
Cancel by setting status = 'CANCELLED' inside a transaction rather than deleting the row, which keeps a history and frees the room under the overlap rule above. Use the same room lock if cancellation and rebooking can interleave.
To verify the locking actually works, run a test with two threads, each with its own connection, calling book() for the same room and overlapping dates at the same time. Exactly one should return a reservation ID and the other should throw RoomUnavailableException. Then remove the FOR UPDATE step and rerun it many times. Without the lock you may see both succeed, which demonstrates the race. Whether and how often it appears depends on timing, so loop the test rather than relying on one run.
Quick Recap
Before you call it production-ready
- Read credentials from environment variables or a secrets manager, never from source.
- Use a pooled
DataSource. - Decide and document the date convention, the statuses that consume inventory, the cancellation rules and the room-allocation policy. None of these come from JDBC or MySQL.
- Add payment, authentication and an interface separately. This design covers only the data and booking core.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
