Java Execute multiple SQL statements and write in excel

Question

I want to execute multiple SQL statements and write their output in excel file. I have written some code for this but while executing this throws an error

java.sql.BatchUpdateException: Can not issue SELECT via executeUpdate() or executeLargeUpdate(). at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1158) at com.mysql.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:1049) at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:959) at Demo.DatabaseQry(Demo.java:133) at Demo.main(Demo.java:48) Caused by: java.sql.SQLException: Can not issue SELECT via executeUpdate() or executeLargeUpdate(). at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:957) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:896) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:885) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860) at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1504) at com.mysql.jdbc.StatementImpl.executeBatchInternal(StatementImpl.java:1023) ... 3 more

My Code is...

public static void DatabaseQry(String ipAdd, String dbNm, String dbUnm, String dbPasswd)
    {
        String ipAddress, dbName, dbUserId, dbPassword, fileLocation;

        Connection conn = null;
        Statement stmt = null;
        try {
            ipAddress = ipAdd;
            dbName = dbNm;
            dbUserId = dbUnm;
            dbPassword = dbPasswd;

            BufferedReader brd = new BufferedReader(new InputStreamReader(System.in));
            System.out.println("Enter Output File location: ");
            fileLocation = brd.readLine();

            // Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            // Open a connection
            System.out.println("Connecting to database...");

            conn = DriverManager.getConnection("jdbc:mysql://" + ipAddress + ":3306/" + dbName, dbUserId, dbPassword);

            // Execute a query
            System.out.println("Working on Query...");
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                      ResultSet.CONCUR_UPDATABLE);

            //String TotalWorkflow =  "SELECT COUNT(*) AS TOTAL_WORKFLOW FROM M_OBJECT WHERE TYPE_ IN(7)";
            String TotalAssets = "SELECT COUNT(*) AS TOTAL_ASSET FROM M_OBJECT WHERE TYPE_ IN(1,2,3)";
            String TotalJobs = "SELECT COUNT(*) AS TOTAL_JOBS FROM M_OBJECT WHERE TYPE_ IN(6)";
            String TotalTasks = "SELECT COUNT(*) AS TOTAL_TASKS FROM M_OBJECT WHERE TYPE_ IN(5)";
            conn.setAutoCommit(false);
            //stmt.addBatch(TotalWorkflow);
            stmt.addBatch(TotalAssets);
            stmt.addBatch(TotalJobs);
            stmt.addBatch(TotalTasks);

            //Workflow total count
            ResultSet rs = stmt.executeQuery("SELECT COUNT(*) AS TOTAL_WORKFLOW FROM M_OBJECT WHERE TYPE_ IN(7)");
            rs.last();
            stmt.executeBatch();

            conn.commit();
            rs.last();
            System.out.println("Batch executed");

            // Excel file generation code
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("Readings");

            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short)12);
            font.setFontName("Arial");
          //  font.setColor(IndexedColors.DARK_BLUE.getIndex());
            font.setBold(true);

            HSSFCellStyle style = workbook.createCellStyle();
            style.setBorderTop((short) 6); // double lines border
            style.setBorderBottom((short) 2); // single line border
            style.setFont(font);
            style.setFillPattern(CellStyle.BORDER_DASH_DOT);

            sheet.createFreezePane(0, 1); // Freeze 1st Row   sheet.createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)

            // Extract data from result set
            for (int i = 1; rs.next(); i++) {
                HSSFRow row = sheet.createRow(1);
                HSSFRow rowhead = sheet.createRow((short) 0);
                rowhead.setRowStyle(style);

                // For Workflow
                rowhead.createCell(8).setCellValue("WORKFLOWS");
                row.createCell(8).setCellValue(rs.getString("TOTAL_WORKFLOW"));

                // For Assets
                rowhead.createCell(9).setCellValue("ASSETS");
                row.createCell(9).setCellValue(rs.getString("TOTAL_ASSET"));

                // For Jobs
                rowhead.createCell(10).setCellValue("JOBS");
                row.createCell(10).setCellValue(rs.getString("TOTAL_JOBS"));

                // For Tasks
                rowhead.createCell(11).setCellValue("TASKS");
                row.createCell(11).setCellValue(rs.getString("TOTAL_TASKS"));

                //row.createCell(1).setCellValue(rs.getString("EXCEPTION_COUNT_"));
            }

            // FileOutputStream fileOut = new FileOutputStream(filename);
            FileOutputStream fileOut = new FileOutputStream(fileLocation + "\\Readings.xls");
            workbook.write(fileOut);
            fileOut.close();
            System.out.println("Excel file has been generated");

            // Clean-up environment
            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
            System.out.println("\n\nPress \"Enter\" to exit...\n");
            try {
                System.in.read();
            } catch (Exception e) {
                e.printStackTrace();
            }
            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("Execution Completed");
    }

Answer 1

Final Code:

