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

Update database table using JDBC efficiently

Hello,

I have a flat file containing fileds such as:

UniqueID(Email address), MainCode, Subcode1, Subcode2, Subcode3, subcode5.

I am constraint to using a Class with instance variables to extract the fields of this file.

Assuming there could be thousands of records in the file and millions of records in the Database table.


Once I have the objects of this class, I need to look at:

First, If the UniqueID(Email Address) exists in the Database table.
If it does, then, Extract the MainCode, and depending on the value of this field....

update certain fields of the Database table...

It might be the case that certain UniqueID(Email Address) does not exist in the table.

I need to know what would be a better approach - in terms of updating the database table...

Just iterate throught the ArrayList of this Class objects and update the database table,

Or some sort of advanced JDBC features...???

Some illustration is appreciated...

Please advise ..

Thanks and regards..

_Esam
0
_Esam
Asked:
_Esam
  • 8
  • 6
1 Solution
 
Mayank SAssociate Director - Product EngineeringCommented:
Try to put it in a stored procedure and just call the SP, passing the parameters to it.
0
 
_EsamAuthor Commented:
Sorry, missed one requirement:

While updating the table, I need to know which records were successfully updated...and for that record...
get the email address and write it to a file..

How I do that while using SP...
Could you provide some illustration or links to this problem....
I am not proficient with SP...

Thanks..

_Esam
0
 
Mayank SAssociate Director - Product EngineeringCommented:
You can return the IDs of the rows which got successfully updated as OUT parameters:

http://www.javaalmanac.com/egs/java.sql/CallProcedure.html 
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
_EsamAuthor Commented:
What if some of the parameter values are null?

_Esam..
0
 
Mayank SAssociate Director - Product EngineeringCommented:
I think that can be handled within the SP, there shouldn't be any issues.
0
 
_EsamAuthor Commented:
Could you please provide some link(s) or illustration on this!
-- I mean.... java code containing SP and SP doing PL/SQL ....and returning results to the SP..
java using the results?


Is this too much to ask :)
I really need to look at some illustration...

I had a chance to look at the java almanac examples earlier...


Thax..
_Esam
0
 
_EsamAuthor Commented:
Well,
I am not familiar with certain aspects of SP.
Here is the scenario:

While updating the table with the SP,
I find out that one column value is more than what it is supposed to be -- I find it with the value passed to SP...
Now, I want to log an error from the java application that for this record ... this column value is more that what it is supposed to ...
How I do that from SP/Java????

Please advise..

Thax..
_Esam
0
 
_EsamAuthor Commented:
I need an answer to my previors posts.........

Thax....._Esam.........
0
 
Mayank SAssociate Director - Product EngineeringCommented:
Do appreciate that I have my own work to do as well so I am not always online on EE :) and I guess we are in 2 different time-zones, so be patient.

The Java code which is used for calling SPs is in the Java almanac sample. You will also get a complete tutorial on JDBC at http://java.sun.com/docs/books/tutorial/jdbc/

As for writing SPs, I suggest you ask a Q in the DB forums - they will be able to help you better.

>> How I do that from SP/Java????

Once you have the values returned from the SP in a Java variable, you can do it. For example, let's say the SP returns it in an OUT parameter as a comma-separated value. It can be done like:

String[] values = outParameter.split ( "," ) ;

for ( int i = 0 ; i < values.length ; i ++ )
  log message for values[i]
0
 
_EsamAuthor Commented:
Yes, Indeed, I do appreciate all the help and learning to be more patient  :)
I did think about posting a SP Q in the DB forum but I also wanted to know more from the Java side of it.

I think I needed to explain the processing requirements... then it would have made more sense...
I will get back with the post .. in a few hours (5-6)..

Thanks and regards,
_Esam
0
 
_EsamAuthor Commented:
You can look at this Q in the Oracle DB forum that explains the scenario...

http://www.experts-exchange.com/Databases/Oracle/Q_21857079.html

Let me know what you think..


Thanks..
_Esam
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> but I also wanted to know more from the Java side of it

From the Java side, things are fine. Java is not worried about how the SP works internally. From the Java side, you can call any kind of SP and you can also cache the data locally if you want, in a disconnected rowset rather than a connected result-set.
0
 
_EsamAuthor Commented:
Hi,

I also need to know what are my options if I don't have the choice on using SP?
Can I use batchUpdate? with JDBC?

How do I do that for the above condition? as mentioned in the first post?

Thanks.
_Esam
0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> Can I use batchUpdate? with JDBC?

Yes, you can. Use statement.executeBatch ()
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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