2

Using Java 8 , Oracle 11g and Spring Boot, we normally run a SQL select something like:

String sqlQuery="select col1,col2 from myTable where col1 = :colValue";
Map<String, Object> parameters = new HashMap<String, Object>();
parameters.put("colValue", "xxx");
// Then run the query :
List<MyClass>= jdbcTemplate.query(sqlQuery,parameters,(rs, rowNum) -> new MyClass
      (rs.getString("col1"),rs.getString("col2"))  );

It is also possible to supply a List of col1 values

String sqlQuery="select col1,col2 from myTable where col1 in ( :colList) ";
Map<String, List<String>> parameters = new HashMap<String, List<String>();
List<String> myColList= Arrays.asList("xxx", "yyy");
parameters.put("colList", myColList );

But what if we want to include SQL wildcards in the column values:

String sqlQuery="select col1,col2 from myTable where col1 like ( :colList) ";
List<String> myColList= Arrays.asList("%xxx%", "yyy%");

We can't mix LIKE and IN constructs in a where clauses, so is there any other way of doing it? I know I can dynamically construct my own where clause but I'm trying to avoid that. Any pointers appreciated.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
DS.
  • 604
  • 2
  • 6
  • 24
  • Does this answer your question? [how to pass list parameter in IN clause using jdbcTemplate](https://stackoverflow.com/questions/37518094/how-to-pass-list-parameter-in-in-clause-using-jdbctemplate) – Arvind Kumar Avinash May 15 '20 at 10:02
  • @ArvindKumarAvinash yes it does, but I had oversimplified my original question. I've updated the question to include the real problem – DS. May 15 '20 at 10:07

1 Answers1

0

You can use MapSqlParameterSource

Set<Integer> myColList = new HashSet<>(Arrays.asList("xxx", "yyy"));
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("colList", myColList);
Eklavya
  • 17,618
  • 4
  • 28
  • 57
  • Thanks, that works, but I over simplified my original question, sorry. I've updated it to include the real problem – DS. May 15 '20 at 10:10
  • @DS. You can't do `like` query like `In` query. You have to use same column multiple times with `OR` condition – Eklavya May 15 '20 at 10:15
  • yes, but can you assign the values to the OR using MapSqlParameterSource or similar – DS. May 15 '20 at 10:20
  • I don't think so, you have to manually construct where condition – Eklavya May 15 '20 at 10:26