• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 330
  • Last Modified:

How to batchUpdate in JDBC?

Hi,

This is the simple scenario.
I have an array of objects (few thousands) with the instance variables EmailId, Code, SignoutDate, TimeStamp

I want to get the values from these objects and update just one table, EMAIL table.

EMAIL table has: EmailID, SignOutDate, SignInDate, FailureCount

I have to look at the Code, and based on this, update the EMAIL table.

If I see the Code says the email was Undeliverable, I will need to Increment the FailureCount from 0 to 1.
If I see the Code says the email wants to signout, I will need to put the SignOutDate in the EMAIL table.

Since the EMAIL table will have millions of records, WHAT ARE MY OPTIONS ON USING JDBC BATCH UPDATE?
I MAY NOT WANT TO USE STORED PROCEDURES?

Could you let me know how to go about that?

Thax...

_Esam
0
_Esam
Asked:
_Esam
  • 6
  • 5
1 Solution
 
_EsamAuthor Commented:
Hi CEHJ,

I need little more clarification with the logic itself :).
I did find out about that but that's just for simple insert???
I need to know how I could use this batchUpdate for modifications with certain conditions?
Could you please explain that given the conditions in my post?

Thanks...
_Esam
0
 
CEHJCommented:
Something like

ps.setInt("FailureCount", 0);

if ("Undeliverable".equals(code)) {
      ps.setInt("FailureCount", 1);
}
else if ("signout".equals(code)) {
      ps.setDate("SignOutDate", someDate);
}
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
_EsamAuthor Commented:
How efficient it would be if I have few thousand objects and 5 million records to go through for the updates?

Thanks.
_Esam
0
 
CEHJCommented:
About as efficient as it can get probably
0
 
_EsamAuthor Commented:
Then what would be an inefficient approach?

_Esam
0
 
CEHJCommented:
Not using a PreparedStatement, not using a batch, etc.
0
 
_EsamAuthor Commented:
Can I have an example of this batchUpdate with Update statment?
This one shows insert only...

I need some illustration on using the update sql....

Thanks and regards,,,

_Esam..
0
 
CEHJCommented:
String sql = "update EMAIL set EmailID = ?, SignOutDate = ?, SignInDate = ? FailureCount = ?";
PreparedStatement ps = conn.prepareStatement(sql);

Set the parameters as per my example above then

0
 
_EsamAuthor Commented:
Looks Good for now...
Will be back leter.....:)

Thanks much CEHJ...

_Esam
0
 
CEHJCommented:
OK :-)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now