File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes OO, Patterns, UML and Refactoring and the fly likes Designing Table specific DAO Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Engineering » OO, Patterns, UML and Refactoring
Bookmark "Designing Table specific DAO" Watch "Designing Table specific DAO" New topic
Author

Designing Table specific DAO

Rajeshwari Natarajan
Ranch Hand

Joined: Mar 05, 2003
Posts: 67
Hi all,

I need advice on using the DAO pattern.

We have to develop a J2EE application with Oracle as database. There are 300 odd tables. We would not be going for any ORM tools since the cusotmer does not favor them.

1) One approach is to use one DAO for each database table/View.

- For searching against multiple tables, Database View will be used.
- All the insert, update, delete operations for that table will be handled by the same DAO.

The downside of this approach are
- Large number of DAOs - there is a DAO for each table and view in the database
- When data has to be inserted/updated in multiple tables, multiple DAOs have to be called. A helper class has to be introduced to handle the multiple DAO calls

2) The second approach is to use a single DAO for all related operations. The number of DAOs will be driven by the business functionality.

The downside of this approach are
- Difficult to maintain code, since the same table maybe getting updated (for different attributes) by different DAOs

Which of these approaches would you recommend, based on your experience.


regards<br />Rajeshwari. N
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Rajeshwari Natarajan:
We would not be going for any ORM tools since the cusotmer does not favor them.


Why is he not in favor of "them"? In which way is this a business decision at all?

Which of these approaches would you recommend, based on your experience.


If I were in that situation, I would start with a single DAO, and refactor as I see fit. To remove all that duplication that is likely to emerge, I'd expect to have to develop my own ORM tool in the middle run. Which isn't necessarily a bad thing - it will be an ORM tool that I fully understand, and that only does what I really need it to do. Still, it would be an ORM tool, so I'd feel a little bit worried about the customer not being in favor of them. I'd want to know what is root cause of this disfavour.


The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

Originally posted by Rajeshwari Natarajan:
Hi all,

I need advice on using the DAO pattern.

We have to develop a J2EE application with Oracle as database. There are 300 odd tables. We would not be going for any ORM tools since the cusotmer does not favor them.


Riight. So the customer is willing to pay for the J2EE application and a new custom ORM tool. What ever they want to pay for is fine by me, but personally I would get in my management's ass for letting the customer thing this is even remotely a good decision. Resolving all the ORM problems that have been solved already? Well, your paying...



Originally posted by Rajeshwari Natarajan:

1) One approach is to use one DAO for each database table/View.

- For searching against multiple tables, Database View will be used.
- All the insert, update, delete operations for that table will be handled by the same DAO.

The downside of this approach are
- Large number of DAOs - there is a DAO for each table and view in the database
- When data has to be inserted/updated in multiple tables, multiple DAOs have to be called. A helper class has to be introduced to handle the multiple DAO calls

2) The second approach is to use a single DAO for all related operations. The number of DAOs will be driven by the business functionality.

The downside of this approach are
- Difficult to maintain code, since the same table maybe getting updated (for different attributes) by different DAOs

Which of these approaches would you recommend, based on your experience.


Well you start with #1. Then you end up with #2 as a way to manage your transactions. But what you create in #2 is not so much a 'DAO'. I don't really have a name for it. So I do call it DAO, but its really a manipulator of DAOs.
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
I don't think it makes sense to ask that the no-ORM decision be overturned without knowing a bit more about the problem. It could be driven by a lot of domain experience.

I've worked on several systems where the database had grown and developed to a point where it did not suit any commercial ORM tool. I've also worked on systems where even a DAO was a complication which cost more than it was worth.

Can you tell us abot more about the kind of database operations your software will need to do?


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Rajeshwari Natarajan
Ranch Hand

Joined: Mar 05, 2003
Posts: 67
Usage of ORM tools is not the IT standard of the customer. So we really do not have a choice on this !

Regarding the nature of the application, it is going to be primarily transaction driven, and most of the screens will update 2 or more tables.

There are very few simple CRUD scenarios.
Just curious, would ORM tools prove advantageous in this scenario?
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Rajeshwari Natarajan:
Usage of ORM tools is not the IT standard of the customer.


I don't understand this. Are the also discouraging the use of for loops or something?

Or perhaps they fear that they have to pay for those tools? Are they aware of the fact that there are open source ORM tools?
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
My guess is that they have been "burned" in the past by spending a lot of time and money on trying to fit their application into the way an ORM tool wants to work. Applications where the database has grown as a driving force are often like this. I don't know any ORM tools which can talk to an application-specific PL/SQL interface, for example.

It's quite likely that this application won't need to access all of the database, anyway, so a "just enough" application could well be a good way of getting started. If later refactoring moves it toward a more generic ORM approach, great.
Mr. C Lamont Gilbert
Ranch Hand

Joined: Oct 05, 2001
Posts: 1170

I thought both hibernate and JDO allow direct SQL to the database. They just dont allow direct SQL to return 'objects.'

The very idea that ORM can't do something you can do without ORM is preposterous. ORM would be extremely advantageous.

The bottom line is either you use an existing ORM tool, or you create your own ORM. But there will be ORM. If you recreate it, then you will spend at least 50% of your time solving ORM issues that have been solved already. I can't imagine why anyone would want to do that.

I presently have an application that is implemented with two different persistence techniques, an ORM tool and raw SQL. I am really sick of maintaining the raw sql version, furthermore it was much more work to implement and it has less features.

