Advanced Application Development

This page documents the advanced application development implemented for Phase P8 of the Room Reservation System project. The goal of this phase is to extend the Java prototype application with transaction handling and database connection pooling.

The prototype was extended in two main directions:

• database connection pooling was implemented using HikariCP;
• explicit transaction handling was added for use-cases that execute multiple related database operations.

The implementation affects the following source files:

• src/main/java/mk/finki/roomreservation/App.java
• src/main/java/mk/finki/roomreservation/DatabasePool.java
• pom.xml

The application still connects to the same PostgreSQL database schema named project through the SSH tunnel on localhost:9999.

Database Connection Pooling

Description

In the previous version of the prototype, the application used a direct JDBC connection. In Phase P8, the application was changed to use a database connection pool.

Connection pooling is useful because database connections are expensive to create repeatedly. Instead of creating a new physical database connection for every operation, the application initializes a pool of reusable connections. When a use-case needs database access, the application requests a connection from the pool, uses it, and then returns it to the pool.

The connection pool is implemented in the class:

src/main/java/mk/finki/roomreservation/DatabasePool.java

The Maven configuration file pom.xml was also updated with the required HikariCP dependency.

Implementation

The application initializes the connection pool after the user enters the database URL, database username, and database password. If the pool is created successfully, the application displays a confirmation message and then shows the main menu.

The screenshot shows that the HikariCP connection pool starts successfully. The application then confirms that the database connection pool was initialized and that the database connection is successful.

The important confirmation messages are:

Database connection pool initialized successfully.

Connected to database successfully.

After the connection pool is initialized, the application displays the main menu:

Main menu

1. Search available rooms
2. Create reservation request
3. Approve or reject reservation
4. Exit
   Choose option:

Usage in the application

The application uses connections from the pool in the implemented use-cases. A connection is requested from the pool before executing SQL statements. When the operation finishes, the connection is closed in the Java code, but this does not destroy the physical database connection. Instead, the connection is returned to the pool and can be reused.

This improves the structure of the application because database access is managed through a separate pooling class instead of being handled manually in every part of the application.

Transactions

Transactions are required when one logical operation contains multiple database operations that must succeed or fail together.

In this prototype, transactions are used in the following implemented use-cases:

• Create reservation request
• Approve or reject reservation

The application uses explicit transaction control in Java. The general transaction structure is:

connection.setAutoCommit(false);

try {
// execute several related SQL statements
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
} finally {
connection.setAutoCommit(true);
}

This means that all database changes inside the transaction are saved only if the complete operation is successful. If an error occurs, the transaction is rolled back and the database is not left in a partially modified state.

Transaction 1: Create reservation request

Data requirements description

Creating a reservation request may require changes in more than one table.

A reservation request can include:

• only a room;
• only requested equipment;
• both a room and requested equipment.

When the requester creates a reservation with additional equipment, the application must insert data into:

• project.reservations
• project.reservation_equipment

These operations must be executed as one transaction. If the reservation is inserted but inserting the requested equipment fails, the whole operation should be rolled back. This prevents incomplete reservation data from being saved.

Implementation

The transaction is implemented in the Create reservation request use-case in App.java.

The application performs the following steps:

1. The requester chooses the option Create reservation request from the main menu.
2. The system displays a list of users.
3. The requester selects the requester from the list.
4. The system asks for the reservation date.
5. The requester enters the reservation date.
6. The system asks for the start time and end time.
7. The requester enters the requested time interval.
8. The system asks whether the reservation should include a room.
9. If the reservation includes a room, the system displays available rooms.
10. The requester selects one of the available rooms from the list.
11. The system asks whether additional/general equipment should be requested.
12. If equipment is requested, the requester selects equipment and requested quantity.
13. The application starts a transaction.
14. The application inserts a new record into project.reservations.
15. If requested equipment exists, the application inserts corresponding records into project.reservation_equipment.
16. If all operations are successful, the transaction is committed.
17. If an error occurs, the transaction is rolled back.

Demo execution

The following screenshot shows the successful execution of the create reservation transaction.

In this test, the requester creates a reservation request through the Java prototype. The application displays available users and rooms, the requester selects values from the displayed lists, and the reservation request is created successfully.

