SQL 语句在工具中工作，但在使用 JDBC 的 Java 中不起作用

Question

我正在使用以下SQL查询从数据库中提取一些数据。我使用的DBMS是H2，一个原生的Java实现。尽管我可以使用H2工具检索所述数据，但当我将其用作JAVA时，它给我带来了以下错误：PreparedStatement

";预期的 “）”;SQL 语句： ...

SELECT * FROM (

   SELECT temp.*, ROW_NUMBER() OVER (PARTITION BY horseid ORDER BY updated desc) as num

   FROM(

   select horseid, name, breed, min_speed, max_speed, updated from horsehistory

   UNION

   select id, name, breed, min_speed, max_speed, updated from horse

   ) as temp

   WHERE updated<? AND horseid in ?

)

WHERE num = 1;

我尝试过使用换行符，带有行分隔符的字符串生成器，但无济于事。

StringBuilder sb= new StringBuilder();

    appendString(sb, "SELECT * FROM (");

    appendString(sb, "SELECT temp.*, ROW_NUMBER() OVER (PARTITION BY horseid ORDER BY updated desc) AS num");

    appendString(sb, "FROM(");

    appendString(sb, "SELECT horseid, name, breed, min_speed, max_speed, updated FROM horsehistory");

    appendString(sb, "UNION");

    appendString(sb, "SELECT id, name, breed, min_speed, max_speed, updated FROM horse");

    appendString(sb, ") AS temp");

    appendString(sb, "WHERE updated < ? AND horseid IN ?");

    appendString(sb, ")");

    appendString(sb, "WHERE num = 1;");

    String sql=sb.toString();

appendString 函数只是将换行符追加到字符串。我在准备的声明中使用它

我做错了什么？

编辑：这是有问题的完整错误

Syntax error in SQL statement "SELECT * FROM ( SELECT TEMP.*, ROW_NUMBER() OVER (PARTITION[*] BY        HORSEID ORDER BY UPDATED DESC) AS NUM FROM ( SELECT HORSEID, NAME, BREED, MIN_SPEED, MAX_SPEED, UPDATED FROM HORSEHISTORY UNION SELECT ID, NAME, BREED, MIN_SPEED, MAX_SPEED, UPDATED FROM HORSE ) AS TEMP WHERE UPDATED < ? AND HORSEID IN (?) ) WHERE NUM = 1  "; expected ")";

分区后有一个 [*]。这是否意味着错误发生在那里？

Answer 1

您必须将数组参数括在括号中：?...&nbsp;horseid&nbsp;IN&nbsp;(?)&nbsp;...

Answer 2

啊，您正在使用 ，感谢您添加该标志。 似乎不支持 。请参阅此处H2H2PARTITION