UCANAccess::4.0.2意外令牌：164更新查询

Question

我需要制作一个Jar来格式化Access数据库中的电话号码，并为此使用了UcanAccess，所有查询都可以正常工作，直到我需要用格式化的电话号码更新数据库列。

public void updatePhonenumberColumn()
{
    try {
        for (int i = 0; i < this.phoneNumbers.size(); i++) {
            String SQL = "UPDATE [" + this.table + "] SET [" + this.phonenumberColumn + "]=" + this.phoneNumbers.get(i) + " WHERE [" + this.id + "]=" + i;
            PreparedStatement preparedStatement = connection.prepareStatement(SQL);
            preparedStatement.executeUpdate();
        }
    } catch (SQLException e) {
        System.err.println(e.getMessage() + "\nErrorcode: " + e.getErrorCode());
    }
}

正如其他文章中所建议的那样，我尝试使用括号，格式化的电话号码被自己存储在ArrayList中，然后我尝试用update语句添加。如果有什么不清楚的，我会努力提供进一步的信息。

    public void updatePhonenumberColumn()
{
    String sql = "SELECT " + this.phonenumberColumn + " FROM " + this.table;

    try {
        Statement statement = connection.createStatement();
        this.resultSet = statement.executeQuery(sql);
        System.out.println(this.resultSet.getFetchSize());
    } catch (SQLException e) {
        System.err.println(e.getMessage() + "\nErrorcode: " + e.getErrorCode());
    }
}

    public void executeQueryPhoneNumber(String tableName, String columnName)
{
    this.actualColumn = columnName;
    this.phonenumberColumn = columnName;
    this.table = tableName;
    try {
        this.query = this.connection.createStatement();
        this.resultSet = query.executeQuery("SELECT " + this.phonenumberColumn + " FROM " + this.table);
        appendItems(this.phoneNumbers, this.resultSet);
    } catch (SQLException e) {
        System.err.println(e.getMessage() + "\nErrorCode: " + e.getErrorCode());
    }

}

我将非常感谢任何想法

Answer 1

“意外标记：164”听起来像您的电话号码中有空格，您的动态SQL失败了。您应该使用参数化查询，如下所示：

String sql = "UPDATE [" + this.table + "] SET [" + this.phonenumberColumn + "]=? WHERE [" + this.id + "]=?";
PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < this.phoneNumbers.size(); i++) {
    preparedStatement.setString(1) = this.phoneNumbers.get(i);
    preparedStatement.setInt(2) = i;
    preparedStatement.executeUpdate();
}