DAO design pattern and using it across multiple tables

I'm looking for feedback on the Data Access Object design pattern and using it when you have to access data across multiple tables. It seems like that pattern, which has a DAO for each table along with a Data Transfer Object (DTO) that represents a single row, isn't too useful for when dealing with data from multiple tables. I was thinking about creating a composite DAO and corresponding DTO that would return the result of, let's say per开发者_如何学运维forming a join on two tables. This way I can use SQL to grab all the data instead of first grabbing data from one using one DAO and than the second table using the second DAO, and than composing them together in Java.

Is there a better solution? And no, I'm not able to move to Hibernate or another ORM tool at the moment. Just straight JDBC for this project.

I would agree with your approach. My DAOs tend to be aligned more at the object level, rather than from a DB Table perspective. I may manage more than one object through a DAO, but they will very likely be closely related. There is no reason not to have SQL accessing two tables living in one DAO.

And for the record, I have banished the acronym DTO from my vocabulary and code.

Ideally, how you store your data in a database, and then how you access them, should be derived from the nature of the relationship among the domain entities in your domain model. That is, Relational Model should follow from Domain Model. For example, if you have two entities, say, User and Address.

Scenario #1: Address are never accessed independently, they are always an attribute of User. In this case, Address is a Value Object and User is an Entity, and there are guides on how to store this relationship. One way is to store Address attributes of Address alongside of attributes of User, in a single table. In this case, UserDao will handle both objects.

Scenario #2: Address can be associated to a User, but also can be separate on its own, an entity. In this case, an approach different from the first one is needed. You may have a separate DAO and table for the Address type.

My point is, that more often this important idea is ignored that Domain Model should be the core of the application, driving other layers.

For instance, if your domain model is properly define and you are well aware of the type of entities you have and the relationship among them, then your persistence (relational tables and their relationships, your DAOs, etc) will evolve as a very logical consequence of what you have in the domain model.

In other words, if you spend some time studying your model, you will be able to trace your problem in determining how to organize your DAOs to a place in the domain model. If you can clearly define the type of the objects and the nature of relationship among them in the domain model, it will, help you resolve your problem in DAL layer.