在同一个查询中混合使用HQL和SQL [英] Mix HQL and SQL in the same query

问题描述

我试图在同一个查询中混合使用HQL和SQl。像从ObjectA obj使用

，TABLE_B tbl where obj.someProp = tbl.COLUMN

因为我客户端需要修改查询，并且学习HQL或映射未映射的表是不成问题的：（b）如果不是hibernate，是否有人知道另一个可以接受的ORM工具这是另一个JPA实现，还是JDO？

我试过这个，当然它不起作用，这对我也有意义：结果是没有映射到对象，所以没有任何编码就没有办法获取对象，我主要是在寻找为什么这种方法很快就无法正常工作的原因。

解决方案

这确实不起作用，我认为它不会，但不是因为没有办法获取对象。

Hibernate does 允许您使用纯SQL查询和为结果映射到实体（或非实体Java对象）提供了很多不同的选项。您可以将您的查询编写为：

  SELECT table_A。* 
 FROM table_A在table_A.some_prop_column = tableB上JOIN table_B。 other_column 
 WHERE ... 
  

并通过Hibernate执行它：

  List objectAs = session.createSQLQuery（yourQuerySQL）.addEntity（ObjectA.class）.list（）; 
  

混合 SQL和HQL的问题在于它很容易获得到它们之间的冲突 - 从HQL生成的SQL可能使用一个表别名，另一个原始SQL;生成的SQL可能包含与原始SQL冲突的表/连接等。

最终，所有这一切都让你绝对没有 - 它甚至不会帮助你的用例。如果你希望有人修改一个混合的HQL / SQL查询，他们仍然需要知道 SQL和HQL-至少在区分这两者的必要级别。

如上所述，如果您的最终用户需要定制该查询，也许您应该使用 Criteria API ，以便根据用户的输入生成您需要的查询。让最终用户修改应用程序中的实际查询是一个巨大的安全漏洞，除非您采取极端预防措施。

I'm trying to mix HQL and SQl in the same query. Like using

"from ObjectA obj, TABLE_B tbl where obj.someProp = tbl.COLUMN"

because my client will need to modify the query, and learning HQL or mapping unmapped tables is out of the question :(

If not hibernate, does somebody knows another ORM tool that can accept this? another JPA implementation, or JDO?

I've tried this., and of course it doesn't work. It makes sense to me also: the results are not mapped to objects, so there's no way to get objects without somekind of coding. I'm mostly looking for reasons why this will not work anytime soon.

解决方案

This indeed doesn't work and I don't think it ever will, but not because "there's no way to get objects".

Hibernate does allow you to use plain SQL queries and provides a lot of different options for mapping the results into your entities (or non-entity java objects). You could write your query as:

SELECT table_A.*
  FROM table_A JOIN table_B on table_A.some_prop_column = tableB.other_column
 WHERE ...

and execute it from Hibernate via:

List objectAs = session.createSQLQuery(yourQuerySQL).addEntity(ObjectA.class).list();

The problem with mixing SQL and HQL is that it's very easy to get into a conflict between them - SQL generated from HQL may use one table alias, raw SQL another; generated SQL may contain tables / joins conflicting with raw SQL, etc...

And it the end, all that gets you absolutely nothing - it won't even help your use case. If you want someone to modify a "mixed" HQL / SQL query they still need to know both SQL and HQL - at least at the level necessary to distinguish between the two.

All that said, if your end user needs to be able to customize that query, perhaps you should be looking at using Criteria API in order to generate the query you need based on user's input. Having end user modify an actual query within the application is a gaping security hole unless you take extreme precautions.

这篇关于在同一个查询中混合使用HQL和SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！