File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Expert Oracle JDBC Programming - book promotion topic Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Expert Oracle JDBC Programming - book promotion topic" Watch "Expert Oracle JDBC Programming - book promotion topic" New topic
Author

Expert Oracle JDBC Programming - book promotion topic

George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
Hello everyone,

I wanted to start the topic for the "Expert Oracle JDBC Programming" book promotion and ask a question that I have been toiling with for a while.

I first learned databases and SQL and then I gained more extensive knowledge of Java, so my initial impetus on any type of web/database development project is to put a lot of business logic in the database.

I understand the implications of doing that are tremendous if the system is to be flexible and distributed or if at some point of its lifecycle it has to accomodate data from a completely different RDBMS the price of my design decision may be very high. That and the fact that I want to be DB implementation independent has forced me to move a lot of my business logic outside of the db, but I know that in some situations performance using stored procedures on the DB side would lead to tremendous gains.

I was wondering if there is any special measure to define where is the line to choose between similiar functionality on both ends, that would help me choose Stored Procedures over a Java class implementation. Should stored procedures be very targeted to extract single pieces of information, but than Oracle's performance on multiply joins maybe much better than trying to compare any kind of utility class in Java??

I would like to ask the author to comment on the subject and give us his thoughts as to what situation or consideration we should have in mind when dealing with Oracle JDBC. Specific examples are greatly encouraged .

Thank you in advance,
George
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
Hello everyone,

I wanted to start the topic for the "Expert Oracle JDBC Programming" book promotion and ask a question that I have been toiling with for a while.


Thanx for starting this very important topic!

I first learned databases and SQL and then I gained more extensive knowledge of Java, so my initial impetus on any type of web/database development project is to put a lot of business logic in the database.

I understand the implications of doing that are tremendous if the system is to be flexible and distributed or if at some point of its lifecycle it has to accomodate data from a completely different RDBMS the price of my design decision may be very high. That and the fact that I want to be DB implementation independent has forced me to move a lot of my business logic outside of the db, but I know that in some situations performance using stored procedures on the DB side would lead to tremendous gains.
I was wondering if there is any special measure to define where is the line to choose between similiar functionality on both ends, that would help me choose Stored Procedures over a Java class implementation.I would like to ask the author to comment on the subject and give us his thoughts as to what situation or consideration we should have in mind when dealing with Oracle JDBC. Specific examples are greatly encouraged .



This is a controversial topic but here are my opinions anyways (consider that almost all my experience is in Oracle.)

In general, when you choose a database, you need to carefully evaluate if your app needs to be database independent (really, truely.) Very few apps need to be really, truely db independent, IMHO. The problem with db independence, as you rightly ponited out, is that it can come at a huge cost - performance, scalability, more bugs, recoding features in the database, many times unusable application - to name just a few. Thus, unless you are very likely to work against more than one databases, your logic for "manipulating" data itself (e.g. insert, delete, select etc.) should lie as close to the database as possible. In other words, I advocate using stored procedures in conjunction with callable statements in general. Does that mean that all the "business logic" should reside in the database? No. Remember if something can be done productively outside database, it should be. For example, if you are doing a financial reporting app, the logic of selecting, updating and deleting data would reside as stored procedures in the database accessed by, say JDBC callable statements API. The logic for transforming the input data to something that just needs to be shown to the user can very well reside outside database - e.g. in the middle tier.

