CODEWITHSUNDEEP
jpajpql
Hani Z.
Hani Z.

Reputation: 61

Search in key value Entity

Salam,

I have a problem with (key, value) entity searching.

I don't figure out how to write the jpql query to search.. this is my problem

I have document entity, mapped to oneToMany "MetaData" entity

@Entity
public class Document implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="id_document")
    private Integer idDocument;

    @Column(name="date_enregistrement")
    private Timestamp dateEnregistrement;

    // other columns

    //bi-directional many-to-one association to MetaData
    @OneToMany(mappedBy="document")
    private List<MetaData> metaData;

    /*
     getters and setters
    */
}

and MetaData entity 

@Entity
@Table(name="meta_data")
public class MetaData implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @Column(name="id_meta_data")
    private Integer idMetaData;

    @Column(name="key")
    private String key;

    @Column(name="value")
    private String value;

    //bi-directional many-to-one association to Document
    @ManyToOne
    @JoinColumn(name="id_document")
    private Document document;

    /*
     getters and setters
    */

}

What i want to do is to search for documents by providing some metadata as parameters.

examples:

Find documents where sender (key = sender) is Youssef (value = Youssef) And receiver (key) is Hamza (value)

it's possible that the client provides more than two parameters.

Thanks in advace

Upvotes: 1

Views: 469

Answers (1)

grizzly
grizzly

Reputation: 608

As key-value pairs can vary in number, I'll suggest to go with CriteraQuery for more flexibilty:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Document> query = criteriaBuilder.createQuery(Document.class);
Root<Document> root = query.from(Document.class);
Join<Document, MetaData> metadataJoin = root.join("metaData", JoinType.INNER);
List<Predicate> predicateList = new LinkedList<Predicate>();
//If a map contains all your key value pairs
for(Map.Entry<String, String> entry : keyValueMap.entrySet()){
  predicateList.add(
    criteriaBuilder.and(
      criteriaBuilder.equal(metadataJoin.get("key"), entry.getKey()), 
      criteriaBuilder.equal(metadataJoin.get("value"), entry.getValue())
    )
  );
}
query.where(criteriaBuilder.or(
  predicateList.toArray(new Predicate[predicateList.size()]))
)
query.groupBy(root.get("idDocument"));
query.having(criteriaBuilder.equal(criteriaBuilder.count(root.get("idDocument")), predicateList.size()));
List<Document> documentList = entityManager.createQuery(query).getResultList();

Upvotes: 1

Related Questions