java - WHERE 子句问题

Question

给出我想要构造的以下 SQL 语句:

SELECT CONCAT(employees.first_name," ", employees.last_name), landlines.number
FROM employees,landlines WHERE employees.id=landlines.emp_id ORDER BY employees.last_name

最初它是使用问号参数创建的，如下所示:

SELECT CONCAT(employees.first_name," ", employees.last_name), landlines.number
FROM employees,landlines WHERE employees.id=? ORDER BY employees.last_name

正如您从 WHERE 条件中看到的，我正在使用固定电话表中的字段引用员工表中的字段。基本上是一个主键字段引用一个外键字段。相当标准的东西。

我的问题是使用PreparedStatement 类。我有一个使用如下所示的 switch case 的方法:

PreparedStatement statement = ...;
...
//wc is a WhereCondition object and getValue() returns an Object which I cast to a particular type
Field.Type value = wc.getKey().getFieldType();
switch (value)
{
  case STRING:
      statement.setString(index, ((String)wc.getValue()));
      index++;
      break;
  case INT:
      if(wc.getValue() instanceof Field)
      {
        Field fld = (Field)wc.getValue();
        statement.setString(index,fld.toString());
      }
      else
        statement.setInt(index, ((Integer)wc.getValue()));
      index++;
      break;
  case FLOAT:
      statement.setFloat(index, ((Float)wc.getValue()));
      index++;
      break;
  case DOUBLE:
      statement.setDouble(index, ((Double)wc.getValue()));
      index++;
      break;
  case LONG:
      statement.setLong(index, ((Long)wc.getValue()));
      index++;
      break;
  case BIGDECIMAL:
      statement.setBigDecimal(index, ((BigDecimal)wc.getValue()));
      index++;
      break;
  case BOOLEAN:
      statement.setBoolean(index, ((Boolean)wc.getValue()));
      index++;
      break;
  case DATE:
      //We don't want to use the setDate(...) method as it expects
      //a java.sql.Date returned which doesn't allow for any time stamp.
      //Let the database perform the conversion from String to Date type.
      statement.setString(index, ((String)wc.getValue()));
      index++;
      break;
  case DBFUNCTION:
      statement.setString(index, ((String)wc.getValue()));
      index++;
      break;
  case IMAGE:
      statement.setString(index, ((String)wc.getValue()));
      index++;
      break;
}

如果你看看这个案例 INT，我试图通过测试 Field 类型来避免 ClassCastException，从而调用 statements.setString(index,fld.toString()。问题是我最终得到了一个 SQL 语句:如下所示的 WHERE 子句:

WHERE employees.id = 'landlines.emp_id'

那些讨厌的引号会阻止查询正确执行。有没有办法设置XXXX字段employees.id的参数(INT类型)，以便landlines.emp_id可以在不添加引号的情况下输入？

Answer 1

这将 JOIN 与参数分开。我无法在您的环境中测试它，所以我不确定它是否正确，但它不需要引号:

SELECT CONCAT(e.first_name," ", e.last_name), l.number
FROM employees e
JOIN landlines l ON e.id=l.emp_id
WHERE e.id=? ORDER BY e.last_name