Advanced Application Development

Transactional

Enrolling a user to a course

@Transactional
public void enrollUserToCourse(Integer courseId, String email) {
    UserEntity userEntity = userEntityRepository.findByEmail(email).get();
    User user = userRepository.findById(userEntity.getId()).get();
    Course course = courseRepository.findById(courseId).get();

    if(userSubscriptionService.hasUserSubscription(email)
        && enrollmentRepository.findByCourseAndUser(course, user).isEmpty()) {

        Enrollment enrollment = new Enrollment();
        enrollment.setUser(user);
        enrollment.setCourse(course);
        enrollment.setEnrollDate(LocalDate.now());
        enrollment.setCompletionStatus("NOT COMPLETE");
        enrollment.setProgressPercentage(0);

        enrollmentRepository.save(enrollment);
    }
}

• This transaction is responsible for enrolling a user in a course.
• The method validates the user subscription and checks if the user is already enrolled before creating a new enrollment entry.

Completing a course and generating a certificate

@Transactional
public void completeCourse(Integer courseId, String email) {
    UserEntity userEntity = userEntityRepository.findByEmail(email).get();
    User user = userRepository.findById(userEntity.getId()).get();
    Course course = courseRepository.findById(courseId).get();

    Random random = new Random();

    Enrollment enrollment = enrollmentRepository.findByCourseAndUser(course, user).get();
    enrollment.setCompletionStatus("COMPLETE");
    enrollment.setProgressPercentage(100);
    enrollmentRepository.save(enrollment);

    Certificate certificate = new Certificate();
    certificate.setEnrollment(enrollment);
    certificate.setIssueDate(LocalDate.now());
    certificate.setStatus("COMPLETE");
    certificate.setCertificateCode("CODE" + random.nextLong());

    certificateRepository.save(certificate);
}

• This transaction updates the enrollment status and generates a certificate after successful course completion.
• Both operations are executed within a single transaction to preserve database consistency.

Adding a user subscription and payment

@Transactional
public void addUserSubscription(String email, Integer subscriptionPlanId) {
    UserEntity userEntity = userEntityRepository.findByEmail(email).get();
    User user = userRepository.findById(userEntity.getId()).get();
    SubscriptionPlan subscriptionPlan = subscriptionPlanRepository.findById(subscriptionPlanId).get();

    UserSubscription userSubscription = new UserSubscription();
    userSubscription.setUser(user);
    userSubscription.setPlan(subscriptionPlan);
    userSubscription.setStartDate(LocalDate.now());
    userSubscription.setEndDate(
        LocalDate.now().plusMonths(subscriptionPlan.getDurationMonths())
    );
    userSubscription.setStatus("ACTIVE");

    userSubscriptionRepository.save(userSubscription);

    Payment payment = new Payment();
    payment.setUser(user);
    payment.setSubscription(userSubscription);
    payment.setAmount(subscriptionPlan.getPrice());

    paymentRepository.save(payment);
}

• This transaction creates a new subscription for a user and also creates a payment record connected to the subscription.

Checking expired subscriptions

@Scheduled(cron = "0 0 0 * * *")
@Transactional
public void checkExpiredSubscriptions() {

    LocalDate today = LocalDate.now();

    List<UserSubscription> expiredSubscriptions =
        userSubscriptionRepository
            .findByEndDateBeforeAndStatusNot(today, "EXPIRED");

    for (UserSubscription subscription : expiredSubscriptions) {
        subscription.setStatus("EXPIRED");
        userSubscriptionRepository.save(subscription);
    }
}

• This scheduled transaction automatically checks and updates expired subscriptions every day.

Creating a support ticket

@Transactional
public void addSupportTicket(
    String email,
    String subject,
    String description
) {

    UserEntity userEntity = userEntityRepository.findByEmail(email).get();
    User user = userRepository.findById(userEntity.getId()).get();

    List<Administrator> admins = administratorRepository.findAll();

    Random random = new Random();
    Administrator assignedAdmin =
        admins.get(random.nextInt(admins.size()));

    SupportTicket supportTicket = new SupportTicket();

    supportTicket.setUser(user);
    supportTicket.setAdmin(assignedAdmin);
    supportTicket.setSubject(subject);
    supportTicket.setDescription(description);
    supportTicket.setStatus("UNRESOLVED");
    supportTicket.setCreatedAt(LocalDateTime.now());

    supportTicketRepository.save(supportTicket);
}

• This transaction creates a support ticket and automatically assigns an administrator responsible for resolving the issue.

Resolving a support ticket

@Transactional
public void resolveSupportTicket(Integer ticketId) {

    SupportTicket supportTicket =
        supportTicketRepository.findById(ticketId).get();

    supportTicket.setStatus("RESOLVED");

    supportTicketRepository.save(supportTicket);
}

• This transaction updates the support ticket status from unresolved to resolved.

Database Connection Pooling

• The application uses Spring Boot with Spring Data JPA to communicate with the PostgreSQL database.
• Database connections are not created manually. Instead, they are automatically managed by HikariCP, which is the default connection pool implementation in Spring Boot.
• HikariCP is included automatically through:

  spring-boot-starter-data-jpa
  

• The following configuration is used in the application:

  spring.application.name=db
  
  spring.datasource.url=jdbc:postgresql://localhost:5432/db_202526z_va_prj_olpms
  spring.datasource.username=db_202526z_va_prj_olpms_owner
  spring.datasource.password=3ad0b6760a91
  spring.datasource.driver-class-name=org.postgresql.Driver
  
  spring.jpa.hibernate.ddl-auto=validate
  
  spring.jpa.show-sql=true
  spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
  
  spring.datasource.hikari.maximum-pool-size=10
  spring.datasource.hikari.minimum-idle=5
  spring.datasource.hikari.idle-timeout=30000
  spring.datasource.hikari.connection-timeout=20000
  

• These values allow the application to:
  • reuse database connections efficiently
  • improve performance
  • reduce connection creation overhead
  • support multiple simultaneous database requests

• Database connections from the pool are automatically used in repository operations such as:
  • courseRepository.save()
  • enrollmentRepository.save()
  • userSubscriptionRepository.save()
  • supportTicketRepository.save()