// Employee.java

/*
 * This sample shows how to pass in simple data types from Java stored
 * procedures into Java, and how to handle return values.
 *
 */
import java.sql.*;

class Employee 
{
  /*
   * find the name of the employee with a given employee number
   *
   * emp_num is a IN parameter, and the method returns a String
   *
   */
  public static String getEmpName(String user, String passwd, String JDBC_SERVICE,
                                  int emp_num) 
    throws SQLException 
  {
    String name = null;
    Connection conn = null;
    Statement stmt = null;

    try 
    {
      // Connect to the database
      DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
      conn = DriverManager.getConnection (JDBC_SERVICE, user, passwd);

      // create a statement
      stmt = conn.createStatement();

      // find the name of the employee with the given employee number
      ResultSet rset = stmt.executeQuery(
        "SELECT ENAME FROM EMPLOYEE WHERE ENUM = " + emp_num);

      // retrieve and print the results
      while (rset.next()) 
      {
        name = rset.getString (1);
      }
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
    finally 
    {
      // don't forget to close the statement
      if (stmt != null)
        stmt.close();

      if (conn != null)
        conn.close();

    }
    // return the name of the employee who has the given employee number
    return name;
  }

  /*
   * update the position and salary of an employee with the
   * given employee number
   *
   * Here, emp_num and raise are IN parameters; position is an IN/OUT 
   * parameter; salary is an OUT parameter.
   *
   */
  public static void updatePositionSalary(String user, String passwd,
                                          String JDBC_SERVICE, int emp_num,
                                          String[] position, int[] salary,
                                          int raise) 
    throws SQLException 
  {
    String pos = null;
    int sal = 0;
    Connection conn = null;
    Statement stmt = null;
    PreparedStatement pstmt = null;

    try
    {
      // Connect to the database
      DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
      conn = DriverManager.getConnection (JDBC_SERVICE, user, passwd);
      stmt = conn.createStatement();

      pstmt = conn.prepareStatement("UPDATE EMPLOYEE SET POSITION = ?, " +
              " SALARY = SALARY + ? WHERE ENUM = ?");

      // set up bind values and execute the update
      pstmt.setString(1, position[0]);
      pstmt.setInt(2, raise);
      pstmt.setInt(3, emp_num);
      pstmt.execute();

      // retrieve the updated position and salary to verify that
      // the data has been updated in the database
      ResultSet rset = stmt.executeQuery(
         "SELECT POSITION, SALARY FROM EMPLOYEE WHERE ENUM = " + emp_num);

      while (rset.next()) 
      {
        pos = rset.getString ("position");
        sal = rset.getInt ("salary");
      }
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
    finally
    {
      if(stmt != null)
        stmt.close();
      position[0] = pos; 
      salary[0] = sal; 
    }
  }
}