Like %?% doesn't work as part of StringBuilder SQL query

Question

Have following String built SQL query:

    StringBuilder querySelect = new StringBuilder("select * from messages ");
    StringBuilder queryWhere = new StringBuilder("where msg_id=?");

        if (fileRSVO.getFileName()!= null){
            queryWhere.append("and descr LIKE %?% ");
        }
querySelect.append(queryWhere);

    List<Map<String, Object>> list = getJdbcTemplate().queryForList(querySelect.toString(), params.toArray()); 
    ...

The problem is in this part:

queryWhere.append("and descr LIKE %?% ")

LIKE doesn't work.
Checked in debug - it's added to all query.
Should it be single quoted or some other trick?

thanks.

EDITED

tried single quotes:queryWhere.append("and descr LIKE '%?%' ") doesn't work

here is debug string:

select * from messages where msg_id=? and descr LIKE '%?%'

score 5 · Answer 1 · answered Mar 21 '11 at 15:09

5

Assuming that the query is run using a PreparedStatement, the problem is probably that queryForList calls setString(1, 'some descr'). This would resolve the SQL to "... and descr LIKE %'some descr'%".

Try altering the code to:

queryWhere.append("and descr LIKE ?");
...
.setString(1, "%some descr%")

answered Mar 21 '11 at 15:09

Justin Muller

1,283
13
21

sorry, don't quite understand, where should I do setSetring()? – sergionni Mar 21 '11 at 15:25
I guessing that somewhere in the queryForList(String, Array) method a call to setString(int, String) is made. – Justin Muller Mar 21 '11 at 18:44

score 3 · Answer 2 · answered Mar 21 '11 at 15:08

3

can you try like this, and see anything changes.

queryWhere.append("and descr LIKE '%?%' ")

answered Mar 21 '11 at 15:08

serhat_pehlivanoglu

982
8
13

just checking... mvn is so slooow – sergionni Mar 21 '11 at 15:10
unfortunately,not yet, see, please, EDITED part – sergionni Mar 21 '11 at 15:20
got:org.springframework.jdbc.InvalidResultSetAccessException and java.sql.SQLException: Invalid column index – sergionni Mar 21 '11 at 15:45
sorry dont have any idea about those error messages. Are you positive that you are building your query string correctly ? – serhat_pehlivanoglu Mar 21 '11 at 15:52
@futile: He wants `?` to be a parameter, not a part of literal. In this case your suggestion is incorrect. – axtavt Mar 21 '11 at 18:31
@axtavt yes,you're right? it should be parameter, I already guessed it) – sergionni Mar 21 '11 at 20:08

axtavt · Accepted Answer · 2011-03-21T20:35:04.340

3

? is not a placeholder for textual substitution, it's a parameter (i.e. it's like variable in Java), therefore it should be treated as a separate token. This should work, but perhaps not in all databases:

queryWhere.append("and descr LIKE ('%' || ? || '%')");

Alternativelty you can pass %s as parts of parameter value, as suggested by Justin Muller.

edited Mar 21 '11 at 20:35

answered Mar 21 '11 at 16:50

axtavt

239,438
41
511
482

queryWhere.append("and descr LIKE '%" + fileRSVO.getFileName()+ "%'") - that it is, anyway you direction is correct – sergionni Mar 21 '11 at 20:18
@serionni: It's a very bad solution, it's vulnerable to SQL injections. Use either my soluton (add `%`s at the database side), or Justin Muller's one (add `%`s when you put `fileRSVO.getFileName()` into `params`). – axtavt Mar 21 '11 at 20:29
@sergionni: Note the correction, this way it should be more compatible with databases. – axtavt Mar 21 '11 at 20:35
@axtavt what does this syntax || mean in this context? – sergionni Mar 22 '11 at 09:36
1

@sergionni: `||` is a standard SQL string concatenation operator. – axtavt Mar 22 '11 at 09:41

score 2 · Answer 4 · answered Mar 21 '11 at 15:09

2

You'd want to do

queryWhere.append("AND descr LIKE'%?%'");

I wrapped %?% in single quotes.

