Java/Database SQL JDBC/Oracle JDBC

All data types for Oracle

 
/**
Code revised from 
Database Metadata with JDBC
A sample from
JDBC Metadata, MySQL, and Oracle Recipes
by Mahmoud Parsian
*/
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
 public class CreateTableAllTypesInOracle {
    public static Connection getConnection() throws Exception {
        String driver = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
        String username = "scott";
        String password = "tiger";
        Class.forName(driver);  // load Oracle driver
        Connection conn = DriverManager.getConnection(url, username, password);
        return conn;
    }
    public static void main(String[] args) {
        PreparedStatement pstmt = null;
        Connection conn = null;
        try {
            conn = getConnection();
            pstmt = conn.prepareStatement("CREATE TYPE varray_type is VARRAY(5) OF VARCHAR(10)");
            pstmt.executeUpdate();
            // Create an OBJECT type
            pstmt = conn.prepareStatement("CREATE TYPE oracle_object is OBJECT(column_string VARCHAR(128), column_integer INTEGER)");
            pstmt.executeUpdate();
            StringBuffer allTypesTable = new StringBuffer("CREATE TABLE oracle_all_types(");
            //                    Column Name          Oracle Type              Java Type
            allTypesTable.append("column_short           SMALLINT, ");          // short
            allTypesTable.append("column_int             INTEGER, ");           // int
            allTypesTable.append("column_float           REAL, ");              // float; can also be NUMBER
            allTypesTable.append("column_double          DOUBLE PRECISION, ");  // double; can also be FLOAT or NUMBER
            allTypesTable.append("column_bigdecimal      DECIMAL(13,0), ");     // BigDecimal
            allTypesTable.append("column_string          VARCHAR2(254), ");     // String; can also be CHAR(n)
            allTypesTable.append("column_characterstream LONG, ");              // CharacterStream or AsciiStream
            allTypesTable.append("column_bytes           RAW(2000), ");         // byte[]; can also be LONG RAW(n)
            allTypesTable.append("column_binarystream    RAW(2000), ");         // BinaryStream; can also be LONG RAW(n)
            allTypesTable.append("column_timestamp       DATE, ");              // Timestamp
            allTypesTable.append("column_clob            CLOB, ");              // Clob
            allTypesTable.append("column_blob            BLOB, ");              // Blob; can also be BFILE
            allTypesTable.append("column_bfile           BFILE, ");             // oracle.sql.BFILE
            allTypesTable.append("column_array           varray_type, ");       // oracle.sql.ARRAY
            allTypesTable.append("column_object          oracle_object)");      // oracle.sql.OBJECT
            pstmt.executeUpdate(allTypesTable.toString());
        } catch (Exception e) {
            // creation of table failed.
            // handle the exception
            e.printStackTrace();
        }
    }
}

Check JDBC Installation for Oracle

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class CheckJDBCInstallation_Oracle {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "pass";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  /**
   * Test Validity of JDBC Installation
   * 
   * @param conn
   *          a JDBC connection object
   * @return true if a given connection object is a valid one; otherwise return
   *         false.
   * @throws Exception
   *           Failed to determine if a given connection is valid.
   */
  public static boolean isValidConnection(Connection conn) throws Exception {
    if (conn == null) {
      // null connection object is not valid
      return false;
    }
    if (conn.isClosed()) {
      // closed connection object is not valid
      return false;
    }
    // for Oracle database:
    // you may use the connection object
    // with query of "select 1 from dual";
    // if the query returns the result, then
    // it is a valid connection object.
    return testConnection(conn, "select 1 from dual");
  }
  /**
   * Test Validity of a Connection
   * 
   * @param conn
   *          a JDBC connection object
   * @param query
   *          a sql query to test against database connection
   * @return true if a given connection object is a valid one; otherwise return
   *         false.
   */
  public static boolean testConnection(Connection conn, String query) {
    ResultSet rs = null;
    Statement stmt = null;
    try {
      stmt = conn.createStatement();
      if (stmt == null) {
        return false;
      }
      rs = stmt.executeQuery(query);
      if (rs == null) {
        return false;
      }
      if (rs.next()) {
        // connection object is valid: we were able to
        // connect to the database and return something useful.
        return true;
      }
      // there is no hope any more for the validity
      // of the connection object
      return false;
    } catch (Exception e) {
      return false;
    } finally {
      // close database resources
      try {
        rs.close();
        stmt.close();
      } catch (Exception e) {
        // ignore
      }
    }
  }
  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      System.out.println("conn=" + conn);
      System.out.println("valid connection = " + isValidConnection(conn));
    } catch (Exception e) {
      // handle the exception
      e.printStackTrace();
      System.exit(1);
    } finally {
      // release database resources
      try {
        conn.close();
      } catch (Exception e) {
        // ignore
      }
    }
  }
}

