Hibernate查询的各种方式效率比较

查询已知表名的实体时推荐使用getHibernateTemplate().executeWithNativeSession() + SQLQuery方式。

以下测试使用JUnit进行，仅查询一次，查询结果为5条记录。各种方式的详细代码及执行时间如下所示：

方式1，正常getHibernateTemplate().find()方式（183ms)：

view plaincopy to clipboardprint?
List list = getHibernateTemplate()  
.find(  
"select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime",  
new Object[] { bussNo, typePath, "1" }); 
List list = getHibernateTemplate()
.find(
"select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime",
new Object[] { bussNo, typePath, "1" });

方式2，使用getHibernateTemplate().execute() + Query方式（214ms）：

view plaincopy to clipboardprint?
List list = (List) getHibernateTemplate().execute(  
        new HibernateCallback() {  
            public Object doInHibernate(Session session)  
                throws HibernateException, SQLException {  
                Query query = session.createQuery("select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
                query.setParameter(0, bussNo);  
                query.setParameter(1, typePath);  
                query.setParameter(2, "1");  
                return query.list();  
            }  
        }); 
List list = (List) getHibernateTemplate().execute(
  new HibernateCallback() {
   public Object doInHibernate(Session session)
    throws HibernateException, SQLException {
    Query query = session.createQuery("select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");
    query.setParameter(0, bussNo);
    query.setParameter(1, typePath);
    query.setParameter(2, "1");
    return query.list();
   }
  });

方式3，使用getHibernateTemplate().executeWithNativeSession() + Query方式（184ms）：

view plaincopy to clipboardprint?
List list = (List) getHibernateTemplate().executeWithNativeSession(  
        new HibernateCallback() {  
            public Object doInHibernate(Session session)  
                    throws HibernateException, SQLException {  
                 Query query = session  
                        .createQuery("select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
                query.setParameter(0, bussNo);  
                query.setParameter(1, typePath);  
                query.setParameter(2, "1");  
                return query.list();  
            }  
        }); 
   List list = (List) getHibernateTemplate().executeWithNativeSession(
     new HibernateCallback() {
      public Object doInHibernate(Session session)
        throws HibernateException, SQLException {
        Query query = session
         .createQuery("select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");
       query.setParameter(0, bussNo);
       query.setParameter(1, typePath);
       query.setParameter(2, "1");
       return query.list();
      }
     });

方式4，使用getHibernateTemplate().execute() + SQLQuery方式（102ms）：

view plaincopy to clipboardprint?
List list = (List) getHibernateTemplate().execute(  
            new HibernateCallback() {  
                        public Object doInHibernate(Session session)  
                                throws HibernateException, SQLException {  
                            SQLQuery query = session  
                                    .createSQLQuery("select o.id from  Sfm_FileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
                            query.setParameter(0, bussNo);  
                            query.setParameter(1, typePath);  
                            query.setParameter(2, "1");  
                            return query.list();  
                        }  
                    }); 
List list = (List) getHibernateTemplate().execute(
   new HibernateCallback() {
      public Object doInHibernate(Session session)
        throws HibernateException, SQLException {
       SQLQuery query = session
         .createSQLQuery("select o.id from  Sfm_FileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");
       query.setParameter(0, bussNo);
       query.setParameter(1, typePath);
       query.setParameter(2, "1");
       return query.list();
      }
     });

方式5，使用getHibernateTemplate().executeWithNativeSession() + SQLQuery方式（68ms）：

view plaincopy to clipboardprint?
List list = (List) getHibernateTemplate().executeWithNativeSession(  
        new HibernateCallback() {  
            public Object doInHibernate(Session session)  
                    throws HibernateException, SQLException {  
                SQLQuery query = session  
                        .createSQLQuery("select o.id from  Sfm_FileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
                query.setParameter(0, bussNo);  
                query.setParameter(1, typePath);  
                query.setParameter(2, "1");  
                return query.list();  
            }  
        }); 
   List list = (List) getHibernateTemplate().executeWithNativeSession(
     new HibernateCallback() {
      public Object doInHibernate(Session session)
        throws HibernateException, SQLException {
       SQLQuery query = session
         .createSQLQuery("select o.id from  Sfm_FileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");
       query.setParameter(0, bussNo);
       query.setParameter(1, typePath);
       query.setParameter(2, "1");
       return query.list();
      }
     });

方式6，使用JDBC （用于比较，代码不够健壮）（37ms）：

view plaincopy to clipboardprint?
PreparedStatement ps = getSession()  
.connection()  
.prepareStatement(  
"select o.id from sfm_fileindex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
ps.setString(1, bussNo);  
ps.setString(2, typePath);  
ps.setString(3, "1");  
ResultSet rs = ps.executeQuery();  
List list = new ArrayList();  
while (rs.next()) {  
list.add(new Long(rs.getLong(1)));  
}  
rs.close();  
ps.close(); 
PreparedStatement ps = getSession()
.connection()
.prepareStatement(
"select o.id from sfm_fileindex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");
ps.setString(1, bussNo);
ps.setString(2, typePath);
ps.setString(3, "1");
ResultSet rs = ps.executeQuery();
List list = new ArrayList();
while (rs.next()) {
list.add(new Long(rs.getLong(1)));
}
rs.close();
ps.close();

 

本文来自优快云博客，转载请标明出处：http://blog.youkuaiyun.com/zhouxianli/archive/2010/01/25/5253063.aspx