如何使用来自基于 Java 的 Web 服务的日期输入调用 Oracle 过程？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1958735/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to call an Oracle procedure with Date input from Java-based webservice?
提问by Alfabravo
Right now I have an Oracle stored procedure with IN and OUT params. The IN params are simple types and collections (customType as table of customObject). The OUT params are a REFCURSOR and some varchars. The thing is: when I send some data-formatted strings to Date IN params, it throws at me this:
现在我有一个带有 IN 和 OUT 参数的 Oracle 存储过程。IN 参数是简单的类型和集合（customType 作为 customObject 的表）。OUT 参数是一个 REFCURSOR 和一些 varchars。问题是：当我将一些数据格式的字符串发送到 Date IN 参数时，它会向我抛出以下内容：
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)
...
The question is: How should I send the Date IN params to Oracle?
问题是：我应该如何将 Date IN 参数发送到 Oracle？
Context
语境
The objects, collections and the procedure itself are as follows:
对象、集合和过程本身如下：
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
...
The Java class i've created to support the webservice (through Axis) basically does the following:
我创建的用于支持网络服务（通过 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;
}
}
And the classes supporting the oracle objects, the request and the response also exist. Thanks in advance!
并且支持oracle对象、请求和响应的类也存在。提前致谢！
Edit 28/12/2009: As suggested, I've done this in the WS class:
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);
(...)
This way to set the dates is replicated with any other Date param. The ConceptosAdicionales class now has a java.sql.Date attribute instead of a String. Important to distinguish between java.util.Date and the sql one. I used this referenceto make the conversion in the way shown here. Hope this helps people over here. Thank you all
这种设置日期的方式与任何其他日期参数一起复制。ConceptosAdicionales 类现在有一个 java. sql.Date 属性而不是字符串。重要的是要区分 java.util.Date 和 sql 之一。我使用此参考以此处显示的方式进行转换。希望这对这里的人有所帮助。谢谢你们
回答by Thorbj?rn Ravn Andersen
Consider refactoring your code to handle dates as java.sql.Date objects instead of Strings. This will allow you to call setDate(....) instead of setString(...), and make your code cleaner.
考虑重构您的代码以将日期作为 java.sql.Date 对象而不是字符串来处理。这将允许您调用 setDate(....) 而不是 setString(...)，并使您的代码更清晰。
回答by duffymo
Have the web service bind the string to a date before passing it back. You want to do this, because it'll validate proper format and type. Binding and validating is necessary to avoid SQL injection as well.
在将字符串传回之前，让 Web 服务将字符串绑定到一个日期。您想这样做，因为它会验证正确的格式和类型。绑定和验证对于避免 SQL 注入也是必要的。
