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

class  StudentRef {
  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( );
	
    String     cmd;
    Statement  stmt = conn.createStatement( );
    ResultSet  rset;
    DatabaseMetaData  dbm = conn.getMetaData( );

    if ( args[0].equals( "drop" ) ) {
      cmd  = "drop sequence userid_seq";
      System.out.println( cmd );
      stmt.execute( cmd );
      cmd  = "drop table student_table";
      System.out.println( cmd );
      stmt.execute( cmd );
      cmd  = "drop type STUDENT";
      System.out.println( cmd );
      stmt.execute( cmd );
    }

    else if ( args[0].equals( "create" ) ) {
      // Check if "student_table" table is there.
      rset = dbm.getTables(
               null, "C##user.id", "STUDENT_TABLE", new String[] {"TABLE"} );
      if ( rset.next( ) ) {
        // Table does exist.
        cmd  = "drop sequence userid_seq";
        System.out.println( cmd );
        stmt.execute( cmd );
        cmd  = "drop table student_table";
        System.out.println( cmd );
        stmt.execute( cmd );
        cmd  = "drop type STUDENT";
        System.out.println( cmd );
        stmt.execute( cmd );
      }
      cmd  = "create sequence userid_seq minvalue 1 ";
      cmd += "start with 1 increment by 1 nocache";
      System.out.println( cmd );
      stmt.execute( cmd );

      cmd  = "create type STUDENT as object ";
      cmd += "( ID integer, name varchar(32), age integer )";
      System.out.println( cmd );
      stmt.execute( cmd );

      cmd  = "create table student_table of STUDENT";
      System.out.println( cmd );
      stmt.execute( cmd );

      cmd  = "insert into student_table ";
      cmd += "select userid_seq.NEXTVAL, 'Digi Mon', 10  from dual";
      System.out.println( cmd );
      stmt.execute( cmd );
      rset.close( );
    }

    else if ( args[0].equals( "insert" ) ) {
      cmd  = "insert into student_table ";
      cmd += "select userid_seq.NEXTVAL, '" + args[1].trim( );
      cmd += "', " + args[2].trim( ) + " from dual";
      System.out.println( cmd );
      stmt.execute( cmd );
    }

    else if ( args[0].equals( "select" ) ) {
      cmd  = "select ref(s) from student_table s where ";
      cmd += "name like '%" + args[1].trim( ) + "%'";
      System.out.println( cmd );
      rset = stmt.executeQuery( cmd );

      while ( rset.next( ) ) {
        // Retrieve the ref object.
        REF ref = (REF) rset.getObject( 1 );
        // Retrieve the object value that the ref points to in the
        //   object table.
        STRUCT student = (STRUCT) ref.getValue( );
        Object attributes[] = student.getAttributes( );
        System.out.println( "\nStudent ID: "  +
          ((BigDecimal) attributes[0]).intValue( ) );
        System.out.println( "Student name: " + 
          (String) attributes[1] );
        System.out.println("Student age:  "  +
          ((BigDecimal)	attributes[2]).intValue( ) );
      }  
      rset.close( );
    }
    stmt.close( );
    conn.close( );
  }
}