_Esam
asked on
How to Update a table based on object values?
Hi,
I have some objects that are from this class:
class Email{
private String emailaddress;
private String TypeCode;
private String SubCode;
private String emailaddressnew;
private Date DateTime;
}
I populated an array of these objects as Email[] array.
These Email[] array will be used to update the following table:
Table: Email
EMAIL_TEXT Varchar2(100) not null PK
FAILURE_COUNT Number(4)
IN_DATE Date
OUT_DATE Date
SRC Varchar2(3)
CREATETMS Timestamp(6)
UPDATETMS Timestamp(6)
Based on the TypeCode value of the object, I will need to update certain fields of the table.
But before this I will have to make sure that the email address even exists in the table.
It is possible that certain email addresses will not be in the table --- in this case I will simply have to do a insert into this table with some object values and appropriate values.
So, if the email address does not exists in the table, do a insert
if the email address exists in the table, do a update.
Need some help on logic and jdbc illustration on this...
Thanks.
_Esam.
I have some objects that are from this class:
class Email{
private String emailaddress;
private String TypeCode;
private String SubCode;
private String emailaddressnew;
private Date DateTime;
}
I populated an array of these objects as Email[] array.
These Email[] array will be used to update the following table:
Table: Email
EMAIL_TEXT Varchar2(100) not null PK
FAILURE_COUNT Number(4)
IN_DATE Date
OUT_DATE Date
SRC Varchar2(3)
CREATETMS Timestamp(6)
UPDATETMS Timestamp(6)
Based on the TypeCode value of the object, I will need to update certain fields of the table.
But before this I will have to make sure that the email address even exists in the table.
It is possible that certain email addresses will not be in the table --- in this case I will simply have to do a insert into this table with some object values and appropriate values.
So, if the email address does not exists in the table, do a insert
if the email address exists in the table, do a update.
Need some help on logic and jdbc illustration on this...
Thanks.
_Esam.
What DB is it?
ASKER
It's Oracle 9.2
_Esam
_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
Could you please illustrate as to how?
I can get the updatable resultset and use it to update or to insert otherwise?
WILL THIS BE EFFICIENT IF THERE ARE MILLIONS OF RECORDS?
Thax.
_Esam....
I can get the updatable resultset and use it to update or to insert otherwise?
WILL THIS BE EFFICIENT IF THERE ARE MILLIONS OF RECORDS?
Thax.
_Esam....
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok.. I got some idea but not quit :)
I still need to figure out how I set up the logic to see if the record exists in the database table,
If it exists, then do the update
If it does not exist, then do the insert?
Thanks.
_Esam...
I still need to figure out how I set up the logic to see if the record exists in the database table,
If it exists, then do the update
If it does not exist, then do the insert?
Thanks.
_Esam...
That's the logic yes. The latter you'd do with moveToInsertRow (see article at last link)
ASKER
I am a little confused!
Do I use a Statement? PreparedStatement ? ResultSet (Updatabae) ? addBatch for the Statement objects???
Let me know ...
_Esam
Do I use a Statement? PreparedStatement ? ResultSet (Updatabae) ? addBatch for the Statement objects???
Let me know ...
_Esam
>>PreparedStatement ? ResultSet (Updatabae) ?
the above two. Read the last article
the above two. Read the last article
ASKER
Well, I read the last article...
I think I understood what is said there but not how I relate my problem there???
I don't see how I can use PreparedStatement with Updateable ResultSet !!!
It has to be Statement object?
Let me know...
Thax.
_Esam.
I think I understood what is said there but not how I relate my problem there???
I don't see how I can use PreparedStatement with Updateable ResultSet !!!
It has to be Statement object?
Let me know...
Thax.
_Esam.
ASKER
I wonder if there is an alternate way of doing this ---- avoing updatable resultset ...
Could it be very time consuming if there are millions of records??
_Esam.....
Could it be very time consuming if there are millions of records??
_Esam.....
An alternative would be to do a select followed by an insert, and yet another would be to catch dup key and then update
ASKER
I am trying to formulate the logic...a little stuck with it..
_Esam...
_Esam...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi CEHJ,
Yes, I finally started thinking like you said above...
I will have to implement and test it tomorrow..done for the day....
I will have to get back to it once more...(sorry to keep the Question hanging.. i don't like that .. :)
Thanks...much..
Regards...
_Esam....
Yes, I finally started thinking like you said above...
I will have to implement and test it tomorrow..done for the day....
I will have to get back to it once more...(sorry to keep the Question hanging.. i don't like that .. :)
Thanks...much..
Regards...
_Esam....
No problem
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
So confused now :)
What do I do?
I want to insert rows if they don't exist in the database table!
I want to update rows if they exist in the database table!
The table will have 4-5 millions of records...
Use sepater queries for these two functionalities?
Use one query with Updatable ResultSet (insert/update) - how I use PreparedStatement with this?
Let me know ..
Thanks..
_Esam
What do I do?
I want to insert rows if they don't exist in the database table!
I want to update rows if they exist in the database table!
The table will have 4-5 millions of records...
Use sepater queries for these two functionalities?
Use one query with Updatable ResultSet (insert/update) - how I use PreparedStatement with this?
Let me know ..
Thanks..
_Esam
ASKER
This is my m_Select :
String m_Select = "SELECT EMAILTEXT, FAILURECT, INDATE, OUTDATE, SRC, CTMS, UTMS FROM EMAIL WHERE EMAILTEXT= ?";
public void upDateContent(){
PreparedStatement lPStatement = null;
boolean lInsertComplete = false;
mConnection.setAutoCommit( false);
lPStatement = mConnection.prepareStateme nt(m_Selec t, ResultSet.TYPE_SCROLL_SENS ITIVE,
ResultSet.CONCUR_UPDATABLE );
//rs = mConnection.createStatemen t();
// add the statements for batch processing
for (int i=0; i<lRecords.length; i++) //my Email[] - array of Email objects
{
lPStatement.setString(1, lRecords[i].getEmailAddres s());
lResult = lPStatement.executeUpdate( );
System.out.println("Nuber of records inserted: " + lResult);
}
else{
}
//lStatement.executeQuery( m_insertEm ailRecordS QL);
//lPStatement = mConnection.prepareStateme nt(m_Inser t);
}
mConnection.commit();
lInsertComplete = true;
}
How/where do I create the ResultSet?
Such that I can do it like you said above:
ResultSet rs = ps.executeQuery();
if (rs.next()) {
// you can update it
}
else {
rs.moveToInsertRow();
// do insert
}
_Esam
String m_Select = "SELECT EMAILTEXT, FAILURECT, INDATE, OUTDATE, SRC, CTMS, UTMS FROM EMAIL WHERE EMAILTEXT= ?";
public void upDateContent(){
PreparedStatement lPStatement = null;
boolean lInsertComplete = false;
mConnection.setAutoCommit(
lPStatement = mConnection.prepareStateme
ResultSet.CONCUR_UPDATABLE
//rs = mConnection.createStatemen
// add the statements for batch processing
for (int i=0; i<lRecords.length; i++) //my Email[] - array of Email objects
{
lPStatement.setString(1, lRecords[i].getEmailAddres
lResult = lPStatement.executeUpdate(
System.out.println("Nuber of records inserted: " + lResult);
}
else{
}
//lStatement.executeQuery(
//lPStatement = mConnection.prepareStateme
}
mConnection.commit();
lInsertComplete = true;
}
How/where do I create the ResultSet?
Such that I can do it like you said above:
ResultSet rs = ps.executeQuery();
if (rs.next()) {
// you can update it
}
else {
rs.moveToInsertRow();
// do insert
}
_Esam
:-)