Supply a list of column values with wildcards, to a sql query

user_1463974 0
Viewed 96

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.

spring-boot oracle11g java
1 Answers

You can use MapSqlParameterSource

Set<Integer> myColList = new HashSet<>(Arrays.asList("xxx", "yyy"));
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("colList", myColList);
user_42073060
Related