Exception on select with in clause using hibernate JPA CriteriaQuery

Question

I have this entity :

@Entity(name="TestEntity")
@Table(name = "TestTable")
public class TestEntity {
    private Long id;
    ... some fields ...
    private List<TestEntity> children;


    @ManyToMany(cascade = CascadeType.ALL)
    @Column
    public List<TestEntity> getChildren() {
        return children;
    }

    public void setChildren(List<TestEntity> children) {
        this.children = children;
    }
}

And I want to create a search criteria with the inner children field with something like this :

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<?> query = builder.createQuery(theClass);
Root from = query.from(theClass);
Path objectPath = from.get("children");
predicate = objectPath.in(1, 2, 3, 4, 5, 6);
Predicate ands = builder.and(predicate to array);
query.select(from).where(ands);

But I have this exception

  Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement : 

  select  testentity0_.id as id1_0_, testentity0_.code as code2_0_, testentity0_.description as descript3_0_, testentity0_.mainType as mainType4_0_ from TestTable testentity0_ cross join TestTable_TestTable children1_, TestTable testentity2_ where testentity0_.id=children1_.TestTable_id and children1_.children_id=testentity2_.id and (. in (1 , 2 , 3 , 4 , 5 , 6)) [42001-182]

did you try the select testen... in your toad or something to check syntax — shareef, Dec 14 '14 at 09:00
Yes, In normal case it is correct. but the exception raises when I want to select on children — Pooya, Dec 14 '14 at 09:07
I once faced about similar problem my case was the children was not fetched at all or fetched as null or empty collection ,,, after many many investigations it appears that if you tried it on glassfish it may or may not work , and in my case it worked but when i deployed it on weblogic there was a mess, the reason is *the libraries and JPA version implementation * especially when you transfer to upper version lik JPA 2.0 2.1 and you did your work on older versions i hope this will give you a hint — shareef, Mar 12 '15 at 13:13

score 0 · Answer 1 · answered Dec 14 '14 at 14:11

0

Invalid JPQL. You can't have a collection field "IN" some other collection. You have to have an element "IN" some collection. Also your element has to be the same type as the elements of the collection "IN" (which yours isn't either). Read up on JPQL in the docs of your JPA implementation

answered Dec 14 '14 at 14:11

Neil Stockton

11,383
3
34
29

Ok, How can I find join table and join the selected table with it? – Pooya Dec 14 '14 at 14:17
You don't "find" any join table in JPQL, it is an internal concept for the JPA implementation. You use fields in your classes. You can easily enough do a JOIN in JPQL (or criteria) as per all JPQL docs. Then you put a WHERE restriction on the element of the collection (TestEntity). For example http://www.datanucleus.org/products/accessplatform_4_0/jpa/jpql_criteria.html – Neil Stockton Dec 14 '14 at 14:23
My implementation is hibernate. But look at the generated sql. it contains all of the correct joins. but the column name in where is dot – Pooya Dec 14 '14 at 14:33
As already said, you need to do a JOIN, and then use the field of the element. In this case you would use the "id" as the prefix of the IN. No interest in which implementation, or what SQL since this is in the JPA spec. – Neil Stockton Dec 14 '14 at 16:48

Exception on select with in clause using hibernate JPA CriteriaQuery

1 Answers1