JDBC Code Templates

JDBC Code Templates

 

1. Connection
  
  
   *. To open a connection using thin driver :
      DriverManager.getConnection("jdbc:oracle:thin:@<mc-name>:<port-no>:<sid>",
                  "scott", "tiger");

      OR

      DriverManager.getConnection("jdbc:oracle:thin:@(description=(address=(host=<mc-name>)(protocol=tcp)(port=<port-no>))(connect_data=(sid=<sid>)))",
          "scott","tiger");

  
   *.  To open a connection using OCI driver.

       To use the default connection.
       DriverManager.getConnection("jdbc:oracle:oci8:@", "scott","tiger");

       Or

      DriverManager.getConnection("jdbc:oracle:oci8:@(description=(address=(host=<mc-name>)(protocol=tcp)(port=<port-no>))(connect_data=(sid=<sid>)))",
          "scott","tiger");

       Or

       DriverManager.getConnection("jdbc:oracle:oci8:@<tns-name>", "scott","tiger");
 
 
   *.  Closing a connection.
       conn.close();

  
   *.  To set auto commit on.
       conn.setAutoCommit(true)

  

   *.  To set the batch size to 100
       ((OracleConnection)conn).setDefaultExecuteBatch (100);

2. Statements

  
   *. To create a statement
      Statement stmt = conn.createStatement();

  
   *. To create a prepared Statement
      PreparedStatement pstmt =

      conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");

  
   *. To Create a callable statement.
       CallableStatement procnone = conn.prepareCall ("begin procnone; end;"

  
   *. To execute a SQL that returns a QUERY
      ResultSet rset = stmt.executeQuery ("select ENAME from EMP");

  
   *. To execute a DML that returns the no of rows affected
      PreparedStatement pstmt =
      conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (123, 'John')");
      int rows = pstmt.executeUpdate ();

      Ps. execute and executeQuery can also be called on a PreparedStatement.
      They return a boolean and resultSet respectively.

      PreparedStatement pstmt =
        conn.prepareStatement ("select ENAME from EMP where EMPNO = ?");
      pstmt.setInt(1,123);
      ResultSet rset = pstmt.executeQuery();

  
   *. To execute a DDL.
      boolean status = stmt.execute("create table temp(col1 int)");

  
   *. To Bind an IN variable.
      PreparedStatement pstmt =

        conn.prepareStatement ("select ENAME from EMP where EMPNO = ?");
      pstmt.setInt(1,123);
      ResultSet rset = pstmt.executeQuery();

  
   *  To Bind an OUT variable
      CallableStatement funcin = conn.prepareCall ("begin ? := funcout (?); end;");
      funcout.registerOutParameter (1, Types.CHAR);
      funcout.registerOutParameter (2, Types.CHAR);

      Where funcout is,
      create or replace function funcout (y out char)
         return char is
      begin
        y := 'tested';
        return 'returned';
      end;

  
   *  To set the batch size to 100
      ((OracleStatement)stmt).setRowPrefetch (100);

  
   *  To explicitly send the row to the server in batch mode.
      int rows = ((OracleStatement)stmt).sendBatch();

3. ResultSets

  
   * Fetch the next row
     rset.next();

  
   * Retrieve data from column i based on column no

     rset.getInt(i);

  
   * Based on Column Name
     rset.getInt("EMPNO");

  
   * Close the resultset
     rset.close();

4. Streams: Long columns in JDBC are streamed.

  
   * To set a long (Stream) column
     pstmt.setAsciiStream (1, <input-stream>, <input-stream-length>);

     If the string data is in Unicode format, then use setUnicodeStream.
     pstmt.setUnicodeStream (1, <input-stream>, <input-stream-length>);

     For long raw columns, use setBinaryStream
     pstmt.setBinaryStream (1, <input-stream>, <input-stream-length>);

  
     create table streamexample (data long)

     PreparedStatement pstmt =
      conn.prepareStatement ("insert into streamexample values (?)");
     InputStream is = new FileInputStream ("notes.txt");
     File file = new File ("notes.txt");
     pstmt.setAsciiStream (1, is, (int)file.length ());

  
   * To retrieve a long column
     ResultSet rset =
      stmt.executeQuery ("select * from streamexample");
    
     InputStream ascii_data = rset.getAsciiStream (1);

     // Loop, reading from the gif stream and writing to the file
     int c;
     while ((c = ascii_data.read ()) != -1)
        Systemm.out.print(c);

5. Objects

  
   * How to create a STRUCT type descriptor

     StructDescriptor type_descriptor =
      StructDescriptor.createDescriptor (<type name>, connection);

  
   * How to create a STRUCT object

     Object[] attributes = { ... };
     STRUCT obj = new STRUCT (<type descriptor>, connection, attributes };

  
   * How to create a SQL to Java type map entry.
   
     map.put (<SQL Type Name>, <Java class which implements SQLData interface>);

  
   * How to retrieve a STRUCT column.

     1. As a SQLData object.

     For example, to map SQL type "PERSON" to Java class "PersonObj.java"
     which implements oracle.jdbc2.SQLData interface:
 
     java.util.Dictionary map = new Hashtable (10);
     map.put ("PERSON", Class.forName ("PersonObj"));
     PersonObj obj = (PersonObj) rset.getObject (1, map);

     2. As a CustomDatum object
 
     CustomDatum obj = rset.getCustomDatum (1, <CustomDatum factory>);

     3. As a oracle.sql.STRUCT object

     STRUCT obj = (STRUCT) rset.getObject (1);
               
  
   * How to retrieve STRUCT attributes.

     1. With Connection default type map.

     STRUCT struct = ...
     Object[] attributes = struct.getAttributes ();

     2. With the requested type map.

     java.util.Dictionary map = ...
     STRUCT struct = ...
     Object[] attributes = struct.getAttributes (map);

     3. Without map, return as oracle Datums.

     Datum[] attributes = struct.getOracleAttributes ();

  
   * How to create a ARRAY type descriptor

     ArrayDescriptor type_descriptor =
       ArrayDescriptor.createDescriptor (<type name>, connection);

  
   * How to create a ARRAY object

     Object[] elements = { ... };
     ARRAY obj = new ARRAY (<type descriptor>, connection, elements };

  
   * How to retrieve a ARRAY column.

     ARRAY obj = ((OracleResultSet) rset).getARRAY (1);
               
  
   * How to retrieve ARRAY elements.

     ARRAY array = (ARRAY) rset.getObject (1);

     Object[] elements = array.getArray ();

     or

     Object[] elements = array.getArray (<type map for elements>);

     or

     Object[] elements = array.getArray (<begin index>, <count>);

     or

     Object[] elements = array.getArray (<begin index>, <count>, <type map>);

  
   * How to retrieve ARRAY elements as a ResultSet which holding the elements.

     ARRAY array = (ARRAY) rset.getObject (1);

     ResultSet array_rset = array.getResultSet ();

     or

     ResultSet array_rset = array.getResultSet (<type map for elements>);

     or

     ResultSet array_rset = array.getResultSet (<begin index>, <count>);

     or

     ResultSet array_rset = array.getResultSet (<begin index>, <count>, <type map>);

  
   * How to retrieve a REF column.

     REF obj = ((OracleResultSet) rset).getREF (1);
  
  
   * To access the value which a REF points to.

     REF ref = ((OracleResultSet)rset).getREF (1);
     Object value = ref.getValue ();    // use connection default map

     or

     REF ref = ((OracleResultSet) rset).getREF (1);
     Object value = ref.getValue (<type map>);   // use specified map

  
   * To change the value which a REF points to. 

     REF ref = ((OracleResultSet) rset).getREF (1);
     Object newValue = ...;
     ref.setValue (newValue); 

 

6. LOBS

  
   * To read a piece of a LOB.

     BLOB blob = ((OracleResultSet) rset).getBLOB (1);
     byte[] bytes = blob.getBytes (<begin index>, <length>);

     CLOB clob = ((OracleResultSet) rset).getCLOB (2);
     String str = clob.getSubString (<begin index>, <length>);

     BFILE bfile = ((OracleResultSet) rset).getBFILE (3);
     byte[] bytes = bfile.getBytes (<begin index>, <length>);

  
   * To read the LOB content as a stream.

     BLOB blob = ((OracleResultSet) rset).getBLOB (1);
     InputStream input_stream = blob.getBinaryStream ();
     input_stream.read (...);

     CLOB Clob = ((OracleResultSet) rset).getCLOB (1);
     Reader char_stream = Clob.getCharacterStream ();
     char_stream.read (...);

     CLOB Clob = ((OracleResultSet) rset).getCLOB (1);
     InputStream input_stream = Clob.getAsciiStream ();
     input_stream.read (...);

     BFILE bfile = ((OracleResultSet) rset).getBFILE (1);
     InputStream input_stream = bfile.getBinaryStream ();
     input_stream.read (...);

  
   * To write a specified amount of data into a LOB.

     BLOB blob = ((OracleResultSet) rset).getBLOB (1);
     byte[] data = ...
     int amount_written = blob.putBytes (<begin index>, data);

     CLOB clob = ((OracleResultSet) rset).getCLOB (1);
     String data = ...
     int amount_written = clob.putString (<begin index>, data);

  
   * To replace the LOB content from a stream.

     BLOB blob = ((OracleResultSet) rset).getBLOB (1);
     OutputStream output_stream = blob.getBinaryOutputStream ();
     output_stream.write (...);

     CLOB clob = ((OracleResultSet) rset).getCLOB (1);

     Writer char_stream = Clob.getCharacterOutputStream ();
     char_stream.write (...);

     CLOB Clob = ((OracleResultSet) rset).getCLOB (1);
     OutputStream output_stream = Clob.getAsciiOutputStream ();
     output_stream.write (...);
       
  
   * To get LOB length.

     long length = blob.length ();
 
     long length = clob.length ();

     long length = bfile.length ();