CODEWITHSUNDEEP
javahibernatehql
Yagnesh Agola
Yagnesh Agola

Reputation: 4639

Convert SQL query to HQL or map result to hibernate entity

I have below SQL query 

select column1 ,column2 ,column3 ,column4 ,column5 ,column6 from (
select ROW_NUMBER() OVER (PARTITION BY column2 ORDER BY null desc) rn,column1,  
column2 ,column3 ,column4 ,column5 ,column6
from TBLCUSTOMERSUBMODULERELATION where column1 = 'somevalue' AND column3 ='somevalue')
where rn=1;

i want to convert this query to HQL.
In my code column1,column2,column3 is EmbeddedId
I have tried to convert or mapped directly this result in my class object using below code 

session = sessionFactory.openSession();
SQLQuery sqlQuery = session.createSQLQuery(qry);
sqlQuery.addEntity(CustomerSubModuleRelationBean.class);

but it gives error like Caused by: java.sql.SQLException: Invalid column name

Is anyone have idea how to use convert this native SQL to HQL or map my result directly to entity class in Hibernate.

Upvotes: 0

Views: 13763

Answers (1)

Mohammadreza Khatami
Mohammadreza Khatami

Reputation: 1332

You can try convert your sql query to hibernate query with adding some alias names to query statement

Check here : Query conversion - Sql to Hql

You are after a native SQL query.

If you are using JPA the syntax is:

Query q = em.createNativeQuery("select foo.* FROM Foo foo " +
                               "where f.x = max(f.x) over " +
                               "(partition by f.y)", Foo.class);

If you need to return multiple types, take a look at the SQLResultSetMapping annotation.

If you're using the the Hibernate API directly:

Query q = session.createSQLQuery("select {foo.*} from Foo foo " +
                                 "where f.x = max(f.x) over "+
                                 "(partition by f.y)");
q.addEntity("foo", Foo.class);

See 10.4.4. Queries in native SQL in the Hibernate documentation for more details.

In both APIs you can pass in parameters as normal using setParameter.

Use this way to initialize your sessionFactory instead of openSession method.

SessionFactory factory = new AnnotationConfiguration()
                             .configure()
                           //.addPackage("com.xyz") //add package if used.
                             .addAnnotatedClass(Employee.class)
                             .buildSessionFactory();

I hope this helps you.

Upvotes: 2

Related Questions