Connect to an Oracle database with JDBC

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@//server.local:1521/prod", "scott", "tiger");
    conn.setAutoCommit(false);
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery("select * from employee");
    while (rset.next()) {
      System.out.println(rset.getString(1));
    }
    stmt.close();
  }
}

Count row in Oracle

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CountRows_Oracle {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "userName";
    String password = "pass";
    Class.forName(driver); 
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static int countRows(Connection conn, String tableName) throws SQLException {
    // select the number of rows in the table
    Statement stmt = null;
    ResultSet rs = null;
    int rowCount = -1;
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT COUNT(*) FROM " + tableName);
      // get the number of rows from the result set
      rs.next();
      rowCount = rs.getInt(1);
    } finally {
      rs.close();
      stmt.close();
    }
    return rowCount;
  }
  public static void main(String[] args) {
    Connection conn = null;
    try {
      conn = getConnection();
      String tableName = "myTable";
      System.out.println("tableName=" + tableName);
      System.out.println("conn=" + conn);
      System.out.println("rowCount=" + countRows(conn, tableName));
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      // release database resources
      try {
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

Create a table in database

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
    String username = "username";
    String password = "welcome";
    String sql = "CREATE TABLE books (id NUMBER(11), title VARCHAR2(64))";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement statement = connection.createStatement();
    statement.execute(sql);
    connection.close();
  }
}

Create Employee Table Oracle

 
import java.sql.Connection;
import java.sql.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;
public class MainClass {
  private static final String EMPLOYEE_TABLE = "create table MyEmployees3 ( "
      + "   id INT PRIMARY KEY, firstName VARCHAR(20), lastName VARCHAR(20), "
      + "   title VARCHAR(20), salary INT " + ")";
  public static Connection getConnection() throws ClassNotFoundException, SQLException {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:database";
    String username = "name";
    String password = "pass";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void main(String args[]) {
    Connection conn = null;
    Statement stmt = null;
    try {
      conn = getConnection();
      stmt = conn.createStatement();
      stmt.executeUpdate(EMPLOYEE_TABLE);
      stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(100, "A")");
      stmt.executeUpdate("insert into MyEmployees3(id, firstName) values(200, "B")");
      System.out.println("CreateEmployeeTableOracle: main(): table created.");
    } catch (ClassNotFoundException e) {
      System.out.println("error: failed to load Oracle driver.");
      e.printStackTrace();
    } catch (SQLException e) {
      System.out.println("error: failed to create a connection object.");
      e.printStackTrace();
    } catch (Exception e) {
      System.out.println("other error:");
      e.printStackTrace();
    } finally {
      try {
        stmt.close();
        conn.close();
      } catch (Exception e) {
      }
    }
  }
}

Creating an OBJECT Type in an Oracle Database

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    stmt.execute("CREATE TYPE object2 AS OBJECT(col_string2 VARCHAR(30), col_integer2 NUMBER)");
    stmt.execute("CREATE TYPE object1 AS OBJECT(col_string1 VARCHAR(30), col_integer2 object2)");
    stmt.execute("CREATE TABLE object1_table(col_integer NUMBER, col_object1 object1)");
  }
}

