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 ();