Author question: JDBC in Java Code.

[Mark Spritzler]

With tools like Hibernate, EJB 3.0 and JDO. When do you see that it is better to use straight JDBC code?

I find Hibernate to generate the exact SQL that I would have created if I used JDBC directly in code.

How do you handle where to place the SQL statements, rather than directly in code?

Do you find that having JDBC code directly in your code, that it is more difficult to maintain than using an ORM tool? Especially if you change say a stored procedure and add a parameter, and you have to find all the places where that procedure is called from.

Mark

[Mark Spritzler]

Just bumping this up.

Mark

[R. M. Menon]

"With tools like Hibernate, EJB 3.0 and JDO. When do you see that it is better to use straight JDBC code?

I find Hibernate to generate the exact SQL that I would have created if I used JDBC directly in code.

How do you handle where to place the SQL statements, rather than directly in code?

Do you find that having JDBC code directly in your code, that it is more difficult to maintain than using an ORM tool? Especially if you change say a stored procedure and add a parameter, and you have to find all the places where that procedure is called from.

Mark"

I believe my views are different from most people in this forum. I thing that SQL statements belong to the database and I find it hard to imagine a tool that can generate SQL statements automatically. SQL is the most underutilized but the most powerful tool that I know of in coding applications. Instead of using the least common denominator for all dbs, I believe in exploiting all the features of database (be it Oracle or SQL Server.) So when using JDBC, I would use CallableStatements and invoke a layer of code that uses stored procedure - unless I have a reason not to do.

If by putting the SQL in Java you want to achieve db independence - good luck!

"Especially if you change say a stored procedure and add a parameter, and you have to find all the places where that procedure is called from.
"

Well, at least here you have to change only if the parameters of an interface (interface to Pl/SQL) changed. If you put it in Java, you have to change it every time the SQL changes!

If by putting SQL in Java you want to scale more since you think middle tier is cheaper - think again - the SQL would ultimately be executed by the database.

Majority of performance issues in my experience have been either misusing or completely underutilizing database. I think it is like buying a ferrari and using it like Geo metro (no insult meant for Geo Metro owners )

[Gagan Indus]

That�s an interesting point of view.

