Sample java code to insert rows into database table using JDBC2.0

The ResultSet object resultset is updatable, scrollable, and sensitive to changes made by itself and others. Even though it is TYPE_SCROLL_SENSITIVE, it is possible that the getXXX methods called after the insertions will not retrieve values for the newly-inserted rows. There are methods in the DatabaseMetaData interface that will tell you what is visible and what is detected in the different types of result sets for your driver and DBMS.  In this code sample, we wanted to demonstrate cursor movement in the same ResultSet object, so after moving to the insert row and inserting two rows, the code moves the cursor back to the result set, going to the position before the first row. This puts the cursor in position to iterate through the entire result set using the method next in a while loop. To be absolutely sure that the getXXX methods include the inserted row values no matter what driver and DBMS is used, you can close the result set and create another one, reusing the Statement object stmt with the same query (SELECT * FROM COFFEES). A result set opened after a table has been changed will always reflect those changes.


import java.sql.*;

  public class InsertRowsIntoDatabaseTable {

   public static void main(String args[]) {

     String url = "jdbc:mySubprotocol:myDataSource";
     Connection con;
     Statement statement;
     try {
       Class.forName("myDriver.ClassName");

     } catch(java.lang.ClassNotFoundException e) {
       System.err.print("ClassNotFoundException: ");
       System.err.println(e.getMessage());
     }

     try {
       con = DriverManager.getConnection(url,
               "login", "pwd");

       statement = con.createStatement(
                ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);          

       ResultSet resultset = statement.executeQuery(
                   "SELECT * FROM COFFEES");

       resultset.moveToInsertRow();

       resultset.updateString("COF_NAME", "Kona");
       resultset.updateInt("SUP_ID", 150);
       resultset.updateFloat("PRICE", 10.99f);
       resultset.updateInt("SALES", 0);
       resultset.updateInt("TOTAL", 0);

       resultset.insertRow();

       resultset.updateString("COF_NAME", "Kona_Decaf");
       resultset.updateInt("SUP_ID", 150);
       resultset.updateFloat("PRICE", 11.99f);
       resultset.updateInt("SALES", 0);
       resultset.updateInt("TOTAL", 0);

       resultset.insertRow();

       resultset.beforeFirst();

       System.out.println(
         "Table COFFEES after insertion:");
       while (resultset.next()) {
         String name = resultset.getString("COF_NAME");
         int id = resultset.getInt("SUP_ID");
         float price = resultset.getFloat("PRICE");
         int sales = resultset.getInt("SALES");
         int total = resultset.getInt("TOTAL");
         System.out.print(
           name + "   " + id + "   " + price);
         System.out.println(
           "   " + sales + "   " + total);
       }

       resultset.close();
       statement.close();
       con.close();

     } catch(SQLException ex) {
       System.err.println(
         "SQLException: " + ex.getMessage());
     }
   }
 }