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

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

 

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

 

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

[java] view plain copy print ?

1. List list = getHibernateTemplate()  
2. .find(  
3. "select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime",  
4. 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）：

[java] view plain copy print ?

1. List list = (List) getHibernateTemplate().execute(  
2.         new HibernateCallback() {  
3.             public Object doInHibernate(Session session)  
4.                 throws HibernateException, SQLException {  
5.                 Query query = session.createQuery("select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
6.                 query.setParameter(0, bussNo);  
7.                 query.setParameter(1, typePath);  
8.                 query.setParameter(2, "1");  
9.                 return query.list();  
10.             }  
11.         });  

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）：

[java] view plain copy print ?

1. List list = (List) getHibernateTemplate().executeWithNativeSession(  
2.         new HibernateCallback() {  
3.             public Object doInHibernate(Session session)  
4.                     throws HibernateException, SQLException {  
5.                  Query query = session  
6.                         .createQuery("select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
7.                 query.setParameter(0, bussNo);  
8.                 query.setParameter(1, typePath);  
9.                 query.setParameter(2, "1");  
10.                 return query.list();  
11.             }  
12.         });  

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）：

[java] view plain copy print ?

1. List list = (List) getHibernateTemplate().execute(  
2.             new HibernateCallback() {  
3.                         public Object doInHibernate(Session session)  
4.                                 throws HibernateException, SQLException {  
5.                             SQLQuery query = session  
6.                                     .createSQLQuery("select o.id from  Sfm_FileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
7.                             query.setParameter(0, bussNo);  
8.                             query.setParameter(1, typePath);  
9.                             query.setParameter(2, "1");  
10.                             return query.list();  
11.                         }  
12.                     });  

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）：

[c-sharp] view plain copy print ?

1. List list = (List) getHibernateTemplate().executeWithNativeSession(  
2.         new HibernateCallback() {  
3.             public Object doInHibernate(Session session)  
4.                     throws HibernateException, SQLException {  
5.                 SQLQuery query = session  
6.                         .createSQLQuery("select o.id from  Sfm_FileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
7.                 query.setParameter(0, bussNo);  
8.                 query.setParameter(1, typePath);  
9.                 query.setParameter(2, "1");  
10.                 return query.list();  
11.             }  
12.         });  

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）：

[java] view plain copy print ?

1. PreparedStatement ps = getSession()  
2. .connection()  
3. .prepareStatement(  
4. "select o.id from sfm_fileindex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
5. ps.setString(1, bussNo);  
6. ps.setString(2, typePath);  
7. ps.setString(3, "1");  
8. ResultSet rs = ps.executeQuery();  
9. List list = new ArrayList();  
10. while (rs.next()) {  
11. list.add(new Long(rs.getLong(1)));  
12. }  
13. rs.close();  
14. 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();