Java date_trunc org.postgresql.util.PSQLException：错误：“$1”处或附近的语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21540527/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
date_trunc org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"
提问by sv.
I get this error while running this Java/JDBC code. Any ideas how to get around it?
运行此 Java/JDBC 代码时出现此错误。任何想法如何解决它？
Seems like it's complaining about the parameter in date_trunc function?
似乎在抱怨 date_trunc 函数中的参数？
org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"Position: 100
org.postgresql.util.PSQLException：错误：“$1”处或附近的语法错误位置：100
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryEx
ecutorImpl.java:2161)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutor
Impl.java:1890)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.ja
va:255)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Stat
ement.java:560)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(Abstract
Jdbc2Statement.java:417)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc
2Statement.java:302)
Java code:
爪哇代码：
static PreparedStatement searchErrorPP = connection.prepareStatement(
"select count(*) from tracking where date_trunc('day', run_date) <=
date_trunc('day', timestamp ?)");
public static int queryCount(java.util.Date date) throws SQLException {
PreparedStatement ps = null;
try {
ps = searchErrorPP;
ps.setDate( 1, new java.sql.Date(date.getTime()));
ResultSet rs = ps.executeQuery();
resulting query which executes fine in pgAdmin:
结果查询在 pgAdmin 中执行良好：
select count(*) from tracking where date_trunc('day', run_date) <=
date_trunc('day', timestamp '2014-11-11 -05:00:00')
采纳答案by Daniel Vérité
When using the type 'string'syntax as in timestamp '2014-11-11 -05:00:00', the value provided must be a constant, not a parameter. It's interpreted and converted to an internal timestamp representation by the SQL engine at the parse stage, before actual execution takes place and before the values for the parameters are known.
使用type 'string'in的语法时timestamp '2014-11-11 -05:00:00'，提供的值必须是常量，而不是参数。在实际执行发生之前和参数值已知之前，它在解析阶段被 SQL 引擎解释并转换为内部时间戳表示。
So when encountering timestamp $1, the parser produces a syntax error because $1is not a literal string.
因此，当遇到 时timestamp $1，解析器会产生语法错误，因为$1它不是文字字符串。
On the other hand, the value of cast($1 as timestamp)will be produced at execute stage, so this is what should be used.
另一方面， 的值cast($1 as timestamp)将在执行阶段产生，所以这是应该使用的。
As for the syntax from the point of view of JDBC, cast(? as timestamp)should be fine. The PostgreSQL specific syntax ?::timestampwith double colons also probably works.
至于从JDBC的语法来看，cast(? as timestamp)应该没问题。?::timestamp带有双冒号的 PostgreSQL 特定语法也可能有效。
回答by muks
I have seen such an error when either of the jdbc connection url components are null.
当 jdbc 连接 url 组件中的任何一个为空时，我已经看到了这样的错误。
E.g. connection url components such as DB host name, dbname, user or password are null.
例如，数据库主机名、数据库名、用户或密码等连接 url 组件为空。
Please check out there and you should find a clue.
请检查那里，你应该找到一个线索。
