I have a List<String> of categories and for each category, I want to add them to my WHERE clause by combining with AND operator like:
SELECT question_id FROM question WHERE category = categ1 AND category = categ2 AND category = ...

Since the size of the categories list is changing, I cannot do something like this:

String sql = "SELECT question_id FROM question WHERE category = ? AND category = ?";
jdbcTemplate.query(sql, stringMapper, "categ1", "categ2");

How can I achieve what I want?

CodePudding user response：

Either check if JDBC Template from Spring handle that for you using a syntax which could be something like (from the doc, I don't think it does)

SELECT question_id FROM question WHERE category in (?...)

Or write your own query with the problems that may arise:

List<Object> parameters = new ArrayList<>(categories.size());
StringBuilder sb = new StringBuilde("SELECT question_id FROM question WHERE 1=1");
if (!categories.isEmpty()) {
  if (categories.size() == 1) {
    sb.append(" and category = ?");
  } else {
    sb.append(" and category in ");
    sb.append(categories.stream()
                        .map(ignored -> "?")
                        .collect(joining(", ", "(", ")")));                      
    sb.append(")"); 
  }
  parameters.addAll(categories);
}
Object[] paramArray = parameters.toArray();
jdbcTemplate.query(sb.toString(), stringMapper, paramArray);

Notes:

1. some security/quality tool may report SQL issues because you are writing a dynamic SQL.
2. Oracle put a limit on 1000 elements per IN. You would have to partition categories per group of 1000 (or less).
3. I used a stream() in a more or less strange fashion in order to generate the "?". If you use commons-lang3, you can replace it by "(" StringUtils.repeat("?", ", ", categories.size()) ")" (the example in the javadoc was probably done with this kind of use).
4. if you only have category as single criteria, you may probably remove the 1=1 as well as the and.

CodePudding user response：

I believe this may work for you:

// The SQL Query
String sql = "SELECT question_id FROM question";
    
// Create the WHERE clause based on the number of items in List...
StringBuilder whereClause = new StringBuilder(" WHERE ");
StringBuilder ps = new StringBuilder("");
for (int i = 0; i < categories.size(); i  ) {
    if (!ps.toString().isEmpty()) {
        ps.append(" AND ");
    }
    ps.append("category = ?");
}
whereClause.append(ps.toString()).append(";");
    
//Append the WHERE clause string to the SQL query string
sql = sql   whereClause.toString();
//System.out.println(sql);
    
/* Convert the categories List to an Object[] Array so as to
   pass in as varArgs to the jdbcTemplate.query() method. */
Object[] psArgs = categories.toArray(new Object[categories.size()]);
jdbcTemplate.query(sql, stringMapper, psArgs);