Java 传对象到Oracle存储过程

  在大数据量处理方面，存储过程有着独特的优势，如果把数据加载到中间件处理，用户体验差不说，对系统也是灾难。测试中遇到几个问题：

1.只有int能传到存储过程中，String 和date不行，是jar包的问题，解决方案：

F:\app\Administrator\product\11.2.0\dbhome_1\jlib\orai18n.jar

D:\program\weblogic\oracle_common\modules\oracle.jdbc_11.2.0\ojdbc6.jar

2.date精度丢失（小时分钟秒）的问题，解决方案用java.sql.Timestamp.

drop table test purge;
create table test as select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,
OBJECT_TYPE,CREATED,LAST_DDL_TIME,STATUS,
TEMPORARY,SECONDARY,EDITION_NAME from dba_objects;

drop type TEST_OBJECT;
CREATE OR REPLACE TYPE TEST_OBJECT AS OBJECT   
(  
  OWNER          VARCHAR2(30),
  OBJECT_NAME    VARCHAR2(128),
  OBJECT_ID      NUMBER,
  DATA_OBJECT_ID NUMBER,
  OBJECT_TYPE    VARCHAR2(19),
  CREATED        DATE,
  LAST_DDL_TIME  DATE,
  STATUS         VARCHAR2(7),
  TEMPORARY      VARCHAR2(1),
  SECONDARY      VARCHAR2(1),
  EDITION_NAME   VARCHAR2(30)  
);  
 
create or replace procedure t_list_to_p(
   testObject  TEST_OBJECT,
   t_count out number,
   dCREATED out date)   
is
  OWNER VARCHAR2(30);
  OBJECT_ID  NUMBER;
  OBJECT_TYPE  VARCHAR2(19);
  OBJECT_NAME  VARCHAR2(128);
  STATUS  VARCHAR2(7);
  CREATED date;
  sqlT varchar2 (32767);  
  SCREATED VARCHAR2(19);
begin  
  OWNER := testObject.OWNER;  
  OBJECT_ID := testObject.OBJECT_ID;
  OBJECT_TYPE := testObject.OBJECT_TYPE;
  STATUS := testObject.STATUS;
  CREATED := testObject.CREATED;
  sqlT := 'select count(1) from test where object_id=:1 and owner=:2 and created>=:3';
  EXECUTE IMMEDIATE  sqlT into t_count using OBJECT_ID,OWNER,CREATED;
  dCREATED := CREATED;
