JDBC GS Delete/Insert with execute

How to use SQL Delete or Insert with Statement object execute() method?

  1. First you need to Create a Database Connection.

We will first do an SQL delete and an SQL Insert with NO auto-generated keys control.

  1. When you have got the database connection, create a regular Statement object with the method, createStatement().
  2. The next step is to write your SQL Delete, SQL Insert or SQL Select statement and give this string as a parameter to the method:
    Method in Statement interface Description
    boolean execute(String sql) Executes the given SQL statement, and returns true if the first result is a ResultSet object; false if it is an update count or there are no results.
    Example of using this method to Delete, Insert and Select rows in a database table:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class SQLInsert {
    
     public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
       // Register a driver for the MySQL database
       Class.forName("com.mysql.jdbc.Driver");
       // Create a url for accessing the MySQL
       // database CarDB
       String url = "jdbc:mysql://localhost:3306/CarDB";
       // user and password to access the database
       String username = "root";
       String password = "root";
       // User the DriverManager to get a Connection to the database
       conn = DriverManager.getConnection(url, username, password);
       // Create a  Statement
       // that is non-updateable, non-scrollable conserning ResultSet
       stmt = conn.createStatement();
       // We Delete the car we want to insert if it exist already.
       ResultSet resultSet = stmt.executeQuery("select * from Carpark " +
               " where regNo='XY34567'");
       if (resultSet.next()) {
        // Delete with execute() method
        stmt.execute("Delete from Carpark where regNo='XY34567'");
       }
       // Inserts a new row with a random generated dayprice
       // In this case we write the SQL and use the execute()
       // method to do the insert.
       String query = "Insert into CarPark (regNo,cartype," +
               "model, dayprice)" +
               " values ('XY34567','BMW',2009," +
               "200 +" + Math.round(Math.random() * 200) +
               ")";
       boolean result = stmt.execute(query);
       // if result false it is NOT a ResultSet
       if (!result) {
        System.out.println("The car is inserted ");
       }
        // Create a  ResultSet with a query that returns
       // all the columns  from the dB table Carpark
       // using the execute() method.
       result = stmt.execute("select * from Carpark ");
       // if result true it is a ResultSet
       if (result) {
        resultSet = stmt.getResultSet();
       }
       // As cursor is at the before first row position
       // we use the next() method to
       // test and read the first row in the ResultSet.
       if (resultSet.next()) {
        // Then we use a loop to retrieve rows and column data
        // and creates a html coded table output
        System.out.println("<table border='1' >");
        do {
         System.out.println("<tr>");
         System.out.print("<td>" + resultSet.getString("regNo") + "</td>");
         System.out.print("<td>" + resultSet.getString("cartype") + "</td>");
         System.out.println("<td>" + resultSet.getInt("model") + "</td>");
         System.out.println("<td>" + resultSet.getDouble("dayPrice") + "</td>");
         System.out.println("</tr>");
        } while (resultSet.next());
        System.out.println("</table>");
       }
      } catch (ClassNotFoundException ex) {
       Logger.getLogger(SQLInsert.class.getName()).log(Level.SEVERE, null, ex);
      } catch (SQLException e) {
       e.printStackTrace();
      } finally {
       try {
        // Close the Statement, which also close the ResultSet
        stmt.close();
        conn.close();
       } catch (Exception xe) {
        xe.printStackTrace();
       }
      }
     }
    }

    You should use the close() method in the Statement object when you do not need it anymore.

    You should also use the close() method in the Connection object when you do not need it anymore.

    You can download this example here (needed tools can be found in the right menu on this page).

    If we run this application the result should be:
    As pure output Translated by a Browser
    The car is inserted 
    <table border='1' >
    <tr>
    <td>AD23443</td><td>BMW</td><td>2001</td>
    <td>300.0</td>
    </tr>
    <tr>
    <td>AX72345</td><td>AUDI</td><td>2003</td>
    <td>350.0</td>
    </tr>
    <tr>
    <td>CE23473</td><td>AUDI</td><td>2001</td>
    <td>400.0</td>
    </tr>
    <tr>
    <td>DE12345</td><td>AUDI</td><td>2003</td>
    <td>250.0</td>
    </tr>
    <tr>
    <td>DE23453</td><td>FORD</td><td>2002</td>
    <td>375.0</td>
    </tr>
    <tr>
    <td>DE34562</td><td>OPEL</td><td>2001</td>
    <td>340.0</td>
    </tr>
    <tr>
    <td>RE23456</td><td>FORD</td><td>2005</td>
    <td>350.0</td>
    </tr>
    <tr>
    <td>XY34567</td><td>BMW</td><td>2009</td>
    <td>393.0</td>
    </tr>
    </table>
    The car is inserted
    AD23443BMW2001 300.0
    AX72345AUDI2003 350.0
    CE23473AUDI2001 400.0
    DE12345AUDI2003 250.0
    DE23453FORD2002 375.0
    DE34562OPEL2001 340.0
    RE23456FORD2005 350.0
    XY34567BMW2009 393.0

