spring jdbctemplate - how to bind list (consisting of more than 1 parameter) into query args

Question

I want to know how to bind list of objects into sql with more than 1 parameter in 'where' clause.

Select * from order where order_id = ? and action_code = ?

Data is like

List<Order> orderList = new ArrayList<>();
orderList.add(1,"KP");
orderList.add(2,"DP");
orderList.add(3,"KP");

Here is what i tried I have a list of Orders. Each order has order id (long) and action code (string). Now i need to query the db with order id and action code as the parameters in where clause.

Select * from order where order_id = ? and action_code = ?.

I tried to pass an array of objects in args. [ this dint work ].

Object[] args = new Object[ size of orderList ]
int i = 0
for (Order order : orderList)
{
   args[i] = new Object[]{order.getOrderId(), order.getActionCode()};
   i++;
}

I am firing the query as below

jdbcTemplate.query(sql, args, rowMapper);

When i run the app, i get below error.

org.springframework.jdbc.UncategorizedSQLException: PreparedStatementCallback; uncategorized SQLException for SQL [SELECT * where ORD_I = ? AND ACT_C = ?]; SQL state [99999]; error code [17004]; Invalid column type; nested exception is java.sql.SQLException: Invalid column type

However if i pass data as below it works

jdbcTemplate.query(sql, new Object[] {orderList.get(0).getOrderId, orderList.get(0).getActionCode()}, rowMapper);

What i have tried so far I have provided the Type as below [ in DB, order_id is number(38), action_code is Char(2))

int[] types = new int[] { java.sql.Types.NUMERIC, java.sql.Types.CHAR 
};
jdbcTemplate.query(sql, args, types, rowMapper);

i got an exception saying . [Same error when i replace java.sql.Types.CHAR with java.sql.Types.VARCHAR ]

org.springframework.dao.InvalidDataAccessApiUsageException: args and argTypes parameters must match

The below set up works

 int[] types = new int[] { java.sql.Types.NUMERIC, java.sql.Types.CHAR 
    };
jdbcTemplate.query(sql, new Object[] {orderList.get(0).getOrderId, orderList.get(0).getActionCode()}, types, rowMapper);

i have referred below issues How set Array/List in Spring jdbcTemplate as parameter? - talks about passing one parameter from the list

how to bind a list of tuples using Spring JDBCTemplate? - talks about tuples. If i have to query 2 tables with tableA.id = tableB.id and tableA.id = ? and tableB.seq_id = ?, then the above solution doesnt work.

I googled a lot but now i have hit a road block. Kindly help.

Answer 1

I know this solution doesn't work with lists, but I'd recommend using NamedParameterJdbcTemplates instead of the regular jdbcTemplate. It's much more straightforward and a relatively minor change:

SQL:

Select * from order where order_id = :orderId and action_code = :actionCode

orderID and actionCode correspond to the String key values in your parameters HashMap (shown below).

Java:

Map<String, Object> parameters = new HashMap<>();
parameters.put("orderID","KP");
parameters.put("actionCode","DP");

namedParameterJdbcTemplate.execute(sqlQueryString, parameters);

You load all your parameters into a key-value map with a string key.

Edit to answer comment:

Why not execute the query inside the loop, and save the results to a map? I'm thinking for pseudo-code:

for (set in parameterSet){
parameters.put(set1, set2); 
ResultSet rs = jdbcTemplate.execute(queryString, parameters); 
otherMap.put(set1 + set2, rs);}

Here set is a combination of the orderID and actionCode parameters, parameterSets are all the combinations (sets) of orderID and actionCode you want to query, and otherMap is some map collection you create to persist the data beyond the for loop, using the concat of your 1st and 2nd set parameters as the key, and the result set as the value.

Edit for follow up comments:

If you have 1000 unique pairs, then that means that each of those unique pair of parameters should return distinct, mutually exclusive ResultSets. For example, feeding in parameters A and B in the WHERE condition should return different ResultSets than feeding in parameters A and C. I don't see a shortcut around this. You're essentially asking 1000 different questions, each with a different answer. The only way to ensure you get all your ResultSets and execute only one query is by feeding in all possible values for the first parameter and second parameter:

WHERE parameter1 IN (... all possible values for parameter1) AND
parameter2 IN (... all possible values for parameter2)

However, this would produce significantly more rows than you'd want. You'd then have to apply your own logic programmatically to filter out the result sets that don't belong to the set of unique parameter combinations.