Where should we put the same select/insert/update etc - in Java or stored procedure? In general, it should be put in the stored procedures for various reasons including:
1. Tuning code within database is easier
2. Creating a layer in Pl/SQL can easily divide logic between develoers
skilled in db versus Java
3. It is easier to secure code in PL/SQL (see the sample chapter on security at http://www.bookpool.com/sm/159059407X)
4. There are some performance benefits (e.g. static SQL in PL/SQL is faster.)

Hope this answers some of your questions.
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
First thank you for the reply. Despite it being a controversial topic I think much of the controversy stems from the fact that people have worked/seen/used different approaches that absolutely made sense in their particular situations, but the world is full of situations and designs should be flexible.

One thing that I am going to personally take back from your response is the notion of whether my code should truely be db independent, as I do agree with your comments about scalability and performance. To that I can add my personal view that not using some of the functionality like random functions concatenation etc. but extracting records in java and doing conversions there is usually slower and "harder" (more code) than writing a sql or pl/sql statement. My practice of doing most processing and using simple statement (insert, delete, update etc) against the db has safegaurded me from db version changes, I use MySQL primarily, but I have to say for shortening some operations and improving performance your suggestion is right on the spot.

Thank you
George
Ken Latta
Greenhorn

Joined: Nov 13, 2004
Posts: 2
I realize that PreparedStatements can be a performance win (because they get parsed once and then don't get recompiled) but I'm puzzled by their use in batching large number of inserts or updates. Is it cleaner to use the standard JDBC batching (addBatch/executeBatch) or is there enough of a performance boost to make Oracle's way (setExecuteBatch) worth it? And wouldn't it take really large batches to see a difference? And how does this interact with connection pooling?
R. M. Menon
Author
Ranch Hand

Joined: Mar 15, 2006
Posts: 50
I realize that PreparedStatements can be a performance win (because they get parsed once and then don't get recompiled) but I'm puzzled by their use in batching large number of inserts or updates. Is it cleaner to use the standard JDBC batching (addBatch/executeBatch) or is there enough of a performance boost to make Oracle's way (setExecuteBatch) worth it? And wouldn't it take really large batches to see a difference? And how does this interact with connection pooling?


The main reason batching helps in such cases is that it saves on network round trips. If you want to transfer 100 books from one shelf to another physically (Asssuming you have to do it manually), wont it be better if you transfer 4 or 4 books in one go - you make fewer trips that way. Of course, it is harmful to try and transfer too many at a time too - so in JDBC terms that translates to the fact that you cant simply use a huge batch size.

I discuss this in detail (including comparing standard and Oracle batches) in the sample chapter 5 at http://www.bookpool.com/sm/159059407X
Amy Medrat
Greenhorn

Joined: Mar 05, 2006
Posts: 15
While using connection pooling, what all care the java application should take if there is a database connectivity problem/database crash/database reboots?
Such that when the database restarts the application resumes the functionality automatically.


medRat
ak pillai
author
Ranch Hand

Joined: Feb 11, 2006
Posts: 288
In my view there is no one size fits all solution. My approach would be stored proc are hard to maintain and not portable.

Stored proc can offer better perfomance for overnight batch processes which involves more updates/inserts/deletes than reads. But for web based online applications are predominantly read based system, so I would use Hibernate, etc. It is also not a good practice to have your business logic scattered in Stored proc and java Classes.

Another aspect is that middle tier is more scalble than scaling the database. Say your demand increases from 100 concurrent clients to 500 concurrent clients on the web, you can add another middle tier server to scale your application. But if you have lot of your code going through the Database stored proc then your Database server will become a bottle-neck.

I would say, generally any large application wil have about 75% - 85% logic in the Java classes and 15% - 25% in stored proc. Having said that some spceial applications requiring more update/insert/delete operations than read would require special considerations.


java j2ee job interview questions with answers | Learn the core concepts and the key areas
Ankur Srivastava
Ranch Hand

Joined: May 11, 2004
Posts: 62
Another aspect is that middle tier is more scalble than scaling the database. Say your demand increases from 100 concurrent clients to 500 concurrent clients on the web, you can add another middle tier server to scale your application. But if you have lot of your code going through the Database stored proc then your Database server will become a bottle-neck.


I am a java apps programmer and have not worked much on the DB side, but i thought if we do db clustering we can address the issue mentioned above or is it that the stored prcodures will not scale well in clustered environment or I should ask should we use stored procs in clustered db environment??

Thank you in advance
- Ankur
George Stoianov
Ranch Hand

Joined: Jan 15, 2006
Posts: 94
The point that Arul makes is really good I think the percentage maybe correct for a read only application and maybe quite different for a transactional system, as pointed out, that requires a lot of inserts/deletes and updates.

Things will certainly change if we look at the corporate web site that mostly presents information and does some logging compared to the admin interface that gives access to all the data management that ensure the data integrity is preserved. I think if complexity in the back-end increases for example before I make an insert in a customer order table I need to update a shipment table, make a few other checks I would say this type of logic is a candidate for trigger or a stored procedure. On the other hand if there is functionality that needs to update the user interface as a result of a choice or action that would most probably be in the middle tier with the Java business logic.

In my mind the question is also one of maintainability. Maintainability is also affected by the available skills in the company so in real world scenarios implementations tend to diverge from what seems the best on paper or even is proven to be the best. I find conversation like this one and a book like this one gives one more valuable information to make those decision sensibly and with good thought of the current and future expectations.

I wanted to ask if the Oracle Express version supports all functionality that a regular full scale 10g server, for example, would have. Is the 4 gig file sizes the only limitation. Do I have to make any adjustments using JDBC with it? Does it use the same driver?

Thank you.
George
adolfo cortes
Greenhorn

Joined: Jun 19, 2002
Posts: 2
Hi,
I agree with the opinion that there is not a magic potion to solve this problem in all projects, in all situations and project evolutions. We can have our Business Logic implemented in DB as the better choice for a project, and this could change to be better a Java Implementation of Business Logic, and reverse. All of the reasons we can read in your questions and responses are good depending on the situations you are considering, and i think that you will not know the best choice at the first of your project life ( development included).

In deep, this is a bet, and the future of your project will decide.

I think that the most important way to solve this question is to be ready to solve a change in this situation or this question when it becomes a risk.

� How can you do this ? I think that you can do it throught flexibility, what you need is to use a flexible well defined architecture that allows you to migrate form one to another strategy attending to your demand.

So your bet could be adapted always.

I know it is difficult, but, a separated level architecture must give you a easy way to do this.

At the First Time, you need a deeper layer for database communication, providing basic operations ( connect, execute, ... ) and dont use this JDBC functions directly from your BL. it will give you a way to change what kind of statement to use, callable, prepared, ..., and much more.

A second layer in deep will provide the data handling, there you can build statements to execute, and use the deeper layer to interactuate with the database. ( how to insert, modify or delete a customer, and another specific operations in my database must be written here )

A third layer in deep where you could write your BL in Java, what to do with a customer , invoking multiple data handling methods.

A four layer in deep where you could write the integration layer of your BL in the rest of the applications, transversal operations, parameter testing... must be done here, before to invoke to the BL.

four layer methods must do common operations, exception control... and call only one BL method.

third layer methods (BL) can be twice:

- If you evaluate that a DB BL is the better choice, your data handling must only call your DB procedures, and the BL java layer must only call one data handling method that builds de call to the procedure and the process of the result.

- If you evaluate that a JAVA BL is the better choice, your data third layer must call and operate on data using propetary code and calling multiples data handling layer methods.

so 1:M or a 1:1 strategy can be implemented, and to migrate, all the application from an strategy to another can be easy, and also can combine both situations, adapting your project situation to the best choice in this moment.

So i think that flexibility in application architecture is the key to bet for a solution with minimal risks. But i think that it will be always a bet, for the best future prediction.

Adolfo.
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Expert Oracle JDBC Programming - book promotion topic