_Esam
asked on
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
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
Mayank S
Try to put it in a stored procedure and just call the SP, passing the parameters to it.
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What if some of the parameter values are null?
_Esam..
_Esam..
I think that can be handled within the SP, there shouldn't be any issues.
ASKER
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
-- 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
ASKER
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
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
ASKER
I need an answer to my previors posts.........
Thax....._Esam.........
Thax....._Esam.........
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]
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]
ASKER
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
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
ASKER
You can look at this Q in the Oracle DB forum that explains the scenario...
https://www.experts-exchange.com/questions/21857079/PL-SQL-Stored-Procedure-in-Java-App.html
Let me know what you think..
Thanks..
_Esam
https://www.experts-exchange.com/questions/21857079/PL-SQL-Stored-Procedure-in-Java-App.html
Let me know what you think..
Thanks..
_Esam
>> 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.
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.
ASKER
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
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
>> Can I use batchUpdate? with JDBC?
Yes, you can. Use statement.executeBatch ()
Yes, you can. Use statement.executeBatch ()