How to Split Massive SQL IN Queries with Spring Boot 3 Using AOP and Multithreading

When an SQL statement contains an IN clause with a large number of values, it can cause performance bottlenecks or errors, especially in MySQL where the size is limited by max_allowed_packet and in Oracle where the IN list is capped at 1000 items.

Typical remedies include using temporary tables or splitting the values into batches.

2. Practical Implementation

2.1 Custom Annotations

@Target(ElementType.METHOD)
@Retention(RetentionPolicy.RUNTIME)
public @interface SplitQuery {
    /** Thread pool bean name; must be Executor */
    String executorName() default "";
    /** Batch size */
    int batchSize() default 100;
    /** Result handler bean name; must be ResultHandler */
    String handlerName() default "";
}

@Target(ElementType.PARAMETER)
@Retention(RetentionPolicy.RUNTIME)
public @interface BatchParam {}

2.2 Result Handler Definition

public interface ResultHandler<T> {
    T process(List<Object> result);
}

public class DefaultResultHandler implements ResultHandler<Object> {
    @Override
    public Object process(List<Object> result) {
        return result;
    }
}

2.3 Aspect Definition

@Aspect
@Component
public class SplitQueryAspect implements ApplicationContextAware {
    private static final Logger logger = LoggerFactory.getLogger(SplitQueryAspect.class);
    private static final Executor defaultExecutor = Executors.newVirtualThreadPerTaskExecutor();
    private ApplicationContext context;

    @Pointcut("@annotation(sq)")
    private void splitPc(SplitQuery sq) {}

    @Around("splitPc(sq)")
    public Object splitQueryAround(ProceedingJoinPoint pjp, SplitQuery sq) throws Throwable {
        int batchSize = sq.batchSize();
        Executor executor = getExecutor(sq.executorName());
        Object[] args = pjp.getArgs();
        MethodSignature ms = (MethodSignature) pjp.getSignature();
        Parameter[] parameters = ms.getMethod().getParameters();
        int index = -1;
        for (int i = 0; i < parameters.length; i++) {
            if (parameters[i].getAnnotation(BatchParam.class) != null) {
                index = i;
                break;
            }
        }
        if (index == -1 || !(args[index] instanceof List) || ((List<?>) args[index]).size() <= batchSize) {
            logger.info("Directly invoking target method...");
            return pjp.proceed();
        }
        ResultHandler<?> resultHandler = getResultHandler(sq.handlerName());
        List<?> data = (List<?>) args[index];
        List<?> partitions = Lists.partition(data, batchSize);
        List<Object> result = partitions.stream()
            .map(chunk -> CompletableFuture.supplyAsync(() -> {
                try {
                    Object[] newArgs = args.clone();
                    newArgs[index] = chunk;
                    logger.info("Processing batch: {}", newArgs[index]);
                    return pjp.proceed(newArgs);
                } catch (Throwable e) {
                    return null;
                }
            }, executor))
            .map(CompletableFuture::join)
            .filter(Objects::nonNull)
            .collect(Collectors.toList());
        return resultHandler.process(result);
    }

    private Executor getExecutor(String name) {
        if (StringUtils.hasLength(name)) {
            try {
                return context.getBean(name, Executor.class);
            } catch (Exception e) {
                logger.warn("Bean {} not found, using default executor", name);
            }
        }
        return defaultExecutor;
    }

    private ResultHandler<?> getResultHandler(String name) {
        if (StringUtils.hasLength(name)) {
            try {
                return context.getBean(name, ResultHandler.class);
            } catch (Exception e) {
                logger.warn("ResultHandler {} not found, using default", name);
            }
        }
        return new DefaultResultHandler();
    }

    @Override
    public void setApplicationContext(ApplicationContext ctx) {
        this.context = ctx;
    }
}

2.4 Business Code

Repository interface:

public interface PersonRepository extends JpaRepository<Person, Long> {
    List<Person> findByAgeAndNameContainingAndIdIn(Integer age, String name, List<Long> ids);
}

Service method with the annotation:

@Service
public class PersonService {
    private final PersonRepository personRepository;
    public PersonService(PersonRepository personRepository) {
        this.personRepository = personRepository;
    }

    @SplitQuery(batchSize = 2, handlerName = "personResultHandler")
    public List<Person> query(Integer age, @BatchParam List<Long> ids, String name) {
        return personRepository.findByAgeAndNameContainingAndIdIn(age, name, ids);
    }
}

Result handler implementation:

@Component("personResultHandler")
public class PersonInResultHandler implements ResultHandler<List<Person>> {
    @Override
    public List<Person> process(List<Object> result) {
        if (result == null) return null;
        return result.stream()
            .flatMap(obj -> ((List<Person>) obj).stream())
            .collect(Collectors.toList());
    }
}

Controller test:

@RestController
@RequestMapping("/persons")
public class PersonController {
    private final PersonService personService;
    public PersonController(PersonService personService) {
        this.personService = personService;
    }

    @GetMapping("/query")
    public ResponseEntity<List<Person>> query() {
        return ResponseEntity.ok(personService.query(11,
            List.of(1L,2L,3L,4L,5L), "a"));
    }
}

Running the endpoint shows the SQL statements being split across multiple threads, as illustrated by the screenshots.