Changes between Version 3 and Version 4 of AdvancedApplicationDevelopment

Timestamp:

08/31/26 17:35:20 (7 days ago)

Author:

223091

Comment:

--

AdvancedApplicationDevelopment

@@ -1 +1 @@
 = 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.
-
-[[Image(p8_connection_pool_initialized.png, width=100%)]]
-
-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.
-
-[[Image(p8_create_reservation_transaction_success.png, width=100%)]]
-
-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.
-
-[[Image(p8_create_reservation_database_verification.png, width=100%)]]
-
-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.
-
-[[Image(p8_approval_transaction_success.png, width=100%)]]
-
-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.
-
-[[Image(p8_approval_database_verification.png, width=100%)]]
-
-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.
+== Overview ==
+
+This phase documents the advanced application-level implementation of the Room Reservation System. The final application is implemented as a full-stack system with a Spring Boot backend and a React frontend.
+
+The focus of this phase is on:
+
+ * database transactions
+ * connection pooling
+ * database access through the backend application
+ * consistency between the documented implementation and the source code uploaded in the project Git repository
+
+The relevant source files are available in the project repository:
+
+ * [source:backend/src/main/resources/application.properties backend/src/main/resources/application.properties]
+ * [source:backend/src/main/java/mk/finki/roomreservation/service/ReservationService.java backend/src/main/java/mk/finki/roomreservation/service/ReservationService.java]
+ * [source:backend/src/main/java/mk/finki/roomreservation/service/AuthService.java backend/src/main/java/mk/finki/roomreservation/service/AuthService.java]
+ * [source:backend/src/main/java/mk/finki/roomreservation/service/ApprovalService.java backend/src/main/java/mk/finki/roomreservation/service/ApprovalService.java]
+ * [source:backend/src/main/java/mk/finki/roomreservation/controller/ReservationController.java backend/src/main/java/mk/finki/roomreservation/controller/ReservationController.java]
+ * [source:backend/src/main/java/mk/finki/roomreservation/controller/AuthController.java backend/src/main/java/mk/finki/roomreservation/controller/AuthController.java]
+ * [source:backend/src/main/java/mk/finki/roomreservation/controller/ApprovalController.java backend/src/main/java/mk/finki/roomreservation/controller/ApprovalController.java]
+
+== Connection Pooling ==
+
+Connection pooling is implemented through Spring Boot's default HikariCP integration. The backend application uses `JdbcTemplate` for database access, while Spring Boot manages database connections internally through a HikariCP connection pool.
+
+The pool configuration is placed in:
+
+[source:backend/src/main/resources/application.properties backend/src/main/resources/application.properties]
+
+The relevant configuration is:
+
+{{{
+spring.datasource.hikari.pool-name=RoomReservationPool
+spring.datasource.hikari.maximum-pool-size=5
+spring.datasource.hikari.minimum-idle=1
+spring.datasource.hikari.connection-timeout=10000
+}}}
+
+The database connection parameters are also configured in the same file:
+
+{{{
+spring.datasource.url=${SPRING_DATASOURCE_URL:jdbc:postgresql://localhost:9999/db_202526z_va_prj_room_reservation}
+spring.datasource.username=${SPRING_DATASOURCE_USERNAME:db_202526z_va_prj_room_reservation_owner}
+spring.datasource.password=${SPRING_DATASOURCE_PASSWORD:}
+}}}
+
+The actual database password is not hard-coded in the source code. It is provided through an environment variable. This makes the application safer and allows the same codebase to run locally and through Docker.
+
+The backend services do not manually open and close raw JDBC connections. Instead, they use Spring's `JdbcTemplate`. When a query is executed, Spring obtains a connection from the HikariCP pool, executes the SQL operation, and returns the connection back to the pool.
+
+== Transaction 1: Creating a Reservation Request ==
+
+The main application-level transaction is implemented in:
+
+[source:backend/src/main/java/mk/finki/roomreservation/service/ReservationService.java backend/src/main/java/mk/finki/roomreservation/service/ReservationService.java]
+
+The transaction is used in the `createReservation` method. This method inserts a new reservation into `project.reservations` and then optionally inserts one or more related equipment records into `project.reservation_equipment`.
+
+The method is marked with `@Transactional`:
+
+{{{
+@Transactional
+public ReservationDetails createReservation(CreateReservationRequest request) {
+    validateCreateRequest(request);
+
+    LocalDate date = LocalDate.parse(request.reservationDate());
+    LocalTime start = LocalTime.parse(request.startTime());
+    LocalTime end = LocalTime.parse(request.endTime());
+
+    List<EquipmentRequest> equipment =
+            request.equipment() == null ? List.of() : request.equipment();
+
+    String insertReservationSql = """
+            INSERT INTO project.reservations (
+                room_id,
+                user_id,
+                reservation_date,
+                start_time,
+                end_time,
+                status
+            )
+            VALUES (?, ?, ?, ?, ?, 'pending')
+            RETURNING reservation_id
+            """;
+
+    Integer reservationId = jdbcTemplate.queryForObject(
+            insertReservationSql,
+            Integer.class,
+            request.roomId(),
+            request.userId(),
+            Date.valueOf(date),
+            Time.valueOf(start),
+            Time.valueOf(end)
+    );
+
+    if (reservationId == null) {
+        throw new RuntimeException("Reservation was not created.");
+    }
+
+    for (EquipmentRequest item : equipment) {
+        jdbcTemplate.update(
+                """
+                INSERT INTO project.reservation_equipment (
+                    reservation_id,
+                    equipment_id,
+                    requested_quantity
+                )
+                VALUES (?, ?, ?)
+                """,
+                reservationId,
+                item.equipmentId(),
+                item.requestedQuantity()
+        );
+    }
+
+    return findReservationDetails(reservationId);
+}
+}}}
+
+This transaction protects the database from partial writes.
+
+If the reservation insert succeeds, but one of the equipment inserts fails, the entire transaction is rolled back. This means that the system will not store a reservation without its required equipment records.
+
+The method also validates the request before writing to the database:
+
+{{{
+private void validateCreateRequest(CreateReservationRequest request) {
+    if (request.userId() == null) {
+        throw new IllegalArgumentException("Requester user is required.");
+    }
+
+    if (request.reservationDate() == null || request.reservationDate().isBlank()) {
+        throw new IllegalArgumentException("Reservation date is required.");
+    }
+
+    if (request.startTime() == null || request.startTime().isBlank()) {
+        throw new IllegalArgumentException("Start time is required.");
+    }
+
+    if (request.endTime() == null || request.endTime().isBlank()) {
+        throw new IllegalArgumentException("End time is required.");
+    }
+
+    LocalTime start = LocalTime.parse(request.startTime());
+    LocalTime end = LocalTime.parse(request.endTime());
+
+    if (!end.isAfter(start)) {
+        throw new IllegalArgumentException("End time must be after start time.");
+    }
+
+    boolean hasRoom = request.roomId() != null;
+    boolean hasEquipment = request.equipment() != null && !request.equipment().isEmpty();
+
+    if (!hasRoom && !hasEquipment) {
+        throw new IllegalArgumentException("Reservation must include at least one resource: room or equipment.");
+    }
+}
+}}}
+
+This validates that the reservation has a requester, a valid date and time interval, and at least one reserved resource.
+
+== Transaction 2: User Registration ==
+
+User registration is implemented in:
+
+[source:backend/src/main/java/mk/finki/roomreservation/service/AuthService.java backend/src/main/java/mk/finki/roomreservation/service/AuthService.java]
+
+The `register` method is also transactional because it writes to two related tables:
+
+ * `project.users`
+ * `project.user_credentials`
+
+The method first inserts the user profile and then inserts the password hash.
+
+{{{
+@Transactional
+public UserOption register(RegisterRequest request) {
+    validateRegisterRequest(request);
+
+    String fullName = request.fullName().trim();
+    String username = request.username().trim();
+    String email = request.email().trim();
+
+    if (usernameExists(username)) {
+        throw new IllegalArgumentException("Username is already taken.");
+    }
+
+    if (emailExists(email)) {
+        throw new IllegalArgumentException("Email is already registered.");
+    }
+
+    Integer userId = jdbcTemplate.queryForObject(
+            """
+            INSERT INTO project.users (
+                username,
+                email,
+                full_name,
+                role
+            )
+            VALUES (?, ?, ?, 'regular')
+            RETURNING user_id
+            """,
+            Integer.class,
+            username,
+            email,
+            fullName
+    );
+
+    if (userId == null) {
+        throw new RuntimeException("User registration failed.");
+    }
+
+    String passwordHash = passwordEncoder.encode(request.password());
+
+    jdbcTemplate.update(
+            """
+            INSERT INTO project.user_credentials (
+                user_id,
+                password_hash
+            )
+            VALUES (?, ?)
+            """,
+            userId,
+            passwordHash
+    );
+
+    return findUserById(userId);
+}
+}}}
+
+This transaction ensures that the application does not create an incomplete account. If the password hash insert fails, the inserted user is rolled back as well.
+
+Passwords are not stored as plain text. The application stores only the BCrypt password hash.
+
+== Transaction 3: Activating an Existing Account ==
+
+The application also supports assigning a password to an already existing database user. This is implemented in the same service:
+
+[source:backend/src/main/java/mk/finki/roomreservation/service/AuthService.java backend/src/main/java/mk/finki/roomreservation/service/AuthService.java]
+
+{{{
+@Transactional
+public UserOption activateExistingAccount(ActivateAccountRequest request) {
+    validateActivationRequest(request);
+
+    UserOption user = findUserByIdentifier(request.identifier());
+
+    if (credentialsExist(user.userId())) {
+        throw new IllegalArgumentException("This account already has a password.");
+    }
+
+    String passwordHash = passwordEncoder.encode(request.password());
+
+    jdbcTemplate.update(
+            """
+            INSERT INTO project.user_credentials (
+                user_id,
+                password_hash
+            )
+            VALUES (?, ?)
+            """,
+            user.userId(),
+            passwordHash
+    );
+
+    return user;
+}
+}}}
+
+This keeps the user account and credential creation consistent.
+
+== Stored Function Call for Approval Workflow ==
+
+Approving or rejecting a reservation is implemented in:
+
+[source:backend/src/main/java/mk/finki/roomreservation/service/ApprovalService.java backend/src/main/java/mk/finki/roomreservation/service/ApprovalService.java]
+
+The Java backend calls a PostgreSQL stored function:
+
+{{{
+public ApprovalResult approveOrReject(ApprovalRequest request) {
+    validateApprovalRequest(request);
+
+    String sql = """
+            SELECT *
+            FROM project.fn_approve_or_reject_reservation(
+                ?,
+                ?,
+                CAST(? AS project.approval_decision_domain),
+                ?
+            )
+            """;
+
+    ApprovalResult result = jdbcTemplate.queryForObject(
+            sql,
+            (rs, rowNum) -> new ApprovalResult(
+                    rs.getInt("out_approval_id"),
+                    rs.getInt("out_reservation_id"),
+                    rs.getString("out_status"),
+                    rs.getString("out_decision"),
+                    rs.getString("out_decision_time")
+            ),
+            request.reservationId(),
+            request.approverId(),
+            request.decision(),
+            request.note()
+    );
+
+    if (result == null) {
+        throw new RuntimeException("Approval operation failed.");
+    }
+
+    return result;
+}
+}}}
+
+The approval logic is intentionally placed inside the database stored function. This demonstrates cooperation between the application layer and the database layer.
+
+The backend validates the request before calling the function:
+
+{{{
+private void validateApprovalRequest(ApprovalRequest request) {
+    if (request.reservationId() <= 0) {
+        throw new IllegalArgumentException("Reservation ID is required.");
+    }
+
+    if (request.approverId() <= 0) {
+        throw new IllegalArgumentException("Approver ID is required.");
+    }
+
+    if (request.decision() == null || request.decision().isBlank()) {
+        throw new IllegalArgumentException("Decision is required.");
+    }
+
+    if (!request.decision().equals("approved") && !request.decision().equals("rejected")) {
+        throw new IllegalArgumentException("Decision must be approved or rejected.");
+    }
+}
+}}}
+
+== Controllers Using the Transactional Services ==
+
+The transactional service methods are exposed through REST controllers.
+
+Reservation creation is exposed through:
+
+[source:backend/src/main/java/mk/finki/roomreservation/controller/ReservationController.java backend/src/main/java/mk/finki/roomreservation/controller/ReservationController.java]
+
+{{{
+@PostMapping("/api/reservations")
+public ReservationDetails createReservation(@RequestBody CreateReservationRequest request) {
+    return reservationService.createReservation(request);
+}
+}}}
+
+User registration and login are exposed through:
+
+[source:backend/src/main/java/mk/finki/roomreservation/controller/AuthController.java backend/src/main/java/mk/finki/roomreservation/controller/AuthController.java]
+
+{{{
+@PostMapping("/register")
+public UserOption register(@RequestBody RegisterRequest request) {
+    return authService.register(request);
+}
+}}}
+
+Approval is exposed through:
+
+[source:backend/src/main/java/mk/finki/roomreservation/controller/ApprovalController.java backend/src/main/java/mk/finki/roomreservation/controller/ApprovalController.java]
+
+{{{
+@PostMapping("/api/approvals")
+public ApprovalResult approveOrReject(@RequestBody ApprovalRequest request) {
+    return approvalService.approveOrReject(request);
+}
+}}}
+
+== Error Handling and Rollback Behavior ==
+
+The application uses a global exception handler:
+
+[source:backend/src/main/java/mk/finki/roomreservation/exception/ApiExceptionHandler.java backend/src/main/java/mk/finki/roomreservation/exception/ApiExceptionHandler.java]
+
+If a validation error or database error occurs, the backend returns a structured error response to the frontend.
+
+Because the important write operations are marked with `@Transactional`, Spring rolls back the transaction when a runtime exception occurs.
+
+This behavior is important for:
+
+ * reservation creation
+ * user registration
+ * account activation
+
+== Summary ==
+
+The final application implements advanced application development concepts through:
+
+ * Spring Boot REST controllers
+ * service-layer database access
+ * Spring `JdbcTemplate`
+ * HikariCP connection pooling
+ * transaction management with `@Transactional`
+ * database stored function integration
+ * environment-based database credentials
+ * BCrypt password hashing
+
+The documentation is aligned with the final Git source code. The previously used prototype class names are no longer referenced. The current implementation documents the actual Spring Boot files that are present in the repository.