Accessing map values in a NamedParameterJdbcTemplate

Question

I have a table with an entity_type and entity_id VARCHAR columns. I want to get all rows that match a list of entity_type and entity_id. I successfully manage to do that:

WHERE (entity_type = 'type1' AND entity_id = 'id1') OR (entity_type = 'type1' AND entity_id = 'id2') OR (entity_type = 'type2' AND entity_id = 'id3')

With this solution:

public class Entity {
  private String type;
  private String id;
  // Getters setters
}

public class Request {
  private List<Entity> entities = new ArrayList<>();
  // Other ctriteria
  // getters setters
}

  Request  request = getRequest();
  SqlParameterSource params = new BeanPropertySqlParameterSource(request);
  StringBuilder query = new StringBuilder();
  query.append("SELECT * FROM TABLE");
  List<String> clauses = new ArrayList<>();
  List<Entity> entities = request.getEntities();
  if (CollectionUtils.isNotEmpty(entities)) {
    List<String> entityClauses = new ArrayList<>();
    for (int i = 0; i < entities.size(); i++) {
      String entity = String.format(":entities[%s]", i);
      entityClauses.add("(ENTITY_ID = " + entity + ".identifier AND ENTITY_TYPE = " + entity + ".type)");
    }
    clauses.add(entityClauses.stream().collect(Collectors.joining(" OR ", "(", ")")));
  }
  // Others clauses...
  if (!clauses.isEmpty()) {
    query.append(clauses.stream().collect(Collectors.joining(" AND ", " WHERE ", "")));
  }
  return getNamedParameterJdbcTemplate().query(query.toString(), params, getRowMapper());

I would like to group by types like that:

WHERE  (entity_type = 'type1' AND entity_id  IN('id1', 'id2')) OR (entity_type = 'type2' AND entity_id IN('id3'))

With a Map<String, Set<String>> instead of a List<Entity>

public class Request {
  private Map<String,Set<String>> entities = new HashMap<>();
  // Other ctriteria
  // getters setters
}

However, then I don't know how to access the map values in the request.

How do I write the Spring JDBC request to achieve that? Is it even possible?

Answer 1

The solution was to turn the BeanPropertySqlParameterSource into a MapSqlParameterSource to be able to add new param

      Request  request = getRequest();
      BeanPropertySqlParameterSource beanParams = new BeanPropertySqlParameterSource(request);
      MapSqlParameterSource params = new MapSqlParameterSource();
      for (String paramName : beanParams.getReadablePropertyNames()) {
        Object value = beanParams.getValue(paramName);
        params.addValue(paramName, value);
      }
      StringBuilder query = new StringBuilder();
      query.append("SELECT * FROM TABLE");
      List<String> clauses = new ArrayList<>();
      List<Entity> entities = request.getEntities();
      if (CollectionUtils.isNotEmpty(entities)) {
        List<String> entityClauses = new ArrayList<>();
        Map<String, List<Entity>> entityByType = entities.stream().collect(Collectors.groupingBy(Entity::getType));
        for(Map.Entry<String, List<Entity>> entry: entityByType.entrySet()) {
          String type = entry.getKey();
          List<String> ids = entry.getValue();
          String paramNameType = "Entity" + type + "_type";
          String paramNameIds = "Entity" + type + "_ids";
          entityClauses.add("(ENTITY_TYPE = :" + paramNameType + " AND ENTITY_ID IN (:" + paramNameIds + "))");
          params.addValue(paramNameType, type);
          params.addValue(paramNameIds, ids);    
        }
        clauses.add(entityClauses.stream().collect(Collectors.joining(" OR ", "(", ")")));
      }
      // Others clauses...
      if (!clauses.isEmpty()) {
        query.append(clauses.stream().collect(Collectors.joining(" AND ", " WHERE ", "")));
      }
      return getNamedParameterJdbcTemplate().query(query.toString(), params, getRowMapper());