Hibernate 之 Hibernate.initialize(Obj)方法 ---减少数据查询时间

在当前项目中遇到一个问题，在查询列表时很慢，通过查找，发现在数据查询时耗费太多时间，因为在查询语句中存在left join fetch，需要的关联查询太多，这样查询时，疑Hibernate是将所有数据查询到内存中后，再进行分页的（这就是网上所讲的fetch产生的假分页问题），所以比较慢，后使用Hibernate.initialize(Obj.getXxx())方法后解决该问题。

修改前查询列表功能代码如下：

@SuppressWarnings ("unchecked" )

    public List<TCustomer> findList( boolean status, TUser user, String name, String code, String ledger, int pageNo, int pageSize)

            throws Exception {

        StringBuffer sb = new StringBuffer ();

        HashMap<String, Object> map = new HashMap<String, Object>();

        sb.append( "select distinct customer from TCustomer as customer left join fetch customer.TCustomerLedgers");

        if (status) {

            sb.append( " where customer.id in( select uc.TCustomer.id from TReUserCustomer as uc where uc.TUser =:user) ");

        } else {

            sb.append( " where customer.id not in( select uc.TCustomer.id from TReUserCustomer as uc where uc.TUser =:user) ");

        }

        map.put( "user" , user);

        converQuerySQL(sb, name, code, ledger, map);

        Session session = this.getSession();

        Query query = session.createQuery(sb.toString());

        this .setQueryParaments(query, map);

        if (pageNo != WebSiteDictionary. D_PAGENO && pageSize != WebSiteDictionary.D_PAGESIZE ) {

            query.setFirstResult(pageSize * (pageNo - 1));

            query.setMaxResults(pageSize);

        }

        List<TCustomer> list = query.list();

        releaseSession(session);

        return list;

    }

修改前测试时打印SQL如下：

Hibernate: select distinct tcustomer0_.id as id35_0_, tcustomerl1_.id as id45_1_, tcustomer0_.customerTypeId as customer2_35_0_, tcustomer0_.customerCode as customer3_35_0_, tcustomer0_.status as status35_0_, tcustomer0_.parentCustomerId as parentCu5_35_0_, tcustomer0_.source as source35_0_, tcustomer0_.fullName as fullName35_0_, tcustomer0_.shortName as shortName35_0_, tcustomer0_.logo as logo35_0_, tcustomer0_.updateTime as updateTime35_0_, tcustomer0_.customerCompanyId as custome11_35_0_, tcustomerl1_.customerId as customerId45_1_, tcustomerl1_.ledger as ledger45_1_, tcustomerl1_.payConditionId as payCondi4_45_1_, tcustomerl1_.credit as credit45_1_, tcustomerl1_.currencyId as currencyId45_1_, tcustomerl1_.shippingTypeId as shipping7_45_1_, tcustomerl1_.personId as personId45_1_, tcustomerl1_.postCode as postCode45_1_, tcustomerl1_.fax as fax45_1_, tcustomerl1_.companyPhone as company11_45_1_, tcustomerl1_.bank as bank45_1_, tcustomerl1_.bankAccount as bankAcc13_45_1_, tcustomerl1_.contactPerson as contact14_45_1_, tcustomerl1_.createUserId as createU15_45_1_, tcustomerl1_.createDate as createDate45_1_, tcustomerl1_.isDelete as isDelete45_1_, tcustomerl1_.customerCode as custome18_45_1_, tcustomerl1_.saleProtocal as salePro19_45_1_, tcustomerl1_.customerId as customerId35_0__, tcustomerl1_.id as id0__ from dbo.T_Customer tcustomer0_ left outer join dbo.T_CustomerLedger tcustomerl1_ on tcustomer0_.id=tcustomerl1_.customerId and ( tcustomerl1_.isDelete is null or tcustomerl1_.isDelete = 0) where tcustomer0_.id not in  (select treusercus2_.customerId from dbo.T_ReUserCustomer treusercus2_ where treusercus2_.userId=?)

 

