Java for batch db update..

[Maky Chopra]

Hi Folks,
I need to do a batch update of db field (thanks for help with the SQL statement for this)..
I have a

public class CreditCard {
long cardNum;
int expiryDate;
public void UpdateExpDate(Long inCardNum, inExpiryDate){
Connection conn = ...
.
.
.
executeQuery(sql);
..
}
}

and for the main() program, I have

public class UpdExpDt{
public static void main(String[] args){
..
.
CreditCard cc = new CreditCard();
read i/p file.. set boolean eof = false on end..
while (!eof) {
cc.UpdateExpDate(card num from i/p file, exp dt from i/p file);
}
}
}

Now, when i cann UpdateExpDate() for every record in the i/p file, it needs to make a connection.. I know this can be very expensive in terms of resources.. One way is that I can setup a connection pool class and use connections from that. Other than that, what can I do to batch process this i/p file ?
Thanks in advance for any help you can provide..

[Jamie Robertson]

have a connection as a class variable.

public class CreditCard {
long cardNum;
int expiryDate;
Connection con;
PreparedStatement ps;
CreditCard()
{
//in constructor connect to database
con = ...connect to database
ps = con.prepareStatement("...?..?..");
}
public void addBatchExpDate(Long inCardNum, inExpiryDate)
{
ps.setInt(1,inCardNum);
ps.setDate (2,inExpiryDate);
ps.addBatch();
}
public void executeBatch()
{
ps.executeBatch();
//plus error/exception/updatecounts
}

one connection/prepared statement per object created.
Jamie

[This message has been edited by Jamie Robertson (edited September 13, 2001).]

[Maky Chopra]

Thanks Jamie !! Just moving the conn to my constructor cut down the time to load 100000 records (in a P-III 800Mhx NT machine with mySQL db) from 3 minutes to 43seconds
Will try the batching tomorrow and see if there's any dramatic improvement ... Thanks again.