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
– pleft, Commented Oct 22, 2021 at 5: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
– Rishabh Ryber, Commented Oct 22, 2021 at 5:27
You can't share private List<Long> groupIDs? Does this property has an annotation over it? like @OneToMany ? You can share such small details without exposing your full code. — pleft
– pleft, Commented Oct 22, 2021 at 5: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
– pleft, Commented Oct 22, 2021 at 5:54

Huy Nguyen · Accepted Answer · 2021-10-22 06:16:42Z

1

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

answered Oct 22, 2021 at 6:16

Huy Nguyen

2,0611 gold badge12 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Query if a array column contains using Criteria

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related