Hibernate查询效率对比

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

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

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

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

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

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

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

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

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();