CODEWITHSUNDEEP
javasqlstringsql-like
sergionni
sergionni

Reputation: 13510

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

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 '%?%'

Upvotes: 2

Views: 10311

Answers (9)

Vladimir suncion cespedes
Vladimir suncion cespedes

Reputation: 1

funciona de esta forma

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

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 220762

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();

Upvotes: 0

24t24t4
24t24t4

Reputation: 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()); 
...

Upvotes: -1

axtavt
axtavt

Reputation: 242686

? 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.

Upvotes: 3

SPee
SPee

Reputation: 674

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.

Upvotes: 0

Paul Tomblin
Paul Tomblin

Reputation: 182772

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

Upvotes: 2

Justin Muller
Justin Muller

Reputation: 1303

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%")

Upvotes: 5

Mike Lewis
Mike Lewis

Reputation: 64137

You'd want to do

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

I wrapped %?% in single quotes.

To see more LIKE query examples, checkout this.

Upvotes: 2

serhat_pehlivanoglu
serhat_pehlivanoglu

Reputation: 982

can you try like this, and see anything changes.

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

Upvotes: 3

Related Questions