// EmployeeByValue.java

/*
 * This is a StoredProcedure that provides entrypoint to get 
 * EmployeeInfo values or EmployeeObject references to the Client
 */

import java.sql.*;

public class EmployeeByValue 
{
  private static Connection conn = null;

  public static byte[] getEmployeeInfo (Integer empno) throws Exception {
    EmployeeInfo emp_info = getEmployee (empno);
    return Util.serializeObject (emp_info);
  }

  public static void updateEmployeeInfo (byte[] info) throws Exception {
    EmployeeInfo emp_info = (EmployeeInfo)Util.deserializeObject (info);
    updateEmployee (emp_info);
  }

  private static EmployeeInfo getEmployee (Integer empno) 
       throws SQLException
  {
    if (empno == null)
      return null;
    else {
      PreparedStatement ps = null;
      ps = getConnection().prepareStatement("select ename, sal, mgr " +
                                            "from emp where empno = ?");
      ps.setInt(1,empno.intValue());
      ResultSet rs = ps.executeQuery();
      if (!rs.next ()) return null;
      Integer mgr  = new Integer(rs.getInt("mgr"));
      if(rs.wasNull())
        mgr = null;
      return new EmployeeInfo (empno.intValue (), rs.getString("ename"), 
                               rs.getInt("sal"), getEmployee (mgr));
    }
  }

  private static void updateEmployee (EmployeeInfo emp_info)
       throws SQLException
  {
    String str = null;
    PreparedStatement ps = null;
    if (emp_info != null) {
      updateEmployee (emp_info.manager);
      if (emp_info.manager != null)
      {
        ps = getConnection().prepareStatement("update emp set ename = ?, " +
                                              "mgr = ?, sal = ? " +
                                              "where empno = ?");
        ps.setString(1,emp_info.ename);
        ps.setInt(2,emp_info.manager.empno);
        ps.setInt(3,emp_info.salary);
        ps.setInt(4,emp_info.empno);
      }
      else
      {
         ps = getConnection().prepareStatement("update emp set ename = ?, " +
                                               "sal = ? where empno = ?");
         ps.setString(1,emp_info.ename);
         ps.setInt(2,emp_info.salary);
         ps.setInt(3,emp_info.empno);

      }
      ps.executeUpdate();
      ps.close();
    }
  }

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

    return conn;
  }
}