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) {
        if (userRepository.findByUsername(username).isPresent()) {
            throw new IllegalArgumentException("Username already exists!");
        }

        User newUser;
        if ("OWNER".equalsIgnoreCase(role)) {
            newUser = new PetOwner();
        } else if ("SITTER".equalsIgnoreCase(role)) {
            newUser = new PetSitter();
        } else {
            throw new IllegalArgumentException("Invalid role selected!");
        }

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

        // Saving a PetOwner or PetSitter automatically saves to the parent 'users' table with InhertianceType.JOINED
        if (newUser instanceof PetOwner) {
            return petOwnerRepository.save((PetOwner) newUser);
        } else {
            return petSitterRepository.save((PetSitter) newUser);
        }
    }

Creating a new booking

    @Transactional
    public Booking createBooking(String ownerId, String sitterId, LocalDate dateFrom, LocalDate dateTo, String address, List<String> petIds, String serviceType) {
        PetOwner owner = petOwnerRepository.findById(ownerId).orElseThrow(() -> new IllegalArgumentException("Invalid owner"));
        PetSitter sitter = petSitterRepository.findById(sitterId).orElseThrow(() -> new IllegalArgumentException("Invalid sitter"));
        List<Pet> pets = petRepository.findAllById(petIds);

        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)));
        }
        
        return bookingRepository.save(booking);
    }

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) {
        PetOwner owner = petOwnerRepository.findById(ownerId).orElseThrow(() -> new IllegalArgumentException("Invalid owner ID"));
        PetType petType = petTypeRepository.findById(petTypeId).orElseThrow(() -> 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);
        
        return petRepository.save(pet);
    }

Managing Reviews

    @Transactional
    public Review addReview(String bookingId, Integer rating, String comment) {
        Booking booking = bookingRepository.findById(bookingId).orElseThrow(() -> new IllegalArgumentException("Invalid booking"));
        Review review = new Review();
        review.setBooking(booking);
        review.setRating(rating);
        review.setComment(comment);
        return reviewRepository.save(review);
    }

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

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)