Making Changes to the Database

To write changes to the database, such as for INSERT or UPDATE operations, you will typically create a PreparedStatement object. Instead of calling the same statement over and over with different inputs, you can instead use a PreparedStatement, which allows you to execute a statement with varying sets of input parameters. The java.sql.PreparedStatement interface allows binding parameters which are associating a parameter (indicated by a placeholder like ? in the prepared statement) with an actual Java value. Use setXXX( ) methods on the PreparedStatement object to bind data into the prepared statement to be sent to the database. The following example shows how to use a prepared statement to execute INSERT operations that add rows to the emp_tbl2 table: 

SQL> CREATE TABLE  emp_tbl2 (
  2    empno  INTEGER      PRIMARY KEY,
  3    ename  VARCHAR(64)  NOT NULL );
Table created.

SQL> CREATE SEQUENCE  empid_seq2  MINVALUE 1
  2    START WITH 1 INCREMENT BY 1 NOCACHE;
Sequence created.

The program inserts the following three employees: 

SQL> INSERT INTO  emp_tbl2
  2    SELECT  empid_seq2.NEXTVAL, 'Leslie'  FROM DUAL;
1 row created.

SQL> INSERT INTO  emp_tbl2
  2    SELECT  empid_seq2.NEXTVAL, 'Marsha'  FROM DUAL;
1 row created.

SQL> INSERT INTO  emp_tbl2  VALUES( 507, 'Annette' );
1 row created.

Name1 (args[0]) =

Name2 (args[1]) =

Name3 (args[2]) =

                        
// Import the following packages to use JDBC.
import  java.sql.*;
import  java.io.*;
import  oracle.sql.*;
import  oracle.jdbc.*;
import  oracle.jdbc.pool.OracleDataSource;

class  MakeChanges {
  public static void  main( String args[ ] ) throws SQLException {
    String user     = "C##user.id";
    String password = "password";
    String database = "65.52.222.73:1521/cdb1";

    // Open an OracleDataSource and get a connection.
    OracleDataSource ods = new OracleDataSource( );
    ods.setURL     ( "jdbc:oracle:thin:@" + database );
    ods.setUser    ( user );
    ods.setPassword( password );
    Connection conn = ods.getConnection( );

    try {
      Statement  stmt = conn.createStatement( );
      // Clear the table EMP_TBL.
      stmt.executeUpdate( "DELETE FROM emp_tbl2" );

      // Prepare to insert two employees in the emp_tbl2 table and
      // use the create sequence for the empno of the employees.
      String  cmd  = "INSERT INTO emp_tbl2 ";
              cmd += "SELECT empid_seq2.NEXTVAL, ? FROM DUAL";
      PreparedStatement  pstmt = conn.prepareStatement( cmd );

      // Add the first employee and the first ? is for ename.
      pstmt.setString( 1, args[0].trim( ) );
      // Do the insertion.
      pstmt.execute( );
      // Add the second employee and the first ? is for ename.
      pstmt.setString( 1, args[1].trim( ) );
      pstmt.execute  ( );

      // Add the third employee with an employee number 507.
      // Prepare to insert new names in the EMP_TBL table.
      cmd  = "INSERT INTO emp_tbl2( empno, ename ) VALUES( ?, ? )";
      pstmt = conn.prepareStatement( cmd );

      pstmt.setInt   ( 1, 507 );        // The first ? is for empno
      pstmt.setString( 2, args[2].trim( ) );   // The second ? is for ename
      pstmt.execute  ( );
      // Close the Statement.
      pstmt.close( );

      cmd = "SELECT * FROM emp_tbl2 ORDER By empno";
      ResultSet  rset = stmt.executeQuery( cmd );
      while ( rset.next( ) ) {
        System.out.print( "empno: " + rset.getInt( 1 ) );
        System.out.print( "ename: " + rset.getString( 2 ) );
      }
      // Close the ResultSet and Statement.
      rset.close  ( );
      stmt.close( );
    }
    catch( SQLException e ) { System.out.println( e ); }
    conn.close( );
  }
}




      “God will not look you over for medals, degrees or diplomas but for scars.”    
      ― Elbert Hubbard