Slide 7.18: Making changes to the database

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_tbl table:

01SQL> CREATE TABLE  emp_tbl (
02  2    empno  INTEGER      PRIMARY KEY,
03  3    ename  VARCHAR(64)  NOT NULL );
04Table created.
05 
06SQL> CREATE SEQUENCE  empid_seq  MINVALUE 1
07  2    START WITH 1 INCREMENT BY 1 NOCACHE;
08Sequence created.
09 
10SQL> INSERT INTO  emp_tbl
11  2    SELECT  empid_seq.NEXTVAL, 'LESLIE'  FROM DUAL;
121 row created.
13 
14SQL> INSERT INTO  emp_tbl  VALUES( 507, 'ANNETTE' );
151 row created.

Name1 (args[0]) =

Name2 (args[1]) =

Name3 (args[2]) =

01// Import the following packages to use JDBC.
02import  java.sql.*;
03import  java.io.*;
04import  oracle.sql.*;
05import  oracle.jdbc.*;
06import  oracle.jdbc.pool.OracleDataSource;
07 
08class  MakeChanges {
09  public static void  main( String args[ ] ) throws SQLException {
10    String user     = "C##userid";
11    String password = "password";
12    String database = "65.52.222.73:1521/cdb1";
13 
14    // Open an OracleDataSource and get a connection.
15    OracleDataSource ods = new OracleDataSource( );
16    ods.setURL     ( "jdbc:oracle:thin:@" + database );
17    ods.setUser    ( user );
18    ods.setPassword( password );
19    Connection conn = ods.getConnection( );
20 
21    try {
22      Statement  stmt = conn.createStatement( );
23      // Clear the table EMP_TBL.
24      stmt.executeUpdate( "DELETE FROM emp_tbl" );
25 
26      // Prepare to insert two employees in the EMP_TBL table and
27      // use the create sequence for the EMPNO of the employees.
28      String  cmd  = "INSERT INTO emp_tbl ";
29              cmd += "SELECT empid_seq.NEXTVAL, ? FROM DUAL";
30      PreparedStatement  pstmt = conn.prepareStatement( cmd );
31 
32      // Add the first employee and the first ? is for ENAME.
33      pstmt.setString( 1, args[0].trim( ) );
34      // Do the insertion.
35      pstmt.execute( );
36      // Add the second employee and the first ? is for ENAME.
37      pstmt.setString( 1, args[1].trim( ) );
38      pstmt.execute  ( );
39 
40      // Add the third employee with an employee number 507.
41      // Prepare to insert new names in the EMP_TBL table.
42      cmd  = "INSERT INTO EMP_TBL( EMPNO, ENAME ) VALUES( ?, ? )";
43      pstmt = conn.prepareStatement( cmd );
44 
45      pstmt.setInt   ( 1, 507 );        // The first ? is for EMPNO
46      pstmt.setString( 2, args[2].trim( ) );   // The second ? is for ENAME
47      pstmt.execute  ( );
48 
49      // Close the statement.
50      pstmt.close( );
51 
52      ResultSet  rs = stmt.executeQuery( "SELECT * FROM emp_tbl" );
53      while ( rs.next( ) ) {
54        System.out.print( "empno: " + rs.getInt( 1 )   + "      " );
55        System.out.print( "ename: " + rs.getString( 2 ) );
56      }
57      rs.close  ( );
58      stmt.close( );
59    }
60    catch( SQLException e ) { System.out.println( e ); }
61    conn.close( );
62  }
63}