Criteria API find Key-Value Pairs in Map

Question

I try to use the Criteria API to create a dynamic JPA-Query. I need to find a key-value pair inside a map of the object. The Object looks similar to the following one.

public class item {
    private UUID id;
    @Column(name = "properties", columnDefinition = "nvarchar")
    private Map<String, Object> properties;
}

I thought I could use the MapJoin join or joinMap:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Item> criteriaQuery = cb.createQuery(Item.class);
Root<Item> itemRoot = criteriaQuery.from(Item.class);
criteriaQuery.select(itemRoot);

Join<String, Object> properties = itemRoot.join("properties");
// or 
//MapJoin<Item, String, Object> properties =  itemRoot.joinMap("properties");

Predicate pre1 = cb.equal(properties.get(ITEM_PROPERTY_1), "123");
Predicate pre2 = cb.equal(properties.get(ITEM_PROPERTY_2), "456");
Predicate propertiesPredicate = cb.and(pre1, pre2);
criteriaQuery.where(propertiesPredicate);
Item item = em.createQuery(criteriaQuery).getSingleResult();

But I've read that this is only for associations. On the join i get an:

IllegalArgumentException: Requested attribute was not a map.

So could sb explain to me, how I will be able to find a key-value pair in a map with the Criteria API?

Edit: I am not able to change anything in the DB.

score 0 · Answer 1 · answered Sep 01 '21 at 09:12

So I need to guess a little bit because you didn't show us your DB Table, that's why I answer a little bit more freely. And as a disclaimer: it might be easier and it would be more efficient to query a real table instead of an serialized object/json. But I would do it this way:

The Table in MSSQL:

create table ${schema}.myTable
(
    id      bigint identity
            constraint PK_myStuff
            primary key,
    properties nvarchar(max) not null
) go

The Java entity (draft):

public class Item extends AbstractPersistable<...> {
    @Column(name = "properties", columnDefinition = "nvarchar")
    private String properties;
}

The Java Specification:

protected Specification<Item> customFilter(String filterArg) {
    return (root, query, cb) ->
            cb.like(root.get(Item_.properties), filterArg);
  }

This way your query searches the properties for a string pattern.

Info: https://vladmihalcea.com/sql-server-json-hibernate/

Criteria API find Key-Value Pairs in Map

1 Answers1