File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes JDBC Connection Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "JDBC Connection " Watch "JDBC Connection " New topic

JDBC Connection

Pol Appan
Ranch Hand

Joined: Aug 26, 2004
Posts: 144
I have a Java Stored Procedure which I deploy to Oracle 9i database.

In one of the method I am performing the following operations:
Quering a table and storing the values in varibales, befrore inserting I am truncating the table and then inserting the values into the table.

Is there any way I can optimise and improve the perfomance?

Pasted below is my code:


try {
Connection con;
con = DriverManager.getConnection("jdbc racle:thin:@localhost:1580 rod","portal","123");
Statement stmt = con.createStatement();
String qry = "select num,card_number,ISSUE_st_DT st_dt,ISSUE_exp_DT exp_dt,recharge_balance from table@LM.SF.COM";
System.out.println("select query "+qry);
ResultSet rs1 = stmt.executeQuery(qry);

qry3 = "truncate table card_det";
ps1 = con.prepareStatement(qry3);

while ({
empno = rs1.getString("card_num");
card_no = rs1.getLong("card_number");
issue_st_dt = rs1.getDate("st_dt").toString();
issue_exp_dt = rs1.getDate("exp_dt").toString();
balance = rs1.getDouble("recharge_balance");
qry2 = "insert into card_det(empno,CARD_NUMBER,ISSUE_DT,EXP_DT,BALANCE)values('"+empno+"','"+card_no+"',to_date('"+issue_st_dt+"','YYYY-MM-DD'),to_date('"+issue_exp_dt+"','YYYY-MM-DD'),'"+balance+"')";
// v1.addElement(rs1.getObject("card_num"));
// non_kwt = rs1.getString("FEMALE");
System.out.println("value of st_dt "+issue_st_dt);
System.out.println("insert qry "+qry2);
ps = con.prepareStatement(qry2);

// String qry2 = "insert into card_det(empno)values('"+v1+"')";
catch(Exception e)

Never take anything for granted because you never know when you will lose it
Ernest Friedman-Hill
author and iconoclast

Joined: Jul 08, 2003
Posts: 24199

Off to our "JDBC" forum.

[Jess in Action][AskingGoodQuestions]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
1) Retrieving through a DB link can slow things down a bit; if possible, use 2 connctions and connect directly to the database instead of using the "@LM.SF.COM" link.
2) Oracle usually benefits from setting a higher fetch size; Oracle's default is 10 rows, 100 rows is often a better first guess. See the setFetchSize method.
setFetchSize() is of course inherited by PreparedStatement. Alternately, you could use Oracle's non-standard extension to Connection, setPrefetchSize().
3) The most obvious performance problem with this is that you aren't anywhere close to using PreparedStatement correctly.

I agree. Here's the link:
subject: JDBC Connection
It's not a secret anymore!