Adrien Brunelat Adrien Brunelat - 7 months ago 10
SQL Question

MySQL: How to constraint couple of values with set of couples of values via JDBC?

Question



With MySQL and using a JDBC template, is there a way to build parameters from Java Lists so that the SQL request matches a couple of values with couples of values in a given set?




Details



The values should match only if the couple of values is present in the list.

It should not match if one is present in a couple of values and the other in another couple further into the list of couples.

That is to say, given that JDBC parametrized query:

SELECT *
FROM TABLE_1
WHERE (COL_1, COL_2) IN (:valuesSet)


Caution: valuesSet is a set of couples

And that Java code:

public void daoMethod(List<MyObject> values1, List<MyObject> values2) {
String query = "";
query = "SELECT *\n" +
"FROM TABLE_1\n" +
"WHERE (COL_1, COL_2) IN (:valuesSet)";

MapSqlParameterSource parameters = new MapSqlParameterSource();

// Build valuesSet here

parameters.addValue("valuesSet", valuesSet);
namedParameterJdbcTemplate.query(query, parameters);
}


Is there an elegant way to build a JDBC template without having to "manually" create the string?

The inserted
:valuesSet
should be something like:

"(values1.get(0), values2.get(0)), (values1.get(1), values2.get(1)), ..."


But how should I build that string?




Current Track



Currently, my first draft solution is to build the string by Java code like this:

List<String> valuesSet = new ArrayList<String>();

for (int i = 0; i < values1.size(); i++) {
String value1 = StringEscapeUtils.escapeSql(values1.get(i).toString());
String value2 = StringEscapeUtils.escapeSql(values2.get(i).toString());

valuesSet.add("('" + value1 + "','" + value2 + "')");
}


But it keeps escaping the result list to make it a String and adds
'
around it. Therefore, it's not working.




TL;DR



Input:

List<Object> objects


Output:

SELECT *
FROM TABLE_1
WHERE (COL_1, COL_2) IN (
('object_1_val1', 'object_1_val2'),
('object_2_val1', 'object_2_val2'),
('object_3_val1', 'object_3_val2'),
('object_4_val1', 'object_4_val2'),
...
)


Mean:

NamedParameterJdbcTemplate

Answer

I found out a workaround using CONCAT():

SELECT *
FROM TABLE_1
WHERE CONCAT(COL_1, ',', COL_2) IN (:valuesSet);

That way, valuesSet can be a simple list of strings and is passed as the following:

List<String> valuesSet = new ArrayList<String>();
for (int i = 0; i < values1.size(); i++) {
    String value1 = StringEscapeUtils.escapeSql(values1.get(i).toString());
    String value2 = StringEscapeUtils.escapeSql(values2.get(i).toString());

    valuesSet.add(value1 + "," + value2;
}

parameters.addValue("valuesSet", valuesSet);

Then, the executed query is something like:

SELECT *
FROM TABLE_1
WHERE CONCAT(COL_1, ',', COL_2) IN (
    'object_1_val1,object_2_val1',
    'object_1_val2,object_2_val2',
    'object_1_val3,object_2_val3',
    'object_1_val4,object_2_val4',
    ...
);