在ibatis中传递和返回自定义数组对象,在java中传递和返回oracle [英] Pass and return custom array object in ibatis and oracle in java
问题描述
我环顾四周寻找一个很好的例子,但我还没有遇到过。我想使用IBATIS框架将自定义字符串数组从java传递到oracle并返回。有没有人有一个很好的链接到一个例子?我正在从IBATIS调用存储过程。
I've looked around for a good example of this, but I haven't run into one yet. I want to pass a custom string array from java to oracle and back, using the IBATIS framework. Does anyone have a good link to an example? I'm calling stored procs from IBATIS.
谢谢
推荐答案
bsanders给了我一个很好的起点 - 这就是我必须做的才能让它在RAD环境中工作(websphere 6.2)。
bsanders gave me a good starting point - here's what I had to do to make it work within the RAD environment (websphere 6.2).
public Object getResult(CallableStatement statement, int i) throws SQLException {
return statement.getArray(i).getArray(); //getting null pointer exception here
}
public void setParameter(PreparedStatement ps, int i, Object param, String jdbcType) throws SQLException {
if (param == null) {
ps.setNull(i, Types.ARRAY);
} else {
String[] a = (String[]) param;
//ARRAY aOracle = ARRAY.toARRAY(a, (OracleConnection)ps.getConnection());
//com.ibm.ws.rsadapter.jdbc.WSJdbcConnection
w = (com.ibm.ws.rsadapter.jdbc.WSJdbcConnection)ps.getConnection());
//com.ibm.ws.rsadapter.jdbc.WSJdbcObject x;
Connection nativeConnection = Connection)WSJdbcUtil.getNativeConnection((WSJdbcConnection)ps.getConnection());
ArrayDescriptor descriptor = ArrayDescriptor.createDescriptor("F2_LIST", nativeConnection);
ARRAY dataArray = new ARRAY(descriptor, nativeConnection, a);
ps.setArray(i, dataArray);
}
}
注意我必须得到的nativeConnection,描述符I不得不做,等等。但是,虽然我可以将数据作为一个字符串数组传递到数据库中,但我还是无法弄清楚为什么我没有得到任何回报。我的OUT参数(getResult(CallableStatement statment,int i)抛出空指针异常,即使我在数据库中的plsql中设置out参数。
Notice the nativeConnection I had to get, the descriptor I had to make, and so on. However, while I can pass things into the database as an array of Strings, I haven't been able to figure out why I'm not getting anything back. My OUT parameter (the getResult(CallableStatement statment, int i) is throwing a null pointer exception, even though I'm setting the out parameter in the plsql in the database.
--stored procedure to take a | delimited ids
PROCEDURE array_test (argument IN f2_list, result OUT f2_list)
AS
l_procname_v VARCHAR2 (50) := 'array_test';
l_param_list VARCHAR2 (2000)
:= l_procname_v || ' param_values: p_string: ';
p_status_n NUMBER;
p_message_v VARCHAR2 (2000);
ret_list f2_list := new f2_list();
l_count_v varchar2(200);
BEGIN
l_count_v := argument.COUNT;
for x in 1..argument.count
LOOP
pkg_az_common_util.az_debug (package_nm,
l_procname_v,
pkg_az_data_type_def.debug_num,
argument(x)
);
end loop;
pkg_az_common_util.az_debug (package_nm,
l_procname_v,
pkg_az_data_type_def.debug_num,
l_count_v
);
ret_list.extend();
ret_list(1) := 'W';
ret_list.extend();
ret_list(2) := 'X';
ret_list.extend();
ret_list(3) := 'Y';
ret_list.extend();
ret_list(4) := 'Z';
result := ret_list;
EXCEPTION
WHEN OTHERS
THEN
p_status_n := pkg_az_common_util.get_error_code;
p_message_v :=
TO_CHAR (p_status_n)
|| '|'
|| 'Oracle Internal Exception('
|| l_procname_v
|| ')'
|| '|'
|| TO_CHAR (SQLCODE)
|| '|'
|| SQLERRM
|| l_param_list;
standard_pkg.log_error (package_nm,
l_procname_v,
SQLCODE,
p_message_v
);
IF p_status_n = 1
THEN
RAISE;
END IF;
END array_test;
以下是我访问它的方式:
Here is how I'm accessing it:
Map queryParamsTest = new HashMap();
String[] testArray = {"A", "B", "C"};
queryParamsTest.put("argument", testArray);
DaoUtils.executeQuery(super.getSqlMapClientTemplate(),
"arrayTest", queryParamsTest, queryParamsTest
.toString()); //just executes query
String[] resultArray = (String[])queryParamsTest.get("result");
for(int x = 0; x< resultArray.length; x++)
{
System.out.println("Result: " + resultArray[x]);
}
<parameterMap id="storedprocParams" class="map">
<parameter property="argument" mode="IN" jdbcType="ARRAY" typeHandler="ArrayTypeHandler"/>
<parameter property="result" mode="OUT" jdbcType="ARRAY" typeHandler="ArrayTypeHandler"/>
</parameterMap>
<procedure id="arrayTest" parameterMap="storedprocParams">
{call pkg_az_basic_dev.array_test(?, ? )}
</procedure>
任何想法?
这篇关于在ibatis中传递和返回自定义数组对象,在java中传递和返回oracle的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!