The book Pattern of Enterprise Application Architecture has the following description over the Table Data Gateway pattern:
“A Table Data Gateway holds all the SQL for accessing a single table or view: selects, inserts, updates, and deletes. Other code calls its methods for all interaction with the database.”
They do not say anything about queries that use multiple tables. Where do I have to put a query that uses multiple tables and generates a result that has as much in common for every single table?
Despite appearing in a book authored by Martin Fowler (for whom I gave a lot of respect), I can’t understand why one would use this pattern. As you have realised, it is pretty limited as most business operations require accessing multiple tables, and sometimes multiple data sources (databases, ldap repositories etc). A secondary concern is performance – you would need to fudge joins between tables at the Java level. When large tables are involved, SQL will generally do this much more elegantly and quicker.
I think the Data Access Object (DAO) pattern is far more useful. The interface should reflect business actions (e.g. placeOrder(), shipOrder(), chargeCreditCard(), listOutstandingOrdersForCustomer() etc) and should hide the fact that multiple tables/repositories etc are involved in a transaction. This also aids testing, as you can easily stub out the database with mock code.
2