Exception caught : java.sql.SQLException: Column c

问题:

 public void updateDeduction(String empId, String dedId, String dedname,String dedamount,String date) throws SQLException{
 //update the contributions
  stmt = conn.createStatement();
 String updateString ="INSERT INTO deductions (empId,dedId,dedName,dedAmount,dedDate) VALUES (";
  updateString +="'"+empId+"', ";
  updateString +="CURDATE(), " ;
  updateString +="'"+dedId+"'";
  updateString +="'"+dedname+"', ";
  updateString +="'"+dedamount+"')";


  stmt.executeUpdate(updateString);

  return;

I am getting error whenever I am clicking on the deduction tab please let me know what to do?

回答1:

It is good practice to use PreparedStatement instead of Statement. It will help you to prevent sql injection attacks. Try to build PreparedStatement like -

String updateString ="INSERT INTO deductions (empId, dedId, dedName, dedAmount, dedDate) VALUES (?,?,?,?,?)";

    PreparedStatement preparedStatement = conn.prepareStatement(updateString);

    preparedStatement.setInt(1, empId);
    preparedStatement.setInt(2, dedId);
    preparedStatement.setString(3, dedName);
    preparedStatement.setDouble(4, dedAmount);
    preparedStatement.setDate(5, dedDate);

    preparedStatement .executeUpdate();

回答2:

The proper way to do this is to use PreparedStatement. I've rewritten OPs code below to show how this is done:

public void updateDeduction(String empId, String dedId, String dedname,String dedamount,String date) throws SQLException{
    //update the contributions
    PreparedStatement updateString = conn.prepareStatement("INSERT INTO deductions (empId,dedId,dedName,dedAmount,dedDate) VALUES (?, ?, ?, ?, ?)", ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE); // you passed in CURDATE() instead of using one of your parameters.
    updateString.setString(1, empId);
    updateString.setString(2, dedId);
    updateString.setString(3, dedName);
    updateString.setString(4, dedAmount);
    updateString.setString(5, date); // you were missing this line
    if (updateString.executeUpdate() == 1) return;
    else throw new RuntimeException("Update failed");
}

Some comments on my code which should make it clearer as to why I used this style. The if line exists to ensure the insert was successful, as executeUpdate is defined to return the number of rows inserted in an insert context. Also, you must declare your statements as updatable if they change rows at all. Hope this helps, and if you need further assistance/explanation, please leave a comment here.

回答3:

You don't have a comma in updateString +="'"+dedId+"'";

Also the order of the values you concatenate into the string do not match the order of fields in the INSERT INTO (...)

The fix would be something like

  updateString +="'"+empId+"', ";
  updateString +="'"+dedId+"', "; //Or updateString += dedId+", ";  If dedId is an integer value in the database.
  updateString +="'"+dedname+"', ";
  updateString +="'"+dedamount+"', ";
  updateString +="CURDATE())" ;

Notice I've re-ordered the string concatenates to match the INSERT INTO (...) field order, and all the fields have commas after them except the last one.