Why is While (rs.next()) statement ending after 1st iteration?

Question

I am using a SELECT statement to get data from a table and then insert it into another table. However the line "stmt.executeQuery(query);" is inserting the first line from the table then exits. When I comment this line out, the while loop loops through all the lines printing them out. The stacktrace isn't showing any errors. Why is this happening?

try{                
    String query = "SELECT * FROM "+schema_name+"."+table;

    rs = stmt.executeQuery(query);

    while (rs.next()) {

        String bundle = rs.getString("BUNDLE");
        String project_cd = rs.getString("PROJECT_CD");
        String dropper = rs.getString("DROPPER");
        String week = rs.getString("WEEK");
        String drop_dt = rs.getString("DROP_DT").replace(" 00:00:00.0","");

        query = "INSERT INTO INDUCTION_INFO (BUNDLE, PROJECT_CD, DROPPER, WEEK, DROP_DT) "
              + "VALUES ("
              + bundle+","
              + "'"+project_cd+"',"
              + dropper+","
              + week+","
              + "to_date('"+drop_dt+"','YYYY-MM-DD'))";

        System.out.println(query);

        stmt.executeQuery(query);
    }
}catch(Exception e){
    e.printStackTrace();
}

Solution 1

You are re-using the Statement that was used to produce rs on the last line of your loop.

This will close the ResultSet rs. As stated in the documentation:

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.

You need to use a second Statement object to execute the INSERT statements.

Solution 2

Statement objects can only do one thing at a time, so when you execute that INSERT, you invalidate the ResultSet which it generated. You'll need to create a second Statement object to perform the INSERT.

From the 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. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists."

Solution 3

if you use the same statement, it will invalidate the previous result set. You should use a different statement to perform updates/inserts.

Solution 4

This is from the Java docs of interface Statement:

By default, only one ResultSet object per Statement object can be open at the same time.

So you better use a second Statement or even better a PreparedStatement.

And to execute an INSERT SQL statement you should use executeUpdate() instead of executeQuery().

View more solutions