To see more LIKE query examples, checkout this.

answered Mar 21 '11 at 15:09

Mike Lewis

63,433
20
141
111

1

strange,doesn't work with single quotes. Debug:select * from messages where msg_id=? and descr LIKE '%?%' – sergionni Mar 21 '11 at 15:18

score 2 · Answer 5 · answered Mar 21 '11 at 15:09

2

I always take the parameter and put the % into the parameter, and then bind it to and descr like ?.

answered Mar 21 '11 at 15:09

Paul Tomblin

179,021
58
319
408

score 0 · Answer 6 · answered Nov 22 '18 at 15:23

If using a third party library to manage dynamic SQL is an option for you, here's a solution that uses jOOQ (disclaimer: I work for the vendor)

ctx.selectFrom(MESSAGES)
   .where(MESSAGES.MSG_ID.eq(msgId))
   .and(fileRSVO.getFileName() != null
      ? MESSAGES.DESCR.contains(descr)
      : DSL.noCondition())
   .fetch();

This is using the Field.contains(T) method, which translates to:

[ field ] like ('%' || escape(value, '\') || '%') escape '\'

Notice the usage of escape(), which is necessary in case your string contains wildcards. Another option would be, of course, to concatenate the wildcards already in Java:

ctx.selectFrom(MESSAGES)
   .where(MESSAGES.MSG_ID.eq(msgId))
   .and(fileRSVO.getFileName() != null
      ? MESSAGES.DESCR.like("%" + descr + "%")
      : DSL.noCondition())
   .fetch();

score 0 · Answer 7 · answered Mar 21 '11 at 15:25

StringBuilder querySelect = new StringBuilder("select * from messages");
StringBuilder queryWhere = new StringBuilder("where msg_id=?");
if (fileRSVO.getFileName()!= null){
  queryWhere.append("and descr LIKE %?% ");
}
// query = "select * from messageswhere msg_id=?and descr LIKE %?% "

As you can see, the query is not valid. You must add spaces:

StringBuilder querySelect = new StringBuilder("select * from messages ");
StringBuilder queryWhere = new StringBuilder("where msg_id=? ");
if (fileRSVO.getFileName()!= null){
  queryWhere.append("and descr LIKE %?% ");
}
// query = "select * from messages where msg_id=? and descr LIKE %?% "

And about the part: descr LIKE %?%

I am not exactly sure how this is passed throught to the database. Just "as is", or as a 2nd parameter that needs to be filled in. If "as is", the ? is seen as a wild-card and all results are taken. If not, the 2nd ? is not filled in (correctly). Try using:

descr LIKE ?

And add the % before and after the value in you java code.

there are spaces there, it's simplified sample of my query – sergionni Mar 21 '11 at 15:28 — sergionni, Mar 21 '11 at 15:28

score 0 · Answer 8 · answered May 10 '21 at 08:03

0

funciona de esta forma

             sql.append(" AND VNOMBRE LIKE :P_VNOMBRE");
             parametros.addValue("P_VNOMBRE", "%"+oficinas.getCAJABUSQUEDA()+"%");

answered May 10 '21 at 08:03

Vladimir suncion cespedes

1

In stackoverflow.com, please use the English language. There are other language-specific variants, as https://es.stackoverflow.com/ for discussions in Spanish. – Paulo Amaral May 10 '21 at 08:39

score -1 · Answer 9 · edited Aug 13 '12 at 09:30

-1

Use this code:

StringBuilder querySelect = new StringBuilder("select * from messages ");
StringBuilder queryWhere = new StringBuilder("where msg_id=?");

if (fileRSVO.getFileName()!= null){
    queryWhere.append("and descr LIKE %?% ");
}
querySelect.append(queryWhere);

List<Map<String, Object>> list = getJdbcTemplate().queryForList(querySelect.toString(), params.toArray()); 
...

edited Aug 13 '12 at 09:30

Rüdiger Hanke

6,215
2
38
45

answered Aug 13 '12 at 05:02

24t24t4

1

Like %?% doesn't work as part of StringBuilder SQL query

9 Answers9