问题描述
我实现了一个Java应用程序,该应用程序使用查询根据给定的ID集查询数据库:
I implemented a Java application which queries a database based on given set of ids using the query:
select * from STUDENT where ID in (?)
一组ID将用于替换?
.但是,偶尔我会收到一个例外:
The set of ids will be used to replace ?
. However, occasionally, I receive an exception:
Caused by: java.sql.SQLException: Numeric Overflow
at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:445)
at oracle.jdbc.driver.NumberCommonAccessor.throwOverflow(NumberCommonAccessor.java:4319)
at oracle.jdbc.driver.NumberCommonAccessor.getInt(NumberCommonAccessor.java:187)
at oracle.jdbc.driver.OracleResultSetImpl.getInt(OracleResultSetImpl.java:712)
at oracle.jdbc.driver.OracleResultSet.getInt(OracleResultSet.java:434)
经过一些测试,我意识到,如果将ID列表划分为多个较小的子列表,则异常停止发生.由于某种原因,jdbc不喜欢将太多的值放入IN (?)
中.我想知道是否有人曾经见过这个问题并对此有解释?由于此问题永远不会在生产环境中发生,而只会在本地环境(资源较少的本地环境)中发生,因此我怀疑这与服务器的资源有关.
After some testing, I realized that if I divide the list of ids into many sub-lists with smaller size, the exception stops happening. For some reason, jdbc doesn't like putting too many values into IN (?)
. I wonder if anyone has seen this issue before and has an explanation for it? As this issue never happens on production environment but only on a local one (which has less powerful resources), I suspect it has something to do with server's resources.
谢谢
更新:我正在使用的源代码是:
Update: the source code that I'm using is:
// create a query
private String getQueryString(int numOfParams) {
StringBuilder out = new StringBuilder();
out.append("select * from STUDENT where ID in (");
for (int i = 0; i < numOfParams; i++) {
if (i == numOfParams - 1) {
out.append("?");
} else {
out.append("?, ");
}
}
out.append(")");
}
// set parameters
private void setParams(PreparedStatement ps, Set<String> params) {
int index = 1;
for (String param: params) {
ps.setString(index++, param);
}
}
public void queryStudent(Connection conn, Set<String> ids) throws Exception {
String query = this.getQueryString(ids.size());
PreparedStatement ps = conn.prepareStatement(query);
this.setParams(ps, ids);
ps.executeQuery();
// do some operations with the result
}
推荐答案
该问题是由GlassFish和应用程序之间的ojdbc驱动程序冲突引起的.为了修复它,我需要:
The issue was caused by conflict of ojdbc driver between GlassFish and application. In order to fix it, I need to:
- 更新应用程序的pom.xml(因为我正在使用maven)以使用最新版本ojdbc是ojdbc6-11.2.0.3
- 将ojdbc6-11.2.0.3添加到GlassFish lib
- 如有必要,请从glassfish中的已部署应用程序的库中手动删除ojdbc jar(显然,取消部署并不会清除该问题)
- Update application's pom.xml (as I'm using maven) to use a latestojdbc which is ojdbc6-11.2.0.3
- Add ojdbc6-11.2.0.3 to GlassFish lib
- If necessary, manually remove the ojdbc jar from deployed applications' lib in glassfish (apparently this is not cleared by undeploy)
这篇关于java.sql.SQLException:使用IN运算符时的数字溢出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!