prepareStatement() appears to be stripping semicol

问题:

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 is an object that provides a connection and prepared statment. It doesn't do any manipulation at all.

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

Notice the missing semicolon.

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

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

This is postgresql if that matters.

Edit: 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.

回答1:

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

• 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.

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

• 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.

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.

回答2:

preparedStatement(String sql) method takes the input String and creates a CallableStatement object for calling database stored procedures.

During the initialization of the CallableStatement object, the sql string is converted into the system's native SQL grammar by the jdbc driver.

Since semicolon is a separator for Postgresql, you may assume that it is removed by the jdbc driver and may differ if using other jdbc driver implementations. So the best bet would be removing it from your SQL string.