如何使用基于Java的webservice的日期输入调用Oracle过程？
问题描述：
现在我有一个带有IN和OUT参数的Oracle存储过程。 IN params是简单的类型和集合（customType作为customObject的表）。 OUT参数是REFCURSOR和一些变量。问题是：当我发送一些数据格式的字符串到日期参数，可以抛出它在我这个：如何使用基于Java的webservice的日期输入调用Oracle过程？
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)
...
的问题是：我应该如何发送日期PARAMS到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服务（通过轴）的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;
}
}
而支持或acle对象，请求和响应也存在。 在此先感谢！
编辑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);
(...)
这样设定的日期被复制与其他任何Date参数。 ConceptosAdicionales类现在有一个java。 sql .Date属性代替String。重要的是区分java.util.Date和sql。我使用this reference以此处显示的方式进行转换。希望这有助于人们在这里。谢谢大家
答
考虑重构代码以将日期处理为java.sql.Date对象而不是字符串。这将允许您调用setDate（....）而不是setString（...），并使代码更清晰。
答
让Web服务在将字符串传递回去之前将该字符串绑定到日期。你想这样做，因为它会验证正确的格式和类型。绑定和验证对于避免SQL注入也是必需的。
我已经这样做了。当从Eclipse的类中重新创建Web服务时，它会提高警告符合标准，为了测试我忽略了这些标准......并且看起来像是有效的。在大约五六个小时内，我可能会把这个线程解决 – Alfabravo 2009-12-28 07:12:14