JDBC:

	JDBC Introduction
	JDBC DriverManager Connection
	JDBC Datasource Connection
	JDBC Connection interface
JDBC General Statement (GS)
	Create a JDBC General Statement (GS)
JDBC GS and the ResultSet
	JDBC GS creating ResultSet
	JDBC GS using ResultSet cursor
	JDBC GS retrieving ResultSet rows
	JDBC GS updating ResultSet rows
	JDBC GS ResultSet Deleting/ Inserting rows
JDBC GS and the executeUpdate method
	JDBC GS executeUpdate method summary
	JDBC GS Update with executeUpdate
	JDBC GS Delete/Insert with executeUpdate
	JDBC GS DDL statements with executeUpdate
JDBC GS and the execute method
	JDBC GS execute method summary
	JDBC GS Update with execute
	JDBC GS Delete/Insert with execute
	JDBC GS DDL statements with execute
JDBC GS and Batch Query
	JDBC General Statement and Batch Query
JDBC PreparedStatement (PS)
	Create JDBC PreparedStatement (PS)
JDBC PS and the ResultSet
	JDBC PS creating ResultSet
	JDBC PS using ResultSet cursor
	JDBC PS retrieving ResultSet rows
	JDBC PS updating ResultSet rows
	JDBC PS ResultSet Deleting/ Inserting rows
JDBC PS and the executeUpdate method
	JDBC PS Update with executeUpdate
	JDBC PS Delete/Insert with executeUpdate
	JDBC PS DDL statements with executeUpdate
JDBC PS and the execute method
	JDBC PS Update with execute
	JDBC PS Delete/Insert with execute
	JDBC PS DDL statements with execute
JDBC CallableStatement (CS)
	Create JDBC CallableStatement (CS)
JDBC CS and the ResultSet
	JDBC CS creating ResultSet
	JDBC CS using ResultSet cursor
	JDBC CS retrieving ResultSet rows
	JDBC CS updating ResultSet rows
	JDBC CS ResultSet Deleting/ Inserting rows
JDBC CS and the executeUpdate method
	JDBC CS Update with executeUpdate
	JDBC CS Delete/Insert with executeUpdate
	JDBC CS DDL statements with executeUpdate
JDBC CS and the execute method
	JDBC CS Update with execute
	JDBC CS Delete/Insert with execute
	JDBC CS DDL statements with execute

JDBC PS Delete/Insert with executeUpdate.

How to use SQL Delete or SQL Insert with PreparedStatement object executeUpdate() 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 parametric placeholders for values 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
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 Delete and Insert rows in a database table:

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 SQL that
   // elete the car we want to insert if it exists already.
   stmt = conn.prepareStatement("Delete from Carpark where regNo=?");
   stmt.setString(1, "XY34567");
   // Execute the SQL with the  executeUpdate() in the
   // PreparedStatement object.
   stmt.executeUpdate();

   // 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 executeUpdate()
   // 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));
   int result = stmt.executeUpdate();
   if (result > 0) {
    System.out.println("The car is inserted ");
   }
   // Create a  ResultSet with a query that returns
   // all the columns  from the dB table Carpark
   ResultSet resultSet = stmt.executeQuery("select * from Carpark ");
   // 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 executeUpdate() 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
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.

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 (?,?)",
           Statement.RETURN_GENERATED_KEYS);
   stmt.setString(1, "DE34562");
   stmt.setInt(2, 10);
   // Execute the SQL with the  executeUpdate() in
   // the PreparedStatement object
   stmt.executeUpdate();

   // 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
   stmt = conn.prepareStatement("select * from Carpark c, " +
           "CARRENTAL r where c.REGNO=r.REGNO " +
           "and r.CARRENTAL_ID=?");
   stmt.setInt(1, newKey);
   // Gets the ResultSet from the PreparedStatement object
   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><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:
8<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: 8 which is for the car:
regNo	cartype	model	day Price	days
DE34562	OPEL	2001	340.0	10.0

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.

Search Engine Submission - AddMe

Needed Tools for JDBC

Installation procedures.