Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

JDBC - Insert if record DNE, Update if record exists

Posted on 2004-08-26
11
Medium Priority
?
4,446 Views
Last Modified: 2008-01-09
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
Comment
Question by:peh803
[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
11 Comments
 
LVL 1

Accepted Solution

by:
howesd earned 1600 total points
ID: 11907062
You could always go for

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

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

0
 
LVL 14

Assisted Solution

by:sudhakar_koundinya
sudhakar_koundinya earned 200 total points
ID: 11907231
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
 
LVL 35

Expert Comment

by:girionis
ID: 11907238
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
Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

 
LVL 19

Author Comment

by:peh803
ID: 11907471
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
 
LVL 19

Author Comment

by:peh803
ID: 11907507
@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
 
LVL 35

Assisted Solution

by:girionis
girionis earned 200 total points
ID: 11908045
> 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
 
LVL 19

Author Comment

by:peh803
ID: 11912677
@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
 
LVL 35

Expert Comment

by:girionis
ID: 11912805
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
 
LVL 19

Author Comment

by:peh803
ID: 11912969
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
 
LVL 19

Author Comment

by:peh803
ID: 11913338
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
 
LVL 35

Expert Comment

by:girionis
ID: 11914170
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

704 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