java.sql.PreparedStatement.setArray():不支持的交叉转换 [英] java.sql.PreparedStatement.setArray() : Unsupported cross conversion
问题描述
这是一个简化的例子,prepareStatementSetter的其他部分与setString和setInt等成功运行。
public List< EmployeeMonitoringResultTo> searchEmployeesForEmployerInRange(最终GetEmployeesForEmployerInRangeCriteria条件){
final StringBuilder sql =
new StringBuilder(select * from CODES c)
.append(其中c.TYPE in(?)); // TYPE has DATA_TYPE 12,TYPE_NAME VARCHAR
return jdbcTemplate.query(
sql.toString(),
new PreparedStatementSetter(){
@Override
public void setValues(PreparedStatement ps)throws SQLException {
ps.setArray(1,searchTypes());
}
},
new SpecificRowMapper&SpecType>()
);
}
private Array searchTypes(){
final Collection< String> collection = fetchStrings();
数组resultArray = null ;;
try {
resultArray = jdbcTemplate.getDataSource()。getConnection()。createArrayOf(VARCHAR,collection.toArray());
} catch(SQLException e){
log.error(创建的java.sql.Array的问题,e);
throw e;
}
return resultArray;
}
导致:com.ibm.db2。 jcc.am.SqlSyntaxErrorException:
[jcc] [10349] [11717] [4.13.80]不支持的源
类型对象的交叉转换; ERRORCODE = -4461,SQLSTATE = 42815 at
com.ibm.db2.jcc.am.id.a(id.java:677)at
com.ibm.db2.jcc.am.id. a(id.java:60)at
com.ibm.db2.jcc.am.id.a(id.java:103)at
com.ibm.db2.jcc.am.ic。 a(ic.java:289)at
com.ibm.db2.jcc.am.ic.a(ic.java:191)at
com.ibm.db2.jcc.am.kc. a(kc.java:1943)at
com.ibm.db2.jcc.am.go.a(go.java:2289)at
com.ibm.db2.jcc.am.go. setArray(go.java:2254)at
com.jolbox.bonecp.PreparedStatementHandle.setArray(PreparedStatementHandle.java:261)
at
net.sf.log4jdbc.PreparedStatementSpy.setArray(PreparedStatementSpy。 java:349)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun。 reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
在java.lang.reflect.Method.invoke(Method.java:597)at
org.hibernate.engine.jdbc.intern al.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122)
... 93更多
toArray方法返回一个对象数组。你可以尝试使用一串数组:
collection.toArray(new String [0]);
您可能无法使用带IN操作符的SQL数组,因此您必须生成SQL而不是使用准备好的语句。 (或至少生成正确数量的问号,然后将参数设置为循环,如评论中建议的那样。)
您还缺少其中
关键字。
I'm getting the exception shown below, when I run the following query with jdbc. This is a simplified example, the other parts of the preparedStatementSetter with setString and setInt, etc. ran successfully.
public List<EmployeeMonitoringResultTo> searchEmployeesForEmployerInRange(final GetEmployeesForEmployerInRangeCriteria criteria) {
final StringBuilder sql =
new StringBuilder( "select * from CODES c ")
.append(" where c.TYPE in ( ? ) "); // TYPE has DATA_TYPE 12, TYPE_NAME VARCHAR
return jdbcTemplate.query(
sql.toString(),
new PreparedStatementSetter() {
@Override
public void setValues(PreparedStatement ps) throws SQLException {
ps.setArray(1, searchTypes());
}
},
new SpecificRowMapper<SpecType>()
);
}
private Array searchTypes() {
final Collection<String> collection = fetchStrings();
Array resultArray = null;;
try {
resultArray = jdbcTemplate.getDataSource().getConnection().createArrayOf("VARCHAR", collection.toArray());
} catch (SQLException e) {
log.error("Problem with created java.sql.Array", e);
throw e;
}
return resultArray;
}
Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][10349][11717][4.13.80] Unsupported cross conversion from source type Object;. ERRORCODE=-4461, SQLSTATE=42815 at com.ibm.db2.jcc.am.id.a(id.java:677) at com.ibm.db2.jcc.am.id.a(id.java:60) at com.ibm.db2.jcc.am.id.a(id.java:103) at com.ibm.db2.jcc.am.ic.a(ic.java:289) at com.ibm.db2.jcc.am.ic.a(ic.java:191) at com.ibm.db2.jcc.am.kc.a(kc.java:1943) at com.ibm.db2.jcc.am.go.a(go.java:2289) at com.ibm.db2.jcc.am.go.setArray(go.java:2254) at com.jolbox.bonecp.PreparedStatementHandle.setArray(PreparedStatementHandle.java:261) at net.sf.log4jdbc.PreparedStatementSpy.setArray(PreparedStatementSpy.java:349) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at java.lang.reflect.Method.invoke(Method.java:597) at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) ... 93 more
The toArray method returns an array of objects. You could try using an array of strings:
collection.toArray(new String[0]);
You probably can't use an SQL array with an IN operator anyway, so you'll have to generate SQL instead of using a prepared statement. (Or at least generate the correct number of question marks and then set the parameters in a loop, like suggested in the comments.)
You are also missing the where
keyword.
这篇关于java.sql.PreparedStatement.setArray():不支持的交叉转换的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!