The use-case demonstrates that the user does not need to remember internal database identifiers such as user_id or room_id. Instead, the application lists the possible values and internally uses the selected database identifiers.

Database verification

The result can be verified in DBeaver by checking the created reservation and the requested equipment records.

SELECT *
FROM project.reservations
ORDER BY reservation_id DESC;

SELECT *
FROM project.reservation_equipment
ORDER BY reservation_id DESC;

The following screenshot shows the database verification after the transaction was executed.

The screenshot confirms that the reservation was inserted into project.reservations. If the reservation included additional requested equipment, the related records are also inserted into project.reservation_equipment.

Transaction discussion

This use-case requires a transaction because creating a reservation request is one logical operation, even when it modifies multiple tables. The reservation and its requested equipment must be saved together. If one part fails, the whole operation should be cancelled.

Therefore, the application commits the transaction only after all related inserts are successful.

Transaction 2: Approve or reject reservation

Data requirements description

Approving or rejecting a reservation also requires multiple related database operations.

When an approver makes a decision, the application must:

• update the reservation status in project.reservations;
• insert a new approval decision into project.approvals.

These two operations must be consistent. The reservation status and the approval record must match. If the approval record cannot be inserted, the reservation status should not remain changed.

Implementation

The transaction is implemented in the Approve or reject reservation use-case in App.java.

The application performs the following steps:

1. The approver chooses the option Approve or reject reservation from the main menu.
2. The system displays a list of pending reservations.
3. The approver selects one reservation from the list.
4. The system displays a list of users who can approve reservations.
5. The approver selects the approving user.
6. The approver chooses whether the reservation should be approved or rejected.
7. The approver enters an optional decision note.
8. The application starts a transaction.
9. The application updates the selected reservation status in project.reservations.
10. The application inserts a new approval record into project.approvals.
11. If both operations are successful, the transaction is committed.
12. If an error occurs, the transaction is rolled back.

Demo execution

The following screenshot shows the successful execution of the approval transaction.

The screenshot shows that the approver selects a pending reservation, chooses the approver user, enters the decision, and saves the approval decision. The application then displays the final reservation details.

Database verification

The result can be verified in DBeaver by checking the reservations and approvals tables.

SELECT *
FROM project.reservations
ORDER BY reservation_id DESC;

SELECT *
FROM project.approvals
ORDER BY approval_id DESC;

The following screenshot shows the database verification after the approval transaction was executed.

The screenshot confirms that the reservation status was updated and that a corresponding approval record was inserted into project.approvals.

Transaction discussion

This use-case requires a transaction because approval is not only a single table change. The status update and the approval insert represent one logical decision.

If the reservation status were updated without inserting an approval record, the database would lose information about who made the decision, when the decision was made, and what note was entered. Therefore, both operations must be committed together or rolled back together.

Rollback behavior

The transaction implementation protects the database from partial changes.

If an error occurs during the create reservation transaction, the inserted reservation and related requested equipment records are rolled back. If an error occurs during the approval transaction, the reservation status update and the approval insert are rolled back.

This behavior preserves database consistency because incomplete multi-step operations are not saved.

Maven configuration

The Maven configuration file pom.xml was updated to include the required dependencies.

The application uses:

• PostgreSQL JDBC driver for connecting to the PostgreSQL database;
• HikariCP for database connection pooling;
• SLF4J implementation for HikariCP logging.

The relevant configuration is stored in:

pom.xml

Final discussion

Phase P8 improves the Java prototype by adding transaction handling and database connection pooling.

The most important improvements are:

• the application uses HikariCP connection pooling;
• database access is centralized through DatabasePool.java;
• the connection pool is initialized once and reused by the application;
• creating a reservation request is executed as a transaction;
• inserting requested equipment is included in the same transaction as the reservation insert;
• approving or rejecting a reservation is executed as a transaction;
• reservation status updates and approval records are saved consistently;
• rollback is used when a multi-step database operation fails;
• the prototype is more reliable and closer to a real database application.

These additions satisfy the Phase P8 requirements because the application now demonstrates both database connection pooling and transactions for use-cases where multiple related database operations must be executed as one unit of work.