• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5522
  • Last Modified:

JDBC - Insert if record DNE, Update if record exists

Java / JDBC Experts:

Background:
  -- I am using NetBeans 3.6 IDE
  -- I have a data source file in XML
  -- I am parsing this xml file using jdom (no problems here)
  -- I am inserting the data into a sql server database table using Microsoft's free jdbc driver for SQL Server (again, all this works fine)

What I need:
  -- I would like your opinion on the best way to first CHECK if the record exists currently in the database AND:
      --> if the record exists, update
      --> if the record does not exist, add new

Maximum points will be awarded to the most straightforward and simple method that uses the fewest number of lines of code.

     Using ADO, vbscript / asp, I would do the following (I am looking for something similar in java):

     rs.open sql, conn, 1, 3, 1
     if rs.eof AND rs.bof then
        'only insert a new record if one does not exist
        rs.addnew()
     end if
     rs.fields("field1") = sfield1Variable
     rs.fields("field2") = sfield2Variable
     rs.fields("field3") = sfield3Variable
     
     rs.update()
     rs.close
     set rs=nothing

Thank you in advance for your help.

Regards,
peh803
0
peh803
Asked:
peh803
3 Solutions
 
howesdCommented:
You could always go for

int fred = stmnt.executeUpdate("update table where cond...")

if (0 == fred )
{
   stmnt.executeUpdate("insert .....");
}

0
 
sudhakar_koundinyaCommented:
try {
        Statement stmt = connection.createStatement();
   
        // Prepare a statement to update a record
        String sql = "UPDATE my_table SET col_string='a new string' WHERE col_string = 'a string'";
   
        // Execute the insert statement
        int updateCount = stmt.executeUpdate(sql);
        // updateCount contains the number of updated rows
         if(updatecount<=0)
          {
 try {
        Statement stmt = connection.createStatement();
   
        // Prepare a statement to insert a record
        String sql = "INSERT INTO my_table (col_string) VALUES('a string')";
   
        // Execute the insert statement
        stmt.executeUpdate(sql);
    } catch (SQLException e) {
    }
          }  
    } catch (SQLException e) {
    }
0
 
girionisCommented:
I assume you send a statement to the database like SELECT * FROM mytable WHERE id = mplampla
If your ResultSet is called "rs" you can do:

if (!rs.next())
{
    PreparedStatement insert = con.prepareStatement("INSERT INTO mytable blahblahblah");
    insert.executeUpdate();
    rs.close();   // the result set
    insert.close();
}

PreparedStatement update = con.prepareStatement("UPDATE mytable blahblahblah VALUES(?, ?, ?)");
update.setString(1, sfield1Variable);
update.setString(1, sfield2Variable);
update.setString(1, sfield3Variable);
update.executeUpdate();
update.close();  // the update statement

conenction.close();   // the connection to the database object
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
peh803Author Commented:
All,

Thank you for the responses.  While I do not have time to look them over / test them now, I will check them all and award points accordingly tomorrow.  Anyone else with any other ideas, please feel free to post.  

At first glance, though, these look good.  

Thank you very much to all who have posted thus far.

peh803
0
 
peh803Author Commented:
@howesd:

Based on your post, I assume that stmnt.executeUpdate returns the number of records updated?  And if the record does not exist to be updated, you get 0, and thus insert?  

...I like it...!

@sudhakar_koundinya:

You're using this same method, right?

@girionis:
Your solution processes an update for each record, right?  Even if it has done an insert?

Thanks again, all.

peh803
0
 
girionisCommented:
> Your solution processes an update for each record, right?  Even if it has done an insert?

Nope, it only does one update. If you want to go through all rcords then either put it in a while loop or have a proper UPDATE SQL query.
0
 
peh803Author Commented:
@girionis:

Thanks for the reply.  What I meant by my comment wasn't clear.  Let me be more specific.

I am already looping through each record in an xml file and importing the data into a SQL Server 2000 table.  Thus, since this logic will be applied inside the loop, the effect it will have will be to first:

Check to see if rs.next() returns false.  If it does indeed return false, we know that a record does not exist, and thus we need to do an insert.  

NOW, once we've done that, we update the same record with the following update statement -- is this not how the code reads?  

i.e.:

1.) query the table using the primary key relationship to see if this record already exists
2.) if it does not exist, go ahead and insert
       --> THEN, update the record as well (the update statement is not in any kind of conditional statement)
3.) if it does indeed exist, !rs.next() returns false, and thus the update is the only thing processed.  

Do I understand this correctly?

Thank you for your help,
peh803
0
 
girionisCommented:
Yes I understand but I see a logical error. In 2) you first do an INSERT and then an UPDATE on the same insert. Why do you need to do the UPDATE and not insert all relevant data in the INSERT statement?
0
 
peh803Author Commented:
Not sure I understand your last statement.  

What I want to do, put simply, is this:

1.) read data from an xml file
2.) check to see if the data in the xml file exists in my sql server table
3.) if a record already exists for this id, update the existing record
4.) if a record does not exist for this id, insert a new record.  

My point is that I do not, in fact, always want to do both an insert and an update.  I want to do as little as possible.

peh803
0
 
peh803Author Commented:
All,

Thank you very much for your help.  I ended up using the approach recommended by howesd, hence howesd gets the lion's share of the points.

Each person, however, is deserving of points for your help.  

I hope you will participate in any future questions I may have.

Regards,
peh803
0
 
girionisCommented:
Thank you for accepting :)

I Was talking about this:

2.) if it does not exist, go ahead and insert
       --> THEN, update the record as well (the update statement is not in any kind of conditional statement)

You do no tneed to first insert and then update, you jsut do an insert with all the info you need. For example instead of doing:

INSERT INTO mytable VALUES(1, 2, 3);  (asumming this wil lcreate a new record with id 100 - id is auto increment)

and then UPDATE MYTABLE SET VALUES(2, 3, 4) WHERE id=100;

you can simply do:

INSERT INTO mytable VALUES(2, 3, 4);
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.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now