CODEWITHSUNDEEP
javaoracle-databasespring-jdbc
Jeff Cook
Jeff Cook

Reputation: 8774

How to handle 999 size in Spring JDBC native Query IN parameter?

I am using the using Spring Batch + Spring JDBC. In my project, we're passing list of say Account to IN query of native SQL. In IN paramater we can max pass the 999 records and remaining list we again need to process.

I developed the following code but that doesn't works well if I have more than 2000 records. How can I make the dynamic, so if any list values comes in code should generate result ? 

if(accNumList.size() < 999) {
    Map<String, Set<Integer>> singletonMap = Collections.singletonMap("valSet1", accNumList);
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.putAll(singletonMap);
    paramMap.put("isFlag", "Y");

    List<Map<String, Object>> queryForMap = namedParameterJdbcTemplate.queryForList(sql, paramMap);
}else {
    int sizeOfRecords = accNumList.size();
    // convert set to List
    List<Integer> data = new ArrayList<>(accNumList);
    List<Integer> subItems = data.subList(0, 999);

    Map<String, List<Integer>> singletonMap = Collections.singletonMap("valSet1", subItems);
    Map<String, Object> paramMap = new HashMap<>();
    paramMap.putAll(singletonMap);  
    List<Map<String, Object>> queryForMap = namedParameterJdbcTemplate.queryForList(sql, paramMap);

    // remaining
    int remaining = sizeOfRecords - 999;
    List<Integer> newsubItems = data.subList(1000, remaining);
    Map<String, List<Integer>> newsingletonMap = Collections.singletonMap("valSet1", newsubItems);

    Map<String, Object> newparamMap = new HashMap<>();
    newparamMap.putAll(newsingletonMap);

    List<Map<String, Object>> newqueryForMap = namedParameterJdbcTemplate.queryForList(sql, newparamMap);

    //final
    List<Map<String, Object>> queryForMapFinal = new ArrayList<>();
    queryForMapFinal.add(paramMap);
    queryForMapFinal.add(newparamMap);
}

Upvotes: 0

Views: 942

Answers (1)

Karol Dowbecki
Karol Dowbecki

Reputation: 44952

Working around the IN limit is inefficient, building a JDBC query in Java is not always the right tool for the job. Consider the following:

  1. Thousands of bound values will result in potentially megabytes of SQL. It will take a long time to send this SQL to the database. The Database might take longer to read the SQL text than execute it as per Tom's answer to "Limit and conversion very long IN list: WHERE x IN ( ,,, ...)" question.

  2. It will be inefficient due to SQL parsing. Not only does it take a long time to parse this long SQL but each invocation has a different number of bound parameters which will be parsed and planned separately (see this article which explains it).

  3. There is a hard limit of bound parameters in a SQL statement. You can repeat the OR a few times to work around the IN limit but you are going to hit the SQL statement limit at some point.

For those types of queries, it's usually better to create temporary tables. Create one before your query, insert all the identifiers into it and join it with the entity table in your query to simulate the IN condition.

Ideally, you can replace the Java code with a stored procedure, especially if you are pulling out tens of thousands of identifiers from the database just to pass them back to the database in the next query.

Upvotes: 2

Related Questions