JDBC调用oracle PLSQL(用jpublisher生成代码)

如果有一个TYPE:

CREATE OR REPLACE type EMP_TYPE AS  object
  (
    id   NUMBER ,
    name VARCHAR2 ,
    birthday DATE ,
    sex CHAR (1) 
  );

有一个Pacakge:

CREATE OR REPLACE PACKAGE EMP_UTIL IS
  FUNCTION get_emp_by_id  (p_id NUMBER)  RETURN EMP_TYPE;
  PROCEDURE create_emp  (p_emp EMP_TYPE);
END EMP_UTIL;

现在如果需要用JDBC调用这个Package的这两个功能。

1. 下载JPublisher。

2. 设置CLASSPATH。

set CLASSPATH=%CLASSPATH%;%JPUB_HOME%\sqlj\lib\translator.jar
set CLASSPATH=%CLASSPATH%;%JPUB_HOME%\sqlj\lib\runtime12.jar
set CLASSPATH=%CLASSPATH%;%JPUB_HOME%\sqlj\lib\jpub.jar
set CLASSPATH=%CLASSPATH%;%ORACLE_HOME%\jdbc\lib\classes12.jar

3. 执行命令: jpub -user=user/pwd@SID -s EMP_UTIL

4. 会生成两个个类:EmpType, EmpTypeRef， 将这两个类拷贝到工作目录下。

5. 新建一个java类来调用这两个功能，代码如下:

public class Sample
{
  Map getTypeMap()
  {
    Map map = new HashMap();
    map.put("EMP_TYPE", EmpType.class);
    return map;
  }

  public EmpType getEmpById(long id)
  {
    Connection conn = null;
    CallableStatement cstmt = null;
    try
    {
      conn = getConnection();
      conn.setTypeMap(getTypeMap());
      cstmt = conn.prepareCall("{? = call emp_util.get_emp_by_id(?)}");
      cstmt.registerOutParameter(1, Types.STRUCT, "EMP_TYPE");
      cstmt.setLong(2, id);
      cstmt.execute();
      return (EmpType) cstmt.getObject(1);
    }
    catch (Exception e)
    {
      return null;
    }
    finally
    {
      cstmt.close();
      conn.close();
    }
  }

  public void createEmp(EmpType emp)
  {
    Connection conn = null;
    CallableStatement cstmt = null;
    try
    {
      conn = getConnection();
      conn.setTypeMap(getTypeMap());
      cstmt = conn.prepareCall("{call emp_util.create_emp(?)}");
      cstmt.setObject(1, emp, Types.STRUCT);
      cstmt.execute();
    }
    catch (Exception e)
    {
    }
    finally
    {
    }
  }
}