How to use SQL Insert with auto-generated keys and the execute() method?

  1. When you have got the database connection, create a regular Statement object with the method, createStatement().
  2. The next step is to write your SQL Insert statement and give this string as the first parameter to one of the following methods.
  3. If you are using the first method, the second parameter should be Statement.RETURN_GENERATED_KEYS.
  4. If you use one of the last two methods, the second parameter must be either an integer array or a string array that in both cases must represent the generated key-columns in the database table.
    Methods in Statement interface Description
    boolean execute(String sql, int autoGeneratedKeys) Same as execute(String sql) but for a SQL INSERT you can signals the driver a given flag about whether the auto-generated keys produced by this Statement object should be made available for retrieval.
    boolean execute(String sql, int[] columnIndexes) Same as execute(String sql) but for a SQL INSERT you can signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval. The array contains the indexes (starting with 1 for the first) of the columns.
    boolean execute(String sql, String[] columnNames) Same as execute(String sql) but for a SQL INSERT you can signals the driver that the auto-generated keys indicated in the given array should be made available for retrieval. The array contains the names of the columns.
  5. After executed SQL Insert, use the getGeneratedKeys() method in the statement object to retrieve the db-table key, which will be one or more columns of key data into a ResultSet.
    Example of using SQL Insert with returned auto-generated keys:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    import java.util.logging.Level;
    import java.util.logging.Logger;
    
    public class SQLAutoKey {
    
     public static void main(String[] args) {
      Connection conn = null;
      Statement stmt = null;
      try {
       // Register a driver for the MySQL database
       Class.forName("com.mysql.jdbc.Driver");
       // Create a url for accessing the MySQL
       // database CarDB
       String url = "jdbc:mysql://localhost:3306/CarDB";
       // user and passord to access the database
       String username = "root";
       String password = "root";
       // User the DriverManager to get a Connection to the database
       conn = DriverManager.getConnection(url, username, password);
       // Create a  Statement
       // that is non-updateable, non-scrollable conserning ResultSet
       stmt = conn.createStatement();
       // Inserts a new rental for a car
       // In this case we write the SQL and use the executeUpdate()
       // method with the possibility to return auto generated
       // key(s) after the insert is done.
       String query = "insert  into CARRENTAL(REGNO,RENTALDAYS) values" +
               "('DE34562',10)";
       String[] Id = {"CARRENTAL_ID"};  // auto gen key in the table CARRENTAL.
       boolean result = stmt.execute(query, Id);
       if (!result) {
        System.out.println("A car rental is inserted ");
       }
       // Using the getGeneratedKeys() method to retrieve
       // the key(s). In this case there is only one.
       ResultSet keyResultSet = stmt.getGeneratedKeys();
       int newKey = 0;
       if (keyResultSet.next()) {
        newKey = (int) keyResultSet.getInt(1);
       }
       // Create a  ResultSet with a query that returns
       // all the columns  from the dB table Carpark
       // using the execute() method.
       result = stmt.execute("select * from Carpark c, " +
               "CARRENTAL r where c.REGNO=r.REGNO " +
               "and r.CARRENTAL_ID=" + newKey);
       ResultSet resultSet = null;
       // if result true it is a ResultSet
       if (result) {
        resultSet = stmt.getResultSet();
       }
        // As cursor is at the before first row position
       // we use the next() method to
       // test and read the first row in the ResultSet.
       if (resultSet.next()) {
        // Then we ese a loop to retrieve rows and column data
        // and creates a html coded table output
        System.out.println("<table border='1' >");
        System.out.println("<tr><th colspan='5'>");
        System.out.println("The new car rental ID is: ");
        System.out.println(newKey + "<br/> which is for the car:");
        System.out.println("</th></tr>");
        System.out.println("<tr>");
        System.out.print("<th>regNo</th>");
        System.out.print("<th>cartype</th>");
        System.out.println("<th>model</th>");
        System.out.println("<th>day<br/>Price</th>");
        System.out.println("<th>days</th>");
        System.out.println("</tr>");
        do {
         System.out.println("<tr>");
         System.out.print("<td>" + resultSet.getString("regNo") + "</td>");
         System.out.print("<td>" + resultSet.getString("cartype") + "</td>");
         System.out.println("<td>" + resultSet.getInt("model") + "</td>");
         System.out.println("<td>" + resultSet.getDouble("dayPrice") + "</td>");
         System.out.println("<td>" + resultSet.getDouble("RENTALDAYS") + "</td>");
         System.out.println("</tr>");
        } while (resultSet.next());
        System.out.println("</table>");
       }
      } catch (ClassNotFoundException ex) {
       Logger.getLogger(SQLAutoKey.class.getName()).log(Level.SEVERE, null, ex);
      } catch (SQLException e) {
       e.printStackTrace();
      } finally {
       try {
        // Close the Statement, which also close the ResultSet
        stmt.close();
        conn.close();
       } catch (Exception xe) {
        xe.printStackTrace();
       }
      }
     }
    }

    You should use the close() method in the Statement object when you do not need it anymore.

    You should also use the close() method in the Connection object when you do not need it anymore.

    You can download this example here (needed tools can be found in the right menu on this page).

    If we run this application the result should be:
    As pure output Translated by a Browser
    A car rental is inserted 
    <table border='1' >
    <tr><th colspan='5'>
    The new car rental ID is: 
    9<br/> which is for the car:
    </th></tr>
    <tr>
    <th>regNo</th><th>cartype</th><th>model</th>
    <th>day<br/>Price</th>
    <th>days</th>
    </tr>
    <tr>
    <td>DE34562</td><td>OPEL</td><td>2001</td>
    <td>340.0</td>
    <td>10.0</td>
    </tr>
    </table>
    A car rental is inserted
    The new car rental ID is: 9
    which is for the car:
    regNocartypemodel day
    Price
    days
    DE34562OPEL2001 340.0 10.0

© 2010 by Finnesand Data. All rights reserved.
This site aims to provide FREE programming training and technics.
Finnesand Data as site owner gives no warranty for the correctness in the pages or source codes.
The risk of using this web-site pages or any program codes from this website is entirely at the individual user.