Spring JdbcTemplate batch query with IN clause

當需要用List資料中的每一筆值去查資料表時，用SQL的IN一次查多筆值會比用for迴圈逐筆查來得有效率，但若List元素數量很多時，可能會超過語法的長度限制，或許需要進行分批IN查詢。

下面將idList分批使用Spring JdbcTemplate進行IN查詢。

package com.abc.dao;

import java.util.ArrayList;
import java.util.Collections;
import java.util.List;

import org.apache.commons.collections4.CollectionUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class EmployeeDao {

    @Autowired
    private JdbcTemplate jdbcTemplate;
    
    public List<Employee> findByIds(List<String> idList) {
        String sql = "select * from EMPLOYEE where ID in (%s)";
        sql = String.format(FIND_BY_IDS, String.join(",", Collections.nCopies(idList.size(), "?")));
    
        List<Employee> result = new ArrayList<>();
        final int batchSize = 100; // up to 100
        List<String> paramList = new ArrayList<>(batchSize);
        for (int i = 0; i < idList.size(); i++) {
            paramList.add(idList.get(i));
            if (paramList.size() == batchSize) {
                List<Employee> employeeList = jdbcTemplate.queryForList(
                        sql, 
                        paramList.toArray(),
                        (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("name")));
                if (CollectionUtils.isNotEmpty(employeeList)) {
                    result.addAll(employeeList);                
                }
                paramList.clear();
            }
        }
        
        if (!paramList.isEmpty()) {
            List<Employee> employeeList = jdbcTemplate.queryForList(
                    sql, 
                    paramList.toArray(),
                    (rs, rowNum) -> new Employee(rs.getInt("id"), rs.getString("name")));
            if (CollectionUtils.isNotEmpty(employeeList)) {
                result.addAll(employeeList);                
            }
        }
        
        return result;
    }
}

• Using a List of Values in a JdbcTemplate IN Clause