java sql in<while,java.sql.SQLException：使用IN运算符时的数字溢出

I implemented a Java application which queries a database based on given set of ids using the query:

select * from STUDENT where ID in (?)

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)

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.

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 params) {

int index = 1;

for (String param: params) {

ps.setString(index++, param);

}

}

public void queryStudent(Connection conn, Set 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

}

解决方案

The issue was caused by conflict of ojdbc driver between GlassFish and application. In order to fix it, I need to:

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)