Hibernate nested queries

Question

We are trying to create hibernate specific query for below lSession.createQuery(lQueryString.toString()); ............

INSERT INTO EMPLOYEE_HISTORY (emp_status,
                              createdBy,
                              releasedate,
                              year)
   SELECT CASE n.emp_Status WHEN 0 THEN 'Draft' ELSE 'Final' END,
          m.createdBy,
          m.releasedate,
          m.year
     FROM EMPLOYEE m, (SELECT COUNT (1) EMPLOYEE_HISTORY   b,EMPLOYEE a where a.emplid=b.emplid and a.year=b.year and b.year=2012 and a.doc_id='XYZ') n 
     where a.doc_id='xyz'

getting following exception,I guess it because of below statement in query
(SELECT COUNT (1) EMPLOYEE_HISTORY   b,EMPLOYEE a where a.emplid=b.emplid and a.year=b.year and b.year=2012 and a.doc_id='XYZ') n


org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ( near line 1, column 374 
INSERT INTO EMPLOYEE_HISTORY (emp_status,
                              createdBy,
                              releasedate,
                              year)
   SELECT CASE n.emp_Status WHEN 0 THEN 'Draft' ELSE 'Final' END,
          m.createdBy,
          m.releasedate,
          m.year
     FROM EMPLOYEE m, (SELECT COUNT (1) EMPLOYEE_HISTORY   b,EMPLOYEE a where a.emplid=b.emplid and a.year=b.year and b.year=2012 and a.doc_id='XYZ') n 
     where a.doc_id='xyz'

The Exception

at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
    at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
    at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
    at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)
    at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
    at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
    at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
    at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:124)
    at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
    at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
    at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1770)

Please provide if any suggestion/Inputs.

Boris Brodski · Accepted Answer · 2013-03-13 10:48:35Z

1

It looks, like you pass SQL instead of HQL to the Session.createQuery(...) method.

See: http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/objectstate.html#objectstate-querying-executing

For example in:

INSERT INTO EMPLOYEE_HISTORY (...)

'EMPLOYEE_HISTORY' should be entity class name (like EmployeeHistory) not the table name.

Also in HQL you can use references defined in your entities to go through associations avoiding implicit joining by ids, like you do here

where a.emplid=b.emplid and ...

Consider example from URL above:

select mother from Cat as cat join cat.mother as mother where cat.name = ?

answered Mar 13, 2013 at 10:48

Boris Brodski

8,7744 gold badges44 silver badges60 bronze badges

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

Collectives™ on Stack Overflow

Hibernate nested queries

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