However, if its a paying job, then doing it without ORM can guarantee a longer contract and more money in the end. And at their insistence.
Rajeshwari Natarajan
Ranch Hand

Joined: Mar 05, 2003
Posts: 67
i understand the support for ORM by all the respondents, but my question is regarding the better of the 2 approaches, given the fact that i CANNOT use a ORM tool.

The database has grown to be a huge one..and cannot be changed now.
Most of the application screens to be developed are transactional in nature.
For a typical screen, i have to fetch data from multiple tables. The screen will udpate 1 or more tables.

It is not going to be a simple CRUD on one table - just fetching data from a table and updating back in the same table.

Given this scenario, what would be the best approach for coding the data access layer?
manuel aldana
Ranch Hand

Joined: Dec 29, 2005
Posts: 308
It is not going to be a simple CRUD on one table - just fetching data from a table and updating back in the same table.


sorry, i am a bit confused. what will there ever be something else as CRUD:
Create
Read
Update
Delete



with DAO layers i often go for your 2nd option. grouping together a DAO to together belonging business cases. otherwise you end up with 300 DAOs (each for one table) which is quite a lot. anyway often you get operations where you update or delete data from different tables which have relations to each other.
[ August 09, 2006: Message edited by: manuel aldana ]

aldana software engineering blog & .more
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
I think the key point is not that the task is not CRUD, but that the task involves reading from, and writing to, multiple tables.

I must admit, my preferred solution to this is to use a lightweight framework which gets all the fiddly JDBC stuff about connections and statements (and in particular closing them in all cases) out of the way. An example call from a product I'm currently working on looks like:



This is the simplest I have managed to make striongly-typed database access in Java. Writing the data back is similar in structure:



I find that this approach allows me to make it pretty clear in the application code what is going on. For extra run-time flexibility I also have a version of this technique which takes a script name instead of the direct SQL, and loads the SQL from a properties file or whatever.

Is that the kind of solution you are looking for ?
[ August 10, 2006: Message edited by: Frank Carver ]
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
Neat.

I'd be tempted to use a scripting language to generate DAOs from DDL or metadata. We use a proprietary vendor RoseModel -> XML -> XSL -> Java thingy that I bet one could replace with a few hundred lines of REXX or whatever you prefer. That really is an ORM tool but you don't have to tell the customer.


A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
Stan Wrote: I'd be tempted to use a scripting language to generate DAOs from DDL or metadata.

I've done this a few times, and knocking around JavaRanch somewhere is "jenny", Paul Wheaton's metadata-to-java generator.

However, in the case which prompted this thread, this approach would not be particularly useful.

The whole point is that table-by-table analysis is not appropriate to an application which needs multi-table queries and updates. And, despite what some ORM-boosters like to think, many real applications need query and transaction tuning which won't work with table-by-table ORM models.
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
For multi-table queries we use database views. The tool can build a DAO for a view as easily as a single table. This is not dynamic as we have to build the views first, but it does the job. Our current vendor framework also allows us to override the SQL for a DAO but by then you're not getting much framework value.

I'd probably consider multi-table updates beyond my abilities to automate and fall back on a hand-built composite DAO that calls two or more of the generated ones.

I'm far from expert in this - no experience with the popular ORMs and such - so I'll try to shut up & listen now.
Sonny Gill
Ranch Hand

Joined: Feb 02, 2002
Posts: 1211

Originally posted by Rajeshwari Natarajan:
Hi all,

I need advice on using the DAO pattern.
...
1) One approach is to use one DAO for each database table/View.
...
2) The second approach is to use a single DAO for all related operations. The number of DAOs will be driven by the business functionality.
...
Which of these approaches would you recommend, based on your experience.


Ok, based on my limited experience, and based on the information provided, I would recommend option 1 with one Dao for each table/view and service/helper objects as needed to bring together operations that affect multiple tables, if there is already existing code which accesses these same tables (perhaps performing CRUD operations). This will make it easy to refactor the combined codebase.

And, have extensive unit/integration test suites that will allow me to change that setup as needed after working on it for a while.

Have you had a look at Spring JDBC?
It is a fairly lightweight framework to ease the pain of JDBC development.


The future is here. It's just not evenly distributed yet. - William Gibson
Consultant @ Xebia. Sonny Gill Tweets
raminaa niilian
Ranch Hand

Joined: Jul 14, 2005
Posts: 550
Originally posted by Frank Carver:
I've done this a few times, and knocking around JavaRanch somewhere is "jenny", Paul Wheaton's metadata-to-java generator.



Hi
you mean that there is an small tool available in javaranch which can produce dao from jdbc metadata ?

thanks
Peer Reynders
Bartender

Joined: Aug 19, 2005
Posts: 2906
Jenny the db code generator


"Don't succumb to the false authority of a tool or model. There is no substitute for thinking."
Andy Hunt, Pragmatic Thinking & Learning: Refactor Your Wetware p.41
Sabarish Sasidharan
Ranch Hand

Joined: Aug 29, 2002
Posts: 73
As Ilja has put it, i think you guys will finally end up developing your own ORM. It may not be a Hibernate, but it might atleast be close to an iBATIS.


Sab<br /> <br />Perfection does not come from belief or faith. Talk does not count for anything. Parrots can do that. Perfection comes through selfless work.<br />Swami Vivekananda
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Designing Table specific DAO
 
Similar Threads
Session Bean + single DAO for multiple tables?
How to tackle simultaneous updates done by different user to a single webpage
Stateless Session Beans Managing JDBC Transactions
Questions regarding the DAO pattern
Use of DAO as a Command?