JDBC GS DDL statements with executeUpdate.
How to do SQL DDL Statements with the executeUpdate() method existing in the Statement Object?
- First you need to Create a Database Connection.
- Then create a regular Statement object with the method, createStatement().
-
The next step is to write your SQL DDL statement
and give this string as a parameter to the method:
Method in Statement interface Description int executeUpdate(String sql) Executes the given SQL statement, and returns the update count. Example of using this method to use SQL DDL statement: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 SQLAlter { 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(); // Create an SQL to add a Column, TotalCost, // to the CARRENTAL Table String alterQuery = "ALTER TABLE CARRENTAL " + "ADD COLUMN TotalCost numeric"; int result = stmt.executeUpdate(alterQuery); // 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) "; result = stmt.executeUpdate(query); // Create a ResultSet with a query that returns // all the columns from the dB table Carpark and CarRental ResultSet resultSet = stmt.executeQuery("select * from Carpark c, " + "CARRENTAL r where c.REGNO=r.REGNO "); // 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> <tr> <td>DE34562</td><td>OPEL</td> <td>2001</td> <td>340.0</td> <td>3400.0</td> </tr> </table>
regNo cartype model day
PriceCost 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 DE34562 OPEL 2001 340.0 3400.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.