Java 传对象到Oracle存储过程

末蓝、 2022-06-17 07:16 174阅读 0赞

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

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();
        }
    }