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!

Kaleb Brasee · Accepted Answer · 2010-10-23 16:57:05Z

2

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();
}

edited Oct 23, 2010 at 16:57

answered Oct 23, 2010 at 16:44

Kaleb Brasee

52.1k9 gold badges113 silver badges114 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tony Ennis · Accepted Answer · 2010-10-23 16:45:42Z

0

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

answered Oct 23, 2010 at 16:45

Tony Ennis

12.4k8 gold badges60 silver badges79 bronze badges

Collectives™ on Stack Overflow

Using Java to populate a specific column in an SQL table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related