Cannot use a LIKE query in a JDBC PreparedStatement?

ghz 1years ago ⋅ 649 views

Question

The query code and query:

ps = conn.prepareStatement("select instance_id, ? from eam_measurement where resource_id in (select RESOURCE_ID from eam_res_grp_res_map where resource_group_id = ?) and DSN like '?' order by 2");
ps.setString(1,"SUBSTR(DSN,27,16)");
ps.setInt(2,defaultWasGroup);
ps.setString(3,"%Module=jvmRuntimeModule:freeMemory%");
rs = ps.executeQuery();
while (rs.next()) { bla blah blah blah ...

Returns an empty ResultSet.

Through basic debugging I have found its the third bind that is the problem i.e.

DSN like '?'

I have tried all kinds of variations, the most sensible of which seemed to be using:

DSN like concat('%',?,'%')

but that does not work as I am missing the ' on either side of the concatenated string so I try:

DSN like ' concat('%',Module=P_STAG_JDBC01:poolSize,'%') ' order by 2

but I just cannot seem to find a way to get them in that works.

What am I missing?

Answer

First, the PreparedStatement placeholders (those ? things) are for column values only, not for table names, column names, SQL functions/clauses, etcetera. Better use String#format() instead. Second, you should not quote the placeholders like '?', it would only malform the final query. The PreparedStatement setters already do the quoting (and escaping) job for you.

Here's the fixed SQL:

private static final String SQL = "select instance_id, %s from eam_measurement"
    + " where resource_id in (select RESOURCE_ID from eam_res_grp_res_map where"
    + " resource_group_id = ?) and DSN like ? order by 2");

Here is how to use it:

String sql = String.format(SQL, "SUBSTR(DSN,27,16)"); // This replaces the %s.
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setInt(1, defaultWasGroup);
preparedStatement.setString(2, "%Module=jvmRuntimeModule:freeMemory%");

See also :

#oracle