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

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


查询已知表名的实体时推荐使用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