I am having difficulty constructing a Hibernate query, and would like some help if possible.

I have a class UserProfileRecordImpl that contains user profile information in a table profile_tbl. I also have a class KeywordRecordImpl that contains keywords in a table keyword_tbl. I've created a many-to-many association using a join table profile_keyword_tbl that allows users to enter keywords describing their profile. The association works fine, and I am able to create user profiles with keywords without any problem.

The query I would like to create, however, is one that would allow me to locate user profiles that contain some or all of the keywords I pass in. For example:

I have a user A with keyword "keyword_a", a user B with "keyword_b", and a user C with keywords "keyword_a" and "keyword_b". When I query for users with keyword "keyword_a" I would like to receive both user A and user C. When I query for users with both "keyword_a" and "keyword_b" I would like to receive ONLY user C.

The query I have thus far looks like this:

SELECT DISTINCT p FROM UserProfileRecordImpl p JOIN p.keywordRecords AS k"
+ " WHERE k.keyword IN (:keywordList)

where keywordList is a simple ArrayList containing the keywords I'm searching for. When I run my first query, it returns user A as expected. However, when I run my second query, I receive all 3 years. The query is essentially performing an "OR" for the keywords, instead of an "AND".

Can anyone tell me how I can write a query that lets me search for users that have ALL of the keywords I specify? Thanks much in advance!!

Steve Nakhla