Insert and sort the data at the same time using java

Question

This is the sample code which I have written:

String sql =" INSERT INTO "+tablename+" (id,timestamp,type,current_num_workers,target_num_workers) 
VALUES (?,?,?,?,?)";
PreparedStatement pstmt=con.prepareStatement(sql);

System.out.println("Fetching records in ascending order...");
String se="SELECT * FROM " + tablename + " ORDER BY type ASC";
PreparedStatement pstmt=con.prepareStatement(sql);

pstmt.setString(1, id); 
pstmt.setString(2, timestamp); 
pstmt.setString(3, type); 
pstmt.setString(4, current_num_workers); 
pstmt.setString(5, target_num_workers);
pstmt.executeUpdate();
pstmt.executeUpdate();

This is the exception which I am facing:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: A result set was 
generated for update.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:226)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement
(SQLServerPreparedStatement.java:592)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute
(SQLServerPreparedStatement.java:508)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7240)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2869)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:243)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:218)
at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate
(SQLServerPreparedStatement.java:461)

Help me to resolve this and where I should sort and insert the details at the same time.

That code won't even compile, because you are declaring the variable `pstmt` twice. — , Oct 22 '19 at 07:40
Don't use `executeUpdate` for SELECT queries . See : https://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#executeUpdate(java.lang.String) — Arnaud, Oct 22 '19 at 07:41
These are two separate things: first insert data into a table and second get the sorted content of the table. Don't mix that. — Ralf Renz, Oct 22 '19 at 07:44
Why not use `insert into ... select ...`? Then there is no need to roundtrip the data through Java. — Mark Rotteveel, Oct 22 '19 at 10:18

Ricardo · Answer 1 · 2019-10-22T08:00:17.673

Try something like this. This assumes that the used fields exist in the query.

String sql =" INSERT INTO "+tablename+" (id,timestamp,type,current_num_workers,target_num_workers) 
VALUES (?,?,?,?,?)";
PreparedStatement pstmtInsert=con.prepareStatement(sql);

System.out.println("Fetching records in ascending order...");
String se="SELECT * FROM " + tablename + " ORDER BY type ASC";
PreparedStatement pstmtSelect=con.prepareStatement(se);
Resultset rs=pstmtSelect.executeQuery();
while(rs.next()){
    pstmtInsert.setString(1, rs.getString("id")); 
    pstmtInsert.setString(2, rs.getString("timestamp")); 
    pstmtInsert.setString(3, rs.getString("type")); 
    pstmtInsert.setString(4, rs.getString("current_num_workers")); 
    pstmtInsert.setString(5, rs.getString("target_num_workers"));
    pstmtInsert.executeUpdate();
}

Insert and sort the data at the same time using java

1 Answers1