java - Using JPA with multiple AND operations

Question

I'm working on a Spring app and defining various find methods on a repository:

@Repository
public interface TicketRepository extends JpaRepository<TicketEntity, Long> {

List<TicketEntity> findByTicketId(@Param("ticketId") Long ticketId);

List<TicketEntity> findByTicketIdAndState(@Param("ticketId") Long ticketId, @Param("state") String state);

List<TicketEntity> findByTicketIdAndStateAndFlagged(@Param("ticketId") Long ticketId, @Param("state") String state, @Param("flagged") String Flagged);

}

The problem is that I have 30 columns which can be optionally filtered on. This is will result in the repository methods becoming unwieldy:

List<TicketEntity> findByTicketIdAndStateAndFlaggedAndCol4AndCol5AndCol6AndCol7AndCol8AndCol9AndCol10AndCol11AndCol12AndCol13AndCol14AndCol15AndCol16AndCol17AndCol18AndCol19AndCol120....);

How should the JPA layer be designed to cater for this scenario ?

If I create an object with attributes:

public class SearchObject {
   private String attribute1; 
   //Getter and Setters
.
.
.
.
}

Can I pass SearchObject into a a find method and Spring JPA will determine which attributes to insert AND statements for depending on which attributes are Null - if the attribute is not null a corresponding AND is generated for that attribute.

Answer 1

1. Create filter object that will contain all optional columns e.g.:

@AllArgsConstructor public class TicketFilter {

private final String col1;
private final Integer col2;

public Optional<String> getCol1() {
    return Optional.ofNullable(col1);
}

public Optional<Integer> getCol2() {
    return Optional.ofNullable(col2);
}

}

2. Extend your Respoitory with JpaSpecificationExecutor

3. Create specification class:

   public class TicketSpecification implements Specification {

   private final TicketFilter ticketFilter;
   
   public TicketSpecification(TicketFilter ticketFilter) {
       this.ticketFilter = ticketFilter;
   }
   
   @Override
   public Predicate toPredicate(Root<Ticket> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) {
       List<Predicate> predicates = new ArrayList<>();
       ticketFilter.getTitle().ifPresent(col1 -> predicates.add(getCol1Predicate(root, col1)));
       ticketFilter.getDescription().ifPresent(col2 -> predicates.add(getCol2Predicate(root, col2)));
       return criteriaBuilder.and(predicates.toArray(new Predicate[0]));
   }
   

   private Predicate getCol1Predicate(Root root, String title) { return root.get("col1").in(col1); }

   }

4. Use your repository: ticketRepository.findAll(specification);