Advanced Application Development (Transactions, Pooling)

Transactional

In PetSitter, there are strict rules about transactions. Write operations (INSERT, UPDATE, DELETE) are labeled with @Transactional to ensure data consistency and enable automatic fallbacks on failure.

Read operations (SELECT) are optimized with @Transactional(readOnly = true) to avoid unnecessary operations in the background and improve performance.

Authenticating a user

    @Transactional(readOnly = true)
    public User authenticate(String username, String password) {
        User user = userRepository.findByUsername(username).orElse(null);
        // P9 - use BCryptPasswordEncoder here
        if (user != null && user.getPassword().equals(password)) {
            return user;
        }
        return null;
    }

Registering a new user

    @Transactional
    public User registerUser(String username, String password, String firstName, String lastName, String email, String role) {
        logger.info("Attempting to register new user '{}' with role '{}'", username, role);

        // 1. Ensure username is unique
        if (userRepository.findByUsername(username).isPresent()) {
            logger.error("VALIDATION FAILED: Username '{}' is already taken", username);
            throw new IllegalArgumentException("Username already exists!");
        }

        // 2. Validate Role Assignment
        User newUser;
        if ("OWNER".equalsIgnoreCase(role)) {
            newUser = new PetOwner();
        } else if ("SITTER".equalsIgnoreCase(role)) {
            newUser = new PetSitter();
        } else {
            logger.error("VALIDATION FAILED: Invalid role '{}' provided", role);
            throw new IllegalArgumentException("Invalid role selected!");
        }

        newUser.setUsername(username);
        newUser.setPassword(password);
        newUser.setFirstName(firstName);
        newUser.setLastName(lastName);
        newUser.setEmail(email);

        // 3. Saving a PetOwner or PetSitter automatically cascades to the parent 'users' table with InheritanceType.JOINED
        User savedUser;
        if (newUser instanceof PetOwner) {
            savedUser = petOwnerRepository.save((PetOwner) newUser);
        } else {
            savedUser = petSitterRepository.save((PetSitter) newUser);
        }
        
        logger.info("Successfully registered user '{}' with ID {}", savedUser.getUsername(), savedUser.getUserId());
        return savedUser;
    }

Creating a new booking

    @Transactional
    public Booking createBooking(String ownerId, String sitterId, LocalDate dateFrom, LocalDate dateTo, String address, List<String> petIds, String serviceType) {
        logger.info("Attempting to create booking. Owner: {}, Sitter: {}", ownerId, sitterId);

        // 1. Initial Validation
        if (dateFrom == null || dateTo == null || dateFrom.isAfter(dateTo)) {
            logger.error("VALIDATION FAILED: Invalid date range provided");
            throw new IllegalArgumentException("Booking dates are invalid");
        }
        if (petIds == null || petIds.isEmpty()) {
            logger.error("VALIDATION FAILED: No pets selected for booking");
            throw new IllegalArgumentException("At least one pet must be selected");
        }

        // 2.Fetch Entities
        PetOwner owner = petOwnerRepository.findById(ownerId).orElseThrow(() -> {
            logger.error("VALIDATION FAILED: Owner not found with ID: {}", ownerId);
            return new IllegalArgumentException("Invalid owner");
        });
        
        PetSitter sitter = petSitterRepository.findById(sitterId).orElseThrow(() -> {
            logger.error("VALIDATION FAILED: Sitter not found with ID: {}", sitterId);
            return new IllegalArgumentException("Invalid sitter");
        });
        
        List<Pet> pets = petRepository.findAllById(petIds);
        if (pets.size() != petIds.size()) {
            logger.error("VALIDATION FAILED: One or more pet IDs do not exist in the database");
            throw new IllegalArgumentException("Invalid pets selected");
        }

        // 3. Entity Construction
        Booking booking = new Booking();
        booking.setOwner(owner);
        booking.setSitter(sitter);
        booking.setDateFrom(dateFrom);
        booking.setDateTo(dateTo);
        booking.setAddress(address);
        booking.setPets(pets);
        
        if (serviceType != null && !serviceType.isEmpty()) {
            serviceRepository.findAll().stream()
                .filter(s -> s.getType().equals(serviceType))
                .findFirst()
                .ifPresent(service -> booking.setServices(List.of(service)));
        }
        
        // 4. Write Operation
        Booking savedBooking = bookingRepository.save(booking);
        logger.info("Successfully created booking {} for owner {}", savedBooking.getBookingId(), owner.getUsername());
        return savedBooking;
    }

