Win a copy of Getting started with Java programming language this week in the Beginning Java forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

How to update multiple rows in java jdbc on duplicate value  RSS feed

 
Deon Gee
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a .jsp page that has a multi checkbox. I'm able to insert the multiple checkbox values but when use an update, it adds the current checkbox rows plus the additional checkbox value rows. If I check one more box it should add 1 more row and so on.

Here is my code that works for inserting data, this works okay:

CollDAO.java

    Insert checkbox records



If I select 2 check boxes this is what it looks like.

rID  |  qID  |  cID -- CORRECT OUTPUT

101 | 121 | 9

100 | 121 | 13

Here's code for Updating checkbox records, this does not work.




If I select 3 check boxes this is what get updated.

rID  |  qID  |  cID  -- WRONG OUTPUT

105 | 121 | 2

104 | 121 | 9

103 | 121 | 13

101 | 121 | 9

100 | 121 | 13

This is what it suppose to look like.

rID  |  qID  |  cID  -- CORRECT OUTPUT

103 | 121 | 2

101 | 121 | 9

100 | 121 | 13

I've been working on this for a week, can you help me?

Thank you



 
Dave Tolls
Ranch Hand
Posts: 2416
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You need to stick some debugging statements in there to log where in your code you have got to and what the values are.
At the moment you are guessing that it is going into the UPDATE section of your code, but since new lines are being inserted into the database it has to be going into the INSERT part.
 
Deon Gee
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
mmm so should this be and insert and select?
 
Campbell Ritchie
Sheriff
Posts: 53568
126
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch

Use code tags instead of the quote button, which will make your post look much better. Also line 3 is too long. Since you are new, I have corrected both things for you.
 
Tim Cooke
Marshal
Posts: 3557
170
Clojure IntelliJ IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Deon, welcome to the Ranch!

I fixed up the code tags for you, they were so nearly right It makes the code much easer to read don't you think?

I hope you get your problem sorted out swiftly.
Staff note (Tim Cooke):

Unknown to each other, Campbell and I were tweaking the code tags at the same time.

 
Dave Tolls
Ranch Hand
Posts: 2416
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Deon Gee wrote:mmm so should this be and insert and select?


No, what I'm saying is that, for whatever reason (and debugging logs will help here) your code is not going into the updateCol1 method.  It is going into the addCol1 method.
To figure out why then you need logs.
 
Deon Gee
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How do I check if record exist so that I will not insert duplicate records?  For example the arrayColId[i] should not be duplicated.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 36171
439
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What database are you using? For example, the merge statement might be available in which case you can have the database do that check for you.
 
Deon Gee
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm using Oracle.
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 36171
439
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Great. Oracle has this merge statement as well. You give it different logic if the row already exists vs is new.
 
Deon Gee
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm using prepared statement is that okay, and JDBC from JAVA DAO.
 
Deon Gee
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh and this is not two different tables, it is updating on one table.  Does merge apply to this?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 36171
439
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, you can use merge with one table.

A PreparedStatement just calls SQL behind the scenes so that is ok.
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!