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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
@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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
@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
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
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?
ASKER
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
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
ASKER
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
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
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);
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);
If your ResultSet is called "rs" you can do:
if (!rs.next())
{
PreparedStatement insert = con.prepareStatement("INSE
insert.executeUpdate();
rs.close(); // the result set
insert.close();
}
PreparedStatement update = con.prepareStatement("UPDA
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