SeerUK SeerUK - 1 year ago 84
Java Question

Cannot use a Like query in a JDBC prepared statement?

OK, first 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");
rs = ps.executeQuery();
while ( { bla blah blah blah ...

Returns an empty resultSet.

Through basic debugging I have found its the 3rd 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('%',?,'%')

bit that doesn' work as I am missing the ' ' either side of the concatenated string so I try

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

but I just can't seem to find a way to get them in that works.

What am I missing?

Answer Source

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:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download