public static void DatabaseQry(String ipAdd, String dbNm, String dbUnm, String dbPasswd)
    {
        String ipAddress, dbName, dbUserId, dbPassword, fileLocation;

        Connection conn = null;
        Statement stmt = null;
        try {
            ipAddress = ipAdd;
            dbName = dbNm;
            dbUserId = dbUnm;
            dbPassword = dbPasswd;

            BufferedReader brd = new BufferedReader(new InputStreamReader(System.in));
            System.out.println("Enter Output File location: ");
            fileLocation = brd.readLine();

            // Register JDBC driver
            Class.forName("com.mysql.jdbc.Driver");

            // Open a connection
            System.out.println("Connecting to database...");

            conn = DriverManager.getConnection("jdbc:mysql://" + ipAddress + ":3306/" + dbName, dbUserId, dbPassword);

            // Execute a query
            System.out.println("Working on Query...");
            stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
                      ResultSet.CONCUR_UPDATABLE);

            String TotalWorkflow =  "SELECT COUNT(*) AS TOTAL_WKFLW FROM TableName WHERE TYP_ IN(7)";
            stmt = conn.createStatement();

            String TotalAssets = "SELECT COUNT(*) AS TOTAL_ASSET FROM TableName WHERE TYP_ IN(1,2,3)";
            String TotalJobs = "SELECT COUNT(*) AS TOTAL_J FROM TableName WHERE TYP_ IN(6)";
            String TotalTasks = "SELECT COUNT(*) AS TOTAL_T FROM TableName WHERE TYP_ IN(5)";

            ResultSet rs = stmt.executeQuery(TotalWorkflow);
            rs.next();
            int totalWorkflowCount = rs.getInt("TOTAL_WKFLW");

            rs = stmt.executeQuery(TotalAssets);
            rs.next();
            int totalAssets = rs.getInt("TOTAL_ASSET");

            rs = stmt.executeQuery(TotalJobs);
            rs.next();
            int totalJobs = rs.getInt("TOTAL_J");

            rs = stmt.executeQuery(TotalTasks);
            rs.next();
            int totalTasks = rs.getInt("TOTAL_T");

            // Excel file generation code
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("Readings");

            HSSFFont font = workbook.createFont();
            font.setFontHeightInPoints((short)12);
            font.setFontName("Arial");
            font.setBold(true);

            HSSFCellStyle style = workbook.createCellStyle();
            style.setBorderTop((short) 6); // double lines border
            style.setBorderBottom((short) 2); // single line border
            style.setFont(font);
            style.setFillPattern(CellStyle.FINE_DOTS);

            sheet.createFreezePane(0, 1); // Freeze 1st Row   sheet.createFreezePane(int colSplit, int rowSplit, int leftmostColumn, int topRow)

                HSSFRow row = sheet.createRow(1);
                HSSFRow rowhead = sheet.createRow((short) 0);
                rowhead.setRowStyle(style);

                // For Workflow
                rowhead.createCell(8).setCellValue("WORKFLOWS");
                row.createCell(8).setCellValue(totalWorkflowCount);

                // For Assets
                rowhead.createCell(9).setCellValue("ASSETS");
                row.createCell(9).setCellValue(totalAssets);

                // For Jobs
                rowhead.createCell(10).setCellValue("JOBS");
                row.createCell(10).setCellValue(totalJobs);

                // For Tasks
                rowhead.createCell(11).setCellValue("TASKS");
                row.createCell(11).setCellValue(totalTasks);

            // FileOutputStream fileOut = new FileOutputStream(filename);
            FileOutputStream fileOut = new FileOutputStream(fileLocation + "\\Readings.xls");
            workbook.write(fileOut);
            fileOut.close();
            System.out.println("Excel file has been generated");

            // Clean-up environment
            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
            System.out.println("\n\nPress \"Enter\" to exit...\n");
            try {
                System.in.read();
            } catch (Exception e) {
                e.printStackTrace();
            }
            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("Execution Completed");
    }

Answer 2

You should do something like this this for all your querystrings:

private static String getResult(String query, String resultParamName) {
    stmt = con.createStatement();
    ResultSet rs = stmt.executeQuery(query);
    rs.last();
    return rs.getString(resultParamName);
}

Exceptionhandling is left to do for yourself. ;-)

Answer 3

You cannot use addBatch for select queries. statement.addBatch() is only for update/insert/delete queries or queries which does not provide any results.

    String TotalAssets = "SELECT COUNT(*) AS TOTAL_ASSET FROM M_OBJECT WHERE TYPE_ IN(1,2,3)";
    stmt1 = con.createStatement();
    ResultSet rsTotalAssets = stmt1.executeQuery(TotalAssets);
    rsTotalAssets.next();
    int totalAssetsCount = rsTotalAssets.getInt("TOTAL_ASSET");

Use the totalAssetsCount in setting the appropriate cell value. Repeat the above code for TotalJobs and TotalTasks.

close the statement and resultset in the finally block

    rsTotalAssets.close();
    stmt1.close();