JDBC PS DDL statements with execute

How to do SQL DDL Statements with the execute() 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
    boolean execute() Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement.
    Example of using this method to use SQL DDL statementspan>:
    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 an SQL ALTER statement to add a Column, TotalCost,
       // to the CARRENTAL Table
       stmt = conn.prepareStatement("ALTER TABLE CARRENTAL " +
                           "ADD COLUMN TotalCost numeric");
       // Executing the SQL with PreparedStatement object
       // execute() method
       boolean result = stmt.execute();
       // Create SQL to Update the new Column, TotalCost,
       // to be the cost for each rental of the cars.
       String query = "Update CARRENTAL r set TotalCost=RENTALDAYS*" +
               "(Select c.dayPrice from CARPARK c where c.RegNO=r.RegNO) ";
       // Executing the SQL with PreparedStatement object
       // execute() method
       stmt = conn.prepareStatement(query);
       result = stmt.execute();
       // Create a  ResultSet with a query that returns
       // all the columns  from the dB table Carpark
       // using the execute() method on the PreparedStatement object
       stmt = conn.prepareStatement("select * from Carpark c, " +
               "CARRENTAL r where c.REGNO=r.REGNO ");
       result = stmt.execute();
       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 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>");
        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>
    
    regNocartypemodel day
    Price
    Cost
    DE12345AUDI2003 250.0 1250.0
    DE12345AUDI2003 250.0 750.0
    RE23456FORD2005 350.0 2450.0
    RE23456FORD2005 350.0 1750.0
    CE23473AUDI2001 400.0 1600.0
    DE34562OPEL2001 340.0 1020.0
    DE34562OPEL2001 340.0 1700.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.