how to pass Map or List<Object> as a parameter to JPA query-CodePudding

I need to pass a map as input parameter of type <Sting,String> or List into two JPA columns and get the result as List of records for all the entries in the map/list.

Something like this:

@Query(
      value =
          "SELECT e from #{#entityName} e where e.name = KEY(someMap) and e.relationName = VALUE(someMap)")
  List<Member> findByNameAndRelationNameIn(
          @Param("someMap") Map<String, String> someMap);

@Query(
      value =
          "SELECT e from #{#entityName} e where e.name IN (:#{#dataSpaceMembers.?[name]}) and e.dataSpaceName IN (:#{#dataSpaceMembers.?[dataSpaceName]})")
  List<DataSpaceMember> findByNameAndDataSpaceIn(
      @Param("dataSpaceMembers") List<DataSpaceMember> dataSpaceMembers);

But the application doesn't run since this is not a valid jpa query. I don't want to run single queries in loop, but looking for a single query which can give result as a list.

CodePudding user response：

I don't think this is possible using @Query notation, however, like Chris said in the comments, you can pretty quickly write some code to dynamically create this kind of statement using a criteria query.

I think the code you're looking for would be something like this, where the supplied Map is a set of key/value pairs with the key being name and the value being relationName.


    EntityManager em;

    public List<Member> get(Map<String, String> map) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Member> cq = cb.createQuery(Member.class);

        Root<Member> root = cq.from(Member.class);
        List<Predicate> predicates = new ArrayList<>();
        for (Map.Entry<String, String> e : map.entrySet()) {
            Predicate a = cb.equal(root.get("name"), e.getKey());
            Predicate b = cb.equal(root.get("relationName"), e.getValue());
            predicates.add(cb.and(a, b));
        }

        cq.where(cb.or(predicates.toArray(new Predicate[0])));
        return em.createQuery(cq).getResultList();
    }

Thr query this produces should have a combination of AND and OR statements that you're desiring, ie

SELECT * FROM member WHERE ((name = ? AND relationName = ?) OR (name = ? AND relationName = ?) ... etc)

Note:

Since a Map can only contain a key once, you might consider using a List<KeyValue<String, String>> or List<Pair<String, String>> instead. Both KeyValue and Pair can be found in Apache commons libraries and I believe Spring also includes a Pair implementation. Or you can write your own.