Criteria API expression for where first element of array column is equal to

331 Views Asked by At

I'm trying to delete some entries from a table and the condition of the delete statement is "if the first element in the userIds array is 98 then delete".

My entity class looks something like this:

@TypeDefs({
    @TypeDef(
            name = "long-array",
            typeClass = LongArrayType.class
    )
})
public class MyEntity {
    @Type(type = "long-array")
    @Column(name = "user_ids", columnDefinition = "_int8")
    private Long[] userIds;
}

Is there a way to achieve this via Criteria API?

I've tried many variations but none of them worked:

var cb = entityManager.getCriteriaBuilder();
var query = cb.createCriteriaDelete(MyEntity.class);
var root = query.from(MyEntity.class);

Path<Long[]> userIdsPath = root.get(MyEntity.USER_IDS);
query.where(cb.equal(userIdsPath.get("1"), cb.literal(userId)));

entityManager.createQuery(query).executeUpdate();

Attempt 2:

query.where(cb.equal(cb.function("trim_array", Long.class, root.get(MyEntity.USER_IDS), cb.literal(1)), userId));

Attempt 3:

query.where(cb.equal(cb.literal(MyEntity.USER_IDS + "[1]"), userId));

Is there a way I can create an expression for the where clause by giving it a hardcoded string? "userIds[1] = 98"

0

There are 0 best solutions below