This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes OO, Patterns, UML and Refactoring and the fly likes is it a good solution ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Engineering » OO, Patterns, UML and Refactoring
Bookmark "is it a good solution ?" Watch "is it a good solution ?" New topic
Author

is it a good solution ?

zb cong
Ranch Hand

Joined: Jan 14, 2002
Posts: 416
hello
all the database provide the function and stored procedure to allow the programmer to encaptulate part of the application logic,such as the java stored procedure from the oracle,it said that it will provide more performance promotion.
who can tell me if such a solution is a good mechanism from the aspect of performance and design pattern?
thank
Junilu Lacar
Bartender

Joined: Feb 26, 2001
Posts: 4445
    
    5

Here's my 2 cents about stored procedures:
Pros:
- Can be more efficient, performance-wise. Depends on how complicated the DB-operations are. Also depends on how well the stored procs can be written.
Cons:
- Separates some of the application logic from the rest if you mix stored procs and dynamic SQL calls.
- The above can makes maintenance a bit more complicated/difficult especially if the stored procs are written in a different language (e.g. PL/SQL or COBOL) from the rest of the application (e.g. Java)
- Also can make debugging complicated/difficult
- May be more difficult to change on the fly.

In general, I would advise to use stored procs only when the performance gains far outweigh other factors to be considered such as maintainability and ease of debugging / changing.
If you use stored procs, insist that a contract/API be clearly defined and agreed upon for each one in order to minimize finger pointing between "application" and "database/stored proc" developers when bugs do show up.


Junilu - [How to Ask Questions] [How to Answer Questions]
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
I'd also add another downside. Using any kind of database-specific features can very easily lock you into a specific vendor's database, and even a specific version of a specific database.
This may not seem a problem while you are exploring the exciting features of a new DB release, but think about 18 months time when that release is obsolete, and you are forced to still use it, known bugs and all.
Java is platform-neutral. You can (mostly) upgrade hardware and operating systems with no problems. Stored procedures (whatever their language) are not. I would only consider using stored procedures for the same reasons I would consider using native methods.


Read about me at frankcarver.me ~ Raspberry Alpha Omega ~ Frank's Punchbarrel Blog
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Frank Carver:
Java is platform-neutral. You can (mostly) upgrade hardware and operating systems with no problems. Stored procedures (whatever their language) are not.

Well, there are Java Stored Procedures arising. AFAIK they are rather platform neutral and can even be executed outside the RDBMS for testing and debugging purposes. I am not totally sure, though...


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
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
My point is really that stored procedures are very database-specific. Different databases support different types of stored procedures, in different languages, with different features.
Using any sort of stored procedures risks locking your development in to a particular version of a particular database product from a particular vendor, and may even lock you into running it on a particular platform.
Using JDBC and standard SQL allows you to pick and choose whatever version of whetever database product from whatever vendor you like.
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
From all I know it seems as if Java Stored Procedures actually *are* using JDBC and other standard Java features - they are simply normal Java code executed directly in the database server.
Java Stored Procedure links
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
Yes, but not all databases have them, and not all databases implement them in the same way.
We all know that writing some Java code which will only run on Windows is probably a bad idea. How can writing some Java code which will only run on Oracle be seen as a good idea ?
Jes Sie
Ranch Hand

Joined: Jul 24, 2001
Posts: 188
Originally posted by Frank Carver:
Yes, but not all databases have them, and not all databases implement them in the same way.
We all know that writing some Java code which will only run on Windows is probably a bad idea. How can writing some Java code which will only run on Oracle be seen as a good idea ?

Writing codes which runs only on Oracle may not be a bad idea......as I've worked with regional asian company which openly declares "We are married to Oracle; if they die, we die too".
This is just a real world example


Thank you.<br /> <br />- Jess Sie<br /> <a href="mailto:jess@see-consulting.com" rel="nofollow">jess@see-consulting.com</a>
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Frank Carver:
Yes, but not all databases have them, and not all databases implement them in the same way.
We all know that writing some Java code which will only run on Windows is probably a bad idea. How can writing some Java code which will only run on Oracle be seen as a good idea ?

It can't!
But I don't think that's specific to Stored Procedures. As long as it doesn't force you to make your code more dependent on a specific product, I can't see anything wrong in using stored procedures.
Do they force you? I am not sure...
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
Do they force you? I am not sure...
I routinely test my home-written database applications on MySQL, PostgreSQL, Access/Jet, and Hypersonic SQL. Although I usually need a few tens of lines of code per database for optimisation and customisation, the same basic application runs unchanged on all these databases.
Although I often use Oracle for clients, I don't have Oracle set up for my own use. In the past, though, I have encountered stored-procedure code which prevented the application being run on anything except Oracle 7.3.4, though.
SQL, accessed via JDBC, runs on all the above databases. I would be astonished to hear of any stored-procedure solution which will run on all those databases. Please correct me!
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Frank Carver:
Do they force you? I am not sure...
I routinely test my home-written database applications on MySQL, PostgreSQL, Access/Jet, and Hypersonic SQL. Although I usually need a few tens of lines of code per database for optimisation and customisation, the same basic application runs unchanged on all these databases.
Although I often use Oracle for clients, I don't have Oracle set up for my own use. In the past, though, I have encountered stored-procedure code which prevented the application being run on anything except Oracle 7.3.4, though.
SQL, accessed via JDBC, runs on all the above databases. I would be astonished to hear of any stored-procedure solution which will run on all those databases. Please correct me!

I am understanding Java Stored Procedures to be simply general Java code as your code mentioned above, running directly on the database server. I might be wrong, though...
Frank Carver
Sheriff

Joined: Jan 07, 1999
Posts: 6920
I am understanding Java Stored Procedures to be simply general Java code as your code mentioned above, running directly on the database server
In principle, yes. But the database software has to know what to do if you ask it to run one. Imagine I issue some SQL such as "call ugh('hello', 13)" the DB must be able to locate the appropriate stored procedure, understand what language it is written in, load the object code/bytecode if necessary, instantiate any objects, pass in any appropriate parameters, run the code, extract any appropriate return parameters etc.
To do all that, the DB has to be built to understand (a) stored procedures as a general concept, and (b) how to deal with the specific language you chose to write them in. If Java stored procedures were automatically available, you'd be able to use them in old versions of Oracle as well as newer ones. (AFAIK, you can't.)
Can you imagine Microsoft building a version of their "Jet" database engine which supports Java of any sort ?
Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Frank Carver:
Can you imagine Microsoft building a version of their "Jet" database engine which supports Java of any sort ?

Uh, no... :roll:
But then, I could easily imagine an architecture allowing me to switch between stored and not-so-stored procedures. But I might be dreaming...
[ January 01, 2003: Message edited by: Ilja Preuss ]
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: is it a good solution ?
 
Similar Threads
How to get a return value from the stored procedure using callable statement
java.lang.OutOfMemoryError on very long time consuming usecase
Servlets and PropertyResourceBundle
Do we over-use hibernate?
Doubt in ePratice Exam Question