spring map optional query parameters to sql prepared statement

Question

I'm creating a REST API in Spring for a project.

The problem I'm facing is how to elegantly create a PreparedStatement with variable number of parameters.

For eg. I have a product collection and I'd have lots of query parameters

/accounts?categoryId=smth&order=asc&price=<1000&limit=10&offset=300

Problem is that these parameters may or may not be set.

Currently I have something that looks like this, but I haven't even started sanitizing for user input

Controller

@RequestMapping(method = RequestMethod.GET)
public List<Address> getAll(@RequestParam Map<String, String> parameters) {
        return addressRepository.getAll(parameters);
}

Repository

@Override
public List<Address> getAll(Map<String, String> parameters) {
    StringBuilder conditions = new StringBuilder();
    List<Object> parameterValues = new ArrayList<Object>();
    for(String key : parameters.keySet()) {
        if(allowedParameters.containsKey(key) && !key.equals("limit") && !key.equals("offset")) {
            conditions.append(allowedParameters.get(key));
            parameterValues.add(parameters.get(key));
        }
    }
    int limit = Pagination.DEFAULT_LIMIT_INT;
    int offset = Pagination.DEFAULT_OFFSET_INT;
    if(parameters.containsKey("limit"))
        limit = Pagination.sanitizeLimit(Integer.parseInt(parameters.get("limit")));
    if(parameters.containsKey("offset"))
        offset = Pagination.sanitizeOffset(Integer.parseInt(parameters.get("offset")));
    if(conditions.length() != 0) {
        conditions.insert(0, "WHERE ");
        int index = conditions.indexOf("? ");
        int lastIndex = conditions.lastIndexOf("? ");
        while(index != lastIndex) {
            conditions.insert(index + 2, "AND ");
            index = conditions.indexOf("? ", index + 1);
            lastIndex = conditions.lastIndexOf("? ");
        }
    }
    parameterValues.add(limit);
    parameterValues.add(offset);
    String base = "SELECT * FROM ADDRESSES INNER JOIN (SELECT ID FROM ADDRESSES " + conditions.toString() + "LIMIT ? OFFSET ?) AS RESULTS USING (ID)";
    System.out.println(base);
    return jdbc.query(base, parameterValues.toArray(), new AddressRowMapper());
}

Can I improve this? Or is there a better way?

Andy Dufresne · Accepted Answer · 2014-12-06 06:58:02Z

2

I found the above code hard to maintain since it has complex logic to build the where clause. Spring's NamedParameterJdbcTemplate could be used to simplify the logic. Follow this link for having a look at a basic example on NamedParameterJdbcTemplate

Here's how the new code should look like

    public List<Address> getAll(Map<String, String> parameters) {
        Map<String, Object> namedParameters = new HashMap<>();
        for(String key : parameters.keySet()) {
            if(allowedParameters.contains(key)) {
                namedParameters.put(key, parameters.get(key));
            }
        }

        String sqlQuery = buildQuery(namedParameters);

        NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(null /* your data source object */);
        return template.query(sqlQuery, namedParameters, new AddressRowMapper());
    }

    private String buildQuery(Map<String, Object> namedParameters) {
        String selectQuery = "SELECT * FROM ADDRESSES INNER JOIN (SELECT ID FROM ADDRESSES ";
        if(!(namedParameters.isEmpty())) {
            String whereClause = "WHERE ";
            for (Map.Entry<String, Object> param : namedParameters.entrySet()) {
                whereClause += param.getKey() + " = :" + param.getValue();
            }

            selectQuery += whereClause;
        }
        return selectQuery + " ) AS RESULTS USING (ID)";
    }

answered Dec 6, 2014 at 6:58

Andy Dufresne

6,1988 gold badges71 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Rares H. Over a year ago

