How to update or insert records based on object values?

Hi,

I have some objects (10-25 thousands) Email[] with field values (one of them - email_address).

I want to test if there exists a record in my EMAIL table based on the email_address) field of my Email[] objects.

If it exists, then update the record, if it does not exists then insert it ---

Please provide me with some good options on how to go about doing this?

Of course, some batch processing would be good...

The Q may need some elaboration..and thus full points...

Thanks..
_Esam
_EsamAsked:
Who is Participating?
 
mukundha_expertCommented:
i made a mistake at first ... no need to catch the exception

the execute update method will return the number of rows affected.

if the number of rows affected by the update query is 0 then it means that the record was not there in the data base , so insert the record .


String update = " update email set FAILURECT = FAILURECT + 1 where primarykey = ? "

String insert = "INSERT INTO EMAIL (EMAILTEXT, FAILURECT, INDT, OUTDT, SRCCD, CREATETMS, UPDTTMS) " + "VALUES (?, ?, ?, ?, ?, ?, ?)";

preparedStatement = connect.prepareStatement ( update ) ;
preparedStatement = connect.prepareStatement ( insert ) ;

// set the values

int ret = preparedStatement.executeUpdate () ;

if ( ret == 0 )
    preparedStatement1.executeUpdate () ;


0
 
CEHJCommented:
Isn't this at least the third time you've asked this question? ;-)
0
 
_EsamAuthor Commented:
I still haven't figured out how to efficiently do this... help me out  :(

I tried few ways but with no concrete result :)

Remember the updatable resultset.. wasn't working for me?

Any good suggestion??

Thax
_Esam

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
_EsamAuthor Commented:
Should I do two sql query for this?

_Esam
0
 
CEHJCommented:
>>Remember the updatable resultset.. wasn't working for me?

Because of the timestamp wasn't it?

The standard way would be to do a select and then an insert/update/.

Another way - kludgy but maybe more efficient is to do an insert and then catch a dup key exception (or not) and then do an update (or not)
0
 
_EsamAuthor Commented:
This is what I finally was thinking to solve it like:

1. Create an ArrayList m_Emails;
2. Do a select, and return all the email_addresses that match the PreparedStatement as Select the email_address from the Email table where the email_text is like ? ( ps.setString(email_address).
3. Now I want go through a loop.

Email[] emlbhv;
for (i=0; i < emlbhv.length; i++){

if (m_Emails.contains(emlbhv[i].getEmailAddress()))
 then, do the update here



}

else
{
do the insert here!
}

Let me know what you think!

Thax...
_Esam
0
 
_EsamAuthor Commented:
OK,
Here is the scenario, the tentative code I have,
Let me know how to tune it...

ArrayList m_Emails = new ArrayList();
 String insert = "INSERT INTO EMAIL (EMAILTEXT, FAILURECT, INDT, OUTDT, SRCCD, CREATETMS, UPDTTMS) " + "VALUES (?, ?, ?, ?, ?, ?, ?)";
   


public boolean updateEmailData(Connection con, EmailBehavior[] emlbhv)
    {
        if (emlbhv == null) return true;
        int i = 0;
        boolean insertComplete = false;
        int[] returnEmail = null;
        try
        {
              con.setAutoCommit(false);
              Statement statement = con.createStatement();
              PreparedStatement lPStatement = null;
              PreparedStatement llPStatement = null;
              boolean executeStatement = false;
              for (i=0; i < emlbhv.length; i++)
              {
                    // process Email Records in EMAIL table
                    // If Email Address is not in DB, insert it.
                    // If Email Address already exists in DB,
                    // update it with feedback data received in EmailBehavior
                    if (m_Emails.contains(emlbhv[i].getEmailAddress()))
                    {
                          
                          if (emlbhv[i].getRecordTypeCode().equals("11"))
                          {
                                llPStatement = con.prepareStatement(update);
                                llPStatement.setInt(1, 1);
                                llPStatement.setString(2, "tira@tt.tv");
                                
                                executeStatement = true;
                                
                              
                          }
                    }
                    else
                    {
                          
                          lPStatement = con.prepareStatement(insert);
                          lPStatement.setString(1, emlbhv[i].getEmailAddress());
                          lPStatement.setInt(2, 0);

                          lPStatement.setDate(3, new java.sql.Date(System.currentTimeMillis()));

                    
                          lPStatement.setDate(4, new java.sql.Date(emlbhv[i].getDateTime().getTime()));
                          lPStatement.setString(5, "FBK");
                          lPStatement.setTimestamp(6, new Timestamp(new Date().getTime()));
                          lPStatement.setTimestamp(7, new Timestamp(new Date().getTime()));
                          lPStatement.executeUpdate();
                                                  executeStatement = true;
                    }                 
              }
              if (!executeStatement) return true;
              returnEmail = statement.executeBatch();
              int the = llPStatement.executeUpdate();
              lPStatement.close();
              llPStatement.close();
              con.commit();
              insertComplete = true;
              statement.close();
        }
        catch(BatchUpdateException e)
        {
              
        }
        catch(SQLException e)
        {
                    
              try
              {
                    con.rollback();
              }
              catch(SQLException e1) {}
        }
        return insertComplete;
    }


Thanks..
_Esam
0
 
mukundha_expertCommented:
instead of doing a select of all email address,

try updating the record first, if exception occurs  catch it and insert that record.

0
 
_EsamAuthor Commented:
How ??
Could you please show some illustration based on my sample?

Thanks.
_Esam
0
 
mukundha_expertCommented:

String update = " update email set EMAILTEXT = ?, FAILURECT = ?, INDT = ?, OUTDT=?, SRCCD=?, CREATETMS=?, UPDTTMS=? where primarykey = ? "


String insert = "INSERT INTO EMAIL (EMAILTEXT, FAILURECT, INDT, OUTDT, SRCCD, CREATETMS, UPDTTMS) " + "VALUES (?, ?, ?, ?, ?, ?, ?)";

preparedStatement = connect.prepareStatement ( update ) ;
preparedStatement = connect.prepareStatement ( insert ) ;

//set all the values for the update prepare statement;
try {
  preparedStatement.executeUpdate();
}
catch{
//set  values for prepareStatement 1 ;
preparedStatement1.executeUpdate();
}

0
 
_EsamAuthor Commented:
>String update = " update email set EMAILTEXT = ?, FAILURECT = ?, INDT = ?, OUTDT=?, SRCCD=?, CREATETMS=?, UPDTTMS=? where primarykey = ? "

What if I want to update FAILURECT = ?,  by adding 1 to it? (Whateever is in the column value, add 1 to it)
Do I have to get a ResultSet for this?

Let me know..

Thanks..
_Esam
0
 
CEHJCommented:
>>try updating the record first, if exception occurs  catch it and insert that record.

Haven't i already mentioned that? You will have to be careful doing that - there are many other reasons that an exception could occur other than a dup key, so make sure it's that, if necessary using the native error code
0
 
mukundha_expertCommented:
no need,
use
>String update = " update email set FAILURECT = FAILURECT + 1 where primarykey = ? "
0
 
_EsamAuthor Commented:
Help me solve this problem...........
I need more illustration than words :)

