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
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