Java Community News - Logic in Database Apps: Stored Procedures or Java?

Articles |
News |
Weblogs |
Books |
Forums

Artima Forums | Articles | Weblogs | Java Answers | News

Sponsored Link •

Java Community News
Logic in Database Apps: Stored Procedures or Java?

23 replies on 2 pages. Most recent reply: May 1, 2006 10:10 PM by Kuassi Mensah

Welcome Guest
Sign In

Back to Topic List

Reply to this Topic

Search Forum

Threaded View


Previous Topic		Next Topic

Flat View: This topic has 23 replies on 2 pages [ « | 1 2 ]

Bill Venners

Posts: 2284
Nickname: bv
Registered: Jan, 2002

Re: Logic in Database Apps: Stored Procedures or Java?

Posted: Apr 20, 2006 10:38 PM

> Bill,
>
> I am not sure what Hibernate does by default, but creating
> a separate history object on the java side and saving it
> using JDBC batch mode would not incure you any additional
> network traffic. I would not go to a SP yet in this case.
>
We initially implemented it all in Java. When we wanted to update a versionable entity, we had in hand the updated entity that we wanted to save. Before saving this updated entity, we wanted to use the existing version of the entity to create a history record. So what we needed to do was to pull the existing entity back up from the database. To do that, though, we had to temporarily evict the updated one from the Hibernate session, because Hibernate enforces object identity in sessions. There can only be one entity of that type with that id in the session. So we temporarily evict the updated one, and read in the existing one.

We then use the existing version to create the history record and persist it. (Here, Hibernate likely doesn't persist it yet, just remembers to persist it later.) We then evict the existing version for the same reason, so we can bring the detached, updated version, back into the session. We then persist the updated version.

Later, when Hibernate does its next batch update, it likely writes the updated record and history record in one batch update. I think that's the behavior you are talking about. The extra network traffic I thought was kind of silly to incur was not so much saving the history record, thought that was part of it. It was primarily the grabbing of the existing one, which couldn't be done batch. In total, there was one extra round trip per update, so I just moved that to the stored procedure and set it up as a trigger.

David Vydra

Posts: 60
Nickname: dvydra
Registered: Feb, 2004

Re: Logic in Database Apps: Stored Procedures or Java?

Posted: Apr 21, 2006 8:41 AM

Bill,

This is a great little example! I think I will use it on interviews :) I certainly would not mess with HB default sesion behavior, but try to add additional logic around it. So why not implement your own logic via JDBC directly. Hibernate session will give you its JDBC connection. If you clone the object after you read it, if its dirty, you have your history record in hand. Next you directly INSERT it with the current transaction. Its been a while since I looked at HB, but I hope there are hooks to do just that. In the end, you still have just one roundtrip to the DB and its DB independent/debuggable in your java IDE, etc.

-d

Bill Venners

Posts: 2284
Nickname: bv
Registered: Jan, 2002

Re: Logic in Database Apps: Stored Procedures or Java?

Posted: Apr 22, 2006 12:39 AM

> This is a great little example! I think I will use it on
> interviews :) I certainly would not mess with HB default
> sesion behavior, but try to add additional logic around
> it. So why not implement your own logic via JDBC directly.
> Hibernate session will give you its JDBC connection. If
> you clone the object after you read it, if its dirty, you
> have your history record in hand. Next you directly INSERT
> it with the current transaction. Its been a while since I
> looked at HB, but I hope there are hooks to do just that.
> In the end, you still have just one roundtrip to the DB
> and its DB independent/debuggable in your java IDE, etc.
>
I hadn't thought of that approach, but I think it would work fine. It would cost memory in the app servers, though, and only in exchange for not having to use stored procedures. It would double the memory consumption of versionable entities. I expect to have a lot of versionable entities, so I'm not sure the cost of memory would be worth what I get for it.

As I mentioned previously in this thread, our stored procedures for making the history records are generated by a code generator, so we don't write them by hand. Thus I don't expect to need to do much debugging of them. If we switch databases someday, I can change the code generator and regenerate all of them in the new dialect. Perhaps I am just a bit naive because of inexperience with stored procedures, but I don't see why I should be so afraid of using them for simple logic like creating history records.

robert young

Posts: 361
Nickname: funbunny
Registered: Sep, 2003

Re: Logic in Database Apps: Stored Procedures or Java?

Posted: Apr 22, 2006 7:05 AM

> Perhaps I
> am just a bit naive because of inexperience with stored
> procedures, but I don't see why I should be so afraid of
> using them for simple logic like creating history records.

You don't sound naive, and there is nothing to fear (well, except the ignorant mob).

What's afoot is age-old: fight for hegemony betwixt warring factions, each seeking control of the future. The stake: employment.

If you're a java kiddie with a CS degree and no experience (or even course work) with databases (RM, SQL, or otherwise); then you will seek to denigrate any approach which minimizes your skill set.

