I use a 2-tier approach myself.
Applications frequently interact with a database using a related set of records in a single transaction. For example, you might pull all the employees from a department in a company. This makes a "working set" and so rather than make the application code directly yank in all the different types of records using DAOs, I use a persistence service layer. Working set objects in this layer have live access to the database (more about this in a minute), and do work within a single database transaction. So this is a business-oriented data access layer.
Underneath that layer is my DAO layer where the DAO objects themselves live. I mentioned that the service layer has "live access" to the database, but what I actually mean is that the service layer does its database I/O by using the DAOs in the DAO layer.
The DAOs are pretty much just finder/CRUD objects. Each DAO is responsible for access to a single table, or in some cases, a parent/child table set. There's no sense of business process here, just raw I/O. The transactional context is inherited from the service method that uses the DAOs. Application logic always talks to service layer objects and methods, never to DAO layer methods. In the relatively rare case where the application logic just needs basic DAO functions, I write a service object that acts as a façade for that DAO. It may be a little more work and overhead, but it pays for itself because it's clean and predictable - you never have to worry about which layer you're getting data from.
I'm generally using the
Java Persistence Architecture (JPA), which works very well with this arrangement. I'm also generally using an Inversion of Control (IoC) injection framework, and that handles plugging in the EntityManager connections for each DAO.
If you're doing raw
JDBC, you can do something similar, although in truth, if you need that much structure, you'll get more maintainable code and a generally more efficient application by using JPA or something like that.
