Java ResultSet.updateRow() 抛出 MySQLSyntaxErrorException

Question

我已经花了 2 天时间尝试解决这个问题，但仍然无法弄清楚代码有什么问题。因此，每当我尝试更新结果集中的日期并在收到此语法异常后更新行时。这一切看起来很简单，但我只是做错了什么。你能帮我解决这个痛苦吗？

public class TableTest extends JFrame {
    public static void main(String[] args) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost/fishman", "root", "");
        Statement stm = connection.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);


        ResultSet rs = stm.executeQuery("SELECT * FROM customers where id = 3 or id = 189 or id = 310 or id = 12 order by Date ");


        while(rs.next()) {
            Date date = rs.getDate("lastcontact");
            if(date == null) {
                rs.updateDate("lastcontact", new java.sql.Date(new Date().getTime()));
                rs.updateRow(); // Throws exception here
            }
        }

        rs.close();
        connection.close();
    }
}

这是我得到的异常

Exception in thread "main" 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 '' 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:411)
    at com.mysql.jdbc.Util.getInstance(Util.java:386)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
    at com.mysql.jdbc.UpdatableResultSet.updateRow(UpdatableResultSet.java:2411)
    at com.TableTest.main(TableTest.java:50)

Answer 1

可能是这样:

"Using ResultSet.updateRow will set the PK of a table (in the generated UPDATE statement) even if the PK field was not in the list of fields to update. This causes problems when permissions do not allow the user to update certain fields in a table (e.g. the PK)."

来自 https://bugs.mysql.com/bug.php?id=71143

从进一步的研究来看，这基本上看起来像是一个已被发现但从未修复的 MySQL 错误...

解决它的一种方法是首先不将 PK 提取到结果集中(例如，而不是"SELECT * FROM tbl" 其中 col1 是 PK，做“SELECT col2, col3...” 等)该解决方法有一个明显的缺点 - 您的结果集中没有 PK 列可供使用。

但在您的情况下这可能会起作用 - 如果“lastcontact”不是您的 PK(而且它可能不是)，您可以更改

SELECT * FROM customers where...

到

SELECT lastcontact FROM customers where...

这可能行得通。

希望这对您有所帮助。