javasql-serverapache-commons-dbutils

Apache DBUtils queryRunner - where in clause


I am trying to work on retrieving multiple records from a list using queryrunner interface on SQL Server database. Seems that the interface throws error while the normal method works.

private void testMethod() throws Exception
{
    List<Object> strList = new ArrayList<Object>();
    strList.add("015190000000vojAAA");
    strList.add("015190000000vokAAA");
    strList.add("015190000000volAAA");
    try (DBUtil dbUtil = new DBUtil())
    {
        String theQuery = "SELECT ID from Document where Id IN (?, ?, ?)";
        for (Object[] row : dbUtil.queryRunner.select(theQuery, new ArrayListHandler(),
                strList.toArray(new Object[strList.size()])))
        {
            logger.info(row[0]);
        }


        /*
         * Normal method
         * PreparedStatement prepareStatement = dbUtil.connection.prepareStatement(theQuery);
        prepareStatement.setString(1, (String)strList.get(0));
        prepareStatement.setString(2, (String)strList.get(1));
        prepareStatement.setString(3, (String)strList.get(2));
        ResultSetMetaData metaData = prepareStatement.getMetaData();
        logger.info(metaData.getColumnCount());
        ResultSet rs = prepareStatement.executeQuery();
        while(rs.next())
        {
            logger.info(rs.getString(1));
        }
        prepareStatement.close();
        */
    }

It seems to be working on a normal method where I was able to set the parameters separately. However, I would like to use the queryrunner interface since my project runs all the queries using this interface. Am I doing anything wrong here?

Further debugging the dbutils lead to the point that the error message Exception in thread "main" java.sql.SQLException:

com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','. Query: SELECT ID from Document where Id IN (?, ?, ?) Parameters: [015190000000vojAAA, 015190000000vokAAA, 015190000000volAAA]
    at org.apache.commons.dbutils.AbstractQueryRunner.rethrow(AbstractQueryRunner.java:392)
    at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:351)
    at org.apache.commons.dbutils.QueryRunner.query(QueryRunner.java:212)
    at com.commvault.db.dao.DBUtil.select(DBUtil.java:160)
    at com.commvault.test.Test.testMethod(Test.java:264)
    at com.commvault.test.Test.main(Test.java:80)

and the code in question with Dbutils is Line 225 below.

/*     */   public void fillStatement(PreparedStatement stmt, Object[] params)
/*     */     throws SQLException
/*     */   {
/* 223 */     ParameterMetaData pmd = null;
/* 224 */     if (!(this.pmdKnownBroken)) {
/* 225 */       pmd = stmt.getParameterMetaData();
/* 226 */       int stmtCount = pmd.getParameterCount();
/* 227 */       int paramsCount = (params == null) ? 0 : params.length;
/*     */ 
/* 229 */       if (stmtCount != paramsCount) {
/* 230 */         throw new SQLException("Wrong number of parameters: expected " + stmtCount + ", was given " + paramsCount);
/*     */
/*     */       }
/*     */ 
/*     */     }
/*     */

The same line works normally when I run it manually.

EDIT: DBUtil is a custom wrapper class that contains other utility methods and encompasses QueryRunner as the private variable.


Solution

  • The Apache DbUtils are ok but the Microsoft JDBC driver has a bug (at least in version 4):

    When your create a PreparedStatement for a SQL command containing parameters in a IN clause

    SELECT ID from Document where Id IN (?, ?, ?)
    

    and call PreparedStatement.getParameterMetaData() the driver raises an exception complaining about a syntax error. Now unfortunately DbUtils calls this method before it sets the parameters.

    This has been observed before.