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<String, Users> queryUserByIdBatch(List<UserWrapBatchService.Request> userReqs);
}

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

    @Override
    public Map<String, Users> queryUserByIdBatch(List<UserWrapBatchService.Request> userReqs) {
        List<Long> userIds = userReqs.stream()
            .map(UserWrapBatchService.Request::getUserId)
            .collect(Collectors.toList());
        QueryWrapper<Users> queryWrapper = new QueryWrapper<>();
        queryWrapper.in("id", userIds);
        List<Users> users = usersMapper.selectList(queryWrapper);
        Map<Long, List<Users>> userGroup = users.stream()
            .collect(Collectors.groupingBy(Users::getId));
        HashMap<String, Users> result = new HashMap<>();
        userReqs.forEach(val -> {
            List<Users> 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<Request> queue = new LinkedBlockingQueue<>();

    @PostConstruct
    public void init() {
        ScheduledExecutorService executor = Executors.newScheduledThreadPool(1);
        executor.scheduleAtFixedRate(() -> {
            int size = queue.size();
            if (size == 0) return;
            List<Request> list = new ArrayList<>();
            for (int i = 0; i < size && i < MAX_TASK_NUM; i++) {
                list.add(queue.poll());
            }
            List<Request> userReqs = new ArrayList<>(list);
            Map<String, Users> 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<Users> 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<Users> completableFuture;
    }
}

Controller exposing the merged endpoint:

@RequestMapping("/merge")
public Callable<Users> 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<Users> 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.