How to update or insert records based on object values?

Isn't this at least the third time you've asked this question? ;-)

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

Should I do two sql query for this?

_Esam

>>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)

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

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

How ??
Could you please show some illustration based on my sample?

Thanks.
_Esam

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();
}

>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

Help me solve this problem...........
I need more illustration than words :)

Thanks.
_Esam

For some reason:

If(rs.next()){}

else {}

wasn't working logically,

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

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

Thanks...
_Esam

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 ()

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

int ret = preparedStatement.executeUpdate ()

if ( ret == 0 )
  insert the record

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

At the beginning, there would be more inserts then updates!

What could be the scenario for this?

Let me know...

Thanks..
_Esam...

>>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

>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

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

Come on experts..
Help me solve this problem...:)

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

_Esam

>>Which one ??? :)

This one

http:Q_21892721.html#16943666

>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

:-)