我如何简化数百个结果集行

how do i simplify these lines. I have hundreds of these lines setting the value in each table cell.

我该如何简化这些线条。我有数百条这样的行在每个表格单元格中设置值。

         dailyResult("SELECT COUNT(*) FROM fault WHERE Type = 'E-MAIL FAULT' AND No_Fault_Number <> 'No Fault Number' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 77, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE Type = 'E-MAIL FAULT' AND No_Fault_Number = 'No Fault Number' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 78, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE CMTS = 'SJ1' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 80, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE CMTS = 'SJ2' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 81, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE CMTS = 'SJ3' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 82, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE CMTS = 'ALA1' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 83, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE CMTS = 'ALA2' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 84, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE CMTS = 'FVW' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 85, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE CMTS = 'MND1' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 86, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE Call_Concern = 'Slow Connection' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 88, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE Call_Concern = 'No Connection' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 89, 1); 
         dailyResult("SELECT COUNT(*) FROM fault WHERE Call_Concern = 'Intermittent Connection' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 90, 1);

Just looking at it makes me feel terrible and i need to find out is there a loop to simplify this? any type of help would be great.

只是看着它让我感觉很糟糕,我需要找出是否有一个循环来简化这个?任何类型的帮助都会很棒。

Here is the method code btw.

这是方法代码btw。

  public void dailyResult(String q, int x, int y){

    try{
        Statement stmtDr = (Statement)daily.createStatement();
        ResultSet rs = stmtDr.executeQuery(q);
    if(rs.next()){
        reportTable1.setValueAt(rs.getInt(1), x, y);
    }
    }catch(Exception e){
                   JOptionPane.showMessageDialog(rootPane, "Error 106\n\nAn error has occured with the resultset procedure method. Please try again later.", "Error!", JOptionPane.ERROR_MESSAGE);

2 个解决方案

#1

you could use group by in your queries, replace this

您可以在查询中使用group by,替换它

dailyResult("SELECT COUNT(*) FROM fault WHERE CMTS = 'MND1' AND CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year , 86, 1);

with

dailyResult("SELECT CMTS,COUNT(*) FROM fault WHERE CallTypeInt = 'TRI' AND DC_Month = " + month + " AND DC_Day = " + day + " AND DC_Year = " + year +" GROUP BY CMTS", 86, 1);

then in dailyResult

然后在dailyResult

ResultSet rs = stmtDr.executeQuery(q);
while(rs.next()){  // we are expecting multiple rows now
    String faultType = rs.getString(1);//what fault is this?
    reportTable1.setValueAt(rs.getInt(2), getXOffset(faultType), y);
}

Something to be careful of is that if no faults are found then no row will be returned but that is the same behaviour as you have now, so I assume you are catering for that.

需要注意的是,如果没有找到任何故障,那么将不会返回任何行,但这与您现在的行为相同,所以我假设您正在为此付出代价。

#2

Use a stored procedure for these queries, and do use the group by command to reduce the length of the query.

对这些查询使用存储过程,并使用group by命令减少查询的长度。

Actually I noticed that CMTS, Call_Concern etc are being used for differently in each query. Perhaps you can bunch some of the queries into one, but I don't think all of them can be grouped together in the present format. Parameterize these columns. All of them. Perhaps you need to look into the business logic to do that. Once that is done, you can have just one query for the entire stuff.

实际上我注意到CMTS,Call_Concern等在每个查询中的使用方式不同。也许您可以将一些查询合并为一个,但我不认为所有这些查询都可以按当前格式组合在一起。参数化这些列。他们都是。也许你需要研究业务逻辑来做到这一点。一旦完成,您只需要对整个内容进行一次查询。

#1