Batch Request Merging in Java to Reduce Database Connections

The author introduces the problem of multiple users (ids 1, 2, 3) each sending a request to fetch their basic information, which would normally trigger three separate database queries and waste valuable connection resources.

To solve this, the article proposes aggregating the requests on the server side, issuing a single SELECT ... WHERE id IN (...) statement, and then distributing the results back to the original callers based on a unique request ID.

Technical means include a LinkedBlockingQueue for request buffering, a ScheduledThreadPoolExecutor that periodically consumes the queue, and Java 8 CompletableFuture objects to hold the asynchronous results.

Code implementation – Service layer :

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

@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
list = userGroup.get(val.getUserId());
            if (!CollectionUtils.isEmpty(list)) {
                result.put(val.getRequestId(), list.get(0));
            } else {
                result.put(val.getRequestId(), null);
            }
        });
        return result;
    }
}

Batch request handling service (queues requests, merges them every 10 ms, and completes the associated futures):

@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 executor = Executors.newScheduledThreadPool(1);
        executor.scheduleAtFixedRate(() -> {
            int size = queue.size();
            if (size == 0) return;
            List
list = new ArrayList<>();
            for (int i = 0; i < size && i < MAX_TASK_NUM; i++) {
                list.add(queue.poll());
            }
            List
userReqs = new ArrayList<>(list);
            Map
response = userService.queryUserByIdBatch(userReqs);
            for (Request r : list) {
                r.completableFuture.complete(response.get(r.requestId));
            }
        }, 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 static class Request {
        String requestId;
        Long userId;
        CompletableFuture
completableFuture;
    }
}

Controller exposing the merged endpoint:

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

A high‑concurrency test creates 30 threads, each issuing three requests concurrently, demonstrating that the server merges dozens of requests into a few SQL statements.

Issues to watch :

Java 8 CompletableFuture lacks a built‑in timeout; the article later shows a queue‑based workaround.

SQL statements have length limits, so the batch size is capped by MAX_TASK_NUM .

Queue‑based timeout solution replaces the future with a LinkedBlockingQueue<Users> that blocks for a configurable period (e.g., 3 seconds) when polling for the result, thus providing a timeout mechanism.

public Users queryUser(Long userId) {
    Request request = new Request();
    request.requestId = UUID.randomUUID().toString().replace("-", "");
    request.userId = userId;
    LinkedBlockingQueue
usersQueue = new LinkedBlockingQueue<>();
    request.usersQueue = usersQueue;
    queue.offer(request);
    try {
        return usersQueue.poll(3000, TimeUnit.MILLISECONDS);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    return null;
}

In summary, merging requests into a single batch query can dramatically reduce database connection usage and improve throughput for high‑traffic services, at the cost of a small additional latency before the actual business logic runs.