You have below scenario:
Spring boot app has a jpa layer, make store procedure call, the app received one request with a parameter array (contains 100 parameters used to pass to jpa call), the stored procedure can only process one parameter at a time, the service layer has a loop logic to loop through parameters array and call jpa layer per parameter, how many db connections would need for this scenario?
To determine the number of connections used, we have to look at how Spring manages Transactions. In a standard Spring Boot JPA application, connection usage is tied directly to the transaction boundary (usually defined by @Transactional).
Here is the breakdown of how many database connections will be used in your scenario.
The Short Answer
- Best Case: 1 Connection
- Worst Case: 100 Connections
Scenario 1: The Best Case (Single Transaction)
Condition: The Service layer method is annotated with @Transactional.
If the loop resides inside a method marked with @Transactional, Spring’s TransactionManager will bind a single database connection to the current thread when the method starts.
- How it works: The first JPA call requests a connection; the manager provides one and holds onto it. Every subsequent call in that loop (all 100 iterations) will reuse that exact same connection.
- Why: This ensures atomicity. If the 99th call fails, the previous 98 can be rolled back.
- Result: 1 Connection used for the duration of the loop.
Scenario 2: The Worst Case (No Explicit Transaction)
Condition: There is no @Transactional on the Service layer, and the JPA repository methods are using default settings.
In this case, each call to the JPA layer is treated as an individual, atomic unit of work.
- How it works: For every iteration of the loop, the JPA provider (Hibernate) asks the connection pool (HikariCP) for a connection. It opens the connection, executes the stored procedure, commits, and returns the connection to the pool.
- Why: Because there is no “surrounding” transaction to keep the connection open, the framework must fetch and release a connection 100 times.
- Result: 100 sequential connections used. While only 1 connection is active at any single microsecond, you are performing the “borrow and return” handshake 100 times, which is a significant performance overhead.
Comparison Table
| Feature | Best Case (Transactional) | Worst Case (Non-Transactional) |
|---|---|---|
| Total Connections | 1 | 100 (sequential) |
| Performance | High (Low overhead) | Low (High connection acquisition overhead) |
| Data Integrity | All-or-nothing (Atomic) | Partial success possible (Non-atomic) |
| Database Load | Lower | Higher (Session management overhead) |
Important Consideration: The “True” Worst Case
There is a “hidden” worst case regarding Concurrency. If 10 users hit this endpoint simultaneously:
- Transactional: 10 users 1 connection = 10 active connections held for the duration of the loop.
- Non-Transactional: 10 users 1 connection = 10 active connections, but they are released much faster, potentially allowing the pool to breathe—however, if your loop is fast, you could still exhaust the connection pool.
Recommendation
For a stored procedure loop, you should almost always use a Single Transaction. However, even better is to refactor the Stored Procedure to accept an Array or Table-Valued Parameter so you can make 1 single database call instead of 100. This reduces network latency (the “chatter”) between your app and the DB.
Would you like me to show you how to refactor the service layer to ensure it only uses one connection?