Sorry for the rather long answer.
I think, for a modern application approach, you should consider the connection to be injected into the dao, not taken from a factory.
Your problem is, as I understand it, that from the perspective from the service layer, you want small grained DAO functionality (CRUD) that should be combined to more complex database operations on the service layer. You want to ensure the integrity of the complex operations using db transactions. The issue arising is the visibility of transaction handling and database access outside the dao classes. Your business code gets dirty with transaction and connection handling code, distracting away from the BL to happen.
This is a common problem also on ORM based approaches. For example on plain Hibernate applications, you need to decide, if you are already in a running session or if you need to start a new session. If you encapsulate the hibernate code into seperate classes, you need to handle the opening and closing of the session in a smart way to avoid code duplication.
Another arising issue is the stacking of methods: If each methods handles the connection/transaction, you cannot easily have one method call another.
I have some ideas for you, that you may want to consider:
First on the stacking issue:
- Wrapping the connection/transaction methods with reference counting
- The connections are given out using a connection/transaction provider. commit and rollback are methods of the provider. If you open a transaction twice, you need to commit it twice to do the actual commit. On rollback, you may want to reconsider the strategy and rollback immediately ignoring the references.
This allows to start transactions and connections on any level of your code, calling beginTransaction is idempotent regarding the database, but will raise a counter. You need to take care a lot, not to skip a commit on your code, otherwise your transaction boundaries are not going to be executed.
Using this method will clutter your code at every level with obtainConnection/startTransaction/commit/rollback/returnConnection calls. There is a lot of boilerplate here.
Yet, keep this method in mind, I solved the stacking issue with this for my frameworks. Now, we need to find ways to remove all this connection clutter from our code. Find the following approaches useful, with an emphasis on the third one.
Dependency injection of connection: Your DAOs are not singletons but throw-away objects, receiving the connection on creation time. The calling code will control the connection creation for you.
- PRO: easy to implement
- CON: DB connection preparation / error handling in the business layer
- CON: DAOs are not singletons and you produce a lot of trash on the heap (your implementation language may vary here)
- CON: Will not allow stacking of service methods
Bind the connection/transaction to the running thread: Your DAOs can be singletons. You bind runtime information in a thread local context object. The business layer will initialize the context for the DAO. For web applications, this can happen on request start.
- PRO: DAOs can be singletons
- PRO: easy to implement
- PRO: given a matching call-lifecycle, the database setup is not visible to the service layer
- CON: if your call-lifecycle has no good initialization point, this is more complicated.
- CON: each call-lifecycle has one transaction, stopping and re-starting a transaction can be a mess.
- CON: Connection handling still visible outside the DAO layer
- CON: Nesting call-lifecycles in the same thread will pose the stacking issue again.
Use Proxy classes to implement the connection handling transparently on a context object in the thread during service method invocation:
- The DAO and Service objects are singletons, the services are wrapped with a proxy. The invocation handler of the proxy will prepare the connection object outside the service class.
- The service class decides with an annotation on the method, if there should be one transaction or immediate commits.
- Service can define a transaction boundary inside a service class using lambda functions.
- PRO: No more connection / transaction handling inside DAO or Service class
- PRO: Service is in full control over transaction boundaries by annotations on method calls.
- PRO: DAO will find connection in context and will not care on transaction boundaries.
- PRO: DAO functions can be fine grained and combined to complex operations without additional coding.
- PRO: Services and DAO are singletons.
- PRO: Stacking is solved by approach given above, doing reference counting. Since the invocation handler is the place to obtain the connection, you will not suffer from lost commit/close as the invocation handler will take care of it on return from the proxied object.
- PRO: works with call-lifecycles like on web applications
- PRO: This is how the frameworks do it :-)
- CON: Complex, yet straight forward implementation (you need annotations, proxy, interfaces, context, a factory for your proxied service implementations, possibly major refactorings of your code)
I have implemented all three approaches (without Spring etal). The last one is especially very powerful and cleans up your service infrastructure a lot. You will demystify the frameworks - it is fun to do :-)
Yet: Depending on your needs, you may be better off with a lightweight framework provided by some project.
For the case where you need to implement a transaction boundary inside an otherwise transaction-less service function, you can:
- refactor the function into it's own service method with the appropriate transaction annotation
- write a class that executes a lambda function in a transactional context and returns you the result