Fetching bookings for a user

    @Transactional(readOnly = true)
    public List<Booking> getBookingsForSitter(String sitterId) {
        return bookingRepository.findBySitter_UserIdOrderByDateFromDesc(sitterId);
    }

    @Transactional(readOnly = true)
    public List<Booking> getBookingsForOwner(String ownerId) {
        return bookingRepository.findByOwner_UserIdOrderByDateFromDesc(ownerId);
    }

Adding a new pet

    @Transactional
    public Pet addPet(String name, Integer age, String specialNeeds, String description, String photoUrl, String ownerId, String petTypeId) {
        logger.info("Attempting to add pet '{}' for owner ID: {}", name, ownerId);

        if (name == null || name.isBlank()) {
            logger.error("VALIDATION FAILED: Pet name is null or empty");
            throw new IllegalArgumentException("Pet name is strictly required");
        }

        // 1. Read/Verify Owner exists
        PetOwner owner = petOwnerRepository.findById(ownerId).orElseThrow(() -> {
            logger.error("VALIDATION FAILED: Owner not found with ID: {}", ownerId);
            return new IllegalArgumentException("Invalid owner ID");
        });
        
        // 2. Read/Verify PetType exists
        PetType petType = petTypeRepository.findById(petTypeId).orElseThrow(() -> {
            logger.error("VALIDATION FAILED: Pet type not found with ID: {}", petTypeId);
            return new IllegalArgumentException("Invalid pet type ID");
        });
        
        Pet pet = new Pet();
        pet.setName(name);
        pet.setAge(age);
        pet.setSpecialNeeds(specialNeeds);
        pet.setDescription(description);
        pet.setPhoto(photoUrl);
        pet.setOwner(owner);
        pet.setPetType(petType);
        
        // 3. Insert the new Pet
        Pet savedPet = petRepository.save(pet);
        logger.info("Successfully added pet '{}' under owner '{}'", savedPet.getName(), owner.getUsername());
        
        return savedPet;
    }

Managing Reviews

    @Transactional
    public Review addReview(String bookingId, Integer rating, String comment) {
        logger.info("Attempting to add review for booking ID: {}", bookingId);

        if (rating == null || rating < 1 || rating > 5) {
            logger.error("VALIDATION FAILED: Invalid rating: {}", rating);
            throw new IllegalArgumentException("Rating must be between 1 and 5");
        }

        Booking booking = bookingRepository.findById(bookingId).orElseThrow(() -> {
            logger.error("VALIDATION FAILED: Booking not found with ID: {}", bookingId);
            return new IllegalArgumentException("Invalid booking");
        });

        // 1. Validate Business Logic
        if (!"Completed".equalsIgnoreCase(booking.getStatus())) {
            logger.error("VALIDATION FAILED: Cannot review booking in status: {}", booking.getStatus());
            throw new IllegalStateException("Only completed bookings can be reviewed");
        }

        // 2. Create and save the review
        Review review = new Review();
        review.setBooking(booking);
        review.setRating(rating);
        review.setComment(comment);
        
        Review savedReview = reviewRepository.save(review);
        logger.info("Successfully inserted review for booking: {}", bookingId);
        
        // 3. Update the booking status
        booking.setStatus("Reviewed");
        bookingRepository.save(booking);
        logger.info("Booking {} status successfully updated to Reviewed", bookingId);
        
        return savedReview;
    }

    @Transactional(readOnly = true)
    public Double getAverageRating(String sitterId) {
        return reviewRepository.getAverageRatingForSitter(sitterId);
    }

Cascade Delete

This method deletes a user and all of their related data. If the application crashes before the final step, all previous deletions in the transaction are automatically rolled back, preventing orphaned data.

    @Transactional
    public void deleteUserAccount(String userId) {
        logger.info("Attempting to securely delete user account with ID: {}", userId);
        
        // 1. Fetch and delete all bookings associated with the user
        List<Booking> ownerBookings = bookingService.getBookingsForOwner(userId);
        ownerBookings.forEach(b -> bookingService.deleteBooking(b.getBookingId()));
        
        List<Booking> sitterBookings = bookingService.getBookingsForSitter(userId);
        sitterBookings.forEach(b -> bookingService.deleteBooking(b.getBookingId()));
        
        // 2. Delete all pets owned by this user
        List<Pet> pets = petService.getPetsByOwner(userId);
        pets.forEach(p -> petService.deletePetAdmin(p.getPetId()));
        
        // Uncomment this temporarily for testing, validation for deletion
        // if (true) {
        //     throw new RuntimeException("Simulated Server Crash!");
        // }
        
        // 3. Delete the user finally
        userRepository.deleteById(userId);
        logger.info("Successfully deleted user account and all related data for ID: {}", userId);
    }

