Hibernate hql query syntax error

Question

trying to execute this query :

public List<Product> getChildrenById(int id) {          
        String hql= " FROM Product"
                + " START WITH parent_id = ?"
            + " CONNECT BY NOCYCLE PRIOR  id = parent_id"
            + " ORDER SIBLINGS BY id"; 
        return (List<Product>) entityManager.createQuery(hql).setParameter(1, id).getResultList();
    }

i got a syntax error:org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: WITH near line 1, column 44 [ FROM com.example.gmao.model.Product START WITH parent_id = ? CONNECT BY NOCYCLE PRIOR id = parent_id ORDER SIBLINGS BY id];

any help? thx

my product class is defined as below: public class Product { @Id @GeneratedValue(strategy=GenerationType.AUTO) @Column(name="id") private int id; @Column(name="name") private String value; @OneToMany(fetch=FetchType.LAZY) @JoinColumn(name = "parent_id", nullable = true) private Set children = null; ..} — assia, Jan 28 '18 at 14:21
START WITH ... CONNECT BY is not [HQL query](https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html). This is a proprietary SQL syntax coming from Oracle Database and, as far as I know, it is supported by DB2 too. If you are using one of these databases, you can use native queries (using `createSQLquery`, not `ceateQuery`). You can't do recursive queries directly in Hibernate/JPA, see [an answer to this question](https://stackoverflow.com/questions/2486757/hql-recursion-how-do-i-do-this). — krokodilko, Jan 28 '18 at 14:38
i'm working with informix db, the query is working fine but when used with hibernate it causes this error. — assia, Jan 28 '18 at 17:19

Hibernate hql query syntax error

0 Answers0