java - 如何从基于 Java 的 Web 服务调用具有日期输入的 Oracle 过程？

Question

现在我有一个带有 IN 和 OUT 参数的 Oracle 存储过程。 IN 参数是简单类型和集合(customType 作为 customObject 的表)。 OUT 参数是 REFCURSOR 和一些 varchar。问题是:当我将一些数据格式的字符串发送到 Date IN params 时，它会向我抛出以下错误:

java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss[.fffffffff]
        at java.sql.Timestamp.valueOf(Timestamp.java:185)
        at oracle.sql.DATE.toBytes(DATE.java:720)
        at oracle.sql.DATE.<init>(DATE.java:222)
        at oracle.jdbc.oracore.OracleTypeDATE.toDatum(OracleTypeDATE.java:66)
        at oracle.sql.StructDescriptor.toOracleArray(StructDescriptor.java:717)
        at oracle.sql.StructDescriptor.toArray(StructDescriptor.java:1375)
        at oracle.sql.STRUCT.<init>(STRUCT.java:159)
        at oracle.sql.OracleSQLOutput.getSTRUCT(OracleSQLOutput.java:114)
        at oracle.sql.STRUCT.toSTRUCT(STRUCT.java:524)
        at oracle.jdbc.oracore.OracleTypeADT.toDatum(OracleTypeADT.java:227)
        at oracle.jdbc.oracore.OracleTypeADT.toDatumArray(OracleTypeADT.java:274)
        at oracle.jdbc.oracore.OracleTypeUPT.toDatumArray(OracleTypeUPT.java:115)
        at oracle.sql.ArrayDescriptor.toOracleArray(ArrayDescriptor.java:1314)
        at oracle.sql.ARRAY.<init>(ARRAY.java:152)
        ...

问题是:我应该如何将 Date IN 参数发送到 Oracle？

上下文

对象、集合和过程本身如下:

create or replace type fd_customTypeObj1 is table of fd_customType1;

create or replace type fd_customType1 is object (
valorCuota_Inic               number,
fecpagoCuota_Inic             date
);

create or replace type fd_customTypeObj2 is table of fd_customType2;

create or replace type fd_customType2 is object (
cod_tpOper                    varchar2(4),
valorCpto                     number,
fecpagoCpto                   date
);

procedure complex_procedure
 ( p_Trans                        varchar2,
   p_Canal                        varchar2,
   p_Ofic                         integer,
   p_TpId                         varchar2,
   ...
   p_cod_proy                     number,
   p_vlrTotal                     number,
   p_vlrCuotaInic                 number,
   p_fecCuotaInic                 date,
   p_vlrCuotaInicFija             number,
   p_fecCuotaInicFija             date,
   p_periodicidad                 varchar2,
   p_ColcuotasIrreg               fd_customTypeObj1,
   p_ColOtrosCptos                fd_customTypeObj2,
   p_listadoPlanPagos       out   rc_refcursor_type,
   p_Cod_Rspta              out   varchar2,
   p_Rspta                  out   varchar2,
   p_Fecha_Oper             out   varchar2,
   p_Hora_Oper              out   varchar2
  )
  is
  ...

我创建的用于支持 Web 服务(通过 Axis)的 Java 类基本上执行以下操作:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Types;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.driver.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

import com.osmosyscol.commons.log.SimpleLogger;

public class WSStackOverflowRules {

    // ---------------------------------------------