Pooling

Since PetSitter uses Spring Boot, the database connections are automatically handled with HikariCP.

HikariCP comes with the spring-boot-starter-data-jpa dependency, but I explicitly forced the library version to <hikaricp.version>6.2.1</hikaricp.version> in the pom.xml file for more verbose logging capabilities.

HikariCP and logging parameters inside application.properties:

spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.connection-timeout=20000
spring.datasource.hikari.idle-timeout=300000
spring.datasource.hikari.max-lifetime=1200000
spring.datasource.hikari.auto-commit=true

logging.level.com.zaxxer.hikari.HikariConfig=DEBUG
logging.level.com.zaxxer.hikari=DEBUG
logging.level.org.springframework.transaction.interceptor=TRACE
logging.level.org.springframework.orm.jpa.JpaTransactionManager=DEBUG

Pooling configuration

• maximum-pool-size (20): Number of concurrent database connections. This prevents the PostgreSQL server from having too many connections during heavy usage.
• minimum-idle (5): Instructs HikariCP to always keep at least 5 idle in the background, so sudden incoming requests can query the database instantly without disrupting regular users connection.
• connection-timeout (20s): If all 20 connections are in use, new requests will wait 20 seconds for a free slot until it throws a timeout exception.
• idle-timeout (5m): Any connection after the minimum 5th connection that is not actively used for 5 minutes will be terminated
• max-lifetime (20m): Every connection can last up to maximum of 20 minutes.
• auto-commit (true): The database automatically commits/saves every change without explicitly being told to do so.

Logging configuration

Additional verbose logs for the connection pools:

• com.zaxxer.hikari.HikariConfig=DEBUG: Outputs the pooling settings Hikari uses when the application is started.
• com.zaxxer.hikari=DEBUG: Shows pooling status in real time (active vs. idle connections) and logs when connections are created or closed.
• org.springframework.transaction.interceptor=TRACE: Logs exactly when Spring executes a @Transactional method in the code
• org.springframework.orm.jpa.JpaTransactionManager=DEBUG: Logs the actual database transactions happening in the background

Pooling Logs

Below is the log output proving the successful initialization and population of the HikariCP connection pool upon application startup. As configured, exactly 5 background connections are established and kept idle for immediate request processing.

2026-05-25T15:27:56.827Z  INFO 1 --- [petsitter] [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2026-05-25T15:27:56.842Z DEBUG 1 --- [petsitter] [           main] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (7c6a30fc-4cfe-446a-a352-818177bca0d1)
2026-05-25T15:27:57.233Z DEBUG 1 --- [petsitter] [           main] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (7c6a30fc-4cfe-446a-a352-818177bca0d1)
2026-05-25T15:27:57.235Z  INFO 1 --- [petsitter] [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@2ab9e43e
2026-05-25T15:27:57.340Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (3367dbcd-5010-4605-ab92-423e4562c990)
2026-05-25T15:27:57.869Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (3367dbcd-5010-4605-ab92-423e4562c990)
2026-05-25T15:27:57.870Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@411815df
2026-05-25T15:27:57.900Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - After adding stats (total=2/20, idle=2/5, active=0, waiting=0)
2026-05-25T15:27:57.901Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (a71e892a-e173-41ba-909b-ca4e547437fd)
2026-05-25T15:27:58.525Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (a71e892a-e173-41ba-909b-ca4e547437fd)
2026-05-25T15:27:58.525Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@4fdfe38c
2026-05-25T15:27:58.555Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - After adding stats (total=3/20, idle=3/5, active=0, waiting=0)
2026-05-25T15:27:58.556Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (670b5f5c-bddb-4a45-83ef-aa7a30a54530)
2026-05-25T15:27:58.631Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (670b5f5c-bddb-4a45-83ef-aa7a30a54530)
2026-05-25T15:27:58.632Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@4be07eaf
2026-05-25T15:27:58.662Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - After adding stats (total=4/20, idle=4/5, active=0, waiting=0)
2026-05-25T15:27:58.662Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Attempting to create/setup new connection (81793045-e2cb-42de-9b40-e06e476bbea7)
2026-05-25T15:27:58.735Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.PoolBase          : HikariPool-1 - Established new connection (81793045-e2cb-42de-9b40-e06e476bbea7)
2026-05-25T15:27:58.735Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection org.postgresql.jdbc.PgConnection@ee2e9b7
2026-05-25T15:27:58.765Z DEBUG 1 --- [petsitter] [onnection-adder] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - After adding stats (total=5/20, idle=5/5, active=0, waiting=0)