Search code examples
javahibernatejpajpa-2.0jpql

JPA Query For Exists In

I have the following Entities (reduced and renamed for this example)

@Entity
public class Case {

@Id
private Long id;


@ManyToOne(optional=false)
private CourtConfiguration courtConfiguration;

@ElementCollection(fetch=FetchType.EAGER)
private List<String> caseNumbers;

}

Second Entity

@Entity
public class CourtConfiguration {

@Id
private Long id;

String countyId;

String referenceId;

.... 

}

I am trying to search using JPQL for all Cases that have a certain courtConfiguration countyId and have caseNumbers containing all of a provided set of important caseNumbers.

So my query needs the countyId and set of caseNumbers as parameters. Called countyId and importantCaseNumbers respectively.

I have tried and failed to get it to work.

My query looks like this 

String query = "SELECT case FROM Case case JOIN case.caseNumbers caseNumbers WHERE ";
query       += "case.caseConfiguration.countyId = :countyId ";

The bit above works until I add my caseNumber conditions. I have tried a foreach importantNumbers to extend the query and as soon as the list of important numbers goes above one it doesn't work. No values get returned.

for (String importantCaseNum : importantCaseNumbers) {
    query += " AND '"+importantCaseNum+"' in (caseNumbers)";
}

Any suggestions/pointers appreciated. I guess what I am looking for is a case.caseNumbers contains (importantNumbers) clause.

Update I have reverted to native SQL for my query as I didn't want to tie myself into hibernate by using HQL. Thanks to @soulcheck and @mikko for helping me out. I'll post up when the hibernate JPA fix is available.

Thanks Paul

Solution

  • Syntactically correct way to build this JPQL query is with MEMBER OF. But because of problem reported in HHH-5209 it doesn't work with old Hibernate versions (fix version 4.1.8, 4.3.0.Beta1). According bug report HQL version of this query works, so your options includes at least:

    1. Using JPQL query and switching to some other JPA implementation

    2. Using HQL instead and sticking with Hibernate:

      query += " AND '"+importantCaseNum+"' in elements(caseNumbers)";