SQL native query to get a list of objects where date expiring in X

Question

I have some "Card" object:

@Entity
@Table(name = "card", schema = "public")
@Data
@EqualsAndHashCode(callSuper = false)
public class Card  {
    @Id
    @GeneratedValue
    private Long id;
    
    @Column(name="name")
    private String name;

    @Column(name="scope_time_limit")
    private LocalDateTime expireDate;
}

and I want to prepare some native query where I can get the List of expiring cards based on the days I specify. I have this method:

 List<Card> getExpiringCards(Integer numberOfDaysToExpiringCard) {
        return cardRepository.getExpiringCardsByDay(numberOfDaysToExpiringAgreement);
    }

What is the most optimal query? I was thinking about native query.

Thanks in advance.

A [mcve] is a great start when asking SQL questions. – jarlh Dec 27 '21 at 11:56 — jarlh, Dec 27 '21 at 11:56

ata · Accepted Answer · 2021-12-27T12:14:36.310

You lack some details about what are you using and how are you accessing your DB etc. However, I wanted to show an example using Spring Data just for the rough idea.

Your repository:

public interface CardRepository extends JpaRepository<Card, Integer> {
   List<Card> findByExpireDateAfter(LocalDateTime expireDate);
}

Somewhere in your service or wherever you use it:

  @Autowired
  CardRepository cardRepository;  

  List<Card> getCardsWillBeExpiredInDays(int days) {
    LocalDateTime someDaysLater = LocalDateTime.now().plusDays(days);
    return cardRepository.findByCreatedAfter(someDaysLater);
  }

SQL native query to get a list of objects where date expiring in X

1 Answers1