Trouble with ResultSet using executeUpdate

Question

I am new to programming and have run into a problem while using executeUpdate with the resultSet next() method. It iterates once only through the result set then the execute update closes the result set. I get error: ResultSet not open. Operation "next" not permitted. Verify that autocommit is off. I have added the con.setAutoCommit(false) statement but problem still persists. I need to run the update multiple times with different variable values. Here is the code I have:

    try {
        String eidQuery = "SELECT EID FROM EMPLOYEE_DATA WHERE ACTIVE = TRUE ORDER BY EID";
        int nextEID;
        Statement st = con.createStatement();
        con.setAutoCommit(false);
        rs = st.executeQuery(eidQuery);
        while (rs.next()){
            nextEID = rs.getInt(1);
            String getDailyTotals = "SELECT DATE, SUM(TOTAL), MAX(OUT_1) FROM PUNCHES WHERE EID = " + nextEID + " AND DATE >= '" + fd + "' "
                    + "AND DATE <= '" + td + "' GROUP BY DATE";

            ResultSet rs2 = st.executeQuery(getDailyTotals);
            while (rs2.next()){
                double dailyTotal = rs2.getDouble(2);
                if (dailyTotal > 8){
                    double dailyOT = dailyTotal-8;
                String dailyDate = rs2.getDate(1).toString();
                Timestamp maxTime = rs2.getTimestamp(3);
                String updateOT = "UPDATE PUNCHES SET OT = " + dailyOT + " WHERE EID = " + nextEID + " AND DATE = '" + dailyDate + "' AND OUT_1 = '" + maxTime + "'";

                st.executeUpdate(updateOT);

            }
            }
        }

        rs = st.executeQuery("SELECT PUNCHES.EID, EMPLOYEE_DATA.FIRST_NAME, EMPLOYEE_DATA.LAST_NAME, SUM(PUNCHES.OT) FROM PUNCHES "
                + "JOIN EMPLOYEE_DATA ON PUNCHES.EID = EMPLOYEE_DATA.EID WHERE PUNCHES.DATE >= '" + fd + "' AND PUNCHES.DATE <= '" + td + "' GROUP BY EMPLOYEE_DATA.FIRST_NAME, EMPLOYEE_DATA.LAST_NAME, PUNCHES.EID");

        Reports.setModel(DbUtils.resultSetToTableModel(rs));

    } catch (SQLException ex) {
        Logger.getLogger(GUI.class.getName()).log(Level.SEVERE, null, ex);
        JOptionPane.showMessageDialog(null, ex);
    }

You are calling `st.executeUpdate` at least 3 times. Break each task up into parts. — SedJ601, Apr 06 '18 at 15:56

score 2 · Accepted Answer · answered Apr 06 '18 at 15:54

You're new to programming and (obviously) Java. Here are a few recommendations that I can offer you:

Do yourself a favor and learn about PreparedStatement. You should not be creating SQL by concatenating Strings.
You are committing the classic newbie sin of mingling database and UI Swing code into a single, hard to debug lump. Better to decompose your app into layers. Start with a data access interface that encapsulates all the database code. Get that tested and give your UI an instance to work with.
Do not interleave an update query inside the loop over a ResultSet. Better to separate the two completely.
Read about MVC. You'll want your Swing View to be separate from the app Controller. Let the Controller interact with the data access interface, get the results, and give the results to the View for display. Keep them decoupled and separate.
Learn JUnit. It'll help you with testing.

Additional note: you can improve the process by having a single query that returns the relevant data to process rather than executing lots of queries. — Luiggi Mendoza, Apr 06 '18 at 15:56
Exactly. You could do this much more efficiently if you knew SQL better. — duffymo, Apr 06 '18 at 15:57

score 2 · Answer 2 · answered Apr 06 '18 at 15:57

From the java.sql.ResultSet javadoc:

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

After you execute the update, the prior ResultSet is closed. You need to rework your code to account for that. https://docs.oracle.com/javase/7/docs/api/java/sql/ResultSet.html

The easiest way to rework might be to use two Statements, one for the query and one for the update, but as noted in duffymo's answer there's a fair amount more you could do to improve things.

score 0 · Answer 3 · answered Apr 06 '18 at 16:04

From API's statement documentation "By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects"

You need two different Statements if you want to read two different ResultSet in the same nested loops.

Trouble with ResultSet using executeUpdate

3 Answers3