Creating an Oracle Table to Store Java Types

  
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);

    Statement stmt = connection.createStatement();
    stmt.execute("CREATE TYPE number_varray AS VARRAY(10) OF NUMBER(12, 2)");
    stmt.execute ("CREATE TYPE my_object AS OBJECT(col_string2 VARCHAR(30), col_int2 INTEGER)");
    //    Column Name          Oracle Type             Java Type
    String sql = "CREATE TABLE oracle_all_table("
        + "col_short           SMALLINT, "          // short
        + "col_int             INTEGER, "           // int
        + "col_float           REAL, "              // float; can also be NUMBER
        + "col_double          DOUBLE PRECISION, "  // double; can also be FLOAT or NUMBER
        + "col_bigdecimal      DECIMAL(13,0), "     // BigDecimal
        + "col_string          VARCHAR2(254), "     // String; can also be CHAR(n)
        + "col_characterstream LONG, "              // CharacterStream or AsciiStream
        + "col_bytes           RAW(2000), "         // byte[]; can also be LONG RAW(n)
        + "col_binarystream    RAW(2000), "         // BinaryStream; can also be LONG RAW(n)
        + "col_timestamp       DATE, "              // Timestamp
        + "col_clob            CLOB, "              // Clob
        + "col_blob            BLOB, "              // Blob; can also be BFILE
        + "col_array           number_varray, "     // oracle.sql.ARRAY
        + "col_object          my_object)";         // oracle.sql.OBJECT
    stmt.executeUpdate(sql);

  }
}

