Using Java to populate a specific column in an SQL table

Question

Essentially, I have a methods that manipulates data taken from a table to create a new object "ZExpert." ZExpert has parameters int id, int domain, and double ZExpert. I have added a column to the table I took the data from called "Z_Expert_Score."

I want to put the double ZExpert score from the object in the column Z_Expert_Score, in the row where "Customer_Expert_ID"=domain and "Customer_ID"=id. Here is my attempt

ZExpert[] allZExpert = scrCalc.getzExpertAll();

for(int i = 0; i < allZExpert.length; i++) {
  ZExpert currentZExpert = allZExpert[i]; 
  int id = currentZExpert.getId();
  int domain = currentZExpert.getDomain();
  double ZExpertScore = currentZExpert.getzExpert();

  Statement statement = conn.createStatement();
  statement.executeUpdate ("INSERT INTO CONSUMER_EXPERT_ID 
                             where CONSUMER_EXPERT_ID="+domain+"AND CONSUMER_ID="+id+ "(Z_EXPERT_SCORE) VALUES("+ZExpertScore+")");
}

Thanks in advance!

Answer 1

You need to use an update SQL statement, not an insert.

UPDATE CUSTOMER_TABLE SET Z_EXPERT_SCORE = ? WHERE CONSUMER_EXPERT_ID= ? AND CONSUMER_ID= ?

Plus, you should be using a PreparedStatement, not combining Strings together to create the SQL statement.

ZExpert[] allZExpert = scrCalc.getzExpertAll();

for(int i = 0; i < allZExpert.length; i++) {
  ZExpert currentZExpert = allZExpert[i]; 
  int id = currentZExpert.getId();
  int domain = currentZExpert.getDomain();
  double ZExpertScore = currentZExpert.getzExpert();

  String sql = "UPDATE CUSTOMER_TABLE SET Z_EXPERT_SCORE = ? WHERE CONSUMER_EXPERT_ID= ? AND CONSUMER_ID= ?";
  PreparedStatement statement = con.prepareStatement(sql);
  statement.setDouble(1, zExpertScore);
  statement.setInt(2, domain);
  statement.setInt(3, id);
  statement.executeUpdate();
}

Answer 2

If you're augmenting rows that already exist you'll want to use an UPDATE statement.