JDBC PS Delete/Insert with execute.
How to use SQL Delete or SQL Insert with PreparedStatement object execute() method?
- First you need to Create a Database Connection.
We will first do an SQL delete and an SQL Insert with NO auto-generated keys control.
- With a DB connection, you must write the SQL Delete or the SQL Insert statement and let this statement be the parameter to the below specified prepareStatement() method.
- If you have any parameter in your SQL statement, then it is time to set these using the PreparedStatment setXXX() methods.
-
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. -
With the returned PreparedStatement object, you can execute the following
method to perform the SQL Update statement:
Method in PreparedStatement interface Description boolean execute() Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL 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 SQLInsert { 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 the select all columns fro a DB table Carpark // where regNo=XY34567 stmt = conn.prepareStatement("select * from Carpark " + " where regNo=?"); stmt.setString(1, "XY34567"); // Gets the ResultSet from the PreparedStatement object ResultSet resultSet = stmt.executeQuery(); // We delete the car we want to insert if it exists already. if (resultSet.next()) { // Delete with execute() method in the PreparedStatement object stmt=conn.prepareStatement("Delete from Carpark where regNo=?"); stmt.setString(1, "XY34567"); stmt.execute(); } // Create a PreparedStatement with SQL that // Inserts a new row with a random generated dayprice // In this case we write the SQL and use the execute() // method to do the insert. stmt = conn.prepareStatement("Insert into CarPark (regNo,cartype," + "model, dayprice)" + " values (?,?,?,?)"); stmt.setString(1, "XY34567"); stmt.setString(2, "BMW"); stmt.setInt(3, 2009); stmt.setDouble(4, 200 + Math.round(Math.random() * 200)); boolean result = stmt.execute(); // if result false it is NOT a ResultSet if (!result) { System.out.println("The car is inserted "); } // Create a PreparedStatement with SQL that // that returns for a ResultSet // all the columns from the dB table Carpark // using the execute() method in the PreparedStatement object. stmt=conn.prepareStatement("select * from Carpark"); result = stmt.execute(); // 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' >"); 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) { Logger.getLogger(SQLInsert.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> <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>
DE12345 AUDI 2003 250.0 AD23443 BMW 2001 300.0 DE23453 FORD 2002 375.0 RE23456 FORD 2005 350.0 CE23473 AUDI 2001 400.0 DE34562 OPEL 2001 340.0 AX72345 AUDI 2003 350.0 XY34567 BMW 2009 234.0
How to do SQL Insert combined with auto-generated keys retrieval using the execute() method?
- With a DB connection, you must write the SQL Insert statement and let this statement be the first parameter to one of the below specified prepareStatement() methods.
- If you have any parameter in your SQL Insert statement, then it is time to set these using the PreparedStatment setXXX() methods.
- If you are using the first method, the second parameter should be Statement.RETURN_GENERATED_KEYS.
- If you use one of the last two methods, the second parameter must be either an integer array or a string array that in both cases must represent the generated key-columns in the database table.
-
Now you can create a PreparedStatment object with one of the methods:
Methods in Connection interface Description PreparedStatement prepareStatement(String sql, int autoGeneratedKeys) Creates a default PreparedStatement object that has the capability to retrieve auto-generated keys. This parameter is ignored if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys PreparedStatement prepareStatement(String sql, int[] columnIndexes) Creates a default PreparedStatement object capable of returning the auto-generated keys designated by the given array. The array contains the indexes (starting with 1 for the first) of the columns. The driver will ignore the array if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys. PreparedStatement prepareStatement(String sql, String[] columnNames) Creates a default PreparedStatement object capable of returning the auto-generated keys designated by the given array. The array contains the names of the columns. The driver will ignore the array if the SQL statement is not an INSERT statement, or an SQL statement able to return auto-generated keys. -
With the returned PreparedStatement object, you can execute the following
method to perform the SQL Insert statement:
Method in PreparedStatement interface Description boolean execute() Executes the SQL statement in this PreparedStatement object, which may be any kind of SQL statement. -
After executed SQL Insert, use the getGeneratedKeys()
method in the PreparedStatement object to retrieve the db-table
key, which will be one or more columns of key data into a ResultSet.
Example of using SQL Insert with returned auto-generated keys:
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 SQLAutoKey { 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 // Inserts a new rental for a car // In this case we write the SQL and use the execute() stmt = conn.prepareStatement("insert into " + "CARRENTAL(REGNO,RENTALDAYS) values" + "(?,?)"); stmt.setString(1, "DE34562"); stmt.setInt(2, 10); stmt.execute(); // Using the getGeneratedKeys() method to retrieve // the key(s). In this case there is only one key column ResultSet keyResultSet = stmt.getGeneratedKeys(); int newKey = 0; if (keyResultSet.next()) { newKey = (int) keyResultSet.getInt(1); } // Create a PreparedStatement // 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 execute() method of the PreparedStatement object stmt = conn.prepareStatement("select * from Carpark c, " + "CARRENTAL r where c.REGNO=r.REGNO " + "and r.CARRENTAL_ID=?"); stmt.setInt(1, newKey); boolean 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><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) { Logger.getLogger(SQLAutoKey.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 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:regNo cartype model day
Pricedays DE34562 OPEL 2001 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.