Meaningless Drivel is fun!*
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: 24183
    
  34

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.

 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Connection
 
Similar Threads
JDBC Connection
cannot insert datetime type
Storing Time values?
SQL Dates
How can I generate dynamic tables taking values from database