java.sql.SQLException：使用 IN 运算符时数字溢出
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16569578/
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
java.sql.SQLException: Numeric Overflow while using IN operator
提问by Long Thai
I implemented a Java application which queries a database based on given set of ids using the query:
我实现了一个 Java 应用程序，它使用以下查询根据给定的 id 集查询数据库：
select * from STUDENT where ID in (?)
The set of ids will be used to replace ?. However, occasionally, I receive an exception:
这组 id 将用于替换?. 但是，偶尔，我会收到一个异常：
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)
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.
经过一些测试，我意识到如果我将 id 列表分成许多较小的子列表，异常就会停止发生。出于某种原因，jdbc 不喜欢将太多值放入IN (?). 我想知道是否有人以前见过这个问题并对此有解释？由于此问题从未发生在生产环境中，而仅发生在本地环境中（资源较少），因此我怀疑它与服务器资源有关。
Thanks
谢谢
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
}
采纳答案by Long Thai
The issue was caused by conflict of ojdbc driver between GlassFish and application. In order to fix it, I need to:
该问题是由 GlassFish 和应用程序之间的 ojdbc 驱动程序冲突引起的。为了修复它，我需要：
- Update application's pom.xml (as I'm using maven) to use a latest ojdbc 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)
- 更新应用程序的 pom.xml（因为我使用的是 maven）以使用最新的 ojdbc，即 ojdbc6-11.2.0.3
- 将 ojdbc6-11.2.0.3 添加到 GlassFish 库
- 如有必要，从 glassfish 中部署的应用程序库中手动删除 ojdbc jar（显然这不会被取消部署清除）
回答by LexLythius
Did you check MySQL and/or JDBC max packet sizesetting? That usually bites you with large IN (...) lists.
您是否检查了 MySQL 和/或 JDBC最大数据包大小设置？这通常会用大量的 IN (...) 列表咬你。
回答by Paulo Mello
This occurs with the ID property or some other integer property type of your entity look your stacktrace>
这发生在您的实体的 ID 属性或其他一些整数属性类型中，查看您的堆栈跟踪>
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)
Any value returned from the query does not fit on this property! Change the properties Integer and try to work with the next integer types (long, Long BigInteger) in all fields of Integer type in your entity.
从查询返回的任何值都不适合此属性！更改属性 Integer 并尝试使用实体中所有 Integer 类型字段中的下一个整数类型（long、Long BigInteger）。
