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