Since there are a lot of such kiddies churning out java "infrastructure", especially in the "enterprise" space, they seek COBOL-ize java; force it to be lingua franca in that space. It so happens that (not a coincidence) COBOL functions largely ignorant of RM and SQL, prefering the 1960s file approach. This approach yields language lock-in and lots of code.

On the other side are veterans of data development (even, dare I say, pre-Codd) who have seen how much more structured systems are when you start with the datastore and work outward. In the early days of COBOL (when it was only batch, and 132 column paper reports represented output), the mantra was "design the outputs, then the inputs".

This mantra led to spaghetti file based systems and was Codd's target.

Today, the java mantra is "design the screens, then the inputs". One then wanders down the 40 year old path of yore. It's kind of like the dark ages of Real History; learning has ceased and practice reverted leeching and casting out of witches. (The java kiddies are blissfully unaware that, save the <>, tagged text data (XML) was au courant with IBM and DEC in the 1960s. It was of only limited use then, too.)

But nature, and math, can't be stopped. So the winners will continue to be those who follow the data-centric mantra. And you generally don't hear much from them on forums; it's such a huge competitive advantage and the java kiddies are so full of themselves.

David Vydra

Posts: 60
Nickname: dvydra
Registered: Feb, 2004

Re: Logic in Database Apps: Stored Procedures or Java?

Posted: Apr 22, 2006 7:50 AM

Bill,

Lets start from the end of your post. You certainly should not be afraid of stored procedures - they are an important tool that is sometimes the most appropriate to use. My comments were mere musings on design alternatives that poped into my head. Since you have a custom code generator - which is still rare on these sorts of projects - you certainly have less to worry about with respect to maintenance.

So next time we get together we shall debate the merits of Hibernate vs Ibatis for your project :)

Cheers,
David

David Vydra

Posts: 60
Nickname: dvydra
Registered: Feb, 2004

Re: Logic in Database Apps: Stored Procedures or Java?

Posted: Apr 22, 2006 8:10 AM

Robert,

Not sure if you were repying to my earlier post, but I agree with alot of what you said. I have certainly seen many inappropriate uses of Java in enterprise systems. It just dawned on me that I have about 7 years of SQL/Oracle and 7 years of Java behind me. All I can say is that I approach each project with an open mind and decide on Java vs Stored Procedures on a case by case basis. I would expect any senior software engineer I work with to be able to explain to me the rationale for the particular decision that was made. The most difficult problem for me is to find projects with professionals who care to educate themselves in various technologies (or managers who care to educate their staff) and make decisions based on sound software engineering principles.

Having said that, I am not sure if your post serves well to encourage folks to learn about what is available - it sounds a bit too much like "them vs us". Next year will be my 20th in software development and I have no desire to call anyone a "kiddie" and loose an opportunity for a real dialog.

Regards,
David
www.testdriven.com

>
> If you're a java kiddie with a CS degree and no experience
> (or even course work) with databases (RM, SQL, or
> otherwise); then you will seek to denigrate any approach
> which minimizes your skill set.
>
> Since there are a lot of such kiddies churning out java
> "infrastructure", especially in the "enterprise" space,
> they seek COBOL-ize java; force it to be lingua franca in
> that space. It so happens that (not a coincidence) COBOL
> functions largely ignorant of RM and SQL, prefering the
> 1960s file approach. This approach yields language
> lock-in and lots of code.
>

robert young

Posts: 361
Nickname: funbunny
Registered: Sep, 2003

Re: Logic in Database Apps: Stored Procedures or Java?

Posted: Apr 22, 2006 11:47 AM

> Robert,
>
> Not sure if you were repying to my earlier post,

No. To Bill's only.

Bill Venners

Posts: 2284
Nickname: bv
Registered: Jan, 2002

Re: Logic in Database Apps: Stored Procedures or Java?

Posted: Apr 24, 2006 3:31 PM

> So next time we get together we shall debate the merits of
> Hibernate vs Ibatis for your project :)
>
I'd like to learn more about iBATIS. Azad Balour told me that he had turned to iBATIS in an area where Hibernate's session was growing so big over a long running transaction that synchronizing it with the database was really slowing things down. I'd like to understand first just how iBATIS works, but then in what situations it makes sense to use it over an ORM like Hibernate or EJB3.

Kuassi Mensah

Posts: 1
Nickname: kuassi
Registered: May, 2006

Re: Logic in Database Apps: Stored Procedures or Java?

Posted: May 1, 2006 10:10 PM

> You certainly should not be afraid of stored procedures -> they are an important tool that is sometimes the most
> appropriate to use.

Thank you, this is exactly the attitude i've been promoting in my book http://www.amazon.com/gp/product/1555583296/

The sample chapter is available @ http://db360.blogspot.com/2006/03/stored-procedure-as-database.html#links

Kuassi, http://db360.blogspot.com/

Flat View: This topic has 23 replies on 2 pages [ « | 1 2 ]

Previous Topic

Next Topic