When calling Spring Boot REST API multiple times using the same JDBC connection

Question

I have a REST web service written in Java/Spring Boot, running on Tomcat 9.

I need to create a MySQL temporary table in one web service call and access it in another service call. Since the temporary table is connection scoped, I need to use the same connection I used when creating the table in the first call and in the "select" query in the second call.

The problem is that Tomcat manages JDBC connections in a connection pool. In the second service call, no matter how many JDBC connections I create, I can select any one connection from the connection pool. In this case, the temporary table will not be visible.

My question is: How can I select the same connection from the connection pool? Is there any kind of logo/label that differentiates them?

Answer 1

Even if you could ensure that the "same" connection is used on subsequent requests, this won't help because part of allocating a connection from the pool involves resetting things related to session state.

This is by design and a good thing because otherwise you could leak private data from one database session to another unrelated session. For example, this would be a problem if one user stored some sensitive data in a temporary table and then another user's database session could read that temporary table. The same applies to outstanding transactions, session variables, and connection character sets. All of these are reset when a connection is allocated from the pool.

So if you use temporary tables, you should only use them during the same request because they will be deleted after the connection is used.

If you need to use the data from the previous request in the next request, you can:

• Store data in non-temporary tables.
• Store data in cache.
• Write a Java background service to process the data so that you can create and use temporary tables outside of requests. This is more work and may be too complex for a simple web application, but in some services this is the best solution.