another problem with this approach is that I loose type safety on parameters. Being a collection resource, I must paginate. Every time I query the parameter map I would have to check whether it's LIMIT or OFFSET and cast the parameter value to long. I would have to have a set of rules attached to each allowed parameter. Am I making this too complex?

Andy Dufresne Over a year ago

Yes I think you are making it complex :). You lost type safety in the original approach too and thats mainly because you receive a Map<String, String> as parameters. For this you could try @QueryParam but the namedParameters map would still have to be Map<String, Object>. If you think of making the map type safe, you would lose on writing generic code that works for all parameters.

Rares H. · Accepted Answer · 2014-12-06 14:02:20Z

After some thinking I decided type safety is important and I decided to use the following style throughout the API.

@RequestMapping(method = RequestMethod.GET)
public List<Address> getAll(@RequestParam(value = "cityId", required = false) Long cityId,
                            @RequestParam(value = "accountId", required = false) Long accountId,
                            @RequestParam(value = "zipCode", required = false) String zipCode,
                            @RequestParam(value = "limit", defaultValue = Pagination.DEFAULT_LIMIT_STRING) Integer limit,
                            @RequestParam(value = "offset", defaultValue = Pagination.DEFAULT_OFFSET_STRING) Integer offset) {
    Map<String, Object> sanitizedParameters = AddressParameterSanitizer.sanitize(accountId, cityId, zipCode, limit, offset);
    return addressRepository.getAll(sanitizedParameters);
}

Parameter sanitation

public static Map<String, Object> sanitize(Long accountId, Long cityId, String zipCode, Integer limit, Integer offset) {
    Map<String, Object> sanitizedParameters = new LinkedHashMap<String, Object>(5);

    if(accountId != null) {
        if (accountId < 1) throw new InvalidQueryParameterValueException(ExceptionMessage.INVALID_ID);
        else sanitizedParameters.put("ACCOUNT_ID = ? ", accountId);
    }

    if(cityId != null) {
        if (cityId < 1) throw new InvalidQueryParameterValueException(ExceptionMessage.INVALID_ID);
        else sanitizedParameters.put("CITY_ID = ? ", cityId);
    }

    if(zipCode != null) {
        if (!zipCode.matches("[0-9]+")) throw new InvalidQueryParameterValueException(ExceptionMessage.INVALID_ZIP_CODE);
        else sanitizedParameters.put("ZIP_CODE = ? ", zipCode);
    }

    if (limit < 1 || limit > Pagination.MAX_LIMIT_INT) throw new InvalidQueryParameterValueException(ExceptionMessage.INVALID_LIMIT);
    else sanitizedParameters.put("LIMIT ? ", Pagination.sanitizeLimit(limit));

    if(offset < 0) throw new InvalidQueryParameterValueException(ExceptionMessage.INVALID_OFFSET);
    else sanitizedParameters.put("OFFSET ?", Pagination.sanitizeOffset(offset));

    return sanitizedParameters;
}

SQL Query String builder

public static String buildQuery(Tables table, Map<String, Object> sanitizedParameters) {
    String tableName = table.name();
    String baseQuery = "SELECT * FROM " + tableName + " INNER JOIN (SELECT ID FROM " + tableName;
    String whereClause = " ";
    if(sanitizedParameters.size() > 2) {
        whereClause += "WHERE ";
    }
    if(!sanitizedParameters.isEmpty()) {
        for(String key : sanitizedParameters.keySet()) {
            whereClause += key;
        }
        baseQuery += whereClause;
    }
    return baseQuery + ") AS RESULTS USING (ID)";
}

Repository:

@Override
public List<Address> getAll(Map<String, Object> sanitizedParameters) {
    String sqlQuery = CollectionQueryBuilder.buildQuery(Tables.ADDRESSES, sanitizedParameters);
    return jdbc.query(sqlQuery, sanitizedParameters.values().toArray(), new AddressRowMapper());
}

Collectives™ on Stack Overflow

spring map optional query parameters to sql prepared statement

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related