Cannot use a LIKE query in a JDBC PreparedStatement?

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?

Writing Java code in a JSP file instead of in a real Java class (where you **should** have done it) and having problems with the particular Java code doesn't make it a JSP problem. You would face exactly the same problem when doing so in a real Java class. So I removed the `[jsp]` tag since that's irrelevant. — BalusC, May 18 '10 at 12:52
True it is not JSP specific, but then there is nothing *wrong* with using such code in a servlet according to most books I have read, especially when it is a super simple report page of less than 40 lines of code where application architecture is a little bit overkill :) But I appreciate your point! — SeerUK, May 18 '10 at 13:12

BalusC · Accepted Answer · 2010-05-18T13:14:18.623

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:

score 13 · Answer 2 · answered Jun 12 '13 at 13:06

If you want to use LIKE in prepared statement and also want to use % characters in LIKE;

write prepared statement as normally " .... LIKE ? ...." and while assigning parameter value to question mark use

ps.setString(1, "%" + "your string value" + "%");

This will work :)

score 11 · Answer 3 · answered May 18 '10 at 12:42

There are two problems with your statement. You have to understand how bind variables work. The query is not processed by substituing the characters ? with your parameters. Instead, the statement is compiled with placeholders and then, during execution, the actual values of the parameters are given to the DB.

In other words, you parse the following query:

SELECT instance_id, :p1
  FROM eam_measurement
 WHERE resource_id IN (SELECT RESOURCE_ID 
                         FROM eam_res_grp_res_map 
                        WHERE resource_group_id = :p2)
   AND DSN LIKE '?'
 ORDER BY 2

I'm pretty sure the last parameter will be ignored because it is in a delimited character string. Even if it is not ignored, it does not make sense to have ' characters around because Oracle won't bind a parameter in a string (I'm surprised it hasn't raised any error, do you catch exceptions ?).

Now if you replace your DNS LIKE '?' with DSN LIKE ? and bind "%Module=jvmRuntimeModule:freeMemory%" this will make sense and should return the correct rows.

You still have the problem with your first parameter, it won't do what you expect, i-e the query that will be executed will be equivalent to the following query:

SELECT instance_id, 'SUBSTR(DSN,27,16)'
  FROM ...

which is not at all the same as

SELECT instance_id, SUBSTR(DSN,27,16)
  FROM ...

I would suggest parsing (=prepareStatement) the following query if you expect the SUBSTR to be dynamic:

SELECT instance_id, SUBSTR(DSN,?,?)
  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

score 2 · Answer 4 · answered May 18 '10 at 12:28

2

Omit the ' around the ?. Without the ', ? is a placeholder for a parameter. With it, it's an SQL string (i.e. the same as "?" in Java).

Then you must concatenate the string on the Java side; you can't pass SQL functions as parameters to queries; only basic values (like string, integer, etc) because the JDBC driver will convert the parameter to the SQL type the database expects and it cannot execute SQL functions in this step.

answered May 18 '10 at 12:28

Aaron Digulla

321,842
108
597
820

Without the '' around the ? I get an empty resultSet too. Not surprisingly I guess as then the sql would read: and DSN like %Module=P_STAG_JDBC01:poolSize% which oracle does not like and replies with: Missing IN or OUT parameter at index:: 1 (error code=17041) Thus I need to add the ''s to the bind when I set it via ps.setString? – SeerUK May 18 '10 at 12:37
e.g. ps.setString(3,"'Module=jvmRuntimeModule:freeMemory'"); But also gives an empty result set, as does ps.setString(3,"\'Module=jvmRuntimeModule:freeMemory\'"); Though java docs says it should not be necessary to escape an ' inside ""s – SeerUK May 18 '10 at 12:39
@SeerUK: The error message comes from parameter 1, not from parameter 3! – Aaron Digulla May 18 '10 at 14:31

score 1 · Answer 5 · edited Apr 03 '13 at 05:00

1

You can try:

String beforeAndAfter = "%" + yourVariable + "%";

edited Apr 03 '13 at 05:00

Anthon

69,918
32
186
246

answered Apr 03 '13 at 04:40

Hadi

21
1

score 0 · Answer 6 · edited Oct 13 '16 at 07:59

0

PreparedStatement ps = con.prepareStatement(
    "select columname from tablename where LOWER(columnname) LIKE LOWER('"+var+"%')");

Here var is the variable in which value that is to be searched is stored...

edited Oct 13 '16 at 07:59

Stefan van den Akker

6,661
7
48
63

answered Sep 10 '16 at 11:02

Saheba

1

1

Please give a bit more explanation and format your code (you can use "back ticks" or indent it 4 spaces). [How to Answer](http://stackoverflow.com/help/how-to-answer) – AgataB Sep 10 '16 at 11:09

score -1 · Answer 7 · edited Oct 13 '16 at 07:58

-1

This should work:

"\'" + "?" + "\'"

edited Oct 13 '16 at 07:58

Stefan van den Akker

6,661
7
48
63

answered Sep 14 '14 at 17:28

ADITYA VALLURU

111
1
3
10

Cannot use a LIKE query in a JDBC PreparedStatement?

7 Answers7

Linked

Related