各位大神，我数据库中一共才20条数据，但是每次查询的时候都巨慢，
从点击查询到把结果显示在前端，一共需要十多秒。
我在语句执行过程中打印了时间，发现执行完查询语句耗时0.05秒，
遍历结果集耗时13秒，把结果集存入list耗时0.03秒，显然最耗时的就是遍历结果集，
各位大神有啥好的解决办法吗？
你是怎么遍历的？即使是ORM框架映射也没有这么慢啊。
话说能贴一下代码么，特别想知道你是怎么遍历的~~，太好奇了
这个没法回答啊，要看具体的代码和数据，如果仅仅是20条数据的遍历，怎么也不会那么久
最好贴出代码吧,不然不知道你用什么方法遍历就没办法纠错
这是所有的代码
public static void SearchCrisisReport(CrisisReportInfoResponMessage resp, GeneralInfoMessage message,
CrisisReportItemRespon condition, String accountName)
{
Connection conn = DBPool.getConnection();
if(conn == null)
{
logger.error(I18nManager.getValue("db", "ConnFiled"));
resp.responseCommand = I18nManager.getValue("db", "ConnFiled");
return;
}
try
{
String userSql = "select accountName, userName, typeOridial from ManagerList where accountName='"+accountName+"'";
ResultSet rs = DBPool.QueryCommandToDB(conn, userSql);
String userName = "";
int typeOridial = -1;
if(rs != null)
{
Date date = new Date();
SimpleDateFormat formater = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
String d = formater.format(date);
System.out.println("------------1----------"+d);
while(rs.next())
{
userName = rs.getString("userName");
typeOridial = rs.getInt("typeOridial");
}
DBPool.closeResultSet(rs);;
}
if (!userName.trim().equals(""))
{
userName = SlotPath.unescape(userName);
System.out.println("userName is " + userName);
}
HashMap enToCnMap = new HashMap();
userSql = "select crisisCode, crisisName from crisisInfo ";
rs = DBPool.QueryCommandToDB(conn, userSql);
if(rs != null)
{
Date date = new Date();
SimpleDateFormat formater = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
String d = formater.format(date);
System.out.println("------------2----------"+d);
while(rs.next())
{
enToCnMap.put(rs.getString("crisisCode"), rs.getString("crisisName"));
}
DBPool.closeResultSet(rs);
}
StringBuffer reportSql = new StringBuffer();
reportSql.append("select a.* ");
reportSql.append(" from crisisReportList a, storeInfo b ");
reportSql.append(", (select accountName, userName, bigZoneCode, smallZoneCode from managerList ");
reportSql.append(" where accountName = '");
reportSql.append(accountName);
reportSql.append("') c ");
if(typeOridial == BSTBasicUserType.APSMALL_ZONE_MANAGER)
{
// reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 and a.status>0 and c.bigZoneCode = a.bigZone and c.smallZoneCode = a.smallZone ");//能看见本小区所有报告用这句，只能看见本小区指给自己的用下一句
reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 and a.status>0 and c.bigZoneCode = a.bigZone and c.smallZoneCode = a.smallZone and a.respon=c.accountName ");
}
if(typeOridial==BSTBasicUserType.APBIG_ZONE_MANAGER)
{
// reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 and a.status>0 and c.bigZoneCode = a.bigZone ");//能看见本大区所有的报告用这句，只能看见本大区指给自己的用下一句
reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 and a.status>0 and c.bigZoneCode = a.bigZone and a.respon=c.accountName ");
}
if(typeOridial==BSTBasicUserType.APSTORE_MANAGER)
{
reportSql.append(", apmStoreScope d ");
reportSql.append(" where (a.storeId=b.storeId and b.storeId=d.storeId and b.isDel!=1 and c.accountName = a.approver ) ");
}
if(typeOridial==BSTBasicUserType.APSYS_ADMIN || typeOridial==BSTBasicUserType.HQCRISIS_GROUP)
{
reportSql.append(" where a.storeId=b.storeId and b.isDel!=1 ");
reportSql.append(" and a.status>0 ");
}
if (condition != null)
{
if (!condition.storeId.trim().equals(""))
{
reportSql.append(" and ");
reportSql.append("a.storeId ='");
reportSql.append(condition.storeId);
reportSql.append("' ");
}
if (!condition.reportCode.trim().equals(""))
{
reportSql.append(" and reportCode ='");
reportSql.append(condition.reportCode);
reportSql.append("' ");
}
if (!condition.startTime.trim().equals(""))
{
reportSql.append(" and eventTime >='");
reportSql.append(condition.startTime);
reportSql.append("' ");
}
if (!condition.endTime.trim().equals(""))
{
reportSql.append(" and eventTime <='");
reportSql.append(condition.endTime);
reportSql.append("' ");
}
if (!condition.bigZone.trim().equals(""))
{
reportSql.append(" and b.bigZone='");
reportSql.append(condition.bigZone);
reportSql.append("' ");
}
if (!condition.smallZone.trim().equals(""))
{
reportSql.append(" and b.smallZone='");
reportSql.append(condition.smallZone);
reportSql.append("' ");
}
}
reportSql.append("order by storeId, reportCode");
System.out.println(reportSql.toString());
ResultSet rs1 = DBPool.QueryCommandToDB(conn, reportSql.toString());
if (rs1 != null)
{
Date date = new Date();
SimpleDateFormat formater = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
String d = formater.format(date);
System.out.println("------------3----------"+d);
LinkedList reportList = new LinkedList();
int ind1 = rs1.findColumn("reportCode");//查找索引
int ind2 = rs1.findColumn("storeId");
while (rs1.next())
{
CrisisReportItemRespon tmp = new CrisisReportItemRespon();
tmp.reportCode = rs1.getString(ind1);//使用索引
tmp.storeId = rs1.getString(ind2) ;
tmp.createDate = rs1.getString("createDate") ;
tmp.eventLevel = rs1.getString("eventLevel") ;
tmp.eventClass = rs1.getString("eventClass") ;
tmp.eventTime = rs1.getString("eventTime") ;
tmp.eventLocation = rs1.getString("eventLocation") ;
tmp.reportDesc = rs1.getString("reportDesc") ;
tmp.responsibility = rs1.getString("responsibility") ;
tmp.improvement = rs1.getString("improvement") ;
tmp.improveDate = rs1.getString("improveDate") ;
tmp.improveTrack = rs1.getString("improveTrack") ;
tmp.status = rs1.getInt("status") ;
tmp.responType = rs1.getString("responType") ;
tmp.responCreateDate = rs1.getString("responCreateDate") ;
tmp.mailCycle = rs1.getInt("mailCycle") ;
tmp.eventClassCn = (String)enToCnMap.get(tmp.eventClass);
tmp.eventLocationCn = (String)enToCnMap.get(tmp.eventLocation);
tmp.eventLevelCn = (String)enToCnMap.get(tmp.eventLevel);
//下面这几行是为了查危机事件填报者approver加的
String approver = rs1.getString("approver");
String approverSql = "select userName from managerList where accountName = '"+approver+"'";
ResultSet rs2 = DBPool.QueryCommandToDB(conn, approverSql);
if (rs2!=null)
{
while(rs2.next())
{
tmp.approver = rs2.getString("userName");
}
DBPool.closeResultSet(rs2);
}
//下面这几行是为了查询改进人、审批人、审核人用的
String respon = rs1.getString("respon");
String responSql = "select userName from managerList where accountName = '"+respon+"'";
ResultSet rs3 = DBPool.QueryCommandToDB(conn, responSql);
if (rs3!=null)
{
while(rs3.next())
{
tmp.respon = rs3.getString("userName");
}
DBPool.closeResultSet(rs3);
}
reportList.add(tmp);
}
Date date1 = new Date();
SimpleDateFormat formater1 = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
String d1 = formater1.format(date1);
System.out.println("------------4----------"+d1);
DBPool.closeResultSet(rs1);
if (reportList.size() >0)
{
for (int i = 0; i < reportList.size(); i++)
{
CrisisReportItemRespon tmp = (CrisisReportItemRespon)reportList.get(i);
if(tmp.eventClass.equals("D") || tmp.eventClass.equals("E"))
{
StringBuffer estateSql = new StringBuffer();
estateSql.append("select * from crisisEstateReportDetail where reportCode='");
estateSql.append(tmp.reportCode);
estateSql.append("'");
ResultSet estateRs = DBPool.QueryCommandToDB(conn, estateSql.toString());
if (estateRs != null)
{
Date date2 = new Date();
SimpleDateFormat formater2 = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
String d2 = formater2.format(date2);
System.out.println("------------5----------"+d2);
estateRs.last();
int m = estateRs.getRow();
estateRs.beforeFirst();
tmp.estateDetails = new CrisisEstateReportDetailItemRespon[m];
int k = 0;
int index = estateRs.findColumn("reportCode");
while(estateRs.next())
{
CrisisEstateReportDetailItemRespon estateItem = new CrisisEstateReportDetailItemRespon();
estateItem.id = estateRs.getInt("id");
estateItem.reportCode = estateRs.getString(index);
estateItem.eventClass = estateRs.getString("eventClass") ;
estateItem.accidentType = estateRs.getString("accidentType") ;
estateItem.lose = estateRs.getInt("lose");
estateItem.mediaBadReport = estateRs.getString("mediaBadReport") ;
estateItem.isInterruption = estateRs.getInt("isInterruption") ;
estateItem.resaleTime = estateRs.getString("resaleTime") ;
estateItem.interruptionTime = estateRs.getString("interruptionTime") ;
estateItem.accidentReason = estateRs.getString("accidentReason") ;
estateItem.insuranceApply = estateRs.getString("insuranceApply");
estateItem.otherReason = estateRs.getString("otherReason") ;
estateItem.mediaBadReportCn = (String)enToCnMap.get(estateItem.mediaBadReport);
estateItem.accidentTypeCn = (String)enToCnMap.get(estateItem.accidentType);
tmp.estateDetails[k] = estateItem;
k++;
}
DBPool.closeResultSet(estateRs);
}
}
else
{
Date date2 = new Date();
SimpleDateFormat formater2 = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
String d2 = formater2.format(date2);
System.out.println("------------6----------"+d2);
StringBuffer personSql = new StringBuffer();
personSql.append("select * from crisisPersonReportDetail where reportCode='");
personSql.append(tmp.reportCode);
personSql.append("'");
ResultSet personRs = DBPool.QueryCommandToDB(conn, personSql.toString());
if (personRs != null)
{
personRs.last();
int m = personRs.getRow();
personRs.beforeFirst();
tmp.presonDetails = new CrisisPersonReportDetailItemRespon[m];
int k = 0;
int index = personRs.findColumn("reportCode");//从数据库中获取索引
while(personRs.next())
{
CrisisPersonReportDetailItemRespon personItem = new CrisisPersonReportDetailItemRespon();
personItem.id = personRs.getInt("id");
personItem.reportCode = personRs.getString(index);//引用索引
personItem.eventClass = personRs.getString("eventClass") ;
personItem.injuryLevel = personRs.getString("injuryLevel") ;
personItem.injuryClass = personRs.getString("injuryClass");
personItem.injuryContent = personRs.getString("injuryContent") ;
personItem.injuryerName = personRs.getString("injuryerName") ;
personItem.injuryerPhone = personRs.getString("injuryerPhone") ;
personItem.cost = personRs.getFloat("cost");
personItem.injuryerSex = personRs.getInt("injuryerSex");
personItem.injuryerAge = personRs.getInt("injuryerAge");
personItem.injuryerAddress = personRs.getString("injuryerAddress") ;
personItem.stopWorkDate = personRs.getString("stopWorkDate") ;
personItem.entryWorkDate = personRs.getString("entryWorkDate");
personItem.reworkDate = personRs.getString("reworkDate");
personItem.changeWorkDate = personRs.getString("changeWorkDate");
personItem.injuryLevelCn = (String)enToCnMap.get(personItem.injuryLevel) ;
personItem.injuryClassCn = (String)enToCnMap.get(personItem.injuryClass);
personItem.injuryContentCn = (String)enToCnMap.get(personItem.injuryContent) ;
tmp.presonDetails[k] = personItem;
k++;
}
DBPool.closeResultSet(personRs);
}
}
}
resp.items = (CrisisReportItemRespon[]) reportList.toArray(new CrisisReportItemRespon[reportList.size()]);
Date date2 = new Date();
SimpleDateFormat formater2 = new SimpleDateFormat("YY-MM-dd HH:mm:ss.SSS");
String d2 = formater2.format(date2);
System.out.println("------------7----------"+d2);
}
}
resp.responseCommand = "OK";
}
catch(Exception e)
{
logger.error(I18nManager.getValue("db", "SearchCrisisReportFailed")+e.getMessage());
resp.responseCommand = I18nManager.getValue("db", "SearchCrisisReportFailed")+e.getMessage();
DBPool.destroyConnection(conn);
}
DBPool.destroyConnection(conn);
}
这代码斩出来怎么有的在框里，有的不在，各位凑合看下吧，看看啥地方需要改进一下啊，先谢过各位了
你这逻辑处理那么多 是需要花点时间的 坐等大神解决 学习下
求助各位大神，小弟这里先谢过了
http://blog.csdn.net/xy_focus/article/details/51035991
请查考我的JDBC
还有就是没有看到你关闭连接对象，如此下去坑定越来越慢
各位大师，问题已经解决了，我这查询里面逻辑处理过多，再加上网速太慢，所以查询的时候就容易超时，因为我原来是把基本信息和详细信息的值
一次性查出给前端的，现在我把下面逻辑处理中的查询分开了，分两次查询，第一次查出所有的基本信息，然后点击详细信息的时候再进行一次查询，
这样的话就快多了。在这里多谢各位大师的细心解答
我都遍历3分钟了还没有出结果，，，，