技术CTO-关注编程入门知识,提供编程入门教程

您的位置: 首页 > 网络编程 > java开发 > 正文

Hibernate框架查询一万多条数据太慢,要用20多分钟!

来源: 技术CTO 阅读:

public List<T> getRepairlist(ConditionsForm conditionsForm,
PageBean pageBean) {   //当pageBean==null时,,查询一万两千条数据要用二十多分钟
Map<String, Object> params=new HashMap<String,Object>();
String hql="select rl.ANNEXDATENUMBER annexDateNumber,"
+ "rl.ANNEXNUMBER annexNumber,"
+ "rl.BELONGPRODUCTNAME belongProductName,"
+ "rl.BELONGPRODUCTNUMBER belongProductNumber,"
+ "rl.PARTNO partNo,"
+ "rl.PROBLEM problem,"
+ "rl.PRODUCTNAME productName,"
+ "rl.PRODUCTNUMBER productNumber,"
+ "rl.REPAIR_ID repairId,"
+ "rl.RETURNFREIGHTINFO_ID returnFreightInfoId,"
+ "rl.SENDFREIGHTINFO_ID sendFreightInfoId,"
+ "rl.SEQUENCENUMBER sequenceNumber,"
+ "rl.SUPERTANAPA superTanapa,"
+ "rl.USEENVIRONMENT useEnvironment,"
+ "rl.BELONGPRODUCTTYPE belongProductType,"
+ "rl.PROBLEMCODE problemCode,"
+ "rl.REPAIRCODE repairCode,"
+ "rl.repairListId repairListId,"
+ "rl.ACCEPTDATE acceptDate,"
+ "rl.DATEDONE dateDone,"
+ "rl.DATEIN dateIn,"
+ "rl.EXPECTSHOULDDATE expectShouldDate,"
+ "rl.FINALPRICES finalPrices,"
+ "rl.HOURSPRICES hoursPrices,"
+ "rl.JOBNUMBER jobNumber,"
+ "rl.REMARK remark,"
+ "rl.REPAIRDISCOUNT repairDiscount,"
+ "rl.REPAIRHOURS repairHours,"
+ "rl.REPAIRPRICE repairPrice,"
+ "rl.REPAIRSTATUS repairStatus,"
+ "rl.REPAIRTYPE repairType,"
+ "rl.SERVICE_ID serviceId,"
+ "rl.SHIFUBAONEI shiFuBaoNei,"
+ "rl.TJOBNUMBER tJobNumber,"
+ "rl.TOTALPRICES totalPrices,"
+ "rl.USER_ID userId,"
+ "rl.CREATETIME createTime,"
+ "rl.JSBTTIME JSBJTime,"
+ "rl.JSBNZBWTIME JSBNZBWTime,"
+ "rl.YJSTIME YJSTime,"
+ "rl.DQRBNZBWTIME DQRBNZBWTime,"
+ "rl.BJZTIME BJZTime,"
+ "rl.JCKSTIME JCKSTime,"
+ "rl.WXWCTIME WXWCTime,"
+ "rl.TESTHOURS testHours,"
+ "rl.TESTPRICE testPrice,"
+ "rl.REPAIRPRICES repairPrices,"
+ "rl.KSWXTIME KSWXTime,"
+ "rl.REPAIRCARD repairCard,"
+ "rl.JCTIME JCTime,"
+ "rl.ID id"
+ " from motuo_repair_repairlist rl ";
StringBuffer sb=new StringBuffer(hql);
if(conditionsForm!=null){
if(StringUtils.isNotBlank(conditionsForm.getServiceName())){
sb.append(",motuo_service t ");
}if(StringUtils.isNotBlank(conditionsForm.getWayBillNumber())){
sb.append(",motuo_freigth_info f ");
}if(StringUtils.isNotBlank(conditionsForm.getServiceCycle())){
sb.append(",motuo_rework re ");
}
sb.append(" where 1=1");

if(StringUtils.isNotBlank(conditionsForm.getServiceCycle())){
sb.append(" and rl.ID =re.REPAIR_ID and re.SERVICECYCLE='"+conditionsForm.getServiceCycle()+"'");
}
if(StringUtils.isNotBlank(conditionsForm.getServiceName())){
sb.append(" and rl.SERVICE_ID =t.ID and t.SERVICENAME like '%"+conditionsForm.getServiceName()+"%'");
}
if(StringUtils.isNotBlank(conditionsForm.getUserName())){
sb.append(" and rl.USER_ID like '%"+conditionsForm.getUserName()+"%'");
}
if(StringUtils.isNotBlank(conditionsForm.getWayBillNumber())){
sb.append(" and f.ID=rl.SENDFREIGHTINFO_ID and f.WAYBILLNUMBER like '%"+conditionsForm.getWayBillNumber()+"%'");
}
if(StringUtils.isNotBlank(conditionsForm.getJobNumber())){
sb.append(" and rl.JOBNUMBER like '%"+conditionsForm.getJobNumber()+"%'");
}
if(StringUtils.isNotBlank(conditionsForm.getServiceId())){
sb.append(" and rl.SERVICE_ID='"+conditionsForm.getServiceId()+"'");
}
if(StringUtils.isNotBlank(conditionsForm.getSequenceNumber())){
sb.append(" and rl.SEQUENCENUMBER like '%"+conditionsForm.getSequenceNumber()+"%'");
}
if(StringUtils.isNotBlank(conditionsForm.getBelongProductType())){
sb.append(" and rl.BELONGPRODUCTTYPE='"+conditionsForm.getBelongProductType()+"'");
}
if(StringUtils.isNotBlank(conditionsForm.getBelongProductName())){
sb.append(" and rl.PRODUCTNAME like '%"+conditionsForm.getBelongProductName()+"%'");
}
if(StringUtils.isNotBlank(conditionsForm.getShiFuBaoNei())){
sb.append(" and rl.SHIFUBAONEI='"+conditionsForm.getShiFuBaoNei()+"'");
}
if(StringUtils.isNotBlank(conditionsForm.getRepairType())){
sb.append(" and rl.REPAIRTYPE='"+conditionsForm.getRepairType()+"'");
}
if(StringUtils.isNotBlank(conditionsForm.getRepairStatus())){
sb.append(" and rl.REPAIRSTATUS='"+conditionsForm.getRepairStatus()+"'");
}
if(StringUtils.isNotBlank(conditionsForm.getDataInStartTime())&&StringUtils.isNotBlank(conditionsForm.getDataInEndTime())){
sb.append(" and DATE(rl.DATEIN)>=DATE('"+conditionsForm.getDataInStartTime()+"') and DATE(rl.DATEIN)<=DATE('"+conditionsForm.getDataInEndTime()+"')");
}
if(conditionsForm.getIds()!=null){
sb.append(" and repairListId in ('");
for(int i=0;i<conditionsForm.getIds().length;i++){
sb.append(conditionsForm.getIds()[i]+"");
if(i!=conditionsForm.getIds().length-1){
sb.append("','");
}
}
sb.append("')");
}
}
Query query = this.getSession().createSQLQuery(sb.toString()).addScalar("id", StandardBasicTypes.STRING)
.addScalar("userId",StandardBasicTypes.STRING)
.addScalar("serviceId",StandardBasicTypes.STRING)
.addScalar("dateIn",StandardBasicTypes.STRING)
.addScalar("jobNumber",StandardBasicTypes.STRING)
.addScalar("tJobNumber",StandardBasicTypes.STRING)
.addScalar("shiFuBaoNei",StandardBasicTypes.STRING)
.addScalar("repairType",StandardBasicTypes.STRING)
.addScalar("repairPrice",StandardBasicTypes.DOUBLE)
.addScalar("repairStatus",StandardBasicTypes.STRING)
.addScalar("remark",StandardBasicTypes.STRING)
.addScalar("totalPrices",StandardBasicTypes.DOUBLE)
.addScalar("hoursPrices",StandardBasicTypes.DOUBLE)
.addScalar("testHours",StandardBasicTypes.DOUBLE)
.addScalar("testPrice",StandardBasicTypes.DOUBLE)
.addScalar("repairHours",StandardBasicTypes.DOUBLE)
.addScalar("repairPrices",StandardBasicTypes.DOUBLE)
.addScalar("repairDiscount",StandardBasicTypes.DOUBLE)
.addScalar("finalPrices",StandardBasicTypes.DOUBLE)
.addScalar("repairCard",StandardBasicTypes.STRING)
.addScalar("createTime",StandardBasicTypes.STRING)
.addScalar("expectShouldDate",StandardBasicTypes.STRING)
.addScalar("acceptDate",StandardBasicTypes.STRING)
.addScalar("YJSTime",StandardBasicTypes.STRING)
.addScalar("JCKSTime",StandardBasicTypes.STRING)
.addScalar("KSWXTime",StandardBasicTypes.STRING)
.addScalar("BJZTime",StandardBasicTypes.STRING)
.addScalar("DQRBNZBWTime",StandardBasicTypes.STRING)
.addScalar("JSBNZBWTime",StandardBasicTypes.STRING)
.addScalar("JSBJTime",StandardBasicTypes.STRING)
.addScalar("WXWCTime",StandardBasicTypes.STRING)
.addScalar("dateDone",StandardBasicTypes.STRING)
.addScalar("JCTime",StandardBasicTypes.STRING)
.addScalar("productName",StandardBasicTypes.STRING)
.addScalar("productNumber",StandardBasicTypes.STRING)
.addScalar("superTanapa",StandardBasicTypes.STRING)
.addScalar("sequenceNumber",StandardBasicTypes.STRING)
.addScalar("partNo",StandardBasicTypes.STRING)
.addScalar("annexNumber",StandardBasicTypes.STRING)
.addScalar("annexDateNumber",StandardBasicTypes.STRING)
.addScalar("belongProductNumber",StandardBasicTypes.STRING)
.addScalar("belongProductName",StandardBasicTypes.STRING)
.addScalar("belongProductType",StandardBasicTypes.STRING)
.addScalar("useEnvironment",StandardBasicTypes.STRING)
.addScalar("problem",StandardBasicTypes.STRING)
.addScalar("problemCode",StandardBasicTypes.STRING)
.addScalar("repairCode",StandardBasicTypes.STRING)
.addScalar("repairId",StandardBasicTypes.STRING)
.addScalar("returnFreightInfoId",StandardBasicTypes.STRING)
.addScalar("sendFreightInfoId",StandardBasicTypes.STRING)
.addScalar("repairListId",StandardBasicTypes.STRING)
.setResultTransformer(Transformers.aliasToBean(StatRepairVo.class));
List<StatRepairVo> list;
if(pageBean!=null){
pageBean.setCount(query.list().size());
list= (List<StatRepairVo>) this.list(query, pageBean);
 return (List<T>) list;
}
list=query.list();
return (List<T>) list;
}

1/ 去数据库端抓一下原始SQL, 看看DB执行时间是多少. 再看一下执行计划
2/ 看一下你读出来的数据到底有多大, 主要是关注 网络传输 耗时
3/ 你本地mock一下数据库返回, 主要是你要确保, 问题不是出在: 每一行记录 转化为 java对象的时间.
4/ 你一次读1W条记录, 这个有点大. 你有没有调整JVM的大小. -Xmx -Xms之类的
5/ 最方便的, 你上到服务器上, jstack看一上线程卡在哪了, 一个一个问题看.(首先是确定问题不是出在 JVM的堆内存上)
神马鬼。我基本的hibernate还不会用法
引用 4 楼 sushengmiyan 的回复:
神马鬼。我基本的hibernate还不会用法

hibernate只是框架, 换句话说, 只是包装而已. 底层的消耗是不变的. 我说的都是底层帮你做的.

^_^ 如果您热爱技术、热爱编程,想与更多的朋友一起交流学习,欢迎加入本站官方QQ群:345733473 ^_^