IN子句Java Microsoft JDBC中的预准备语句 [英] Prepared Statements IN Clause Java Microsoft JDBC

问题描述

我在这里和其他地方都看到了一些例子，说这应该可行.
在IN CLAUSE中使用"1"参数可以成功:

I've seen examples here and elsewhere that say this should work.
With '1' parameter in the IN CLAUSE I have success:

SELECT AVG( ( High_Temperature + Low_Temperature ) / 2.0 ) 
  FROM obs_masterAll 
  WHERE Observation_Valid_Time = ? AND 
        Location_ID IN ( ? );

Microsoft JDBC Driver 4.2 for SQL Server  4.2.6420.100
Parameter Count = 2
2010, 36.5

但是，在IN CLAUSE中有多个参数，我得到了:

But, with more than one parameter in the IN CLAUSE, I get this:

SELECT AVG( ( High_Temperature + Low_Temperature ) / 2.0 ) 
  FROM obs_masterAll 
  WHERE Observation_Valid_Time = ? 
  AND Location_ID IN ( ?, ? );      <--- PROBLEM HERE

Microsoft JDBC Driver 4.2 for SQL Server  4.2.6420.100
Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near ','.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191)
    at com.microsoft.sqlserver.jdbc.SQLServerParameterMetaData.<init>(SQLServerParameterMetaData.java:423)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.getParameterMetaData(SQLServerPreparedStatement.java:1659)
    at CreateAMultiStationDailyLoadCtrAvgHistogram.main(CreateAMultiStationDailyLoadCtrAvgHistogram.java:68)
Java Result: 1

这是我的代码:

  prepStmt = buildPreparedStatement( conn.getConnectionObject() );  //function below
  conn.printDriverInfo();
  sqlDate = new java.sql.Date( beginDate.getTimeInMillis() );
  ParameterMetaData pmData = prepStmt.getParameterMetaData();
  System.out.println( "Parameter Count = " + pmData.getParameterCount() );
  prepStmt.setDate( 1, sqlDate );

  for( i = 0; i < locID.size(); i++ )
  {
    prepStmt.setString( ( i + 2 ), locID.get(i) );
  }

  rslt = prepStmt.executeQuery();

  private PreparedStatement buildPreparedStatement(java.sql.Connection con)
  throws SQLException
  {
    int i;
    StringBuilder sb = new StringBuilder();
    sb.append("SELECT AVG( ( High_Temperature + Low_Temperature ) / 2.0 ) ");
    sb.append("FROM obs_masterAll ");
    sb.append("WHERE Observation_Valid_Time = ? AND " );
    sb.append("Location_ID IN ( " );

    for( i = 0; i < ( locID.size() - 1 ); i++ )
    {
      sb.append( "?" ).append(", ");
    }

    sb.append( "?" ).append(" );");
    System.out.println( sb.toString() );
    return( con.prepareStatement( sb.toString() ) );
  }

问题是我的代码还是JDBC驱动程序?

Is the problem my code, or the JDBC Driver?

推荐答案

上面关于JDBC驱动程序的响应使我很接近.我使用的是非常旧的Microsoft JDBC驱动程序，因此升级肯定会有所帮助.我现在使用的是Microsoft JDBC驱动程序的6.0版社区技术预览版(版本6.0.6629.101)，而不是上面建议的版本.

The response above regarding the JDBC driver got me close. I was using a very old Microsoft JDBC driver, so the upgrade definitely helped. I'm now using the community technology preview edition of version 6.0 of the Microsoft JDBC driver (version 6.0.6629.101) instead as recommended above.

但是，我仍然遇到问题.

However, I still ran into problems.

当我从代码中删除以下行时:

When I removed the following line(s) from the code:

ParameterMetaData pmData = prepStmt.getParameterMetaData();
System.out.println( "Parameter Count = " + pmData.getParameterCount() );

一切正常！

我不确定为什么"ParameterMetaData"数据类型不起作用，但是当从代码中删除时，它肯定解决了我的问题.

I'm not sure why the 'ParameterMetaData' data type does't work, but when removed from the code, it definitely solved my problems.

已修复！

这篇关于IN子句Java Microsoft JDBC中的预准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！