aspose file tools*
The moose likes JDBC 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
Bookmark "JDBC Connection " Watch "JDBC Connection " New topic
Author

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;
Class.forName("oracle.jdbc.driver.OracleDriver");
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);
ps1.executeUpdate();


while (rs1.next()){
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);
ps.executeUpdate();
ps.close();
}


// String qry2 = "insert into card_det(empno)values('"+v1+"')";
ps1.close();
System.out.println("Connected");
}
catch(Exception e)
{
e.printStackTrace();
}


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

Joined: Jul 08, 2003
Posts: 24166
    
  30

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.
http://java.sun.com/j2se/1.5.0/docs/api/java/sql/Statement.html
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.

 
It is sorta covered in the JavaRanch Style Guide.
 
subject: JDBC Connection
 
Similar Threads
Storing Time values?
SQL Dates
How can I generate dynamic tables taking values from database
cannot insert datetime type
JDBC Connection