It kinda goes against the Java philosophy of being able to 'take your code to some other database the next morning'. It will be interesting to see how many times people actually end up doing that, Does any of you have any experience where the DB was actually changed because of some reason X? (it would be good if you can state reason X, and also state the percentage of how many times it have happened in your career versus how many times it haven't)

In my little experience of few years, it has never happened that we changed the production DB. But i can recall one scenario where we used two DBs without needing to change much, it was when we prototyped a part of the application on MySQL and deployed it on Oracle, mostly expanding on the prototype code.

[Ilja Preuss]

Originally posted by R. M. Menon:
I find it hard to imagine a tool that can generate SQL statements automatically.

What do you find hard to imagine about that? How do you judge the SQL statements that modern tools like Hibernate *are* generating? Just curious...

Instead of using the least common denominator for all dbs, I believe in exploiting all the features of database (be it Oracle or SQL Server.)

I always thought that one reason for using ORM tools was that you exactly *don't* need to restrict yourself to the least common denominator. You just tell the ORM tool *what* you want to do, and it's the job of the tool to determine how it's best done with the specific database you currently use.

To me, that sounds quite promising, at least for the common case.

That doesn't mean that specific database knowledge isn't valuable, of course. I just forwarded the review of your book to our database expert, and he immediately ordered it...

[R. M. Menon]

Originally posted by Gagan Indus:
That�s an interesting point of view.

It kinda goes against the Java philosophy of being able to 'take your code to some other database the next morning'. It will be interesting to see how many times people actually end up doing that, Does any of you have any experience where the DB was actually changed because of some reason X? (it would be good if you can state reason X, and also state the percentage of how many times it have happened in your career versus how many times it haven't)

In my little experience of few years, it has never happened that we changed the production DB. But i can recall one scenario where we used two DBs without needing to change much, it was when we prototyped a part of the application on MySQL and deployed it on Oracle, mostly expanding on the prototype code.

Well, the point is that most people are not aware of how a database works, how Oracle is different from Sql Server e.g. in terms of concurrency models etc. and how critically that can impact your applicaiton. The idea of making db independent application "just in case" is, IMHO, not a smart thing to do. A database is much more than just a place to dump data. There are hundreds of feeatures with in each Database that you can not implement yourself (if you try your implementation would be buggy, slow and disfunctional). Consider just a simple example of Oracle. It gives you features such as Materialized views, analytic functions (in SQL) - within PL/SQL there are hundreds of extremely useful and powerful packages that are optimized and can be used - if you decide to use PL/SQL that is
As I said, one can go with db independence and suffer for what is called a "phantom requirement" by many or one can choose to exploit the DB fully - you paid top dollars for it anyway, correct? But then we can go on and on - this is a never ending discussion.

[R. M. Menon]

What do you find hard to imagine about that? How do you judge the SQL statements that modern tools like Hibernate *are* generating? Just curious...

I find it very hard to impagine a tool that would generate good SQL. Writing SQL even for human beings is an art (unless of course you think of SQL as just simple select, update, delete, insert) etc. Generating SQL to me is the same as generating your program. There are thousands of ways of writing the same SQL and there are few people in the world who can genuinely call them selves SQL experts. The point is that if you know how to use and exploit SQL, you can write really fast and scalable applications. But it is an art and I find it hard to imagine "artistic" tools!

I always thought that one reason for using ORM tools was that you exactly *don't* need to restrict yourself to the least common denominator. You just tell the ORM tool *what* you want to do, and it's the job of the tool to determine how it's best done with the specific database you currently use.

To me, that sounds quite promising, at least for the common case.

That doesn't mean that specific database knowledge isn't valuable, of course. I just forwarded the review of your book to our database expert, and he immediately ordered it...

In Oracle, tell me of an ORM tool that knows about analytic functions, "with" clause, all the built in Oracle functions (e.g. ntile) and how it can write complicated SQLs using these?

As a somewhat extreme but still representative
example, just look at the SQL in
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:14339283697418#14347225101242

The one sql that schedules a golf roster for a tournament.

Now tell me of an ORM tool that does this and can think like Tom Kyte

Btw, asktom.oracle.com is a great site to learn about all these concepts for those interested in Oracle. His books are highly recommended.

[Mark Spritzler]

Instead of using the least common denominator for all dbs

So you have never seen the SQL that Hibernate generates. Hibernate is aware of the database that you are using and uses a lot of the features of that particular database to make things faster. You can use some of the SQL features that are unique to that database through Hibernate. Also Gavin and Christian who created Hibernate know quite a bit about databases and the vendors. So while you can have restraint and concerns over how it does it, unless you know more about it, you can't completely pass judgement on it.

As far as my comments about changing parameters in SPs and the like, I will also bring up a tool from Oracle, to create Java Classes based on Oracle types used as parameters in Stored Procedures called JPublisher. First we have timed queries and stored procedures using JPublisher and Hibernate. Guess what Hibernate was always faster. Also the number of lines of code with JDBC in Java and Hibernate and JPublisher is 500 to 3. Yes you heard me right, it takes 500 lines of code with JDBC and JPublisher, than with Hibernate. Now that is a huge risk, and it is slower. Talk about high costs. So you can't really trust products directly made by the database vendor. You still have to be cautious there.

What always ends up happening when you use JDBC, is your costs rise because of maintenance costs, it is tougher to change something so it is extensible, and when you try to change you are taking higher risks because it usually never works right away and gets frustrating. So then you go and decide to OO its way out of this mess, and what happens you try to write your own Hibernate from scratch.

R.M. I understand you ideas, and I have heard them many times, but it comes from a Database perspective and not a Java perspective. To be honest, I think the costs of Java development is higher than Database development.

I have worked as an Oracle DBA, I have used many things that you see on asktom. My favorite is Over Partition By where you can get aggregates at different levels all within the same query without having to resort to sub queries. I have developed Oracle Forms applications and have written my big share of PL/SQL, I understand your perspective, but I also understand the Java perspective, and the Java stuff you need to do is a much bigger difference than the Oracle stuff, so it needs to be a good compromise. What might be better for Oracle and saves you an extra hour, then costs the Java side an extra 10-20 hours. Trust me, I see it everyday at work.

I agree that if you get to a really complex type of query you need, it is best to let the database handle it, but you can still do that way with Hibernate that doesn't make Java developer have to resort to some really bad coding styles needed when you just use JDBC.

Mark
[ March 17, 2006: Message edited by: Mark Spritzler ]

[R. M. Menon]

Originally posted by Mark Spritzler:


So you have never seen the SQL that Hibernate generates. Hibernate is aware of the database that you are using and uses a lot of the features of that particular database to make things faster. You can use some of the SQL features that are unique to that database through Hibernate. Also Gavin and Christian who created Hibernate know quite a bit about databases and the vendors. So while you can have restraint and concerns over how it does it, unless you know more about it, you can't completely pass judgement on it.

As far as my comments about changing parameters in SPs and the like, I will also bring up a tool from Oracle, to create Java Classes based on Oracle types used as parameters in Stored Procedures called JPublisher. First we have timed queries and stored procedures using JPublisher and Hibernate. Guess what Hibernate was always faster. Also the number of lines of code with JDBC in Java and Hibernate and JPublisher is 500 to 3. Yes you heard me right, it takes 500 lines of code with JDBC and JPublisher, than with Hibernate. Now that is a huge risk, and it is slower. Talk about high costs. So you can't really trust products directly made by the database vendor. You still have to be cautious there.

What always ends up happening when you use JDBC, is your costs rise because of maintenance costs, it is tougher to change something so it is extensible, and when you try to change you are taking higher risks because it usually never works right away and gets frustrating. So then you go and decide to OO its way out of this mess, and what happens you try to write your own Hibernate from scratch.

R.M. I understand you ideas, and I have heard them many times, but it comes from a Database perspective and not a Java perspective. To be honest, I think the costs of Java development is higher than Database development.

I have worked as an Oracle DBA, I have used many things that you see on asktom. My favorite is Over Partition By where you can get aggregates at different levels all within the same query without having to resort to sub queries. I have developed Oracle Forms applications and have written my big share of PL/SQL, I understand your perspective, but I also understand the Java perspective, and the Java stuff you need to do is a much bigger difference than the Oracle stuff, so it needs to be a good compromise. What might be better for Oracle and saves you an extra hour, then costs the Java side an extra 10-20 hours. Trust me, I see it everyday at work.

I agree that if you get to a really complex type of query you need, it is best to let the database handle it, but you can still do that way with Hibernate that doesn't make Java developer have to resort to some really bad coding styles needed when you just use JDBC.

Mark

[ March 17, 2006: Message edited by: Mark Spritzler ]

Mark, I respect your opinions but well, what can I say. We just have to agree to disagree

I have not seen hibernate work - yes but one can still make judgements about automated SQL versus that which is crafted by a human being who knows his/her SQL. After all, hibernate is still software. So yes, there must be a place for hibernate - for apps that require relatively simple SQL access. But for the kind of app I am working on (right now) - no thank you (no offense meant to you or to hibernate creators.) When as a human being, I have to think hard about writing a SQL specific to a situation - how can a generic software beat that? As I said, if this is the case, why not generate all programs then?

Just as you claim you have worked with Oracle, I can claim I have worked with Java as well - that, unfortunately, does not really mean squat to the readers.

Regarding Jpublisher and Hubernate - I have no idea what you are comparing and what the benchmark is. No idea - so no comments. You may be correct, of course! I am not defending JPublisher or JDBC here against hibernate, btw. It is quite possible that using hibernate makes your code shorter and less error prone.

As I said, this is a controversial topic and typically people tend to stick to their stands - hard to change their opinions in forum discussion such as this.

What might be better for Oracle and saves you an extra hour, then costs the Java side an extra 10-20 hours. Trust me, I see it everyday at work.

Surely, the above statement is too generic. Stating that " I see it everyday at work" again does not mean anything to me at least. There could be examples that prove or disprove the above. In my experience, mostly, I see people who know a lot about Java (is just another Programming language *gasp*) but know nothing about the underlying DB thinking it as a data dump.

Peace!
Menon

PS:
Warning - some personal comment on my book related to this topic is coming - take it with as many grains of salts as you desire.

One way my book attempts to differeniate from other books is that I have strived to give performance benchamarks (along with the code used to run the performance benchmark) for anything I say/claim. The actual code so you can see, comment, find mistakes (there may well be mistakes - though the reviewers such as Tom Kyte must have caught most of them, I hope.) You will be surprised how many books claim bogus things - especially when it comes to performance.

[R. M. Menon]

Sorry - looks like I ended up posting twice by pressing some key - sorry about that - the second post is the right one - ignore the first post

[Mark Spritzler]

I deleted the first one for you.

I have not seen hibernate work - yes but one can still make judgements about automated SQL versus that which is crafted by a human being who knows his/her SQL.

How can you judge something you have never seen? That is really scary. And makes my point, that your opinions is biased and short-sighted. You are basing you opinion on probably a couple of ticks of CPU versus the long hours and high costs of maintenance and extensibility. It isn't always all about performance, when the performance gain might not be worth the trouble.

As I said, if this is the case, why not generate all programs then?

That isn't an argument. You are going to an extreme. Again a place where you have to find compromise, what works best and keep all things in mind. If you don't, you usually end up getten bit on the back end, not a pretty sight.

Mark

[R. M. Menon]

Originally posted by Mark Spritzler:
I deleted the first one for you.



That isn't an argument. You are going to an extreme. Again a place where you have to find compromise, what works best and keep all things in mind. If you don't, you usually end up getten bit on the back end, not a pretty sight.

Mark

Thanx for cleaning up the first message..

Well, as I said it is hard to "argue" in a forum. If you treat SQL as simple selects, inserts etc., sure hibernate can generate the SQL. If not, it simply can not (since SQL would be then like a program to me.) So my argument depends on how powerful you think SQL is. SQL is a programming language to me in all senses (albeit set oriented instead of procedural). To generate complex "programs" with SQL automatically is no different than generating automatically Java programs to me. I personally take quite some time in thinking about and writing SQL statements I use. Once done, after code reviews, I have to change it many times as someone else gives me a better way of doing it. I am pretty sure hibernate can not do this automatically (to me that is common sense.) As I said, hibernate may be good where you can get away with simple SQL. But otherwise - no.

Enough said

[Mark Spritzler]

On your last part I agree completely. There are times where you want Stored Procedures or better queries written on the database side, and with Hibernate you can still do that without taking out the Hibernate framework in your development.

I will apologize in thinking that my comments might be a little rude, but I just can't make blanket statements in all cases.

Good Luck with your book and thanks for being here for this great promotion.

Mark

[Paul Clapham]

Originally posted by Gagan Indus:
Does any of you have any experience where the DB was actually changed because of some reason X? (it would be good if you can state reason X, and also state the percentage of how many times it have happened in your career versus how many times it haven't)

Right now we have a database that is running on DB2, but for complex technical reasons we are not sure if we actually have a licence to run DB2. If it turns out we don't have a licence, we will be moving that database to some other product. There's X for you.

How many times haven't we changed our database? It happens every day that we don't change our database. I don't think you're going to get any usable statistics from that question.

[Bill Johnston]

"Does any of you have any experience where the DB was actually changed because of some reason X?"

All the time! I've seen it and seen it and seen it! While I agree that a software package developer might want to do this, I definitely do not think it's wise for an institution. And even in the former case - if the DB vender gets real uppity and fiesty and starts doing things it's own way against pleadings from it's many - or many of it's - customers, well ... hey, it can cripple the software vender. At the very least it can cause it's support staff to have to hear all kinds of flack from the vender's customers.

No, in today's ever changing market, I can't see tying oneself down to any particular DB that tightly. You've got to have an out.

Mind you, performance tuning is real important, but not so important as risking going out of business for.

[Bill Johnston]

"One can only take so much before one can't take anymore" - me

[R. M. Menon]

Originally posted by Bill Johnston:
"Does any of you have any experience where the DB was actually changed because of some reason X?"

All the time! I've seen it and seen it and seen it! While I agree that a software package developer might want to do this, I definitely do not think it's wise for an institution. And even in the former case - if the DB vender gets real uppity and fiesty and starts doing things it's own way against pleadings from it's many - or many of it's - customers, well ... hey, it can cripple the software vender. At the very least it can cause it's support staff to have to hear all kinds of flack from the vender's customers.

No, in today's ever changing market, I can't see tying oneself down to any particular DB that tightly. You've got to have an out.

Mind you, performance tuning is real important, but not so important as risking going out of business for.

I have not seen dbs changing like that but then I was with Oracle for quite some time

It is not just performance tuning - it is scalability - apps being able to function correctly and to the minimum expectations by users with adequate resources used. Changing a database is going to be costly to you anyways. But in order to plan for that event, if you decided to use it just with the least common denominator - then as I would say - good luck! It is simply not worth it to pay all the money and use only a fraction of the features of a database. Also, many times people just think they are using "least common denominator" but they are not.

[Bill Johnston]

Carrying data from one DB to another is one thing. And I think it's expected that some programming will need to be re-done too. But that's what Java is supposed to be able to help with, isn't it?

As to the "performance tuning" bit ... you got me on that one, as I was being overly general. To be more clear then, I mean - in addition to proper DB administration - thought as to where to place logic as to performance has to be given. But I really don't think it should be the only or main criterion.

Everything is a bit of a compromise. But how much portability do you want to give up for performance? How much performance is enough? At what point are you becoming DB dependant?

~Bill

[R. M. Menon]

Originally posted by Bill Johnston:
Carrying data from one DB to another is one thing. And I think it's expected that some programming will need to be re-done too. But that's what Java is supposed to be able to help with, isn't it?

As to the "performance tuning" bit ... you got me on that one, as I was being overly general. To be more clear then, I mean - in addition to proper DB administration - thought as to where to place logic as to performance has to be given. But I really don't think it should be the only or main criterion.

Everything is a bit of a compromise. But how much portability do you want to give up for performance? How much performance is enough? At what point are you becoming DB dependant?

~Bill

Whether you access a DB through Java or C or C++, the database related work is database related work. So I am not sure how using Java would be better. (If we are talking OO versus procedural - sure - Java is (IMO) a pretty good OO language and that helps in programming in general (not just in conjunction with the databases.)

"portability" in the name of db independence - in my experience is just too costly. I guess we just differ on that point and would have to agree to differ

[Ilja Preuss]

Originally posted by R. M. Menon:
I have not seen dbs changing like that but then I was with Oracle for quite some time

Well, I can imagine that when working for Oracle, there is not much value in supporting non-Oracle databases.

But seriously, if you develop a product, for example, it might not be the best strategy to couple it to a specific database vendor.

It is simply not worth it to pay all the money and use only a fraction of the features of a database.

That's only one side of the coin. There are also costs and risks in putting more functionality inside the database, or at least questions that need to be answered before we can decide what to do in a specific situation:

- how well can I test it?
- how easy is it to refactor the code?
- how easy is it to debug the code?
- how long are roundtrip times (code, compile, deploy, run)?
- how much database knowledge is available? How costly is it?
- how important is the database for the project?

And the answer for (at least some of) those questions will likely be different from project to project, so the balance between putting code into Java and putting it into the database will be different, too.

[Ilja Preuss]

Originally posted by R. M. Menon:

As I said, hibernate may be good where you can get away with simple SQL.

Perhaps such projects are just more common than it looks like from your perspective...

And regarding generating programs in general, there are actually quite successful attempts at doing this for very specific domains. Just think about using JAXB for parsing XML.

Might be that vanilla database persistence just is such a domain where it just works "well enough" in the general case? (Doesn't Hibernate even allow to define specific SQL statements for critical parts of the application?)

[R. M. Menon]

Originally posted by Ilja Preuss:


That's only one side of the coin. There are also costs and risks in putting more functionality inside the database, or at least questions that need to be answered before we can decide what to do in a specific situation:

- how well can I test it?
- how easy is it to refactor the code?
- how easy is it to debug the code?
- how long are roundtrip times (code, compile, deploy, run)?
- how much database knowledge is available? How costly is it?
- how important is the database for the project?

And the answer for (at least some of) those questions will likely be different from project to project, so the balance between putting code into Java and putting it into the database will be different, too.

These are fairly generic questions - the answers to them could swing the decision either way. The first 2 are not even relevant to the discussion I think (they are too generic).

Tell me one thing.
I have a piece of SQL. Remember who executes this SQL - Database. Whether you put it in Java or put it in within the database, you are dependent on the database (Just forget about writing SQL that is common denominator - OK - that is so crippling to most non trivial projects - I don't even want to go there.) Putting it in stored procedure simply gives you an API. Anything above this API can be "db independent" without paying any cost in functionality. If you want to port it to another DB (again if there is such reqeurement and it makes sense) Then you could replace the underlying stored procedure implementation. That is IMHO a better way of writing "db independent" code. You exploit DB features to the fullest - and you also have ways of changing the DB. The portions that are really, truely common to the multiple DBs can be moved over to Java may be.

PS: Just because I worked for Oracle (I have moved on to a different company, btw), does not mean that I am trying to sell Oracle My philosophy is simple. If you are using a DB (which is a powerful tool) and if you have no clue how to use it - you are going to be in trouble (just like any other tool). You have to know your tools to use them. Thinking that DB would be just a place to insert, delete data - a data dump is a recipe for disaster. This is something that I hope to convey in my book (and one major reason I wrote it in the first place).

[R. M. Menon]

Perhaps such projects are just more common than it looks like from your perspective...

But then, perhaps they are simply perceived to be due to people not knowing about the capabilities of SQL or stored procedures and how much they loose if they ignore these capabilities

And regarding generating programs in general, there are actually quite successful attempts at doing this for very specific domains. Just think about using JAXB for parsing XML.

Bad example. XML is not a programming language. SQL is - very powerful and underutilized as I have already indicated. But unless one tried to understand this simple thing, there is not much point in this debate. I will listen to you when you have programs generating Java code. Because that is how powerful (in a set oriented way) SQL has become now.

Might be that vanilla database persistence just is such a domain where it just works "well enough" in the general case? (Doesn't Hibernate even allow to define specific SQL statements for critical parts of the application?)

If you use hibernate to "simplify" your DB access layer - not to generate SQL - it may be a great tool. But to generate SQL and be tied up to the way it generates it - is very scary to me unless your project is really simple from DB point of view.

PS: I used to be OK with the "db independence" till I read this book a while back.
http://www.amazon.com/exec/obidos/ASIN/1861004826/jr%5Fbunk-20/102-3002067-8140936.

It really opened up my eyes. Tom's writing style of book - his insistence on proving anything you state is much better than bold, sweeping statements in the books made by other authors without any shred of evidence. This works best especially for explaining performance, scalability related topics.
--------------------

[Ilja Preuss]

Originally posted by R. M. Menon:

Bad example. XML is not a programming language.

JAXB does generate *Java* code for parsing (and verifying, as well as writing) XML files.

But here is another analogy: I'm sure that to get the most out of a modern processor you should have an expert program in assembler to a specific target system. But instead of doing that we typically rely on the compiler (or JVM) to generate the machine code for us from a higher level representation of what we want the computer to do. And I'd think in most cases that's actually is the economically reasonable thing to do, even if it means that the processor we paid for will not be used to its full ability.

[Ilja Preuss]

Originally posted by R. M. Menon:
These are fairly generic questions - the answers to them could swing the decision either way. The first 2 are not even relevant to the discussion I think (they are too generic).

So you are saying that whether I put logic into the database or not doesn't affect how easy it is to test or change it?

[R. M. Menon]

Originally posted by Ilja Preuss:


So you are saying that whether I put logic into the database or not doesn't affect how easy it is to test or change it?

What I meant was that some of those points were a little too generic...

In any case, interestingly, whether you put logic into the database or not does affect how easy it is to test In Oracle, if you put the SQL in PL/SQL and call it from Java, tuning and testing it out is much easier. For example, you can easily generate the tkprof of the SQL by simply running hte PL/SQL for one thing. Secondly, your source code is in PL/SQL (which is in the database) so you want to automatically extract and run it, that also becomes easier. To fix it, you fix and deploy it in the database and all the middle tier servers see the fix immediately.

As far as debugging goes, if you put your code nto a PL/SQL package, and you want to add the functionality of auditing, you could do it very simply by adding another call. You can use plethora of built-in PL/SQL packages for debugging and testing (dbms_trace comes in to mind.) The list goes on and on. I am sure the story would be similar in case of other databases.

I don't want to drag this discussion. Enough said

Thanx!
Menon

[Ilja Preuss]

Well, as an "Agilist", I was actually talking about the automated kind of tests that you run by the press of a button after every little change to the system.

Anyway, all I wanted to say is that "you payed for the db, so you should use it's feature to the maximum" sounded overly simplistic to me - even harmful if taken literally. If after careful consideration of *all* the influences on a project you decide to put more code into the DB, more power to you.

[steve souza]

I think both sides of the argument (sql in java, and sql in the db) have their points. I personally think the pendulum in the java community has swung too far towards everything should be in java code.

I worked on a project recently and had some developers that knew nothing about java code, and by letting them create and test and run stored procs it allowed them to be productive on our team. Also, these stored procs were easily usable in other languages like powerbuilder. I do think these stored procs should be hidden in an abstraction layer as much as possible, so if necessary the abstraction layer could be swapped out to work when porting from say Oracle to SQL Server.

Having said that, for enterprise databases switching the back end is a lot harder than just worrying about java code. We have enterprise databases that have such a huge ammount of code that it would be a massive undertaking from a coding standpoint to redo all the apps (multiple java apps many taking different approaches to persistance (remember entity EJBs?)/powerbuilder/unix scripts/cron jobs/backup scripts/db configuration checks/bcp/c programs/c++ programs and much more). Truthfully, in many cases, I think it is more likely we abandon Java than our backend database! And I think this is a point that Java developers never think about. Will you be a Ruby shop in 5 years?

This doesn't count the efforts involved in retraining existing dba's that are Sybase ASE experts to become Oracle experts.

Just musings here, but I think Java developers too often have a java centric viewpoint of the world.
[ March 19, 2006: Message edited by: steve souza ]

[George Stoianov]

Hello everyone,

I wanted to add my humble opinion for any visitor later in time. I think the points being made by both sides are very good and interesting . I also thing that much of the decision making one undergoes is based on one's skills and background.

What I mean by that is if you were a PL/SQL developer knowing all the power, speed and direct data access PL/SQL gives you, with all the mighty tools Oracle has at your disposal you would probably not reach for anything Java unless you absolutely exhausted all opportunities in your field...and that can go far... I know of cases and have done some similar things myself where someone had parsed an xml file in a stored procedure.

I think that there are two sides to this, one is your own knowledge and the other one is the best tool for the specific task/problem, yes parsing an xml file is better done in java because you can easily abstract file format or pull the file of a network stream if you wanted to or had to etc. etc. (for the pl/sql buffs please correct me if that is better done in sql ), but I do not think trying to manage the processing of millions of rows of data keeping track of different buffers, logs and caches is a very good choice for Java. Databases also end up containing all the data which is very very important to management and us casual bank clients. True a good programming language like java is needed to make all the different systems talk and communicate and add flexibility and extensibility etc., but when it comes to pure grunt data management work (and more of you are a pl/sql buff) there is nothing better than an RDBMS (and there is even one in Java http://www.hsqldb.org/ ).

Thank you for the book.
George

[Mark Spritzler]

Just musings here, but I think Java developers too often have a java centric viewpoint of the world.

Unfortunately, in this case, it was too much of an Oracle centric viewpoint, and an unwillingness to be open to other possibilities to make a better design. And coming from someone, me, that has 15 years Database experience. I am still for having stored procedures, and you can still use Hibernate to encapsulate your database access too. To me, in many cases this is the best solution. For simple CRUD stuff, let Hibernate do its job. When you have more complicated logic or queries, let PL/SQL do its job. You have to be open to both. I found in this case there wasn't that openess.

Mark

[Manoj Raghuwanshi]

In which case it is preferably easy to implement all the business logic in database stored procedures instead of in java application?
[ March 20, 2006: Message edited by: Manoj Raghuwanshi ]

[Mark Spritzler]

Originally posted by Manoj Raghuwanshi:
In which case it is preferably easy to implement all the business logic in database stored procedures instead of in java application?

[ March 20, 2006: Message edited by: Manoj Raghuwanshi ]

Actually, we aren't talking about business logic in stored procedures but SQL in stored procedures.

It is important to define business logic. But the common use of it, would mean that you do not want any business logic in your stored procedures, just SQL. Because then you would be tightly coupling your business logic with the Database.

Mark

[R. M. Menon]

Originally posted by Mark Spritzler:


Unfortunately, in this case, it was too much of an Oracle centric viewpoint, and an unwillingness to be open to other possibilities to make a better design. And coming from someone, me, that has 15 years Database experience. I am still for having stored procedures, and you can still use Hibernate to encapsulate your database access too. To me, in many cases this is the best solution. For simple CRUD stuff, let Hibernate do its job. When you have more complicated logic or queries, let PL/SQL do its job. You have to be open to both. I found in this case there wasn't that openess.

Mark

Well, I did agree that for simple SQL cases, it is OK to do it automatically? Also, if Hibernate allows you to combine the best of both worlds - sure - I am all for it.
It is just that what would be a series of simple SQL statements can often be combined to be another, equivalent series of different (mostly less number of SQL statements) by a SQL expert - the second series being much more scalable than the first one. It is not a question of being open minded - it is just that in discussions such as these, it is hard to put together years of experience into words that are good enough to convince the people on the other side of the camp Typically, both sides end up thinking the other side is not open enough
PS 1: I certainly dont think my comments were "Oracle centric" in any way - they were "DB centric" - whether the DB is Oracle or SQL Server. Making it sound like "Oracle centric" seems to imply that my opinion is coming from a product vendor bias - which was not the case. I thought I conveyed that once or twice in my statements but perhaps it did not come out correctly.
PS 2: Once again, citing one's years of experience does not help anyone. In Oracle, I know very well known and "renowned" Oracle experts who are wrong all the time (most of the time, in fact! ) This is not a personal remark on anyone - it is just that IMHO, years of experience does not necessarily mean that one has to be correct.

[R. M. Menon]

Originally posted by Mark Spritzler:


Actually, we aren't talking about business logic in stored procedures but SQL in stored procedures.

It is important to define business logic. But the common use of it, would mean that you do not want any business logic in your stored procedures, just SQL. Because then you would be tightly coupling your business logic with the Database.

Mark

Ahh - business logic in stored procedures is wrong? If that is the assumption then I don't agree with that. I know there would be lots of "flames" against this statement - esp. in this Java forum - but I just wanted to put that thought in there.

Secondly, if you are talking of SQL - as I mentioned, when you write SQL, it is really hard to be database independent for most real world applications. Most likely, either you are really writing bad SQL (least common denominator) or you are writing really slow and non scalable ones. To avoid using DB features to be DB independent is a recipe for disaster - I would say that again and again.

[R. M. Menon]

Originally posted by steve souza:
I think both sides of the argument (sql in java, and sql in the db) have their points. I personally think the pendulum in the java community has swung too far towards everything should be in java code.

I worked on a project recently and had some developers that knew nothing about java code, and by letting them create and test and run stored procs it allowed them to be productive on our team. Also, these stored procs were easily usable in other languages like powerbuilder. I do think these stored procs should be hidden in an abstraction layer as much as possible, so if necessary the abstraction layer could be swapped out to work when porting from say Oracle to SQL Server.

Having said that, for enterprise databases switching the back end is a lot harder than just worrying about java code. We have enterprise databases that have such a huge ammount of code that it would be a massive undertaking from a coding standpoint to redo all the apps (multiple java apps many taking different approaches to persistance (remember entity EJBs?)/powerbuilder/unix scripts/cron jobs/backup scripts/db configuration checks/bcp/c programs/c++ programs and much more). Truthfully, in many cases, I think it is more likely we abandon Java than our backend database! And I think this is a point that Java developers never think about. Will you be a Ruby shop in 5 years?

This doesn't count the efforts involved in retraining existing dba's that are Sybase ASE experts to become Oracle experts.

Just musings here, but I think Java developers too often have a java centric viewpoint of the world.

[ March 19, 2006: Message edited by: steve souza ]

I agree with the general sentiments here. Java community tends to look at things from the Java point of view. The thought is to treat the database as a place to dump and retrieve data - no more. No need to learn SQL PL/SQL or T/SQL - just select, insert, delete, update - the same thing would work for all Databases.

I think regardless of all the infrastructure Java brings with it - it is a Programming language at the end of the day! It is good in its domain but it can not be a substitute for the domain of database and SQL.

"Truthfully, in many cases, I think it is more likely we abandon Java than our backend database!"

True. From business point of view, in most cases, the most important thing is the data stored in the database. As middle tier programmers (esp. Java programmers), we tend to overlook that more often than not.

[R. M. Menon]

Originally posted by Ilja Preuss:
Well, as an "Agilist", I was actually talking about the automated kind of tests that you run by the press of a button after every little change to the system.

Sure, You can still write such tests. In fact putting code in Pl/sql (or Transact SQL) would make it easier - you can write separate tests for stored procedures and Java layers.

Originally posted by Ilja Preuss:
Anyway, all I wanted to say is that "you payed for the db, so you should use it's feature to the maximum" sounded overly simplistic to me - even harmful if taken literally. If after careful consideration of *all* the influences on a project you decide to put more code into the DB, more power to you.

"over simplistic" - is in the eyes of the beholder. To me doing things that DB can handle best within DB sounds like common sense. To exploit and fully use the capabilities of a very powerful tool seems the right thing to do. Especially if it makes your application that much more functional and scalable.

[Ilja Preuss]

Originally posted by R. M. Menon:
Sure, You can still write such tests. In fact putting code in Pl/sql (or Transact SQL) would make it easier - you can write separate tests for stored procedures and Java layers.

Cool. Of course there is more to it: can I run the stored procdures tests in a matter of seconds from my IDE? Etc. pp.

Actually, I'm less interested in this specific point - what I'm trying to convey by those examples is that there is much more to consider than performance and scalability - things that in in some cases might even be much more important, economically.

"over simplistic" - is in the eyes of the beholder.

That's why I wrote "sounded to me", if I remember correctly...

To me doing things that DB can handle best within DB sounds like common sense. To exploit and fully use the capabilities of a very powerful tool seems the right thing to do.

That same argument can be used for hibernate, or any other tool: "We invested so many dollars in hibernate training, and it is such a powerful tool - we should make as much use of it as possible."

Doesn't sound good to me, either.

Especially if it makes your application that much more functional and scalable.

If those are your main concerns. Costs, time to market and a bunch of other variables also need to be part of the equation, in my not so humble opinion. The more of those variables we consider, and the more alternatives we actually know, the better we can come to a reasonable decision.

[Bill Johnston]

Lot of good points - and IMHO a few not so great ones - mentioned here. Me, I program a lot in SQL (mostly Oracle, but a smidgeon MySql too), and PL/SQL; but I certainly do not consider myself to be an "expert", as the term is bandied about in this thread. We write a lot of O9i PL/SQL packages here.

And I certainly do not consider myself to be an "expert" java.sql (JDBC) programmer either; I do not do enough Java programming because we are not a "Java" shop.

But I know enough of both, I think, to know that, just as others have mentioned, there are some things that Java just does better and/or easier than PL/SQL, and the reverse is also true. If an institution has both Java JDBC programmers and SQL programmers of equally high competence levels (preferably both skills in each person); and a management staff with the capability of true objectivity and technical competence to consistently make the right decisions, and at that without budgetary consideration or constraints, then that staff would have the best chance of affecting the most correct balance between the two technologies.

In life, people often stress over the problem of obtaining the very best possible solution to a problem, when often this is not only not possible but often even if and when it is possible, the best possible solution today is not necessarily the best for tomorrow. Good enough is often not only good enough, but the best that can be hoped for.

On the whole, I think, room to maneuver, ease of maintenance, quality of support and cost are more important in real life than is efficience. And even if that were not true from a purely logical standpoint, in most cases management would ensure that these criterion take precedence. That is ... until the system crashes or otherwise produces noticeably poor performance.

My 2cents,

~Bill :roll:

[Roger Chung-Wee]

That same argument can be used for hibernate, or any other tool: "We invested so many dollars in hibernate training, and it is such a powerful tool - we should make as much use of it as possible."

Doesn't sound good to me, either.

It depends where you are coming from. I work for a corporate which uses several products like:

- DB2 on a mainframe
- ORACLE on UNIX
- WebLogic Server on UNIX

Frankly, we are locked into these products, but I don't detect any concern. Caveat: I'm much too lowly to be told about concerns anyway. What matters to the department, I think, is that these products work and that there are support contracts in place. After all, you don't want to tell the CEO that the solution to a major system bug depends on whether someone can answer a call for help on an internet forum. :roll:

Inevitably, proprietary features of these products get used. This may not be ideal, but in the context of what I've described, it is defensible.

[Mark Spritzler]

R. M. Menon

Your last two posts, in regards to my post, I agree with you completely. Those are actually the same things I have been trying to say. I just felt that what you had been posting was to avoid using Hibernate, and that you hadn't looked into Hibernate to make such a claim.

What always ends up happening, no matter what with Databases and Java, is that it doesn't translate as well into OO, and that you end up building these mapping classes and build a layer between the two, to decouple things, and what you end up doing is created another Hibernate, and I hate re-inventing the wheel, especially since they have done such a great job with Hibernate.

it is just that in discussions such as these, it is hard to put together years of experience into words that are good enough to convince the people on the other side of the camp

I agree completley.

So I think in the end, it looks like we end up saying the same thing. I just want it to be easy for Java developers and DBAs to create a system that is the best it can be, and to look at what works best and is easiest to maintain for everyone.

Thanks

Mark