// EmployeeByRef.java
/*
 * This is a StoredProcedure that provides entrypoint to get 
 * EmployeeObject references to the Client
 */

import java.sql.*;
import java.util.Vector;
import oracle.jdbc.*;

public class EmployeeByRef
{
  static Vector references = new Vector ();
  static Connection conn = null;

  public static Integer getEmployee (Integer empno) 
       throws SQLException
  {
    if (empno == null) return null;
    EmployeeObject emp = makeEmployee (empno.intValue());
    if (emp == null) return null;
    references.addElement (emp);
    return new Integer (references.size () - 1);
  }

  public static Integer employeeSalary (Integer id) {
    EmployeeObject emp = (EmployeeObject)references.elementAt (id.intValue ());
    if (emp == null)
      return null;
    return new Integer (emp.salary);
  }

  public static void raiseEmployee (Integer id, float percent) 
    throws SQLException
  {
    EmployeeObject emp = (EmployeeObject)references.elementAt (id.intValue ());
    if (emp != null)
      emp.raise (percent);
  }

  public static void updateEmployee (Integer id)
       throws SQLException
  {
    EmployeeObject emp = (EmployeeObject)references.elementAt (id.intValue ());
    PreparedStatement pst = 
      getConnection().prepareStatement("update emp set ename = ?, " + 
                                       "sal = ? where empno = ?");
    pst.setString(1,emp.ename);
    pst.setInt(2,emp.salary);
    pst.setInt(3,emp.empno);
    pst.executeUpdate();
  }

  private static EmployeeObject makeEmployee (int empno)
       throws SQLException
  {
    
    PreparedStatement pst = 
      getConnection().prepareStatement("select ename, sal from emp " +
                                       "where empno = ?");
    pst.setInt(1,empno);
    ResultSet rs = pst.executeQuery();
    
    if (!rs.next ()) return null;
    return new EmployeeObject (empno, rs.getString("ename"), rs.getInt("sal"));
  }

  private static Connection getConnection() 
    throws SQLException
  {
    if(conn == null)
    {
      conn = (new OracleDriver()).defaultConnection();
    }
    return conn;
  }   

  private static void closeConnection() 
    throws SQLException
  {
    if(conn != null)
      conn.close();
  }
}