Modified: How to update multiple rows in java jdbc on duplicate value

Question

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 update: CollDAO.java:

//Insert checkbox records
public void addColl(String qId, String[] arrayColId) {
  try {
    PreparedStatement ps = con.preparedStatement("insert into colTable(qId, colId) values(?,?)");

   for(int i = 0; i < arrayColId.length; i++) {
     ps.setString(1, qId);
     ps.setString(2, arrayColId[i]);
     ps.executeUpdate();
   }
  } catch (SQLException e) {
     e.printStackTrace();
  }
}

If I select 2 checkboxes this is what it looks like.

rowid | qID | cID -- CORRECT

:101: | :121: | :9:

:100: | :121: | :13:

//Update checkbox records
public void updateColl(String qId, String[] arrayColId) {
  try {
    String sql = "update colTable set colId=?, where qId=?";
   PreparedStatement ps = con.preparedStatement(sql);

   for(int i = 0; i < colId; i++) {
     ps.setString(1, colId[i]);
     ps.setString(2, qId);
     ps.executeUpdate();
   }

  } catch (SQLException e) {
     e.printStackTrace();
  }
}

If I select 3 checkboxes this is what get updated.

rowid | 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.

rowid | qID | cID -- CORRECT OUTPUT

:103: | :121: | :2:

:101: | :121: | :9:

:100: | :121: | :13:

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

Thank you

You need to check the values in arrayColID. – SedJ601 Feb 27 '17 at 14:29 — SedJ601, Feb 27 '17 at 14:29

Youcef LAIDANI · Accepted Answer · 2017-09-10T18:41:07.343

0

In your case you should to use Batch instead :

PreparedStatement ps = con.preparedStatement("your query");

for(int i = 0; i < arrayColId.length; i++) {
     ps.setString(1, qId);
     ps.setString(2, arrayColId[i]);
     ps.addBatch();    
}

ps.executeBatch();

You can learn more here Reusing a PreparedStatement multiple times and Statament batching

Edit

For your update you can use :

connection.setAutoCommit(false);

int arrayVals = Math.min(arrayColId.length, arrayQId.length);
for (int i = 0; i < arrayVals; i++) {
    ps.setString(1, arrayColId[i]);
    ps.setString(2, arrayQId[i]);
    ps.addBatch(); //add a batch
}

ps.executeBatch();//execute your batch

connection.commit();//when you finish you should to commit your transaction

edited Sep 10 '17 at 18:41

answered Feb 27 '17 at 14:43

Youcef LAIDANI

55,661
15
90
140

@Gee you can use batch with Insert or update or any operation – Youcef LAIDANI Feb 27 '17 at 18:57
yes @Gee, with both of them, did you know how to complete your code, or i should to edit my answer? – Youcef LAIDANI Feb 27 '17 at 19:02
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/136788/discussion-between-ycf-l-and-gee). – Youcef LAIDANI Feb 27 '17 at 19:29
@Gee did you set your action between `connection.setAutoCommit(false);` and `connection.commit();` – Youcef LAIDANI Feb 27 '17 at 19:32

Modified: How to update multiple rows in java jdbc on duplicate value

1 Answers1