在当前项目中遇到一个问题,在查询列表时很慢,通过查找,发现在数据查询时耗费太多时间,因为在查询语句中存在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要好,需要根据实际情况考虑。
相关推荐
mysqld --initialize 初始化 chown mysql:mysql /var/lib/mysql -R 授权 systemctl start mysqld 开启mysql服务 systemctl status mysqld 查看mysql 状态 cat /var/log/mysqld.log | grep password 查看root初始...
org.apache.poi:poi:4.1.2 org.apache.poi:poi-ooxml:4.1.2 org.apache.poi:poi-ooxml-schemas:4.1.2 org.apache.xmlbeans:xmlbeans:3.1.0 ...commons-codec:commons-codec:1.13 org.apache.commons:commons-...
MySQL是世界上最受欢迎的关系型数据库管理系统之一,尤其在Web应用程序中被广泛使用。本文将详细介绍MySQL 8.0.20的安装过程,该版本适用于Linux发行版中的EL7(Enterprise Linux 7,如CentOS 7)。提供的压缩包文件...
《Hibernate Validator 深入解析与应用》 Hibernate Validator 是一个强大的Java Bean验证框架,它基于JSR 303(Bean Validation)和JSR 349(Bean Validation 1.1)标准,提供了丰富的验证注解和自定义验证规则。...
- 初始化数据库:`sudo /usr/local/mysql/bin/mysqld --initialize-insecure` - 设置开机启动与启动服务:`sudo chkconfig --add mysqld` 和 `sudo service mysqld start` 3. **Windows 版本(mysql-8.0.18-winx...
2. 性能提升:MariaDB在查询处理、存储引擎、并发控制等方面进行了优化,从而提供了比MySQL更快的数据读写速度和更高的并发性能。 3. 存储引擎:MariaDB支持多种存储引擎,包括InnoDB(用于事务处理)和Aria(快速...
// When building OkHttpClient, the OkHttpClient.Builder() is passed to the with() method to initialize the configuration OkHttpClient = RetrofitUrlManager.getInstance().with(new OkHttpClient.Builder...
内涵jacob_1.14.3-x64.dll;jacob_1.14.3-64.jdk(maven地址);解决方法:Could not initialize class com.jacob.com.ComThread
Cannot initialize the monitoring on 192.168.52.189. Error while creating the RPC client. Ensure that the machine can be connected and that it runs the rstat daemon (use rpcinfo utility for this ...
为了解决spring整合cxf,xfire遇到的jar包冲突问题,将XmlSchema.1.1.1.jar和xfire-all1.2.6.jar修改后进行了重新编译打包
MySQL是世界上最受欢迎的开源关系型数据库管理系统之一,其8.0.19版本为用户提供了一系列增强功能和优化,尤其在性能、安全性以及易用性方面。这个“mysql-8.0.19-winx64-debug-test.zip”压缩包特别针对Windows 10...
MySQL是世界上最流行的关系型数据库管理系统之一,尤其在Web应用程序中广泛应用。版本8.0.26是MySQL的一个重要更新,提供了许多性能优化、安全性增强和新功能。在Windows平台上,MySQL通常以安装程序或zip文件的形式...
对于SmartUpload,需要实例化`SmartUpload`对象,并调用其`initialize()`方法初始化,然后使用`upload()`方法处理上传。 4. **验证和保存文件**:在处理请求的过程中,你可以对上传的文件进行验证,如检查文件类型...
39. **generic** - 泛型,允许创建类型安全的容器,减少类型转换错误。 40. **heap** - 堆,内存管理的一部分,用于动态分配对象。 41. **implements** - 关键字,实现接口或抽象类的方法。 42. **import** - ...
ofd转换工具类, ofdrw-converter-1.17.8.jar
42. **Initialize** - 初始化,给变量赋初值,通常在构造器中完成。 43. **Instanceof** - 运算符,检查对象是否是特定类或其子类的实例。 44. **Interface** - 接口,定义方法但不提供实现,用于规范类的行为。 ...
at org.hibernate.service.internal.AbstractServiceRegistryImpl.initializeService(AbstractServiceRegistryImpl.java:225) at org.hibernate.service.internal.AbstractServiceRegistryImpl.getService...
- 使用 `rpm -qa | grep mariadb` 命令查询已安装的 MariaDB。 - 如果存在,执行 `rpm -e --nodeps mariadb-libs-5.5.65-1.el7.x86_64` 强制删除 MariaDB 及其依赖。 - 清理残留文件,通过 `find / -name mysql` ...
5. **初始化数据库**:使用`sudo /usr/local/mysql/bin/mysqld --initialize-insecure`初始化数据库,这将创建一个无密码的root用户。为了安全,随后应设置root用户的密码。 6. **启动与设置开机启动**:运行`sudo ...
MySQL是世界上最受欢迎的开源关系型数据库管理系统之一,其版本8.0.16是一个重要的更新,带来了许多性能改进和新特性。本文将详细介绍如何在Linux平台上对`mysql-8.0.16.tar.gz`源码包进行编译和安装。 1. **下载与...