end t_list_to_p;  

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Types;
import java.text.SimpleDateFormat;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;
public class TestObjectToProdure {
    static final String driver_class  = "oracle.jdbc.driver.OracleDriver";
    static final String connectionURL = "jdbc:oracle:thin:@10.100.15.18:1521:ora11";
    static final String userID        = "test";
    static final String userPassword  = "test";
    public void runTest(TestVO testVO) {
        Connection  con = null;
        CallableStatement stmt = null ;
        try {
            Class.forName (driver_class).newInstance();
            con = DriverManager.getConnection(connectionURL, userID, userPassword);
            StructDescriptor tDescriptor = StructDescriptor.createDescriptor("TEST_OBJECT", con);
            Object[]  tObject  = new Object[11];
            tObject[0] = testVO.getOWNER();
            tObject[1] = testVO.getObject_name();
            tObject[2] = testVO.getObject_id();
            tObject[3] = testVO.getDATA_OBJECT_ID();
            tObject[4] = testVO.getOBJECT_TYPE();
            tObject[5] = testVO.getCREATED()  ;
            tObject[6] = testVO.getLAST_DDL_TIME() ;
            tObject[7] = testVO.getSTATUS()  ;
            tObject[8] = testVO.getTEMPORARY()  ;
            tObject[9] = testVO.getSECONDARY() ;
            tObject[10]= testVO.getEDITION_NAME() ;
            STRUCT struct = new STRUCT(tDescriptor, con, tObject);

            stmt = con.prepareCall("{call t_list_to_p(?,?,?)}");
            stmt.setObject(1, struct);
            stmt.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR);
            stmt.registerOutParameter(3, Types.DATE );
            stmt.execute();
            String sqlStatment=stmt.getString(2);
            System.out.println(stmt.getTimestamp(3));
        }  catch (Exception e) {
            e.printStackTrace();
        }finally{
            if(stmt != null){
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if(con != null){
                try {
                    con.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
    public static void main(String[] args) throws Exception{
        TestObjectToProdure testObjectToProdure = new TestObjectToProdure();
        TestVO testVO = new TestVO();
        testVO.setObject_id(20);
        testVO.setOWNER("SYS");
        testVO.setOBJECT_TYPE("TABLE");
        testVO.setSTATUS("1");
        testVO.setCREATED(getDate());
        testObjectToProdure.runTest(testVO);
    }
    public static java.sql.Timestamp getDate() throws Exception{
        SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String dateString = "2009-8-15 08:01:00";
        java.util.Date currentTime = formatter.parse(dateString);
        java.sql.Timestamp sTime=new java.sql.Timestamp(currentTime.getTime());
        return sTime;
     }
}

import java.sql.Date;
import java.sql.Timestamp;

public class TestVO {
   private int object_id;
   private String object_name;
   private String OBJECT_TYPE;
   private String OWNER;
   private String STATUS;
   private String TEMPORARY;
   private String SECONDARY;
   private String EDITION_NAME;
   private int  DATA_OBJECT_ID;
   private Timestamp CREATED;
   private Date LAST_DDL_TIME;
public Timestamp getCREATED() {
    return CREATED;
}
public void setCREATED(Timestamp created) {
    CREATED = created;
}
public int getDATA_OBJECT_ID() {
    return DATA_OBJECT_ID;
}
public void setDATA_OBJECT_ID(int data_object_id) {
    DATA_OBJECT_ID = data_object_id;
}
public String getEDITION_NAME() {
    return EDITION_NAME;
}
public void setEDITION_NAME(String edition_name) {
    EDITION_NAME = edition_name;
}
public Date getLAST_DDL_TIME() {
    return LAST_DDL_TIME;
}
public void setLAST_DDL_TIME(Date last_ddl_time) {
    LAST_DDL_TIME = last_ddl_time;
}
public int getObject_id() {
    return object_id;
}
public void setObject_id(int object_id) {
    this.object_id = object_id;
}
public String getObject_name() {
    return object_name;
}
public void setObject_name(String object_name) {
    this.object_name = object_name;
}
public String getOBJECT_TYPE() {
    return OBJECT_TYPE;
}
public void setOBJECT_TYPE(String object_type) {
    OBJECT_TYPE = object_type;
}
public String getOWNER() {
    return OWNER;
}
public void setOWNER(String owner) {
    OWNER = owner;
}
public String getSECONDARY() {
    return SECONDARY;
}
public void setSECONDARY(String secondary) {
    SECONDARY = secondary;
}
public String getSTATUS() {
    return STATUS;
}
public void setSTATUS(String status) {
    STATUS = status;
}
public String getTEMPORARY() {
    return TEMPORARY;
}
public void setTEMPORARY(String temporary) {
    TEMPORARY = temporary;
}
    public String toString() {
        StringBuffer sb = new StringBuffer(512);
        sb.append("TestVO[");
        sb.append("\n    object_id=").append(this.object_id);
        sb.append("\n    object_name=").append(this.object_name);
        sb.append("\n    OBJECT_TYPE=").append(this.OBJECT_TYPE);
        sb.append("\n    OWNER=").append(this.OWNER);
        sb.append("\n    STATUS=").append(this.STATUS);
        sb.append("\n    TEMPORARY=").append(this.TEMPORARY);
        sb.append("\n    SECONDARY=").append(this.SECONDARY);
        sb.append("\n    EDITION_NAME=").append(this.EDITION_NAME);
        sb.append("\n    DATA_OBJECT_ID=").append(this.DATA_OBJECT_ID);
        sb.append("\n    CREATED=").append(this.CREATED);
        sb.append("\n    LAST_DDL_TIME=").append(this.LAST_DDL_TIME);
        sb.append("\n]");
        return sb.toString();
    }
}