mySQL 上的 java setDate

Question

我有两个来自函数参数的 sql 日期:

public void aaa (java.sql.Date sqlDate1, java.sql.Date sqlDate2)

函数内部是:

String sql = "SELECT ROOM_TYPE_ID , COUNT(ROOM_ID) AS Available_Room_Quantity FROM ROOM " +
             "WHERE Room_ID NOT IN (SELECT ROOM_ID FROM ROOM_RECORD WHERE START_DATE<=? AND END_DATE>=? ) "+
             "GROUP BY ROOM_TYPE_ID";

statement = conn.prepareStatement(sql);
statement.setDate(1, sqlDate1);
statement.setDate(2, sqlDate2);
ResultSet rs = statement.executeQuery(sql);

while(rs.next())
{

    String first = rs.getString("Available_Room_Quantity");
    String last = rs.getString("ROOM_TYPE_ID");

    System.out.print("ROOM_TYPE_ID: " + first);
    System.out.print(" Available_Room_Quantity" + last + "\n");
}
rs.close();

运行时出错，startdate和enddate在mysql中都声明为日期。

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in
your SQL syntax; check the manual that corresponds to your MySQL server version
for the right syntax to use near '? AND END_DATE>=? ) GROUP BY ROOM_TYPE_ID' at
line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)

at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)

at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
at java.lang.reflect.Constructor.newInstance(Unknown Source)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:408)
at com.mysql.jdbc.Util.getInstance(Util.java:383)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1062)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4226)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4158)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2615)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2776)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2834)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2783)
at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1569)

Answer 1

不要再次传递查询。你在PreparedStatement构造的时候已经通过了:

statement = conn.prepareStatement(sql);

只需从 executeQuery() 方法中删除查询字符串。

如果您再次传递它，那么 ? 将不会被实际值替换，因此您会在 ? 附近收到无效的查询字符串语法错误。 AND END_DATE>=?

应该是

ResultSet rs = statement.executeQuery( );
                                      ^----------- Remove query string from here