Search code examples
dictionaryjpacriteria

jpa 2 criteria with map key and value


In my entity I have this field

@ElementCollection
@CollectionTable
@MapKeyColumn(name = "SERVER_ID")
@Column(name = "IS_SYNC")
private Map<String, Boolean> serverSyncs = new HashMap<>();

I'm trying to get all entities of my table that do not have an entry with the key equals to "serverId" (passed as parameter in my function) or that have an entry but the value is false.

This is what I've done for now

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = builder.createQuery(clazz);
Root<T> root = criteriaQuery.from(clazz);
MapJoin<T, String, Boolean> mapRoot = root.joinMap("serverSyncs");

List<T> result = session.createQuery(
        criteriaQuery.where(
                builder.or(
                        mapRoot.isNull(),
                        builder.not(mapRoot.key().in(serverId)),
                        builder.and(
                                mapRoot.key().in(serverId),
                                mapRoot.value().in(false)
                        )
                )
        )
).list();

The thing is I get this error on my query

Could not locate CollectionPersister for role : ca.tecsar.core.model.AbstractServerEntity.serverSyncs

Question is : how can I achieve what I want with JPA 2.0 Criteria?

Example of what I need to retrieve

id|SERVER_ID|IS_SYNC
1|0000000001|true
1|0000000002|false
2|0000000003|false

If I ask for SERVER_ID = 3,I should get entity 1 and 2
If I ask for SERVER_ID = 2,I should get entity 1
If I ask for SERVER_ID = 1,I should get nothing


Solution

  • So I couldn't do it with JPA 2 Criteria but I've succeeded with a SQL Query. I have a table named PUNCH and the map table is PUNCH_SERVERSYNCS.

    SELECT p.PUNCHID
    FROM PUNCH p
    LEFT JOIN PUNCH_SERVERSYNCS pss
    ON p.PUNCHID = pss.PUNCH_PUNCHID
    WHERE (pss.IS_SYNC = false AND pss.SERVER_ID = 'Server2')
    OR NOT EXISTS (SELECT p.PUNCHID FROM PUNCH_SERVERSYNCS pss2 WHERE 
    pss2.PUNCH_PUNCHID = p.PUNCHID AND pss2.SERVER_ID = 'Server2')
    GROUP BY p.PUNCHID