Inner join using native query in hibernate

Question

I have a table "questions" which has fields id, version along with other fields. A question can have multiple records with the same id and different version. I need to select the question record for each question with the highest version.

My sql query is

select * from questions v1 inner join 
    (select id, max(version) as highest_version from 
        questions group by id
     )as v2 on v1.id = v2.id and v1.version = v2.highest_version;

This query works from Sequel pro. But I need to run this from Java and I am using hibernate.

My Java code is:

String assertQuestionQuery = "select v1 from Question v1 inner join "
        + "(select t.id, max(t.version) as highest_version "
        + "from Question t "
        + "group by t.id) "
        + "as v2 on v1.id =  v2.id and v1.version = v2.highest_version";

Query q = sourceEm.createQuery(assertQuestionQuery, Question.class);
List<Question> questionVersions = q.getResultList();

I am getting the following error:

ERROR org.hibernate.hql.internal.ast.ErrorCounter line 1:87: unexpected token: (

If I remove the parenthesis I am getting the following error:

ERROR org.hibernate.hql.internal.ast.ErrorCounter line 1:87: unexpected token: select

@srinivas can you try with following changes. createNativeQuery instead of createQuery and update the query, use select v1.* from instead of select v1 from — Madhusudana Reddy Sunnapu
– Madhusudana Reddy Sunnapu, Commented Mar 26, 2016 at 8:33

jon martin solaas · Accepted Answer · 2016-03-26 08:20:45Z

1

createQuery is for creating JPA/HQL query, try createNativeQuery instead.

answered Mar 26, 2016 at 8:20

jon martin solaas

4681 gold badge4 silver badges14 bronze badges

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

Comments

Madhusudana Reddy Sunnapu · Accepted Answer · 2016-03-26 10:43:54Z

0

Try with the following changes.

Use createNativeQuery(..) instead of createQuery(..)
Update the query to use select v1.* from... instead of select v1 from...

answered Mar 26, 2016 at 10:43

Madhusudana Reddy Sunnapu

8,2772 gold badges21 silver badges29 bronze badges

Comments

Tri Nguyen · Accepted Answer · 2016-03-26 09:59:47Z

I often use Native SQL Query as below:

@Override
public List<MyObj> findListNew() {
    Session session = null;
    Transaction tx = null;
    List<MyObj> objects = null;

    try {
        session = HibernateUtil.getSessionFactory().openSession();
        tx = session.beginTransaction();    

        // Write Native SQL here
        StringBuffer SqlCommand =new StringBuffer();
        SqlCommand.append("select * \n");               
        SqlCommand.append(" from tableA a , tableB b \n");          
        SqlCommand.append(" where a.tb_id = b.id; ");

        // Map SQL results to your class MyObj
        SQLQuery query = (SQLQuery) session.createSQLQuery(SqlCommand.toString())
                .addScalar("AttributeOfMyObj",IntegerType.INSTANCE)
                .setResultTransformer(Transformers.aliasToBean(MyObj.class));

        objects = query.list();
        tx.commit();            

    } catch (HibernateException e) {
    } catch (Exception e) {
    } finally {
        if (session != null)
            session.close();
    }

    return objects;

}

You can find more instruction & examples at Hibernate official site: https://docs.jboss.org/hibernate/orm/3.3/reference/en/html/querysql.html

Collectives™ on Stack Overflow

Inner join using native query in hibernate

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related