We are using Spring 4.1.6 (MVC, TM, and JDBC), running under Java 8, against an Oracle 12c database. Let's say for now this is a database of persons.
I have a stateless @Service (called PersonService) with a @Transactional method (called searchPersons()) that currently makes use of several injected stateless @Repository DAOs in the following fashion:
- Start transactional context (implicitly), ensuring that subsequent operations all use the same Connection and hence the same Oracle session.
- Clear out all records in a TEMP_PERSON_IDS global temporary table (there is one of these per Oracle session).
- Do query #1 (using some given search criteria) to just search for the IDs of desired persons, and store these in TEMP_PERSON_IDs. There might be up to ~10,000. This is the only writing done in this "transaction".
- Do query #2, joining with PERSONS_CORE table to get the core information of all persons in TEMP_PERSON_IDS.
- Do query #3, joining with PERSON_ADDRESSES table to get the addresses of all persons in TEMP_PERSON_IDS (possibly multiple per person).
- Do query #4, joining with PERSON_VEHICLES table to get the vehicles of all persons in TEMP_PERSON_IDS (possibly multiple per person).
- Do query #5, joining with PERSON_BANK_ACCOUNTS table to get the bank accounts of all persons in TEMP_PERSON_IDS (possibly multiple per person).
- Do query #6 ... #15 likewise, where each query joins TEMP_PERSON_IDS with different tables to return very different collections of Person-related domain objects.
- Throw the various collections of Person-related domain objects together in a manner useful for the application, and return them.
In single-threaded form, it all works great -- with a mere 15 queries, I can get hundreds of properties across dozens of tables, regardless of how many Persons are returned by the original query.
But everything above has to be as blindingly fast as possible, and I want to multi-thread queries #2..#15, using an ExecutorService backed by a thread pool or similar.
The challenge, though, is that if I delegate a given DAO's work to another thread, the DAO (backed by a JDBCTemplate, backed by a DataSource) will not use the Connection stored in the Spring TM's ThreadLocal dictionary. So it sees a different session, with the wrong TEMP_PERSON_IDs table.
What I want, I think, is to create the various DAOs on-demand in the searchPersons() method, backed by SingleConnectionDataSource objects that use the same Connection in the searchPersons() transactional context. Then I would build a Future around the DAO invocation that could be submitted to the Executor.
Question: is there an elegant way to do this in Spring? Or will I have to forgo Spring bean initialization and manually construct these DAOs, being certain to inject all their dependencies (jdbcTemplates, etc)?
