我希望使用java在excel中打印计数总数，但有例外情况。

Question

我想在excel中打印"5“”空指针异常“"15”"ZZZZZ异常“"7”"XYZ异常“

我正在从mysql数据库中获取所有异常。

下面的代码给出了excel文件中所有异常的输出，但不知道如何将这些异常分组？还有一个帮助，这段代码水平地打印所有数据，比如“null指针异常”"ZZZZZ异常“"XYZ异常”。

你能帮我把它垂直打印出来，用计数将所有的异常分组吗？谢谢。

public class Abcd {

    public static void main(String[] args) {

        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(
                    "jdbc:mysql://10.9.1.1:3306/XXXXX", "Unm", "pass");
    System.out.println("Creating statement...");
            stmt = conn.createStatement();
            ResultSet rs = stmt
                    .executeQuery("select EXCEPTION_MESSAGE from TABLENAME where E_TYPE = 'FAILED' and O_TYPE = 6 and TIME_ >= '2016-4-8 00:00:00' and TIME_ <= '2016-4-11 00:00:00'");

            // Excel file generation code
            String filename = "D:/RONAKExcelFile.xls" ;
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("FirstSheet");
            HSSFRow rowhead = sheet.createRow((short) 0);
            rowhead.createCell(0).setCellValue("List of Exceptions");
            HSSFRow row = sheet.createRow((short)1);
            int i = 0;
            while (rs.next()) {
                // Retrieve by column name
                String msg = rs.getString("EXCEPTION_MESSAGE");
                // Display values
                System.out.println("Exception=> " + msg);
                row.createCell(i).setCellValue(msg);
                i++;
                System.out.println("Length = " + msg.length());
            }
            FileOutputStream fileOut = new FileOutputStream(filename);
            workbook.write(fileOut);
            fileOut.close();
            System.out.println("Excel file has been generated!");
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            System.out.println("Unable to make connection with database...!");
            se.printStackTrace();
        } catch (Exception e) {
            // Handle errors for Class.forName
            e.printStackTrace();
        } finally {
            // finally block used to close resources
            try {
                if (stmt != null)
                    stmt.close();
            } catch (SQLException se2) {
            }// nothing we can do
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException se) {
                se.printStackTrace();
            }// end finally try
        }// end try
        System.out.println("Goodbye!");
    }// end main
}

Answer 1

一些建议：

1.)在查询中使用GROUP BY语句：

SELECT
    EXCEPTION_MESSAGE, COUNT(*) AS EXCEPTION_COUNT
FROM
    ....
GROUP BY
    EXCEPTION_MESSAGE;

2.)现在，您的ResultSet的每个条目都匹配您的“目标-excel”中的一行。也就是说，您的while循环应该如下所示：

for(int i=0; rs.next(); i++)
{
    HSSFRow row = sheet.createRow(i);
    row.createCell(0).setCellValue(rs.getString("EXCEPTION_MESSAGE"));
    row.createCell(1).setCellValue(rs.getString("EXCEPTION_COUNT"));
}