将SQL查询转换为HQL或将结果映射到休眠实体 [英] Convert SQL query to HQL or map result to hibernate entity

问题描述

我有下面的SQL查询

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;  

我想将此查询转换为HQL.
在我的代码中，column1,column2,column3是EmbeddedId
我尝试使用以下代码

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);

但它给出类似Caused by: java.sql.SQLException: Invalid column name

有人知道如何使用将本机SQL转换为HQL或将我的结果直接映射到Hibernate中的实体类.

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

推荐答案

您可以尝试通过向查询语句添加一些别名来将sql查询转换为休眠查询

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

您正在执行本机SQL查询.

You are after a native SQL query.

如果您使用的是JPA，则语法为:

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.

如果您直接使用的是Hibernate API:

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);

请参见 10.4. 4，有关更多详细信息，请参见Hibernate文档中的本机SQL查询.

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

在两个API中，您都可以使用setParameter正常传递参数.

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

使用这种方法来初始化sessionFactory而不是openSession方法.

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();

希望对您有帮助.

这篇关于将SQL查询转换为HQL或将结果映射到休眠实体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！