Query if a array column contains using Criteria

Question

I have a table named users with a column groupIDs which is list of Long type.

Can I create a query using Criteria so that select all users which have a given groupID in it's groupIDs column. I am using Spring boot and PostgreSQL.

I need result something like:

SELECT * 
FROM users 
WHERE groupID IN groupIDs

Can you share a bit more of your code? E.g. the `User` entity and its relation with groupsIds? — pleft, Oct 22 '21 at 05:22
@pleft Sorry, I am not allowed to share code, but UserEntity has just a list that contains Long values representing groups they are part of, and there is no other connection implemented. — Rishabh Ryber, Oct 22 '21 at 05:27
You can't share `private List groupIDs`? Does this property has an annotation over it? like `@OneToMany` ? You can share such small details without exposing your full code. — pleft, Oct 22 '21 at 05:35
Your SQL Query in your question does not reflect what you describe: *Can I create a query using Criteria so that select all users which have a given groupID in it's groupIDs column*. -> This is not an "IN" query. In an "IN" query the input parameter is the **list** whereas in your description the input parameter is the **specific groupId**. Please clarify your question. — pleft, Oct 22 '21 at 05:54

score 1 · Accepted Answer · answered Oct 22 '21 at 06:16

You can use list parameters.

Sql Query:

SELECT * 
FROM users 
WHERE groupID IN (:groupIds)

Then assign parameters:

Map<String,Object> params = new HashMap<>();
params.put("groupIds",Arrays.asList(1l,2l....));
dao.query(sql,params);

For changing to Criteria, refer to:

https://stackoverflow.com/a/42531461/1439560

Query if a array column contains using Criteria

1 Answers1