Batch Request Merging in Spring Boot to Reduce Database Connections

The article explains the problem of multiple concurrent requests each opening a separate database connection, which wastes resources, and proposes merging these requests into a single batch SQL query on the server side.

Technical Means

LinkedBlockingQueue – a blocking queue used to collect incoming requests.

ScheduledThreadPoolExecutor – a scheduled thread pool that periodically processes the queued requests.

CompletableFuture – used to return results asynchronously (originally without timeout support).

Code Implementation

Query User Service Interface

public interface UserService {
    Map
queryUserByIdBatch(List
userReqs);
}

User Service Implementation

@Service
public class UserServiceImpl implements UserService {
    @Resource
    private UsersMapper usersMapper;

    @Override
    public Map
queryUserByIdBatch(List
userReqs) {
        List
userIds = userReqs.stream()
            .map(UserWrapBatchService.Request::getUserId)
            .collect(Collectors.toList());
        QueryWrapper
queryWrapper = new QueryWrapper<>();
        queryWrapper.in("id", userIds);
        List
users = usersMapper.selectList(queryWrapper);
        Map
> userGroup = users.stream()
            .collect(Collectors.groupingBy(Users::getId));
        HashMap
result = new HashMap<>();
        userReqs.forEach(val -> {
            List
usersList = userGroup.get(val.getUserId());
            if (!CollectionUtils.isEmpty(usersList)) {
                result.put(val.getRequestId(), usersList.get(0));
            } else {
                result.put(val.getRequestId(), null);
            }
        });
        return result;
    }
}

Batch Request Service

@Service
public class UserWrapBatchService {
    @Resource
    private UserService userService;
    public static int MAX_TASK_NUM = 100;
    private final Queue
queue = new LinkedBlockingQueue();

    @PostConstruct
    public void init() {
        ScheduledExecutorService scheduledExecutorService = Executors.newScheduledThreadPool(1);
        scheduledExecutorService.scheduleAtFixedRate(() -> {
            int size = queue.size();
            if (size == 0) return;
            List
list = new ArrayList<>();
            for (int i = 0; i < size; i++) {
                if (i < MAX_TASK_NUM) {
                    list.add(queue.poll());
                }
            }
            List
userReqs = new ArrayList<>();
            for (Request request : list) {
                userReqs.add(request);
            }
            Map
response = userService.queryUserByIdBatch(userReqs);
            for (Request request : list) {
                Users result = response.get(request.requestId);
                request.completableFuture.complete(result);
            }
        }, 100, 10, TimeUnit.MILLISECONDS);
    }

    public Users queryUser(Long userId) {
        Request request = new Request();
        request.requestId = UUID.randomUUID().toString().replace("-", "");
        request.userId = userId;
        CompletableFuture
future = new CompletableFuture<>();
        request.completableFuture = future;
        queue.offer(request);
        try {
            return future.get();
        } catch (InterruptedException | ExecutionException e) {
            e.printStackTrace();
        }
        return null;
    }

    public class Request {
        String requestId;
        Long userId;
        CompletableFuture
completableFuture;
        // getters and setters omitted for brevity
    }
}

Controller Invocation

@RequestMapping("/merge")
public Callable
merge(Long userId) {
    return () -> userBatchService.queryUser(userId);
}

A high‑concurrency test using 30 threads demonstrates that the server merges dozens of requests into a single SQL call, significantly reducing the number of database connections.

Important Issues

Java 8's CompletableFuture lacks a built‑in timeout mechanism.

The generated SQL has length limits, so the batch size must be capped (the example uses MAX_TASK_NUM ).

Using Queue with Timeout to Solve CompletableFuture Timeout Limitation

The article introduces an alternative implementation that replaces CompletableFuture with a LinkedBlockingQueue that supports timed polling, allowing a 3‑second timeout for each request.

@Service
public class UserWrapBatchQueueService {
    // similar structure to UserWrapBatchService but uses LinkedBlockingQueue
usersQueue
    // request method polls the queue with a timeout of 3000 ms
}

Conclusion

Request merging and batch processing can dramatically save connection resources for databases or remote services, though it introduces a small waiting latency and is therefore unsuitable for low‑concurrency scenarios.