The JDBC specification (like the ODBC specification) acknolewdges the fact that the SQL may vary between RDBMS since none of them is completely compliant to any of the SQL standard. While this may become true in the future, the JDBC specification had to specify a way to ensure portability for JDBC applications. It defines escapes that the JDBC driver should translate into SQL for its backend. For more information consult the section 4.1.5 from the JDBC Technology Guide:Getting Started (bundled with the Sun JRE documentation) and the section 13.4 from the JDBC 3.0 specification.
The parsing of the sql statements for these escapes can be disabled using Statement.setEscapeProcessing(false).
Connection.nativeSQL(String sql) provides another way to have escapes processed. It translates the given SQL to a SQL suitable for the PostgreSQL backend.
Example 1-1. Using jdbc escapes
To use the JDBC escapes, you simply write your SQL replacing date/time literal values, outer join and functions by the JDBC escape syntax. For example :
ResultSet rs = st.executeQuery("SELECT {fn week({d '2005-01-24'})}");
is the portable version for
ResultSet rs = st.executeQuery("SELECT extract(week from DATE '2005-01-24')");
You can specify which escape character to use in strings comparison (with LIKE) to protect wildcards characters ('%' and '_') by adding the following escape : {escape 'escape-character'}. The driver supports this only at the end of the comparison expression.
For example, you can compare string values using '|' as escape character to protect '_' :
rs = stmt.executeQuery("select str2 from comparisontest where str1 like '|_abcd' {escape '|'} ");