Bootcamp: Exercicio de Agendamento de Tarefas implementado nas sessões de check-outs estendidas

application.yml

##
# Spring Boot, JPA/Hibernate, DataSource (Hikari)
##
spring:
  application:
    name: nosso-cartao-propostas
  output:
    ansi:
      enabled: ALWAYS
  datasource:
    platform: postgres
    url: ${DATABASE_URL:jdbc:postgresql://localhost:5432/proposal}
    username: ${DATABASE_USERNAME:keycloak}
    password: ${DATABASE_PASSWORD:password}
    hikari:
      connection-timeout: 1000
      idle-timeout: 10000
      minimum-idle: 10
      maximum-pool-size: 20
  jpa:
    show-sql: ${JPA_SHOW_SQL:true} # better off configured in Logstack
    properties:
      hibernate:
        format_sql: ${JPA_FORMAT_SQL:true}
        jdbc:
          batch_size: 5 # used in the AttachActiveCardsToProposalsJob class
        order_inserts: true
        order_updates: true
    hibernate:
      ddl-auto: update # should not be used in production
    open-in-view: false

AssociarPropostasAosCartoesJob.java

package br.com.zup.edu.nossocartao.propostas.cards.jobs;

import br.com.zup.edu.nossocartao.propostas.cards.CardRepository;
import br.com.zup.edu.nossocartao.propostas.cards.integration.CardsClient;
import br.com.zup.edu.nossocartao.propostas.cards.integration.FindCardByProposalIdResponse;
import br.com.zup.edu.nossocartao.propostas.cards.model.Card;
import br.com.zup.edu.nossocartao.propostas.proposals.ProposalRepository;
import br.com.zup.edu.nossocartao.propostas.proposals.model.Proposal;
import br.com.zup.edu.nossocartao.propostas.proposals.model.ProposalStatus;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Component;
import org.springframework.transaction.support.TransactionTemplate;

import java.util.List;

@Component
public class AssociarPropostasAosCartoesJob {

    @Autowired
    private ProposalRepository proposalRepository;
    @Autowired
    private CardsClient client;
    @Autowired
    private CardRepository cardRepository;
    @Autowired
    private TransactionTemplate transactionManager;

    @Scheduled(fixedDelay = 5*1000) // e se rodar a cada 12h ou 24h?
    public void execute() {

        boolean pending = true;
        while (pending) {

            //noinspection ConstantConditions
            pending = transactionManager.execute((transactionStatus -> {

                List<Proposal> eligibleProposals = proposalRepository.findTop5ByStatusOrderByCreatedAtAsc(ProposalStatus.ELIGIBLE);
                if (eligibleProposals.isEmpty()) {
                    return false;
                }

                eligibleProposals.forEach((proposal -> {

                    FindCardByProposalIdResponse response = getFindCardByProposalIdResponse(proposal); // timeout=100
                    if (response != null) {
                        Card card = response.toModel();
                        cardRepository.save(card); // auto-commit=false

                        proposal.attachTo(card);
                        proposalRepository.save(proposal); // auto-commit=false
                    }
                })); // tx-timeout=1s

                return true;
            }));

        }
    }

    private FindCardByProposalIdResponse getFindCardByProposalIdResponse(Proposal proposal) {
        try {
            FindCardByProposalIdResponse response = client.findCardByProposalId(proposal.getId().toString());
            return response;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

}

ProposalRepository.java

package br.com.zup.edu.nossocartao.propostas.proposals;

import br.com.zup.edu.nossocartao.propostas.proposals.model.Proposal;
import br.com.zup.edu.nossocartao.propostas.proposals.model.ProposalStatus;
import org.hibernate.LockOptions;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Lock;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.jpa.repository.QueryHints;
import org.springframework.stereotype.Repository;

import javax.persistence.LockModeType;
import javax.persistence.QueryHint;
import java.util.List;
import java.util.UUID;

@Repository
public interface ProposalRepository extends JpaRepository<Proposal, UUID> {

    public boolean existsByDocument(String document);

    List<Proposal> findByStatus(ProposalStatus status);

    @Lock(LockModeType.PESSIMISTIC_WRITE) // for update
    @QueryHints({
            @QueryHint(name = "javax.persistence.lock.timeout", value = (LockOptions.SKIP_LOCKED + "")) // ...skip locked
    })
    List<Proposal> findTop5ByStatusOrderByCreatedAtAsc(ProposalStatus status);

}

queries-utilizadas-na-sessao.sql

select *
  from proposal p 
 where p.status = 'ELIGIBLE'
 order by p.created_at asc
 limit 5
 for update skip locked
 ;
 
 select *
  from proposal p 
 where p.status = 'ELIGIBLE'
 order by p.created_at asc
 limit 5
 for update -- sem skip locked
 ;
 
 select *
  from proposal p 
 where p.id = '8ab46c71-b89e-4d50-931f-d0d8e9125f25'
 for update skip locked
 ;
 
 update proposal 
   set created_at = now()
 where id = '8ab46c71-b89e-4d50-931f-d0d8e9125f25'
 ;