Demo ResultSet Oracle

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DemoResultSetOracle {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "username";
    String password = "password";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      System.out.println("conn=" + conn);
      // prepare query
      String query = "select id, name, age from employees";
      // create a statement
      stmt = conn.createStatement();
      // execute query and return result as a ResultSet
      rs = stmt.executeQuery(query);
      // extract data from the ResultSet
      while (rs.next()) {
        String id = rs.getString(1);
        String name = rs.getString(2);
        int age = rs.getInt(3);
        System.out.println("id=" + id);
        System.out.println("name=" + name);
        System.out.println("age=" + age);
        System.out.println("---------------");
      }
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      // release database resources
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

Get Column Names From ResultSet for Oracle

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
public class GetColumnNamesFromResultSet_Oracle {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "username";
    String password = "pass";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static void getColumnNames(ResultSet rs) throws SQLException {
    if (rs == null) {
      return;
    }
    // get result set meta data
    ResultSetMetaData rsMetaData = rs.getMetaData();
    int numberOfColumns = rsMetaData.getColumnCount();
    // get the column names; column indexes start from 1
    for (int i = 1; i < numberOfColumns + 1; i++) {
      String columnName = rsMetaData.getColumnName(i);
      // Get the name of the column"s table name
      String tableName = rsMetaData.getTableName(i);
      System.out.println("column name=" + columnName + " table=" + tableName + "");
    }
  }
  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    try {
      conn = getConnection();
      // prepare query
      String query = "select id, name, age from employees";
      // create a statement
      stmt = conn.createStatement();
      // execute query and return result as a ResultSet
      rs = stmt.executeQuery(query);
      // get the column names from the ResultSet
      getColumnNames(rs);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      // release database resources
      try {
        rs.close();
        stmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}

Get Object From Oracle Database Using STRUCT

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    conn = getOracleConnection();
    stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT emp, age FROM employee");
    while (rs.next()) {
      oracle.sql.STRUCT emp = (oracle.sql.STRUCT) rs.getObject(1);
      Object[] empValues = emp.getAttributes();
      String name = (String) empValues[0];
      java.math.BigDecimal badgeNumber = (java.math.BigDecimal) empValues[1];
      int age = rs.getInt(2);
      System.out.println("name=" + name);
      System.out.println("badgeNumber=" + badgeNumber);
      System.out.println("age=" + age);
    }
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "userName";
    String password = "password";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Get Oracle Table Names

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    Statement stmt = null;
    ResultSet rs = null;
    stmt = conn.createStatement();
    //only for Oracle
    rs = stmt.executeQuery("select object_name from user_objects where object_type = "TABLE"");
    while (rs.next()) {
      String tableName = rs.getString(1);
      System.out.println("tableName=" + tableName);
    }
    stmt.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Get Parameter MetaData From Oracle JDBC Driver

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.Statement;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    Statement st = conn.createStatement();
    st.executeUpdate("drop table survey;");
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    
    
    PreparedStatement pstmt = null;
    ParameterMetaData paramMetaData = null;
    String query = "select * from survey where id > ? ";
    System.out.println("conn=" + conn);
    pstmt = conn.prepareStatement(query);
    paramMetaData = pstmt.getParameterMetaData();
    if (paramMetaData == null) {
      System.out.println("db vendor does NOT support ParameterMetaData");
    } else {
      System.out.println("db vendor supports ParameterMetaData");
      // find out the number of dynamic parameters
      int paramCount = paramMetaData.getParameterCount();
      System.out.println("paramCount=" + paramCount);
    }
    pstmt.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:caspian";
    String username = "mp";
    String password = "mp2";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Insert BLOG(Picture or Photo) Data Type Into Oracle Database

 
/*
Defining the Table: Oracle and MySql
create table MyPictures (
   id INT PRIMARY KEY,
   name VARCHAR(0),
   photo BLOB
);
*/
import java.io.File;
import java.io.FileInputStream;
import java.io.OutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import oracle.jdbc.OracleResultSet;
public class Main {
  public static void main(String[] args) throws Exception {
    Connection conn = getOracleConnection();
    int rows = 0;
    FileInputStream fin = null;
    OutputStream out = null;
    ResultSet rs = null;
    Statement stmt = null;
    oracle.sql.BLOB photo = null;
    conn.setAutoCommit(false);
    stmt = conn.createStatement();
    String id = "001";
    String binaryFileName = "fileName.dat";
    rows = stmt.executeUpdate("insert into my_pictures(id, photo ) values ("" + id
        + "", empty_blob() )");
    System.out.println(rows + " rows inserted");
    rs = stmt.executeQuery("select photo from  my_pictures where id = "" + id
        + "" for update nowait");
    rs.next();
    photo = ((OracleResultSet) rs).getBLOB(1);
    fin = new FileInputStream(new File(binaryFileName));
    out = photo.getBinaryOutputStream();
    // Get the optimal buffer size from the BLOB
    byte[] buffer = new byte[photo.getBufferSize()];
    int length = 0;
    while ((length = fin.read(buffer)) != -1) {
      out.write(buffer, 0, length);
    }
    conn.rumit();
    out.close();
    fin.close();
    rs.close();
    stmt.close();
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "userName";
    String password = "password";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Insert custom type to Oracle

 
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
public class InsertCustomType_Oracle {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "userName";
    String password = "pass";
    Class.forName(driver); // load Oracle driver
    return DriverManager.getConnection(url, username, password);
  }
  public static void main(String[] args) {
    String id = "001";
    String isbn = "1234567890";
    String title = "java demo";
    String author = "jexp";
    int edition = 1;
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      String insert = "insert into book_table values(?, BOOK(?, ?, ?, ?))";
      pstmt = conn.prepareStatement(insert);
      pstmt.setString(1, id);
      pstmt.setString(2, isbn);
      pstmt.setString(3, title);
      pstmt.setString(4, author);
      pstmt.setInt(5, edition);
      pstmt.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      try {
        pstmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}
/**
 * A class to hold a copy of "BOOK" data type
 */
class Book implements SQLData, Serializable {
  public static final String SQL_TYPE_NAME = "BOOK";
  public String isbn;
  public String title;
  public String author;
  public int edition;
  public Book() {
  }
  public Book(String isbn, String title, String author, int edition) {
    this.isbn = isbn;
    this.title = title;
    this.author = author;
    this.edition = edition;
  }
  // retrieves the fully qualified name of the SQL
  // user-defined type that this object represents.
  public String getSQLTypeName() {
    return SQL_TYPE_NAME;
  }
  // populates this object with data it reads from stream
  public void readSQL(SQLInput stream, String sqlType) throws SQLException {
    this.isbn = stream.readString();
    this.title = stream.readString();
    this.author = stream.readString();
    this.edition = stream.readInt();
  }
  // writes this object to stream
  public void writeSQL(SQLOutput stream) throws SQLException {
    stream.writeString(this.isbn);
    stream.writeString(this.title);
    stream.writeString(this.author);
    stream.writeInt(this.edition);
  }
  public void print() {
    System.out.println("isbn=" + isbn);
    System.out.println("title=" + title);
    System.out.println("author=" + author);
    System.out.println("edition=" + edition);
  }
}

Inserting an OBJECT Value into an Oracle Table

  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
public class Main {
  public static void main(String[] argv) throws Exception {
    String driverName = "oracle.jdbc.driver.OracleDriver";
    Class.forName(driverName);
    String serverName = "127.0.0.1";
    String portNumber = "1521";
    String sid = "mydatabase";
    String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
    String username = "username";
    String password = "password";
    Connection connection = DriverManager.getConnection(url, username, password);
    Statement stmt = connection.createStatement();
    stmt.execute("CREATE TYPE object2 AS OBJECT(col_string2 VARCHAR(30), col_integer2 NUMBER)");
    stmt.execute("CREATE TYPE object1 AS OBJECT(col_string1 VARCHAR(30), col_integer2 object2)");
    stmt.execute("CREATE TABLE object1_table(col_integer NUMBER, col_object1 object1)");
    stmt.execute("INSERT INTO object1_table VALUES(1, object1("str1", object2("obj2str1", 123)))");
  }
}

OracleDataSource Demo

 
import java.sql.*;
import oracle.jdbc.pool.*;
public class TestThinDSApp {
  public static void main(String args[]) throws ClassNotFoundException,
      SQLException {
    // These settings are typically configured in JNDI
    // so they a implementation specific
    OracleDataSource ds = new OracleDataSource();
    ds.setDriverType("thin");
    ds.setServerName("dssw2k01");
    ds.setPortNumber(1521);
    ds.setDatabaseName("orcl"); // sid
    ds.setUser("scott");
    ds.setPassword("tiger");
    Connection conn = ds.getConnection();
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt
        .executeQuery("select "Hello Thin driver data source tester "||"
            + "initcap(USER)||"!" result from dual");
    if (rset.next())
      System.out.println(rset.getString(1));
    rset.close();
    stmt.close();
    conn.close();
  }
}

Register custome type to Oracle

 
import java.util.*;
import java.io.*;
import java.sql.*;
public class InsertCustomType2_Oracle {
  public static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "name";
    String password = "pass";
    Class.forName(driver); // load Oracle driver
    return DriverManager.getConnection(url, username, password);
  }
  public static void main(String[] args) {
    String id = "001";
    String isbn = "1234567890";
    String title = "Java Oracle";
    String author = "jexp";
    int edition = 1;
    // create the Book object
    Book book = new Book(isbn, title, author, edition);
    book.print();
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = getConnection();
      // create type map
      java.util.Map map = conn.getTypeMap();
      System.out.println("map=" + map);
      map.put("BOOK", Class.forName("Book"));
      System.out.println("map=" + map);
      String insert = "insert into book_table(ID, BOOK) values(?, ?)";
      pstmt = conn.prepareStatement(insert);
      pstmt.setString(1, id);
      pstmt.setObject(2, book);
      pstmt.executeUpdate();
    } catch (Exception e) {
      e.printStackTrace();
      System.exit(1);
    } finally {
      try {
        pstmt.close();
        conn.close();
      } catch (SQLException e) {
        e.printStackTrace();
      }
    }
  }
}
/**
 * A class to hold a copy of "BOOK" data type
 */
class Book implements SQLData, Serializable {
  public static final String SQL_TYPE_NAME = "BOOK";
  public String isbn;
  public String title;
  public String author;
  public int edition;
  public Book() {
  }
  public Book(String isbn, String title, String author, int edition) {
    this.isbn = isbn;
    this.title = title;
    this.author = author;
    this.edition = edition;
  }
  // retrieves the fully qualified name of the SQL
  // user-defined type that this object represents.
  public String getSQLTypeName() {
    return SQL_TYPE_NAME;
  }
  // populates this object with data it reads from stream
  public void readSQL(SQLInput stream, String sqlType) throws SQLException {
    this.isbn = stream.readString();
    this.title = stream.readString();
    this.author = stream.readString();
    this.edition = stream.readInt();
  }
  // writes this object to stream
  public void writeSQL(SQLOutput stream) throws SQLException {
    stream.writeString(this.isbn);
    stream.writeString(this.title);
    stream.writeString(this.author);
    stream.writeInt(this.edition);
  }
  public void print() {
    System.out.println("isbn=" + isbn);
    System.out.println("title=" + title);
    System.out.println("author=" + author);
    System.out.println("edition=" + edition);
  }
}

Serialized And Deserialize Object Oracle

 
/*
Code revised from 
JDBC Recipes: A Problem-Solution Approach
# By Mahmoud Parsian
# ISBN: 1-59059-520-3
# 664 pp.
# Published: Sep 2005
publisher: apress.ru

*/

import java.io.InputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.OutputStream;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import oracle.sql.BLOB;
/****
CREATE TABLE java_objects (object_id NUMBER, object_name varchar(128), object_value BLOB DEFAULT empty_blob(), primary key (object_id));
SQL> desc java_objects;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OBJECT_ID                                 NOT NULL NUMBER
 OBJECT_NAME                                        VARCHAR2(128)
 OBJECT_VALUE                                       BLOB
SQL> select SEQUENCE_NAME, MIN_VALUE, MAX_VALUE, INCREMENT_BY, LAST_NUMBER from  user_sequences;
SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY LAST_NUMBER
------------------------------ ---------- ---------- ------------ -----------
ID_SEQ                                  1 1.0000E+27            1          21
JAVA_OBJECT_SEQUENCE                    1 1.0000E+27            1           1
*/
public class Main {
  public static void main(String[] args) throws Exception {
    String WRITE_OBJECT_SQL = "BEGIN "
        + "  INSERT INTO java_objects(object_id, object_name, object_value) "
        + "  VALUES (?, ?, empty_blob()) " + "  RETURN object_value INTO ?; " + "END;";
    String READ_OBJECT_SQL = "SELECT object_value FROM java_objects WHERE object_id = ?";
    Connection conn = getOracleConnection();
    conn.setAutoCommit(false);
    List<Object> list = new ArrayList<Object>();
    list.add("This is a short string.");
    list.add(new Integer(1234));
    list.add(new java.util.Date());
    // write object to Oracle
    long id = 0001;
    String className = list.getClass().getName();
    CallableStatement cstmt = conn.prepareCall(WRITE_OBJECT_SQL);
    cstmt.setLong(1, id);
    cstmt.setString(2, className);
    cstmt.registerOutParameter(3, java.sql.Types.BLOB);
    cstmt.executeUpdate();
    BLOB blob = (BLOB) cstmt.getBlob(3);
    OutputStream os = blob.getBinaryOutputStream();
    ObjectOutputStream oop = new ObjectOutputStream(os);
    oop.writeObject(list);
    oop.flush();
    oop.close();
    os.close();
    // Read object from oracle
    PreparedStatement pstmt = conn.prepareStatement(READ_OBJECT_SQL);
    pstmt.setLong(1, id);
    ResultSet rs = pstmt.executeQuery();
    rs.next();
    InputStream is = rs.getBlob(1).getBinaryStream();
    ObjectInputStream oip = new ObjectInputStream(is);
    Object object = oip.readObject();
    className = object.getClass().getName();
    oip.close();
    is.close();
    rs.close();
    pstmt.close();
    conn.rumit();
    // de-serialize list a java object from a given objectID
    List listFromDatabase = (List) object;
    System.out.println("[After De-Serialization] list=" + listFromDatabase);
    conn.close();
  }
  private static Connection getHSQLConnection() throws Exception {
    Class.forName("org.hsqldb.jdbcDriver");
    System.out.println("Driver Loaded.");
    String url = "jdbc:hsqldb:data/tutorial";
    return DriverManager.getConnection(url, "sa", "");
  }
  public static Connection getMySqlConnection() throws Exception {
    String driver = "org.gjt.mm.mysql.Driver";
    String url = "jdbc:mysql://localhost/demo2s";
    String username = "oost";
    String password = "oost";
    Class.forName(driver);
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
  public static Connection getOracleConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:databaseName";
    String username = "userName";
    String password = "password";
    Class.forName(driver); // load Oracle driver
    Connection conn = DriverManager.getConnection(url, username, password);
    return conn;
  }
}

Test Data Encryption Integrity

 
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class TestDataEncryptionIntegrity {
  public static void main(String[] argv) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Properties prop = new Properties();
    prop.setProperty("user", "scott");
    prop.setProperty("password", "tiger");
    prop.setProperty("oracle.net.encryption_client", "REQUIRED");
    prop.setProperty("oracle.net.encryption_types_client", "( RC4_40 )");
    prop.setProperty("oracle.net.crypto_checksum_client", "REQUIRED");
    prop.setProperty("oracle.net.crypto_checksum_types_client", "( MD5 )");
    Connection conn = DriverManager.getConnection(
        "jdbc:oracle:thin:@dssw2k01:1521:orcl", prop);
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt
        .executeQuery("select "Hello Thin driver Encryption & Integrity "
            + "tester "||USER||"!" result from dual");
    while (rset.next())
      System.out.println(rset.getString(1));
    rset.close();
    stmt.close();
    conn.close();
  }
}

Test DataSource LookUp

 
/*
Java Programming with Oracle JDBC
by Donald Bales 
ISBN: 059600088X
Publisher: O"Reilly
*/

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class TestDSLookUp {
  public static void main(String[] args) throws SQLException, NamingException {
    Context ctx = null;
    try {
      Properties prop = new Properties();
      prop.setProperty(Context.INITIAL_CONTEXT_FACTORY,
          "com.sun.jndi.fscontext.RefFSContextFactory");
      prop.setProperty(Context.PROVIDER_URL, "file:/JNDI/JDBC");
      ctx = new InitialContext(prop);
    } catch (NamingException ne) {
      System.err.println(ne.getMessage());
    }
    DataSource ds = (DataSource) ctx.lookup("joe");
    Connection conn = ds.getConnection();
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt
        .executeQuery("select "Hello Thin driver data source tester "||"
            + "initcap(USER)||"!" result from dual");
    if (rset.next())
      System.out.println(rset.getString(1));
    rset.close();
    stmt.close();
    conn.close();
  }
}

Test OCINet 8 App

 
/*
Java Programming with Oracle JDBC
by Donald Bales 
ISBN: 059600088X
Publisher: O"Reilly
*/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestOCINet8App {
  // Test the Net8 syntax
  public static void main(String args[]) throws ClassNotFoundException,
      SQLException {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    // or you can use:
    //  DriverManager.registerDriver(
    //   new oracle.jdbc.driver.OracleDriver());
    Connection conn = DriverManager.getConnection(
        "jdbc:oracle:oci8:@(DESCRIPTION = "
            + "(ADDRESS_LIST = (ADDRESS = "
            + "(PROTOCOL = TCP)(HOST = dssw2k01)(PORT = 1521)))"
            + "(CONNECT_DATA = (SERVICE_NAME = dssw2k01)))",
        "scott", "tiger");
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt
        .executeQuery("select "Hello OCI driver tester "||USER||"!" result from dual");
    while (rset.next())
      System.out.println(rset.getString(1));
    rset.close();
    stmt.close();
    conn.close();
  }
}

Test Oracle JDBC Driver Installation

 
public class TestJDBCDriverInstallation_Oracle {
  public static void main(String[] args) {
    System.out.println("TestJDBCDriverInstallation_Oracle begin");
    try {
      String className = "oracle.jdbc.driver.OracleDriver";
      Class driverObject = Class.forName(className);
      System.out.println("driverObject=" + driverObject);
      System.out.println("your installation of JDBC Driver OK.");
    } catch (Exception e) {
      // your installation of JDBC Driver Failed
      System.out.println("Failed: JDBC Driver Error: " + e.getMessage());
    }
    System.out.println("TestJDBCDriverInstallation_Oracle end.");
  }
}

Test SSL

 
/*
Java Programming with Oracle JDBC
by Donald Bales 
ISBN: 059600088X
Publisher: O"Reilly
*/

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
public class TestSSL {
  public static void main(String[] argv) throws Exception {
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    Properties prop = new Properties();
    prop.setProperty("user", "scott");
    prop.setProperty("password", "tiger");
    // THIS DOES NOT WORK YET
    prop
        .setProperty("oracle.net.ssl_cipher_suites",
            "(ssl_rsa_export_with_rc4_40_md5, ssl_rsa_export_with_des40_cbc_sha)");
    prop.setProperty("oracle.net.ssl_client_authentication", "false");
    prop.setProperty("oracle.net.ssl_version", "3.0");
    prop.setProperty("oracle.net.encryption_client", "REJECTED");
    prop.setProperty("oracle.net.crypto_checksum_client", "REJECTED");
    Connection conn = DriverManager
        .getConnection(
            "jdbc:oracle:thin:@(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCPS)(HOST = dssw2k01)(PORT = 2484))) (CONNECT_DATA = (SERVICE_NAME = DSSW2K01)))",
            prop);
    Statement stmt = conn.createStatement();
    ResultSet rset = stmt.executeQuery("select "Hello Thin driver SSL "
        + "tester "||USER||"!" result from dual");
    while (rset.next())
      System.out.println(rset.getString(1));
    rset.close();
    stmt.close();
    conn.close();
  }
}