org.hibernate.Query .iterate（）与.getResultList（）查询生成 [英] org.hibernate.Query .iterate() VS .getResultList() query generation

问题描述

我使用 org.hibernate.Query Api来查询结果。但是我遇到了一个奇怪的问题。
这是假设我的查询选择DISTINCT abc FROM ABC abc where ORDER BY abc.name ASC 。

我已经用 Oracle数据库测试过了。

假设我的代码如下

  public static List executeQuery（EntityManager em，Query query）{
 org.hibernate.Query hibernateQuery = query.unwrap（org.hibernate.Query 。类）; 
 
 //这段代码用于从缓存中获取基于id 
的数据if（EntityCacheable）{
 
 Iterator< Entity> it = hibernateQuery.iterate（）; 
 return; 
} else {
 
 //这个finnaly返回结果
 return query.getResultList（）; 
 
 
 
 
 
 
 $ b现在问题出现在上面的查询中， hibernateQuery.iterate（）生成如下所示的查询
 
 
 
  select abc.id FROM ABC abc where ORDER通过abc.name ASC ，这是无效的。因为你在 order by子句中的内容应该存在于 select子句中。
 
 
 
但是 query.getResultList（）产生如下的内容，
 
 
  select abc.id，abc.name FROM ABC abc where ORDER BY abc.name ASC   - 所以我很安全
 
 
 
 I可以在我的查询中进行更改并继续进行，但这是 Hibernate API 中的问题还是什么。
解决方案 
这似乎不是一个与 Hibernate API 有关的问题，实际上这是所需的行为。
 
 
 
  Query.iterate（）：
 
 
 
将查询结果作为 Iterator 。如果查询包含多个结果预行，结果将返回为 Object [] 。实体返回的实例结果按要求初始化。第一个SQL查询仅返回标识符。
 
 
 
执行 1 + N SQL 查询。第一个查询只返回所有记录的标识符，并且当迭代返回的迭代器时，每次执行包含WHERE子句（如 WHERE id = N ）的单独SQL查询时， 。如果记录存在于缓存中，则执行第一个查询，并且不执行其余N个查询，并从缓存中获取记录。
 迭代<员工> iterator1 = session.createQuery（from Employee）。iterate（）; //选择EMP_ID从EMP 
 while（iterator1.hasNext（））{
 System.out.println（iterator1.next（））; // SELECT * FROM EMP WHERE EMP_ID =？ 
} 
迭代器<员工> iterator2 = session.createQuery（from Employee）。iterate（）; //从SELECT EMP_ID FROM EMP 
 while（iterator2.hasNext（））{
 System.out.println（iterator2.next（））; //从缓存中，不需要SQL 
 
 $ / code $ / pre 
 $ b 
  Query.getResultList（）：执行1个SQL查询并加载整个数据。即使记录存在于缓存中，也会执行新的SQL查询以从数据库加载记录。
 列表< Employee> list1 = session.createQuery（from Employee）。list（）; // SELECT * FROM EMP 
 for（Employee e：list1）{
 System.out.println（e）; 
} 
列表< Employee> list2 = session.createQuery（from Employee）。list（）; // SELECT * FROM EMP 
 for（Employee e：list2）{
 System.out.println（e）; 
} 
  
 
I am using org.hibernate.Query Api to query for result. But i ran into strange problem.
This is suppose my query select DISTINCT abc FROM ABC abc where ORDER BY abc.name ASC.


I have tested this with Oracle database.

let say i have code as below
public static List executeQuery(EntityManager em, Query query) {
        org.hibernate.Query hibernateQuery = query.unwrap(org.hibernate.Query.class);

        // this piece of code is used to fetch data from cache based on id
        if(EntityCacheable){

        Iterator<Entity> it = hibernateQuery.iterate();
        return "";
        }else{

        //This finnaly return the result
            return query.getResultList();
        }
    }
Now the problem is for the above query, hibernateQuery.iterate() generates query like below

select abc.id FROM ABC abc where ORDER BY abc.name ASC, which is not valid. Since what you have in order by clause should be there in select clause also.

But query.getResultList() generates something as below,

select abc.id, abc.name FROM ABC abc where ORDER BY abc.name ASC -- so i am safe here

I can make changes in my query and proceed further, but is this an issue in Hibernate API or what.
解决方案
This does not seems to be an issue with Hibernate API, infact this the desired behaviour.

Query.iterate():

Return the query results as an Iterator. If the query contains multiple results pre row, the results are returned in an instance of Object[].Entities returned as results are initialized on demand. The first SQL query returns identifiers only.

Executes 1+N SQL queries. The first query only returns the identifier of all the records and when the returned iterator is iterated then each time a separate SQL query is executed that contains a WHERE clause like WHERE id=N. If the records are present in cache then the first query is executed and the rest N queries are not executed and records are obtained from cache.
Iterator<Employee> iterator1 = session.createQuery("from Employee").iterate(); // SELECT EMP_ID FROM EMP
while(iterator1.hasNext()) {
    System.out.println(iterator1.next()); // SELECT * FROM EMP WHERE EMP_ID=?
}
Iterator<Employee> iterator2 = session.createQuery("from Employee").iterate(); // SELECT EMP_ID FROM EMP
while (iterator2.hasNext()) {
    System.out.println(iterator2.next()); // From cache, no SQL
}
Query.getResultList(): Executes 1 SQL query and loads the entire data. Even if the records are present in cache a fresh SQL query is executed to load the records from the database.
List<Employee> list1 = session.createQuery("from Employee").list(); // SELECT *FROM EMP
for (Employee e : list1) {
    System.out.println(e);
}
List<Employee> list2 = session.createQuery("from Employee").list(); // SELECT *FROM EMP
for (Employee e : list2) {
    System.out.println(e);
}


                        
这篇关于org.hibernate.Query .iterate（）与.getResultList（）查询生成的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！