CODEWITHSUNDEEP
javaspringjpaspring-data
Roman Gagarin
Roman Gagarin

Reputation: 21

How can I add and build dynamic predicates to subquery using jpa specification?

I'm trying to build dynamic subquery with a JPA Specification. How can I add predicates to the subquery and build it?

for example, I'll have 2 tables: User and Usercard:

@Entity
    @Table(name = "users", schema = "someschema")
    public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    private String name;
    private String surName;
    private String email;

    @OneToMany
    private List<Usercard> usercardList;

//other methods...
}

and

@Entity
@Table(name = "usercard", schema = "someschema")
public class Usercard {
    @Id
    private Long id;
    private String account;
    private String value;

    @ManyToOne
    private User user;

//other methods...
}

I have my repo:

@Repository
public interface UserRepository extends JpaRepository<User, Long>, 
JpaSpecificationExecutor<User> {}

And trying to build smth like:

public List<User> findByPredicate(String email) {
    return userRepository.findAll((Specification<User>) (root, 
        criteriaQuery, criteriaBuilder) -> {

        List<Predicate> predicates = new ArrayList<>();
        if (email != null) {
        predicates.add(criteriaBuilder.and(criteriaBuilder.equal( 
                                       root.get("email"), email)));
        }
        return criteriaBuilder.and(predicates.toArray(new 
                Predicate[predicates.size()]));
    });
}

but for subquery with predicates.

I've tried methods like this:

public List<User> findByUsercardAccount(String email, String account) {
    return userRepository.findAll(new Specification<User>() {
        @Override
        public Predicate toPredicate(Root<User> root, CriteriaQuery<?> 
            criteriaQuery, CriteriaBuilder criteriaBuilder) {

            Subquery<Usercard> subquery = 
                criteriaQuery.subquery(Usercard.class);
            Root<Usercard> subRoot = subquery.from(Usercard.class);
            List<Predicate> predicates = new ArrayList<>();

            //predicates for Users table
            predicates.add(criteriaBuilder.and(criteriaBuilder.equal( 
                       root.get("email"), email)));
            //predicates for Usercard table
            predicates.add(criteriaBuilder.equal(subRoot.get("account"), 
                       account));


            return criteriaBuilder.and(predicates.toArray(new 
                        Predicate[predicates.size()]));
        }
    });
}

So, I need a method for dynamic search within a few tables where I can pass arguments for a dynamic query as well as a dynamic subquery. I would be grateful for any help.

Upvotes: 1

Views: 4172

Answers (1)

Roman Gagarin
Roman Gagarin

Reputation: 21

Found this solution:

public List<User> findByUsercardAccount(String account, String email) {
    return userRepository.findAll((Specification<User>) (root, criteriaQuery, criteriaBuilder) -> {

        Subquery<User> subquery = criteriaQuery.subquery(User.class);
        Root<Usercard> subRoot = subquery.from(Usercard.class);

        List<Predicate> predicates = new ArrayList<>();
        List<Predicate> subPredicates = new ArrayList<>();

        if (account != null)
            subPredicates.add(criteriaBuilder.equal(subRoot.get("account"), account));

        if (email != null)
            predicates.add(criteriaBuilder.and(criteriaBuilder.equal(root.get("email"), email)));

        subquery.select(subRoot.get("id")).where(subPredicates.toArray(new Predicate[predicates.size()]));

        predicates.add(criteriaBuilder.exists(subquery));

        return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
    });
}

}

So, I've created subroot and subquery, added some restrictions (subPredicates) and passed them to the main root as a predicate.

Upvotes: 1

Related Questions