count inserted line in database

[tedy2808]

Hi all,
need your help..i really new to java..so please guide me.
i wanted to create script that can count how many line inserted in database (sql) every time i run my script..based on my script below,it only printed out 1 line inserted.But when i checked in the database, there are almost 100+ lines inserted..i had tried to do try an error but i couldn't found out the problem..anyone can help me on this?

 while (result.next())
                           {
                             pstmt.setString(1, result.getString(1));
                             pstmt.setString(2, result.getString(2));
                             pstmt.setString(3, result.getString(3));
                             pstmt.setString(4, result.getString(4));
                             pstmt.setString(5, result.getString(5));
                             pstmt.setString(6, result.getString(6));
                             pstmt.setString(7, result.getString(7));
                             pstmt.setString(8, result.getString(8));
                             pstmt.setString(9, result.getString(9));
                             pstmt.setString(10, result.getString(10));
                             pstmt.setString(11, result.getString(11));
                             pstmt.setString(12, result.getString(12));
                             pstmt.setString(13, result.getString(13));
                             pstmt.setString(14, result.getString(14));
                             pstmt.setString(15, result.getString(15));
                             pstmt.setString(16, result.getString(16));
                             pstmt.setString(17, result.getString(17));
                                                         pstmt.setString(18, result.getString(18));
                                                         pstmt.setString(19, result.getString(19));
                                                         pstmt.setTimestamp(20, result.getTimestamp(20));
                                                         pstmt.setTimestamp(21, result.getTimestamp(21));

                            pstmt.executeUpdate();
                          } 
                            int rows = 0;
                            rows = pstmt.executeUpdate();
                            connOracle.commit();
                            System.out.printf("%d row(s) inserted!",rows);
                            System.out.println(" ");
                            System.out.println("Done");

[JosAH]

it only printed out 1 line inserted.

You did read the API documentation for the PreparedStatement.executeUpdate( ... ) method did you? Did you read what the method returns?

kind regards,

Jos

[wsaryada]

Hi,

From your code I assume that you want to do an insert or update into a table from a result of another query.

In the while loop you call the executeUpdate method. This method returns number of rows affected by the query. Then you need to increment the total rows affected in the while loop.

int totalRows = 0;
while (rs.next()) {
    ....
    ....
    int rows = pstmt.executeUpdate();
    totalRows = totalRows + rows;
}

System.out.println("Rows inserted: " + totalRows);

Instead of executing every single statement you can do a batch insert or update.

[Prajin]

int rows = 0;
rows = pstmt.executeUpdate();
connOracle.commit();
System.out.printf("%d row(s) inserted!",rows);

For succesful executeUpdate() it return 1, that's why you had 1 as output.
&
For counting number of rows ,'wsaryada' suggested a way.

-Regards