Hibernate named query parameter IN list

Question

I have found lots of answers for how to send a list parameter in to a query and check if a value is in that list but I'm trying to do the opposite - pass in the value and check if it's contained in a list in the object.

I have the following code to try to retrieve a Person using their username.

Person person = uniqueResult(namedQuery(Person.FIND_BY_USERNAME)
    .setParameter("username", username).setMaxResults(1));

The username is contained in a list in the Person object.

@Column(name = "usernames")
@Convert(converter = PersonUsernameConvertor.class)
private List<String> usernames;

Is it possible to get the Person with the username parameter in their list with a NamedQuery or do I need something else? Below is what I have so far but it's not working, I'm guessing because the parameter value is on the left of the equation.

@NamedQuery(name = Person.FIND_BY_USERNAME,
    query = "SELECT p from Person p WHERE :username IN p.usernames)

Ponni · Accepted Answer · 2018-02-27 05:09:06Z

2

Example1:

@NamedQuery(name = Person.FIND_BY_USERNAME,
query = "SELECT p from Person p WHERE p.usernames in (:username)")

If usernames list contains only John and passing the parameter username with john, the above query works and returns the result.

Example2:

@NamedQuery(name = Person.FIND_BY_USERNAME,
query = "SELECT p from Person p WHERE p.usernames like CONCAT('%',:username,'%')")

If usernames list contains John,Joe and passing the parameter username with joe,the above query will check the list whether joe exists in the list or not.

answered Feb 27, 2018 at 5:09

Ponni

4081 gold badge5 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Hibernate named query parameter IN list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related