Solved

JDBC - Insert if record DNE, Update if record exists

Posted on 2004-08-26
11
3,895 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
11 Comments
 
LVL 1

Accepted Solution

by:
howesd earned 400 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 50 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 35

Assisted Solution

by:girionis
girionis earned 50 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

759 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

18 Experts available now in Live!

Get 1:1 Help Now