JDBC CS Delete/Insert with executeUpdate

How to use SQL Delete or SQL Insert with CallableStatement object executeUpdate() method?

  1. First you need to Create a Database Connection.

We will first do an SQL delete and an SQL Insert with NO DB-table key control.

  1. With a DB connection, you must write the SQL statement and let this statement be the parameter to the below specified prepareCall() method.
  2. To delete a row in the DB-table, the SQL statement must do a call to a database stored procedure that do a SQL Delete into a database table
  3. To insert a row in the DB-table, the SQL statement must do a call to a database stored procedure that do a SQL Insert into a database table
  4. If any parametric placeholder exists in your SQL statement which can result in an output from the database-stored procedure or function you want to call, then it is time to set the type for these using the CallableStatement registerOutPutParameter() methods.
  5. If you have any parametric placeholders for values in your SQL statement, then it is time to set these using the CallableStatement setXXX() methods.
  6. Now you can create a CallableStatement object with the method:
    Methods in Connection interface Description
    CallableStatement prepareCall(String sql) Creates a CallableStatement object for calling database-stored procedures.
  7. With the returned CallableStatement object, you can execute the following method to perform the SQL Update statement:
    Method in CallableStatement interface Description
    int executeUpdate() Executes the SQL procedure call statement in this CallableStatement object. The database procedure must do an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
  8. The CallableStatement object will now contain requested database data, which can be retrieved with the getXXX() methods.
    Example of using this executeUpdate() and a CallableStatement object to Delete and Insert rows in a database table:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.CallableStatement;
    
    public class SQLInsert {
    
     public static void main(String[] args) {
      Connection conn = null;
      CallableStatement 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);
        /* ---- USING DB Defined PROCEDURE: deleteCar() ----------
    DELIMITER $$
    DROP PROCEDURE IF EXISTS `cardb`.`deleteCar`$$
    CREATE DEFINER=`root`@`localhost`
    PROCEDURE `deleteCar`(IN pRegNo varchar (10))
    BEGIN
         DELETE FROM CarPark where regNO=pRegNo;
        END$$
    DELIMITER ;
       ------------------------------------------------------- */
        // Delete with executeUpdate() method in the CallableStatement object
        stmt=conn.prepareCall("{ call deleteCar(?) }");
        stmt.setString(1, "XY34567");
      // Execute the SQL with the  executeUpdate() in the
       // CallableStatement object.
       stmt.executeUpdate();
       /* ---- USING DB Defined PROCEDURE: insertCar() ----------
    DELIMITER $$
    DROP PROCEDURE IF EXISTS `cardb`.`insertCar`$$
    CREATE DEFINER=`root`@`localhost`
    PROCEDURE `insertCar`(IN pRegNo varchar(20),
                         IN pCartype varchar(20),
                         IN pModel int,
                         IN pDayprice decimal(10,0))
    BEGIN
    Insert into CarPark (regNo,cartype,
               model, dayprice)
               values (pRegNo,pCartype,pModel,pDayprice);
        END$$
    DELIMITER ;
        -------------------------------------------------- */
       // Create a  CallableStatement with SQL that
       // Inserts a new row with a random generated dayprice
       // In this case we write the SQL and use the executeUpdate()
       // method to do the insert.
       stmt = conn.prepareCall("{ call insertCar(?,?,?,?) }");
       stmt.setString(1, "XY34567");
       stmt.setString(2, "BMW");
       stmt.setInt(3, 2009);
       stmt.setDouble(4, 200 + Math.round(Math.random() * 200));
       int result = stmt.executeUpdate();
       if (result > 0) {
        System.out.println("The car is inserted ");
       }
        /* ---- USING DB Defined PROCEDURE: getCarPark() ----------
        DELIMITER $$
        DROP PROCEDURE IF EXISTS `cardb`.`getCarPark`$$
        CREATE DEFINER=`root`@`localhost`
        PROCEDURE `getCarPark`(IN regInNo varchar(20))
          BEGIN
            select * from CarPark where  regNo LIKE regInNo;
          END$$
        DELIMITER ;
       -------------------------------------------------------- */
       // Create a  ResultSet with a query that returns
       // all the columns  from the dB table Carpark
       ResultSet resultSet = stmt.executeQuery("{ call getCarPark('%') }");
       // 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) {
       ex.printStackTrace();
      } 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
    <table border='1' >
    <tr>
    <td>DE12345</td><td>AUDI</td><td>2003</td>
    <td>250.0</td>
    </tr>
    <tr>
    <td>AD23443</td><td>BMW</td><td>2001</td>
    <td>300.0</td>
    </tr>
    <tr>
    <td>DE23453</td><td>FORD</td><td>2002</td>
    <td>375.0</td>
    </tr>
    <tr>
    <td>RE23456</td><td>FORD</td><td>2005</td>
    <td>350.0</td>
    </tr>
    <tr>
    <td>CE23473</td><td>AUDI</td><td>2001</td>
    <td>400.0</td>
    </tr>
    <tr>
    <td>DE34562</td><td>OPEL</td><td>2001</td>
    <td>340.0</td>
    </tr>
    <tr>
    <td>AX72345</td><td>AUDI</td><td>2003</td>
    <td>350.0</td>
    </tr>
    <tr>
    <td>XY34567</td><td>BMW</td><td>2009</td>
    <td>234.0</td>
    </tr>
    </table>
    DE12345AUDI2003 250.0
    AD23443BMW2001 300.0
    DE23453FORD2002 375.0
    RE23456FORD2005 350.0
    CE23473AUDI2001 400.0
    DE34562OPEL2001 340.0
    AX72345AUDI2003 350.0
    XY34567BMW2009 234.0

