Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

JDBC: how to write high performance preparedstatement

Posted on 2011-03-18
4
Medium Priority
?
541 Views
Last Modified: 2012-05-11
Hi: I have a Java application, which needs access database. So I write JDBC. The JDBC will create a connection(or take a connection from connection pool), then create Preparedstatement, execute it, then connection commit, and prepared statement close, and connection goes back to connection pool. I find this way there are many prepared statement execution in database trace log. Are there some way to create a Prepared statement for once and execute lots of times. Or are there introduction/web links for high performace JDBC programming?

0
Comment
Question by:JianJunShen
  • 2
4 Comments
 

Author Comment

by:JianJunShen
ID: 35163959
batch does not work at my case. Since for one subject, we have 7 sub tables, they are different prepared statement. It is multi thread application, one has to suppose thread might be dead. One thread picks up one subject and then store it. But how to save the preparement sql execution time.
0
 
LVL 47

Accepted Solution

by:
for_yan earned 1600 total points
ID: 35163964
I don't expect any issue in interaction of PreparedStatement and ConnectionPooling.
PreparedStatemnt will give you some advantage if you need to execute multiple similar
SQL statements. As long as you execute them, you hold on to your connection.
If you release Connection, next time in the simplest case you'll have to
create PreparedStatement again. But if you execute sataements rarely enough that
it makes sense to release connection then creating PreparedStatement would not
take relatively much time and probably in that situation will not be very important to you.
Even though there is now some talk about persistence of PreparedStatement in case of pooling,
I guess in most common situations that does not seem to be crucial. Perhaps of course there are some
special cases.

In general, to tell the truth, in my personal experience, with all this talk about PreparedStatements
I never saw something really dramatic in performance improvement because of introducing PreparedStatement.
Where I really saw dramatic improvement is if you can do some part of your operations
exclusively on the Oracle server, rather than through Java client operation.
This is of course obvious thing, but these kind of changes gave me orders of magnitudes time savings.
With PreparedStatements never saw anything on the same level.

 
0
 
LVL 92

Assisted Solution

by:objects
objects earned 400 total points
ID: 35163971
if the prepared statements are different then theres not a lot more you can do.
The power of the PreparedStatement is the ability to use the same statement with different parameters which allows the driver to 'compile' the statement and just run it with different params.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Suggested Courses
Course of the Month10 days, 2 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question