prepareStatement()似乎正在剥离分号 [英] prepareStatement() appears to be stripping semicolon

问题描述

我正在尝试使用以下命令执行准备的语句:

I am trying to execute a prepared statment using the following:

dbaBean.setPrepStmt(dbaBean.getConn().prepareStatement(
    "SELECT id, author, title, url, article_text, date_created " +
            "FROM articles WHERE " +
            "(EXTRACT(YEAR FROM date_created) = ? OR ? is null) " +
            "AND (EXTRACT(MONTH FROM date_created) = ? OR ? is null) " +
            "AND (EXTRACT(DAY FROM date_created) = ? OR ? is null) AND " +
            "(url = ? OR ? is null) " +
            "ORDER BY date_created DESC;"));

dbaBean.getPrepStmt().setString(1, year);
dbaBean.getPrepStmt().setString(2, year);
dbaBean.getPrepStmt().setString(3, month);
dbaBean.getPrepStmt().setString(4, month);
dbaBean.getPrepStmt().setString(5, day);
dbaBean.getPrepStmt().setString(6, day);
dbaBean.getPrepStmt().setString(7, URL);
dbaBean.getPrepStmt().setString(8, URL);

System.out.println(dbaBean.getPrepStmt().toString());

dbaBean是提供连接和准备好的语句的对象.它根本不做任何操作.

dbaBean is an object that provides a connection and prepared statment. It doesn't do any manipulation at all.

我已经很成功地将上面的代码用于其他方法，并且一点也没有遇到任何麻烦.现在，此功能似乎在最后去除分号，例如这是与System.out行一起输出的:

I have used the above code for other methods quite successfully and have had no trouble with it at all. Now this function appears to be stripping the semicolon at the end, e.g. this is output with the System.out line:

SELECT id, author, title, url, article_text, date_created FROM articles WHERE (EXTRACT(YEAR FROM date_created) = NULL OR NULL is null) AND (EXTRACT(MONTH FROM date_created) = NULL OR NULL is null) AND (EXTRACT(DAY FROM date_created) = NULL OR NULL is null) AND (url = 'someurl' OR 'someurl' is null) ORDER BY date_created desc

请注意缺少的分号.

我尝试添加第二个分号，它也被吃掉了".

I have tried adding a second semicolon and it too is 'eaten'.

可以很好地传播其他更改(例如，选择另一个字段).

Other changes are propogated fine (e.g. another field to select).

如果需要的话，这就是PostgreSQL.

This is postgresql if that matters.

我很在乎，因为我在运行此查询时遇到错误，并且我认为分号的删除是潜在的罪魁祸首.下面的答案似乎表明情况并非如此，我需要在其他地方寻找它.

I care because I am having an error running this query and I thought the dropping of the semicolon was the potential culpret. The answers below seem to indicatate that this is not the case and I need to look elsewhere for it.

推荐答案

我花了几分钟时间浏览Postgresql JDBC驱动程序源代码，看来正在发生的事情如下:

I spent a few minutes trawling through the Postgresql JDBC driver source code, and it appears that what is happening is as follows:

• 在创建准备好的语句时，驱动程序将进行部分解析以将SQL拆分为语句，找到占位符并(我认为)删除所有注释.

• When the prepared statement is created, the driver does a partial parse to split the SQL into statements, find placeholders and (I think) remove any comments.

解析结果将一系列片段存储在PreparedStatement对象中.

The result of the parse is stored in the PreparedStatement object a sequence of fragments.

如果在语句上调用toString()，则该方法将重建SQL，为占位符插入值，并在每个语句之间添加分号 ...如果有多个语句.

If you call toString() on the statement, the method reconstructs the SQL, inserting values for the placeholders, and adding a semicolon between each statement ... if there are multiple statements.

因此，消失的分号是功能，而不是错误. JDBC规范中没有任何要求toString()方法应以原始形式...或以任何形式为您提供查询.此外，在编写用于JDBC的SQL时，通常的做法是(至少对于Postgresql使用)省略尾部分号.

So, the disappearing semicolons are a feature, not a bug. Nothing in the JDBC specifications requires that the toString() method should give you the query in its original form ... or in any form at all. And besides, it is normal practice using (at least for Postgresql) to leave off the trailing semicolon when writing SQL for JDBC.

这篇关于prepareStatement()似乎正在剥离分号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！