    public CustomResponseClass liquidar(CustomRequestClass solicitudLiquidar) {

        CustomResponseClass respuesta = new CustomResponseClass();

        try {

            String procedimiento = "call PACKAGE1.complex_procedure(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

            Connection cn = null;

            try {
                DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());

                cn = DriverManager.getConnection( "jdbc:oracle:thin:@<that_ip>:<that_port>:<that_SID>", "<that_user>", "<that_pwd>" );
                OracleCallableStatement callStatement = null;

                ConceptosAdicionales conceptosObject1 = new ConceptosAdicionales();
                conceptosObject1.setCod_tpOper("A1");
                conceptosObject1.setValorCpto(1000); 
                conceptosObject1.setFecpagoCpto("2009-12-29");//TESTING DIRECTLY!!!

                ConceptosAdicionales conceptosObject2 = new ConceptosAdicionales();
                conceptosObject2.setCod_tpOper("B2"); 
                conceptosObject2.setValorCpto(1500); 
                conceptosObject2.setFecpagoCpto("2010-02-27");//TESTING DIRECTLY!!!

                ConceptosAdicionales[] conceptosArray = {conceptosObject1,conceptosObject2};
                CuotasIrregulares[] irregularesArray = {};

                ArrayDescriptor conceptosArrayDesc = ArrayDescriptor.createDescriptor("customTypeObj1", cn);
                ARRAY conceptosArrayObject = new ARRAY(conceptosArrayDesc, cn, conceptosArray);

                ArrayDescriptor irregularesArrayDesc = ArrayDescriptor.createDescriptor("customTypeObj2", cn);
                ARRAY irregularesArrayObject = new ARRAY(irregularesArrayDesc, cn, irregularesArray);


                callStatement = (OracleCallableStatement)cn.prepareCall(procedimiento);

                callStatement.setString(1, solicitudLiquidar.getCod_trans());

                callStatement.setString(2, solicitudLiquidar.getCanal());

                callStatement.setInt(3, solicitudLiquidar.getOficina());

...

                callStatement.setLong(10, solicitudLiquidar.getValor_total());

                callStatement.setLong(11, solicitudLiquidar.getValor_cuotainicial());

                callStatement.setString(12, "30/08/2010");  //TESTING DIRECTLY!!!

                callStatement.setLong(13, solicitudLiquidar.getValor_cuotainicial_fija());

                callStatement.setString(14, "26/02/2009");//TESTING DIRECTLY!!!

...

                ((OracleCallableStatement)callStatement).setArray(17, irregularesArrayObject);
                ((OracleCallableStatement)callStatement).setArray(18, conceptosArrayObject);

                callStatement.registerOutParameter(19, OracleTypes.CURSOR);
                callStatement.registerOutParameter(20, Types.VARCHAR);
                callStatement.registerOutParameter(21, Types.VARCHAR);
                callStatement.registerOutParameter(22, Types.VARCHAR);
                callStatement.registerOutParameter(23, Types.VARCHAR);

                callStatement.executeUpdate();

                ResultSet rs = (ResultSet)callStatement.getObject(19);
                while(rs.next()) {
                    //stuff
                }

                respuesta.setP_Cod_Rspta( callStatement.getString(20) );
                respuesta.setP_Rspta( callStatement.getString(21) );
                respuesta.setP_fecRspta( callStatement.getString(22) );
                respuesta.setP_hora_Rspta( callStatement.getString(23) );

                System.out.println("todo bien, todo bien");
            } catch (Exception e) {
                System.out.println(e.getMessage());
                e.printStackTrace();
            } finally {
                cn.close();
            }

        } catch (Exception e) {
            System.out.println("Error calling web service (WSStackOverflowRules.liquidar)", e);
        }

        return respuesta;
    }
}

支持oracle对象、请求和响应的类也存在。提前致谢!

编辑 28/12/2009:按照建议，我在 WS 类中完成了此操作:

(...)
cn = DriverManager.getConnection( <that_URL>, <that_user>, <that_pwd> );
OracleCallableStatement callStatement = null;
DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
Date setDate = new Date(0);
long dateTime = 0;
java.sql.Date sqlDate = new java.sql.Date(0);

ConceptosAdicionales conceptosObject1 = new ConceptosAdicionales();
conceptosObject1.setCod_tpOper("A1");
conceptosObject1.setValorCpto(1000); 
setDate = (Date) df.parse("29/12/2009");
dateTime = setDate.getTime(  );
sqlDate = new java.sql.Date( dateTime );
conceptosObject1.setFecpagoCpto(sqlDate);
(...)

这种设置日期的方法可以与任何其他日期参数复制。 ConceptosAdicionales 类现在具有 java.sql.Date 属性，而不是字符串。区分 java.util.Date 和 sql 很重​​要。我用过this reference按照此处所示的方式进行转换。希望这对这里的人有帮助。谢谢大家

Answer 1

考虑重构代码以将日期处理为 java.sql.Date 对象而不是字符串。这将允许您调用 setDate(....) 而不是 setString(...)，并使您的代码更简洁。

http://java.sun.com/javase/6/docs/api/java/sql/PreparedStatement.html#setDate%28int,%20java.sql.Date%29