package refcur;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.sql.Statement;

import java.util.ArrayList;
import java.util.List;

import oracle.jdbc.pool.OracleDataSource;


/**
 * A simple sample class to illustrate how to use Stored Procedures/functions,
 * and use an Oracle ref cursor in Java.
 * To use the sample code - you will have to create a Connection object,
 * and pass it as a parameter in the constructor of the class.
 */

public class EmpManager extends Object
{
   Connection dbConnection;

   public EmpManager(Connection connection)
   {
     dbConnection = connection;
   }

   /**
   * Get all Employees for  a given Department_ID.
   */

   public List getEmpDept(int deptId) throws Exception
   {
      //Prepare the call to the stored function.
      CallableStatement cstmt =
      dbConnection.prepareCall("{? = call GetRefCursors.sfGetEmpDept(?)}");
      cstmt.setFetchSize(100);
      //The statement will return a "ref cursor" - or a ResultSet in Java terms...
      cstmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);

      //Set the stored function's in parameters
      cstmt.setInt(2, deptId);

      //... and call the stored function...
      cstmt.executeQuery();
       //System.out.println( "after execute Query " ); 
      //Get the ResultSet
      ResultSet rs = (ResultSet) cstmt.getObject(1);

      //Create a list to hold the account information.
      List employees = new ArrayList();

      //Iterate the ResultSet to fetch the employees.
      while (rs.next())
      {
        //Put information about each employees into the list.
        employees.add(rs.getString("Last_Name") + " , " + rs.getString("First_Name"));
      }
      //And then we tidy up by closing the ResultSet and the Statement.
      // how about not close result set
      // rs.close();
      cstmt.close();

      //Return the newly created list of accounts.
      return employees;
   }
    public static void showOpenCnt( Connection conn, String msg)
      throws SQLException
      {
          Statement stmt = conn.createStatement();
          ResultSet rset =
              stmt.executeQuery
              ( "select a.value, b.name "+
                  "from v$mystat a, v$statname b "+
                 "where a.statistic# = b.statistic# "+
                   "and a.statistic#= 3");
          System.out.println( msg );

          while( rset.next() )
            System.out.println( rset.getString(1)+ " " +
                                rset.getString(2) );
          System.out.println( "-----------------------" );
          //rset.close();
          stmt.close();
      }

   /* --------  Main  ------------ */
   public static void main(String[] args) throws SQLException {
      String jdbcUrl = "jdbc:oracle:thin:@113.130.218.168:1521:dbwrkev1";
      String userid = "hr";
      String password = "hr";
      Connection conn;
      OracleDataSource ds;
      List emlist;
      ds = new OracleDataSource();
      ds.setURL(jdbcUrl);
      conn=ds.getConnection(userid,password);
      System.out.println( "Get the Number of emplyee in a Department" );
      EmpManager em = new EmpManager(conn);
      
      try {
            em.showOpenCnt( conn, "*** Before anything" );
            for(int i = 10;  i <=100; i = i + 10 ) {             
                emlist = em.getEmpDept(i);
                  System.out.println( "Dept ID: " + i + 
                     " Number of employee is " + emlist.size()  );
            }
            em.showOpenCnt( conn, "*** After everything" );
                     
       } catch (Exception e) {
          System.out.println( "somthing wrong " );  
       }  
       // while (true) {
           // do nothing
       // }
    }
    
}