• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JDBC Connection

 
Pol Appan
Ranch Hand
Posts: 144
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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();
}
 
Ernest Friedman-Hill
author and iconoclast
Marshal
Pie
Posts: 24212
35
Chrome Eclipse IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Off to our "JDBC" forum.
 
stu derby
Ranch Hand
Posts: 333
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic