java - 将参数传递给 java 准备语句

Question

我实际上正在尝试执行 delete from mytable where CreationDate < now() - interval '5 month'

我想传递一个参数5至?

public static final StringBuilder SQL_MY_Query= new StringBuilder(
        "delete from mytable where CreationDate < now() - interval '? month'");

ps = con.prepareStatement(SQL_MY_Query.toString());
ps.setInt(1, 5);

我收到错误 Sql Exception org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.我们该如何解决这个问题？

Answer 1

问号必须替换标记；不在字符串文字中。

试试这个:

public static final StringBuilder SQL_MY_Query= new StringBuilder(
        "delete from mytable where CreationDate < now() - interval ? || ' month'");

或者(基于:Using a variable period in an interval in Postgres):

public static final StringBuilder SQL_MY_Query= new StringBuilder(
        "delete from mytable where CreationDate < now() - (? || ' month')::interval");