hibernate查询方式效率对比

http://blog.csdn.net/zhouxianli/archive/2010/01/25/5253063.aspx


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

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

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

view plaincopy to clipboardprint?
01.List list = getHibernateTemplate()  
02..find(  
03."select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime",  
04.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?
01.List list = (List) getHibernateTemplate().execute(  
02.        new HibernateCallback() {  
03.            public Object doInHibernate(Session session)  
04.                throws HibernateException, SQLException {  
05.                Query query = session.createQuery("select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
06.                query.setParameter(0, bussNo);  
07.                query.setParameter(1, typePath);  
08.                query.setParameter(2, "1");  
09.                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）：

view plaincopy to clipboardprint?
01.List list = (List) getHibernateTemplate().executeWithNativeSession(  
02.        new HibernateCallback() {  
03.            public Object doInHibernate(Session session)  
04.                    throws HibernateException, SQLException {  
05.                 Query query = session  
06.                        .createQuery("select o.id from  SfmFileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
07.                query.setParameter(0, bussNo);  
08.                query.setParameter(1, typePath);  
09.                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）：

view plaincopy to clipboardprint?
01.List list = (List) getHibernateTemplate().execute(  
02.            new HibernateCallback() {  
03.                        public Object doInHibernate(Session session)  
04.                                throws HibernateException, SQLException {  
05.                            SQLQuery query = session  
06.                                    .createSQLQuery("select o.id from  Sfm_FileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
07.                            query.setParameter(0, bussNo);  
08.                            query.setParameter(1, typePath);  
09.                            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）：

view plaincopy to clipboardprint?
01.List list = (List) getHibernateTemplate().executeWithNativeSession(  
02.        new HibernateCallback() {  
03.            public Object doInHibernate(Session session)  
04.                    throws HibernateException, SQLException {  
05.                SQLQuery query = session  
06.                        .createSQLQuery("select o.id from  Sfm_FileIndex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
07.                query.setParameter(0, bussNo);  
08.                query.setParameter(1, typePath);  
09.                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）：

view plaincopy to clipboardprint?
01.PreparedStatement ps = getSession()  
02..connection()  
03..prepareStatement(  
04."select o.id from sfm_fileindex o where o.bussNo = ? and o.typePath = ? and o.validStatus = ? order by o.uploadTime");  
05.ps.setString(1, bussNo);  
06.ps.setString(2, typePath);  
07.ps.setString(3, "1");  
08.ResultSet rs = ps.executeQuery();  
09.List list = new ArrayList();  
10.while (rs.next()) {  
11.list.add(new Long(rs.getLong(1)));  
12.}  
13.rs.close();  
14.ps.close(); 



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zhouxianli/archive/2010/01/25/5253063.aspx