java - 更新 "ERROR: column "的 sql 使用了“关系 "account"不存在”，即使它确实存在

Question

我使用了此方法，但没有在查询中使用联接，并且它按预期工作。但我添加了内部联接，现在它无法更新“已使用”列

  public HashMap<String, Comparable> getPhoneNumberAndMarkAsUsed() {
    String[] colNames = { "phone_number.id", "phone_number.phone_number",
        "phone_number.account_id", "phone_number.used AS used",
        "(now() AT TIME ZONE account.timezone)::time AS local_time" };
    String query = "select " + Stream.of(colNames).collect(Collectors.joining(", "))
        + " from account INNER JOIN phone_number ON account.id = phone_number.account_id where phone_number.used = false order by id DESC limit 1 for update";

    HashMap<String, Comparable> account = new HashMap<String, Comparable>();

    try (Connection conn = DriverManager.getConnection(url, props); // Make sure conn.setAutoCommit(false);
        Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet rs = stmt.executeQuery(query)) {
      conn.setAutoCommit(false);

      ResultSetMetaData rsmd = rs.getMetaData();
      int columnsNumber = rsmd.getColumnCount();
      while (rs.next()) {

        for (int i = 1; i <= columnsNumber; i++) {
          if (i > 1)
            System.out.print(",  ");
          String columnValue = rs.getString(i);
          System.out.print(columnValue + " " + rsmd.getColumnName(i));
        }

        // Get the current values, if you need them.
        account.put("phone_number", rs.getString("phone_number"));
        account.put("account_id", rs.getLong("account_id"));

        rs.updateBoolean("used", true);
        rs.updateRow();
      }
      conn.commit();
    } catch (SQLException e) {
      e.printStackTrace();
    }

    return account;
  }

循环打印以下内容

7223 id,  10001234567 phone_number,  1093629 account_id,  f used,  23:32:42.502472 local_time

根据上面的输出，然后我使用“used”列是结果集的一部分。但我收到以下异常

org.postgresql.util.PSQLException: ERROR: column "used" of relation "account" does not exist

这是打印时的查询

select phone_number.id, phone_number.phone_number, phone_number.account_id, phone_number.used AS used, (now() AT TIME ZONE account.timezone)::time AS local_time from account INNER JOIN phone_number ON account.id = phone_number.account_id where phone_number.used = false order by id DESC limit 1 for update

used 属于 phone_number 表，而不是 account 表。如何解决这个问题？

Answer 1

这是您的代码中的问题:

rs.updateBoolean("used", true);

该语句将尝试通过结果集更新表的数据，但要做到这一点，您无法用户加入，并且还有一个问题。当您通过结果集更新时，它将尝试更新帐户表，如果我们发现使用的列是帐户表，则会发生错误。

因此您的代码试图在帐户表中查找“已使用”列，但它不在那里。

试试这个:

String query = "select " + Stream.of(colNames).collect(Collectors.joining(", "))
        + " from phone_number INNER JOIN account phone_number ON account.id = phone_number.account_id where phone_number.used = false order by id DESC limit 1 for update";