Java Tutorial/Database/Oracle

Connect with Oracle"s JDBC Thin Driver

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 = getConnection();
    conn.setAutoCommit(false);
    Statement st = conn.createStatement();
    st.executeUpdate("create table survey (id int,name varchar(30));");
    st.executeUpdate("insert into survey (id,name ) values (1,"nameValue")");
    st = conn.createStatement();
    ResultSet rs = st.executeQuery("SELECT * FROM survey");
    rs.close();
    st.close();
    conn.close();
  }
  private static Connection getConnection() throws Exception {
    String driver = "oracle.jdbc.driver.OracleDriver";
    String url = "jdbc:oracle:thin:@localhost:1521:scorpian";
    String username = "username";
    String password = "password";
    Class.forName(driver);
    return DriverManager.getConnection(url, username, password);
  }
}

Create a function named myfuncinout that returns a VARCHAR value

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();
    String function = "CREATE OR REPLACE FUNCTION myfuncinout(x IN OUT VARCHAR) RETURN VARCHAR IS "
        + "BEGIN x:= x||"outvalue"; RETURN "a returned string"; END;";
    stmt.executeUpdate(function);
  }
}

Create a function named myfuncin which returns a VARCHAR value; the function has an IN parameter named x

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();
    String function = "CREATE OR REPLACE FUNCTION myfuncin(x VARCHAR) RETURN VARCHAR IS "
        + "BEGIN RETURN "a return string"||x; END;";
    stmt.executeUpdate(function);
  }
}

Create a function named myfuncout which returns a VARCHAR value;

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();
    String function = "CREATE OR REPLACE FUNCTION myfuncout(x OUT VARCHAR) RETURN VARCHAR IS "
        + "BEGIN " + "x:= "outvalue"; " + "RETURN "a returned string"; " + "END;";
    stmt.executeUpdate(function);
  }
}

Create a function named myfunc which returns a VARCHAR value; the function has no parameter

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();
    String function = "CREATE OR REPLACE FUNCTION myfunc RETURN VARCHAR IS BEGIN RETURN "a returned string"; END;";
    stmt.executeUpdate(function);
  }
}

Create an Oracle Table to Store Java Types (Using Statement Objects)

// Create a VARRAY type
    stmt.execute("CREATE TYPE char_varray AS VARRAY(10) OF VARCHAR(20)");
    // Create an OBJECT type
    stmt.execute ("CREATE TYPE oracle_object AS OBJECT(column_string VARCHAR(128), column_integer INTEGER)");
    StringBuffer sql = new StringBuffer("CREATE TABLE oracle_all_types(");
    //                    Column Name          Oracle Type          Java Type
    sql.append("column_short         SMALLINT, ");          // short
    sql.append("column_int           INTEGER, ");           // int
    sql.append("column_float         REAL, ");              // float
    sql.append("column_double        DOUBLE PRECISION,");   // double
    sql.append("column_bigdecimal    DECIMAL(13,2), ");     // BigDecimal
    sql.append("column_string        VARCHAR2(254), ");     // String
    sql.append("column_charStream    LONG, ");          // CharacterStream
    sql.append("column_bytes         RAW(3000), ");     // byte[]
    sql.append("column_binarystream  RAW(2000), ");     // BinaryStream
    sql.append("column_timestamp     DATE, ");          // Timestamp
    sql.append("column_clob          CLOB, ");          // Clob
    sql.append("column_blob          BLOB, ");          // Blob
    sql.append("column_bfile         BFILE, ");         // oracle.sql.BFILE
    sql.append("column_array         char_varray, ");   // oracle.sql.ARRAY
    sql.append("column_object        oracle_object)"); // oracle.sql.OBJECT
    stmt.executeUpdate(sql.toString());

//From Book: JDBC Recipes A Problem-Solution Approach

Create procedure myprocinout with an IN/OUT parameter named x; x is an IN parameter and an OUT parameter

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);
    String procedure = "CREATE OR REPLACE PROCEDURE myprocinout(x IN OUT VARCHAR) IS BEGIN "
        + "INSERT INTO oracle_table VALUES(x); " // Use x as IN parameter
        + "x := "outvalue"; " // Use x as OUT parameter
        + "END;";
    Statement stmt = connection.createStatement();
    stmt.executeUpdate(procedure);
  }
}

Create procedure myprocin with an IN parameter named x.

import java.sql.CallableStatement;
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();
    // IN is the default mode for parameter, so both "x VARCHAR" and "x IN VARCHAR" are valid
    String procedure = "CREATE OR REPLACE PROCEDURE myprocin(x VARCHAR) IS " + "BEGIN "
        + "INSERT INTO oracle_table VALUES(x); " + "END;";
    stmt.executeUpdate(procedure);
  }
}

Create procedure myprocout with an OUT parameter named x

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();
    String procedure = "CREATE OR REPLACE PROCEDURE myprocout(x OUT VARCHAR) IS BEGIN "
        + "INSERT INTO oracle_table VALUES("string 2"); x := "outvalue"; END;";
    stmt.executeUpdate(procedure);
  }
}

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

  }
}

Creating a Stored Procedure or Function in an Oracle Database