Thanks.
_Esam
0
 
_EsamAuthor Commented:
For some reason:

If(rs.next()){}

else {}

wasn't working logically,

For some reason, it always returned ture ...
breaking my logic..

Frustrated.................:(

Thanks...
_Esam
0
 
mukundha_expertCommented:
in my DB the datatype of the column am trying to update is int, so when i used the

>String update = " update email set FAILURECT = FAILURECT + 1 where primarykey = ? "

its incrementing to one. if that column is a string then u should use some inbuilt fuctions i think.


as of now, create a prepared statement with that string.
try to execute it and check the output.

String update = " update email set FAILURECT = FAILURECT + 1 where primarykey = ? "
preparedStatement = con.prepareStatement ( update ) ;

// set the values

preparedStatement.executeUpdate ()
0
 
mukundha_expertCommented:
No need to get the result set for that, it will return a integer( no. of rows affected )
check that if its 1 then update is succesful else update failed or no rows found to update.


use can use this condition to insert the new recoerd.
i.e, if the execute update returns 0 then insert a new record
0
 
mukundha_expertCommented:
int ret = preparedStatement.executeUpdate ()

if ( ret == 0 )
  insert the record
0
 
_EsamAuthor Commented:
no need,
use
>String update = " update email set FAILURECT = FAILURECT + 1 where primarykey = ? "

Now, I need some illustration on how I catch the exception and inset the rows??

Thanks.
_Esam
0
 
_EsamAuthor Commented:
At the beginning, there would be more inserts then updates!

What could be the scenario for this?

Let me know...

Thanks..
_Esam...

0
 
CEHJCommented:
>>What could be the scenario for this?

Quite a lot of redundant update code. If you use the insert approach i mentioned before, you will not get the exceptions
0
 
_EsamAuthor Commented:
>Quite a lot of redundant update code. If you use the insert approach i mentioned before, you will not get the exceptions

Which one ??? :)

I am overloaded with info :)

Thanks.
_Esam
0
 
_EsamAuthor Commented:
Yes, there could be many other things if an exception occurs..
Like, now , if an exception occurs....many other things are triggered for it.. like email the admin..I don't want to email the admin for this exception, do I? it is success at the end right?

Is it standard approach to do it like this???

Thax.
_Esam
0
 
_EsamAuthor Commented:
Come on experts..
Help me solve this problem...:)

I don't like to catch exception...
Any other ways?

_Esam
0
 
CEHJCommented:
>>Which one ??? :)

This one

http:Q_21892721.html#16943666
0
 
_EsamAuthor Commented:
>The standard way would be to do a select and then an insert/update/.

Is this the one you were referring to?

How?

Let me know..
Thax
_Esam
0
 
CEHJCommented:
>>Is this the one you were referring to?

No - what i mentioned in my last sentence, which sounds like it would be a good way to go given this statement:

>>At the beginning, there would be more inserts then updates!
0
 
CEHJCommented:
:-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.