JDBC PS DDL statements with executeUpdate.

How to do SQL DDL Statements with the executeUpdate() method existing in the PreparedStatement Object?

1. First you need to Create a Database Connection.
2. With a DB connection, you must write the SQL DDL statement and let this statement be the parameter to the below specified prepareStatement() method.
3. If you have any parameter in your SQL statement, then it is time to set these using the PreparedStatment setXXX() methods.
4. Now you can create a PreparedStatment object with the method:

   Method in Connection interface	Description
   PreparedStatement prepareStatement(String sql)	Creates a PreparedStatement object for sending parameterized SQL statements to the database.

5. With the returned PreparedStatement object, you can execute the following method to perform the SQL DDL statement:

   Method in PreparedStatement interface	Description
   int executeUpdate()	Executes the SQL statement in this PreparedStatement object, which must be 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.

   Example of using this executeUpdate() and a PreparedStatement object to do SQL DDL statement:

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.PreparedStatement;
   import java.util.logging.Level;
   import java.util.logging.Logger;
   
   public class SQLAlter {
   
    public static void main(String[] args) {
     Connection conn = null;
     PreparedStatement 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  PreparedStatement with a SQL that
      // add a Column, TotalCost,
      // to the CARRENTAL Table
      stmt = conn.prepareStatement("ALTER TABLE CARRENTAL " +
                          "ADD COLUMN TotalCost numeric");
      // Use the SQL to update database with the executeUpdate()
      // method in the PreparedStatement
      int result = stmt.executeUpdate();
      // Create a  PreparedStatement with a SQL that
      // Updates the new Column, TotalCost,
      // to be the cost for each rental of the cars.
      stmt = conn.prepareStatement("Update CARRENTAL r " +
              "set TotalCost=RENTALDAYS*" +
              "(Select c.dayPrice from CARPARK c where c.RegNO=r.RegNO) ");
       // Use the SQL to update database with the executeUpdate()
      // method in the PreparedStatement
      result = stmt.executeUpdate();
      // Create a  PreparedStatement with a SQL that
      // returns
      // all the columns  from the dB table Carpark and CarRental
      stmt = conn.prepareStatement("select * from Carpark c, " +
              "CARRENTAL r where c.REGNO=r.REGNO ");
      ResultSet resultSet = stmt.executeQuery();
      // 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>");
       System.out.print("<th>regNo</th>");
       System.out.println("<th>cartype</th>");
       System.out.println("<th>model</th>");
       System.out.println("<th>day<br/>Price</th>");
       System.out.println("<th>Cost</th>");
       System.out.println("</tr>");
       do {
        System.out.println("<tr>");
        System.out.print("<td>" + resultSet.getString("regNo") + "</td>");
        System.out.println("<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("TotalCost") + "</td>");
        System.out.println("</tr>");
       } while (resultSet.next());
       System.out.println("</table>");
      }
     } catch (ClassNotFoundException ex) {
      Logger.getLogger(SQLAlter.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

   <table border='1' > <tr> <th>regNo</th><th>cartype</th> <th>model</th> <th>day<br/>Price</th> <th>Cost</th> </tr> <tr> <td>DE12345</td><td>AUDI</td> <td>2003</td> <td>250.0</td> <td>1250.0</td> </tr> <tr> <td>DE12345</td><td>AUDI</td> <td>2003</td> <td>250.0</td> <td>750.0</td> </tr> <tr> <td>RE23456</td><td>FORD</td> <td>2005</td> <td>350.0</td> <td>2450.0</td> </tr> <tr> <td>RE23456</td><td>FORD</td> <td>2005</td> <td>350.0</td> <td>1750.0</td> </tr> <tr> <td>CE23473</td><td>AUDI</td> <td>2001</td> <td>400.0</td> <td>1600.0</td> </tr> <tr> <td>DE34562</td><td>OPEL</td> <td>2001</td> <td>340.0</td> <td>1020.0</td> </tr> <tr> <td>DE34562</td><td>OPEL</td> <td>2001</td> <td>340.0</td> <td>1700.0</td> </tr> </table>

   regNo cartype model day Price Cost DE12345 AUDI 2003 250.0 1250.0 DE12345 AUDI 2003 250.0 750.0 RE23456 FORD 2005 350.0 2450.0 RE23456 FORD 2005 350.0 1750.0 CE23473 AUDI 2001 400.0 1600.0 DE34562 OPEL 2001 340.0 1020.0 DE34562 OPEL 2001 340.0 1700.0