Solved

How to update or insert records based on object values?

Posted on 2006-06-20
28
205 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:_Esam
  • 14
  • 7
  • 7
28 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 16943582
Isn't this at least the third time you've asked this question? ;-)
0
 

Author Comment

by:_Esam
ID: 16943611
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
 

Author Comment

by:_Esam
ID: 16943629
Should I do two sql query for this?

_Esam
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16943666
>>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
 

Author Comment

by:_Esam
ID: 16943691
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
 

Author Comment

by:_Esam
ID: 16943835
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
 
LVL 10

Assisted Solution

by:mukundha_expert
mukundha_expert earned 440 total points
ID: 16944006
instead of doing a select of all email address,

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

0
 

Author Comment

by:_Esam
ID: 16944054
How ??
Could you please show some illustration based on my sample?

Thanks.
_Esam
0
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 16944230

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
 

Author Comment

by:_Esam
ID: 16944352
>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
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 60 total points
ID: 16944482
>>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
 
LVL 10

Assisted Solution

by:mukundha_expert
mukundha_expert earned 440 total points
ID: 16944524
no need,
use
>String update = " update email set FAILURECT = FAILURECT + 1 where primarykey = ? "
0
 

Author Comment

by:_Esam
ID: 16944540
Help me solve this problem...........
I need more illustration than words :)

Thanks.
_Esam
0
 

Author Comment

by:_Esam
ID: 16944574
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 10

Expert Comment

by:mukundha_expert
ID: 16944583
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
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 16944597
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
 
LVL 10

Expert Comment

by:mukundha_expert
ID: 16944601
int ret = preparedStatement.executeUpdate ()

if ( ret == 0 )
  insert the record
0
 

Author Comment

by:_Esam
ID: 16944650
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
 
LVL 10

Accepted Solution

by:
mukundha_expert earned 440 total points
ID: 16944714
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
 

Author Comment

by:_Esam
ID: 16944754
At the beginning, there would be more inserts then updates!

What could be the scenario for this?

Let me know...

Thanks..
_Esam...

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16944788
>>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
 

Author Comment

by:_Esam
ID: 16944875
>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
 

Author Comment

by:_Esam
ID: 16945082
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
 

Author Comment

by:_Esam
ID: 16945128
Come on experts..
Help me solve this problem...:)

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

_Esam
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16945279
>>Which one ??? :)

This one

http:Q_21892721.html#16943666
0
 

Author Comment

by:_Esam
ID: 16945306
>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
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 60 total points
ID: 16945332
>>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
 
LVL 86

Expert Comment

by:CEHJ
ID: 16946745
:-)
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now