• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

NamedParameterJdbcOperations escape apostrophe in LIKE

 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have a query like this

SELECT * FROM TABLEA WHERE MODULE = :moduleId AND
UPPER(NAME) LIKE UPPER('%sample''s%') AND ID = :userId;

I escape the apostrophe above with ''. Then pass the sql above to...

Map<String, Object> paramMap = new HashMap<String, Object>();
paramMap.put("userId", userId);

namedParameterJdbcOperations.queryForList(sql, paramMap, Long.class);

and I got this error:

SQL state [99999]; error code [17041]; Missing IN or OUT parameter at index:: 3; nested exception is java.sql.SQLException: Missing IN or OUT parameter at index:: 3

when looking at the logs, I saw the that the userId was not being replaced by ?

SELECT * FROM TABLEA WHERE MODULE = ?
AND UPPER(NAME) LIKE UPPER('%sample''s%') AND ID = :userId;

Note that the whole string "AND UPPER(NAME) LIKE UPPER('%sample''s%')" is generated by another function, it is concatenated to the whole sql before being run.
 
Rancher
Posts: 4801
50
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You have two parameters in that SQL (:moduleId and :userId), and you are only providing one in your Map.
 
reply
    Bookmark Topic Watch Topic
  • New Topic