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

I need to create a MySQL temporary table in one web service call and access it later in another service call. As temporary tables are connection scoped, I need to use the same connection used to create the table during the first call 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,whenever I create a JDBC connection, any connection from the pool can be picked up. In this case, the temporary table will not be visible.

My question is: how can I pick the same connection from the connection pool? Is there any kind of id/tag that distinguishes them?

CodePudding user response：

It would not help even if you could ensure that the "same" connection is used on subsequent requests, because part of allocating a connection from the pool involves resetting things related to session state.

This is by design and it's a good thing, because otherwise you could leak private data from one database session to another unrelated session. That would be a problem for example if one user stored some sensitive data in a temporary table, and then that temp table were readable by another user's database session. Likewise for things like unfinished transactions, session variables, and connection character set. All of these are reset when a connection is doled out by the pool.

So if you use temporary tables, you should use them only during the same request, because they will be dropped when you are done using that connection.

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

• Store data in a non-temporary table.
• Store data in a cache.
• Write background service in Java to process the data, so you have code creating and using the temp tables, running asynchronously to the requests. This is a lot more work, and probably overkill for a simple web app, but there are some services where it's the best solution.