Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to Update a table based on object values?

Posted on 2006-06-15
20
Medium Priority
?
215 Views
Last Modified: 2010-03-31
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.
0
Comment
Question by:_Esam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 9
20 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 16913625
What DB is it?
0
 

Author Comment

by:_Esam
ID: 16913790
It's Oracle 9.2

_Esam
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 1760 total points
ID: 16915252
In order to keep your code flexible, you could do a select first, using an updatable ResultSet, which you could use if it returns a row, or insert otherwise
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:_Esam
ID: 16915326
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....
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 1760 total points
ID: 16915403
0
 

Author Comment

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

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16915632
That's the logic yes. The latter you'd do with moveToInsertRow (see article at last link)
0
 

Author Comment

by:_Esam
ID: 16915650
I am a little confused!

Do I use a Statement?   PreparedStatement ? ResultSet (Updatabae) ?  addBatch for the Statement objects???


Let me know ...

_Esam
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16915751
>>PreparedStatement ? ResultSet (Updatabae) ?

the above two. Read the last article
0
 

Author Comment

by:_Esam
ID: 16916015
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.
0
 

Author Comment

by:_Esam
ID: 16916167
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.....
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16916325
An alternative would be to do a select followed by an insert, and yet another would be to catch dup key and then update
0
 

Author Comment

by:_Esam
ID: 16916339
I am trying to formulate the logic...a little stuck with it..

_Esam...
0
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 1760 total points
ID: 16916461
ResultSet rs = ps.executeQuery();
if (rs.next()) {
    // you can update it
}
else {
    rs.moveToInsertRow();
    // do insert
}
0
 

Author Comment

by:_Esam
ID: 16916580
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....
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16916599
No problem
0
 
LVL 92

Assisted Solution

by:objects
objects earned 240 total points
ID: 16916760
> Could it be very time consuming if there are millions of records??

absolutely

if you've got lots of rows then use a batch

http://javaalmanac.com/egs/java.sql/BatchUpdate.html
0
 

Author Comment

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

Author Comment

by:_Esam
ID: 16921119
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.prepareStatement(m_Select, ResultSet.TYPE_SCROLL_SENSITIVE,
                 ResultSet.CONCUR_UPDATABLE);
     //rs = mConnection.createStatement();
     // 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].getEmailAddress());
              
               lResult = lPStatement.executeUpdate();
              
               System.out.println("Nuber of records inserted:  " + lResult);
           }
           else{
                 
           }
          
           //lStatement.executeQuery(m_insertEmailRecordSQL);
           //lPStatement = mConnection.prepareStatement(m_Insert);
          
     }
 
     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
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 16925764
:-)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
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.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

718 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