CMP/Database vs SQL/Database

[J.H.B. Oosterlaar]

Hi,
Why should I choose CMP with (MySQL) database connectivity to store data in entity beans and eventually in the database instead of directly storing in the MySQL database?
Thanks and cheers,
Jeroen Oosterlaar

[Chris Mathews]

Originally posted by J.H.B. Oosterlaar:
Hi,
Why should I choose CMP with (MySQL) database connectivity to store data in entity beans and eventually in the database instead of directly storing in the MySQL database?
Thanks and cheers,
Jeroen Oosterlaar

So you don't have to mess with writing SQL nearly as much. The real question is why CMP over other O/R Mapping Technologies such as JDO.

[J.H.B. Oosterlaar]

Ok, but how messy do you think SQL can get? Why not write a simple database interface instead of using a heavy weighted application server with the beans containing the data and database store functions. I'm not interested in other techniques, I just want to know the difference between CMP with database connectivity and direct database access.

[Chris Mathews]

Originally posted by J.H.B. Oosterlaar:
Ok, but how messy do you think SQL can get?

Depending on what you are doing, it can get pretty messy. For simple database driven websites with only a handful of tables, then you are probably better off writing the simple queries yourself. Compound that with hundreds of database tables and you have a maintenance headache waiting to happen.

Originally posted by J.H.B. Oosterlaar:
Why not write a simple database interface instead of using a heavy weighted application server with the beans containing the data and database store functions.

If all you need is persistance and you have a small application then go with the custom SQL. But for large applications why recreate the wheel? I certainly don't want to invest time in writing an O/R Mapper when I don't have to.

Originally posted by J.H.B. Oosterlaar:
I'm not interested in other techniques, I just want to know the difference between CMP with database connectivity and direct database access.

The difference is more then just the persistance, though you can do considerable work with CMP in a short time. Most CMP implementations do things like transparently optimizing database queries and lazy loading of data. Also CMP, since it is a type of EJB, also gives declarative security, distribution, and transactional services. Though I would argue that these things are better managed in Stateless Session Beans.
I am not a big fan of Entity Beans (CMP or otherwise) but I am a big fan of avoiding work. If using CMP means less boring CRUD SQL then I am all for it. If I can get this without Entity Beans (read JDO) then that is even better.
At the end of day, the less SQL I need to touch, the happier I am.
[ November 30, 2002: Message edited by: Chris Mathews ]

[Chris Mathews]

The other thing that CMP gives you is portability between databases.
With hand-written SQL it is very easy to write queries that are not 100% portable between database vendors. Therefore if you decide, for whatever reason, to switch databases then you will have to break open the code to update any non-complaint SQL.
Of course switching databases typically does not happen in most projects. However, if you are developing a product that must run on multiple databases then I am sure you could see the advantage that CMP holds in portability.

[Mr. C Lamont Gilbert]

Originally posted by J.H.B. Oosterlaar:
Ok, but how messy do you think SQL can get? Why not write a simple database interface instead of using a heavy weighted application server with the beans containing the data and database store functions. I'm not interested in other techniques, I just want to know the difference between CMP with database connectivity and direct database access.

But this is the whole J2EE question no? Why not do it all yourself? To me the answer is simple. Let the container handle as much as it is willing to. That leaves you more time to focus on business logic. If the container is slow, beat the heck outta the Application Server sales rep, or whoever.

Honestyl, current the containers lack of handling autoincrement is kind of disappointing, so for this its always good to beat on Sun...