注：在这个SQL中不存在top关键字。

修改前查询所需时间：24651毫秒

----------------------------------------------

修改后查询列表功能代码如下：

    public List<TCustomer> findList( boolean status, TUser user, String name, String code, String ledger, int pageNo, int pageSize)

            throws Exception {

        StringBuffer sb = new StringBuffer();

        HashMap<String, Object> map = new HashMap<String, Object>();

        sb.append( "select distinct customer from TCustomer as customer");

        if (status) {

            sb.append( " where customer.id in( select uc.TCustomer.id from TReUserCustomer as uc where uc.TUser =:user) ");

        } else {

            sb.append( " where customer.id not in( select uc.TCustomer.id from TReUserCustomer as uc where uc.TUser =:user) ");

        }

        map.put( "user", user);

        converQuerySQL(sb, name, code, ledger, map);

        Session session = this.getSession();

        Query query = session.createQuery(sb.toString());

        this.setQueryParaments(query, map);

        if (pageNo != WebSiteDictionary.D_PAGENO && pageSize != WebSiteDictionary.D_PAGESIZE ) {

            query.setFirstResult(pageSize * (pageNo - 1));

            query.setMaxResults(pageSize);

        }

        List<TCustomer> list = query.list();

        for (TCustomer cus : list) {

            Hibernate. initialize(cus.getTCustomerLedgers());

        }

        releaseSession(session);

        return list;

    }

修改后测试时打印SQL如下：

Hibernate: select distinct top 20 tcustomer0_.id as id35_, tcustomer0_.customerTypeId as customer2_35_, tcustomer0_.customerCode as customer3_35_, tcustomer0_.status as status35_, tcustomer0_.parentCustomerId as parentCu5_35_, tcustomer0_.source as source35_, tcustomer0_.fullName as fullName35_, tcustomer0_.shortName as shortName35_, tcustomer0_.logo as logo35_, tcustomer0_.updateTime as updateTime35_, tcustomer0_.customerCompanyId as custome11_35_ from dbo.T_Customer tcustomer0_ where tcustomer0_.id not in  (select treusercus1_.customerId from dbo.T_ReUserCustomer treusercus1_ where treusercus1_.userId=?)

注：修改后，在SQL存在top关键字，但SQL语句会增加，需要增加20条如下语句，用于查询出关联类。

Hibernate: select tcustomerl0_.customerId as customerId35_1_, tcustomerl0_.id as id1_, tcustomerl0_.id as id45_0_, tcustomerl0_.customerId as customerId45_0_, tcustomerl0_.ledger as ledger45_0_, tcustomerl0_.payConditionId as payCondi4_45_0_, tcustomerl0_.credit as credit45_0_, tcustomerl0_.currencyId as currencyId45_0_, tcustomerl0_.shippingTypeId as shipping7_45_0_, tcustomerl0_.personId as personId45_0_, tcustomerl0_.postCode as postCode45_0_, tcustomerl0_.fax as fax45_0_, tcustomerl0_.companyPhone as company11_45_0_, tcustomerl0_.bank as bank45_0_, tcustomerl0_.bankAccount as bankAcc13_45_0_, tcustomerl0_.contactPerson as contact14_45_0_, tcustomerl0_.createUserId as createU15_45_0_, tcustomerl0_.createDate as createDate45_0_, tcustomerl0_.isDelete as isDelete45_0_, tcustomerl0_.customerCode as custome18_45_0_, tcustomerl0_.saleProtocal as salePro19_45_0_ from dbo.T_CustomerLedger tcustomerl0_ where  ( tcustomerl0_.isDelete is null or tcustomerl0_.isDelete = 0)  and tcustomerl0_.customerId=?

 

修改后查询所需时间：452毫秒

 

由此可见，在使用Hibernate.initialize(Obj.getXxx())方法后，减少了查询时间，但也不足以说明该方法就比left join fetch要好，需要根据实际情况考虑。

 

fetch假分页http://www.esnsc.com/news581.html