How to do SQL Insert with DB-table key control using the executeUpdate() method?

  1. With a DB connection, you must write the SQL statement and let this statement be the parameter to the below specified prepareCall() method.
  2. To insert a row in the DB-table, the SQL statement must do a call to a database stored procedure of function that do a SQL Insert into a database table and returns a DB-table key value.
  3. If you have any parametric placeholders for values in your SQL statement, then it is time to set these using the CallableStatement setXXX() methods.
  4. If any parametric placeholder exists in your SQL statement which can result in an output from the database-stored procedure or function you want to call, then it is time to set the type for these using the CallableStatement registerOutPutParameter() methods.
  5. Now you can create a CallableStatement object with the method:
    Methods in Connection interface Description
    CallableStatement prepareCall(String sql) Creates a CallableStatement object for calling database-stored procedures.
  6. With the returned CallableStatement object, you can execute the following method to perform the SQL Update statement:
    Method in CallableStatement interface Description
    int executeUpdate() Executes the SQL procedure call statement in this CallableStatement object. The database procedure must do an SQL Data Manipulation Language (DML) statement, such as INSERT, UPDATE or DELETE; or an SQL statement that returns nothing, such as a DDL statement.
  7. The CallableStatement object will now contain requested database data, which can be retrieved with the getXXX() methods.
    Example of using this executeUpdate() method and a CallableStatement object to Insert rows in a database table with TB-table key control:
    import java.sql.CallableStatement;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    public class SQLAutoKey {
     public static void main(String[] args) {
      Connection conn = null;
      CallableStatement 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);
        /* --- USING DB Defined FUNCTION: insertCarRental() ------
    DELIMITER $$
    DROP FUNCTION IF EXISTS `cardb`.`insertCarRental`$$
    CREATE DEFINER=`root`@`localhost`
    FUNCTION `insertCarRental`(preg varchar(20), pdays Int)
    RETURNS int
      BEGIN
        DECLARE rental_id int;
        INSERT into CARRENTAL (REGNO, RENTALDAYS)
             values (preg,pdays);
        SELECT MAX(CARRENTAL_ID) into rental_id FROM CARRENTAL;
        RETURN rental_id;
      END$$
    DELIMITER ;
         --------------------------------------------------- */
       // Create a  CallableStatement
       // with a SQL that
       // Inserts a new rental for a car
       // In this case we write the SQL and use the executeUpdate()
       stmt = conn.prepareCall("{ ? = call insertCarRental(?,?) }");
       stmt.registerOutParameter(1,java.sql.Types.INTEGER );
       stmt.setString(2, "DE34562");
       stmt.setInt(3, 10);
       stmt.executeUpdate();
       int newKey = stmt.getInt(1);
        /* ---- USING DB Defined PROCEDURE: getCarRental() ------
    DELIMITER $$
    DROP PROCEDURE IF EXISTS `cardb`.`getCarRental`$$
    CREATE DEFINER=`root`@`localhost`
    PROCEDURE `getCarRental`(IN pId varchar(5))
    BEGIN
       select * from Carpark c,
              CARRENTAL r where c.REGNO=r.REGNO
              AND r.CARRENTAL_ID LIKE pId;
     END$$
    DELIMITER ;
       ------------------------------------------------------- */
      // Create a  CallableStatement
       // with a SQL that
       // Create a  ResultSet with a query that returns
       // all the columns from the dB table Carpark
       // for the last inserted row
       // using the executeUpdate() method of the CallableStatement object
       stmt = conn.prepareCall("{ call getCarRental(?) }");
       stmt.setString(1, String.valueOf(newKey));
       int result=stmt.executeUpdate();
       ResultSet 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' >");
        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) {
       ex.printStackTrace();
      } 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
    <table border='1' >
    <tr><th colspan='5'>
    The new car rental ID is:
    108<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>
    The new car rental ID is: 108
    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.