JDBC CS Update with executeUpdate.

How do we perform SQL Update with the executeUpdate() method contained in a JDBC-CallableStatement object.

1. First you need to Create a Database Connection.
2. With a DB connection, you must write the SQL statement and let this statement be the parameter to the below specified prepareCall() method.
3. The SQL statement must do a call to a database stored procedure that do a SQL Update into a database table.
4. If you have any parametric placeholders for values in your SQL statement, then it is time to set these using the PreparedStatement setXXX() methods.
5. 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.
6. Now you can create a CallableStatment 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 method to Update a database table through a database-stored procedure:

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.CallableStatement;
   
   public class SQLUpdate {
   
    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: updateCarPark() ----------
   DELIMITER $$
   DROP PROCEDURE IF EXISTS `cardb`.`updateCarPark`$$
   CREATE DEFINER=`root`@`localhost`
   PROCEDURE `updateCarPark`(IN pfakt double)
   BEGIN
         Update CarPark Set newPrice=dayPrice*pfakt;
       END$$
   DELIMITER ;
        ---------------------------------------------------------- */
      // Create a  CallableStatement with a SQL that
      // Calculate NewPrice for all cars using executeUpdate() method
      stmt = conn.prepareCall("{ call updateCarPark(?) }");
      stmt.setDouble(1, 0.8);
      stmt.executeUpdate();
       /* ---- 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 ese 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("<td>" + resultSet.getDouble("newPrice") + "</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> <td>200.0</td> </tr> <tr > <td>AD23443</td><td>BMW</td><td>2001</td> <td>300.0</td> <td>240.0</td> </tr> <tr > <td>DE23453</td><td>FORD</td><td>2002</td> <td>375.0</td> <td>300.0</td> </tr> <tr > <td>RE23456</td><td>FORD</td><td>2005</td> <td>350.0</td> <td>280.0</td> </tr> <tr > <td>CE23473</td><td>AUDI</td><td>2001</td> <td>400.0</td> <td>320.0</td> </tr> <tr > <td>DE34562</td><td>OPEL</td><td>2001</td> <td>340.0</td> <td>272.0</td> </tr> <tr > <td>AX72345</td><td>AUDI</td><td>2003</td> <td>350.0</td> <td>280.0</td> </tr> <tr > <td>XY34567</td><td>BMW</td><td>2009</td> <td>202.0</td> <td>161.0</td> </tr> </table>

   DE12345 AUDI 2003 250.0 200.0 AD23443 BMW 2001 300.0 240.0 DE23453 FORD 2002 375.0 300.0 RE23456 FORD 2005 350.0 280.0 CE23473 AUDI 2001 400.0 320.0 DE34562 OPEL 2001 340.0 272.0 AX72345 AUDI 2003 350.0 280.0 XY34567 BMW 2009 202.0 161.0