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?

t开发者_如何学运维hanks.

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

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

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

can you try like this, and see anything changes.

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

You'd want to do

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

I wrapped %?% in single quotes.

To see more LIKE query examples, checkout this.

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

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.

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

funciona de esta forma

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

         

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