Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Designing Table specific DAO

 
Rajeshwari Natarajan
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ilja Preuss
author
Sheriff
Posts: 14112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Mr. C Lamont Gilbert
Ranch Hand
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?
 
Rajeshwari Natarajan
Ranch Hand
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 14112
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1170
Eclipse IDE Hibernate Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 67
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 308
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 ]
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 8791
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1211
IntelliJ IDE Mac
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
raminaa niilian
Ranch Hand
Posts: 551
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2968
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jenny the db code generator
 
Sabarish Sasidharan
Ranch Hand
Posts: 73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic