java中ibatis和oracle中传递和返回自定义数组对象
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/136034/
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
Pass and return custom array object in ibatis and oracle in java
提问by Justin
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.
我已经环顾四周寻找一个很好的例子，但我还没有遇到一个。我想使用 IBATIS 框架将自定义字符串数组从 java 传递到 oracle 并返回。有没有人有一个很好的例子链接？我正在从 IBATIS 调用存储过程。
Thanks
谢谢
回答by bsanders
You've got to start with a custom instance of TypeHandler. We'd prefer to implement the simpler TypeHandlerCallback, but in this scenario we need access to the underlying Connection.
您必须从 的自定义实例开始TypeHandler。我们更愿意实现更简单的TypeHandlerCallback，但在这种情况下，我们需要访问底层的Connection.
public class ArrayTypeHandler implements TypeHandler {
public void setParameter(PreparedStatement ps, int i, Object param, String jdbcType)
throws SQLException {
if (param == null) {
ps.setNull(i, Types.ARRAY);
} else {
Connection conn = ps.getConnection();
Array loc = conn.createArrayOf("myArrayType", (Object[]) param);
ps.setArray(i, loc);
}
}
public Object getResult(CallableStatement statement, int i)
throws SQLException {
return statement.getArray(i).getArray();
}
...
}
Then, to wire it up in the iBATIS config:
然后，在 iBATIS 配置中连接它：
<?xml version="1.0"?>
<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd">
<sqlMap namespace="arrayTest">
<parameterMap id="storedprocParams" class="map">
<parameter property="result" mode="OUT" jdbcType="ARRAY" typeHandler="ArrayTypeHandler"/>
<parameter property="argument" mode="IN" jdbcType="ARRAY" typeHandler="ArrayTypeHandler"/>
</parameterMap>
<procedure id="storedproc" parameterMap="arrayTest.storedprocParams">
{? = call My_Array_Function( ? )}
</procedure>
</sqlMap>
Hope this helps!
希望这可以帮助！
回答by Justin
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).
bsanders 给了我一个很好的起点——这是我必须做的才能让它在 RAD 环境（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);
}
}
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.
注意我必须获得的 nativeConnection，我必须制作的描述符，等等。但是，虽然我可以将事物作为字符串数组传递到数据库中，但我无法弄清楚为什么我没有得到任何回报。我的 OUT 参数（getResult(CallableStatement statment, int i) 抛出空指针异常，即使我在数据库的 plsql 中设置了 out 参数。
--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>
Any ideas?
有任何想法吗？
回答by bsanders
Try using statement.getObject(i)and then casting to an array.
尝试使用statement.getObject(i)然后转换为数组。
回答by tomasb
Well, guys in company found out the solution: you need to have implemented getResult method(s) in your typeHandler and provided additional attribute jdbcTypeName=ORACLE_REAL_ARRAY_TYPE in your mapper
好吧，公司里的人找到了解决方案：你需要在你的 typeHandler 中实现 getResult 方法并在你的映射器中提供额外的属性 jdbcTypeName=ORACLE_REAL_ARRAY_TYPE