import java.sql.CallableStatement;
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);
    CallableStatement cs = connection.prepareCall("{? = call myfuncinout(?)}");
    
    Statement stmt = connection.createStatement();
    
    // Create procedure myproc with no parameters
    String procedure = "CREATE OR REPLACE PROCEDURE myproc IS "
        + "BEGIN "
        + "INSERT INTO oracle_table VALUES("string 1"); "
        + "END;";
    stmt.executeUpdate(procedure);
  }
}

Execute Method

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class ExecuteMethod {
  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    boolean executeResult;
    try {
      String driver = "oracle.jdbc.driver.OracleDriver";
      Class.forName(driver).newInstance();
      System.out.println("Connecting to database...");
      String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";
      conn = DriverManager.getConnection(jdbcUrl, "test", "mypwd");
      stmt = conn.createStatement();
      String sql = "INSERT INTO Employees VALUES" + "(1,"G","4351",{d "1996-12-31"},500)";
      executeResult = stmt.execute(sql);
      processExecute(stmt, executeResult);
      sql = "SELECT * FROM Employees ORDER BY hiredate";
      executeResult = stmt.execute(sql);
      processExecute(stmt, executeResult);
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      try {
        if (conn != null)
          conn.close();
      } catch (SQLException se) {
        se.printStackTrace();
      }
    }
  }
  public static void processExecute(Statement stmt, boolean executeResult) throws SQLException {
    if (!executeResult) {
      int updateCount = stmt.getUpdateCount();
      System.out.println(updateCount + " row was " + "inserted into Employee table.");
    } else {
      ResultSet rs = stmt.getResultSet();
      while (rs.next()) {
        System.out.println(rs.getInt("SSN") + rs.getString("Name") 
            + rs.getDouble("Salary") + rs.getDate("Hiredate") + rs.getInt("Loc_id"));
      }
    }
  }
}

Get list of stored procedure names

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Main {
  private static String url = "jdbc:oracle:thin:@localhost:1521:javaDemo";
  private static String username = "java";
  private static String password = "";
  public static void main(String[] args) throws Exception {
    Class.forName("oracle.jdbc.driver.OracleDriver");
    Connection conn = DriverManager.getConnection(url, username, password);
    DatabaseMetaData metadata = conn.getMetaData();
    ResultSet result = metadata.getProcedures(null, "JAVA", "%");
    while (result.next()) {
      System.out.println(result.getString("PROCEDURE_CAT") + " - "
          + result.getString("PROCEDURE_SCHEM") + " - " + result.getString("PROCEDURE_NAME"));
    }
  }
}

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)))");
  }
}

Rollback a transaction

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class Rollback {
  public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    boolean executeResult;
    try {
      String driver = "oracle.jdbc.driver.OracleDriver";
      Class.forName(driver).newInstance();
      System.out.println("Connecting to database...");
      String jdbcUrl = "jdbc:oracle:thin:@localhost:1521:ORCL";
      conn = DriverManager.getConnection(jdbcUrl, "yourName", "mypwd");
      stmt = conn.createStatement();
      conn.setAutoCommit(false);
      if (!conn.getAutoCommit())
        System.out.println("Auto-commit is set to false");
      String sql = "INSERT INTO Location VALUES(715,"Houston")";
      stmt.executeUpdate(sql);
      sql = "INSERT INTO Employees VALUES" + "(8,"K","4351",{d "2000-02-00"},715)";
      stmt.executeUpdate(sql);
      conn.rumit();
    } catch (SQLException se) {
      String msg = se.getMessage();
      msg = "SQLException occured with message: " + msg;
      System.out.println(msg);
      System.out.println("Starting rollback operations...");
      try {
        conn.rollback();
      } catch (SQLException se2) {
        se2.printStackTrace();
      }
    } catch (Exception e) {
      e.printStackTrace();
    } 
  }
}

Store and retrieve an object from a table

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.io.Serializable;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
public class Main {
  public static void main(String args[]) throws Exception {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
    String URL = "jdbc:odbc:dbname";
    Connection dbConn = DriverManager.getConnection(URL, "user", "passw");
    Employee employee = new Employee(42, "AA", 9);
    ByteArrayOutputStream baos = new ByteArrayOutputStream();
    ObjectOutputStream oos = new ObjectOutputStream(baos);
    oos.writeObject(employee);
    byte[] employeeAsBytes = baos.toByteArray();
    PreparedStatement pstmt = dbConn
        .prepareStatement("INSERT INTO EMPLOYEE (emp) VALUES(?)");
    ByteArrayInputStream bais = new ByteArrayInputStream(employeeAsBytes);
    pstmt.setBinaryStream(1, bais, employeeAsBytes.length);
    pstmt.executeUpdate();
    pstmt.close();
    Statement stmt = dbConn.createStatement();
    ResultSet rs = stmt.executeQuery("SELECT emp FROM Employee");
    while (rs.next()) {
      byte[] st = (byte[]) rs.getObject(1);
      ByteArrayInputStream baip = new ByteArrayInputStream(st);
      ObjectInputStream ois = new ObjectInputStream(baip);
      Employee emp = (Employee) ois.readObject();
    }
    stmt.close();
    rs.close();
    dbConn.close();
  }
}
class Employee implements Serializable {
  int ID;
  String name;
  double salary;
  public Employee(int ID, String name, double salary) {
    this.ID = ID;
    this.name = name;
    this.salary = salary;
  }
}