對這文章發表回應
發表限制: 非會員 可以發表
發表者： 冷日 發表時間： 2019/5/30 13:49:01
update statement in Java
I have the following code to update a record. The code compiles however it skips everything in the try statement and displays the error message in the catch statement. I am not sure what it is that I am missing as it doesn't display any sort of syntax error.
You're swallowing the exception, which is generally a bad idea. At the very least call e.printStackTrace() so that you have an output of the exception.
As it happens, you have a syntax error in your SQL statement: UPDATE item SET Name = ?, Size = ?, Price = ?, WHERE ItemCode = ? - remove the comma from after Price = ?.
To address the confusion about why the UPDATE statement still doesn't work, despite fixing the syntax error, allow me to explain in more detail (far easier to do this in the answer, rather than in comments).
The ? character in your SQL String is a placeholder for a value that you'll set with one of the various set_() methods (in your case, only ever setString(). Each placeholder is numbered with an index starting from 1 - the first ? that appears in your string represents index 1, the second represents index 2, etc.
Your SQL string looks like this:
You're setting values for your placeholders like this:
Have you tried changing that UPDATE statement to something like this:
I took out the final , (comma) after the Price = ? because it may conflict with the rest of the SQL.
Edit:
Also, you may want to rearrange the textArea_Code.getText() line to be the last value for the prepared statement. Otherwise, the ItemCode value may not be matching up with the order of the ? placeholders (as the moment, it looks like textArea_price.getText() is getting used as the value for ItemCode... which might be dangerous for what gets updated!)
Instead, what about something like this:
In this way, all of the ? placeholders correctly match up with the values being set:
This is because parameters need to be in the same order as the placeholders (see this documentation for more information.)
What is the output of executeUpdate? Does it return any number of rows updated? If not, then obviously your update clause needs to be, well, updated!
If the executeUpdate does return a positive number, indicating that it updated number of rows, may be your commit policy is not set to auto commit. In that case you may want to commit after executeUpdate.
After getting DB connection set auto commit to true so that DB will be updated without any errors.
原文出處：sql - update statement in Java - Stack Overflow
I have the following code to update a record. The code compiles however it skips everything in the try statement and displays the error message in the catch statement. I am not sure what it is that I am missing as it doesn't display any sort of syntax error.
try {
PreparedStatement st = db.con.prepareStatement("UPDATE item SET Name = ?, Size = ?, Price = ?, WHERE ItemCode = ?");
st.setString(1, textArea_Code.getText());
st.setString(2, textArea_name.getText());
st.setString(3, textArea_size.getText());
st.setString(4, textArea_price.getText());
st.executeUpdate();
JOptionPane.showMessageDialog(frame, "Updated");
} catch (SQLException e ) {
JOptionPane.showMessageDialog(frame, "update not successful");
}You're swallowing the exception, which is generally a bad idea. At the very least call e.printStackTrace() so that you have an output of the exception.
As it happens, you have a syntax error in your SQL statement: UPDATE item SET Name = ?, Size = ?, Price = ?, WHERE ItemCode = ? - remove the comma from after Price = ?.
To address the confusion about why the UPDATE statement still doesn't work, despite fixing the syntax error, allow me to explain in more detail (far easier to do this in the answer, rather than in comments).
The ? character in your SQL String is a placeholder for a value that you'll set with one of the various set_() methods (in your case, only ever setString(). Each placeholder is numbered with an index starting from 1 - the first ? that appears in your string represents index 1, the second represents index 2, etc.
Your SQL string looks like this:
UPDATE item SET Name = ?, // 1
Size = ?, // 2
Price = ? // 3
WHERE ItemCode = ? // 4
You're setting values for your placeholders like this:
st.setString(1, textArea_Code.getText()); // ItemCode is fourth in the SQL, should be 4
st.setString(2, textArea_name.getText()); // Name is first in the SQL, should be 1
st.setString(3, textArea_size.getText()); // Size is second in the SQL, should be 2
st.setString(4, textArea_price.getText()); // Price is third in the SQL, should be 3
Have you tried changing that UPDATE statement to something like this:
"UPDATE item SET Name = ?, Size = ?, Price = ? WHERE ItemCode = ?"
I took out the final , (comma) after the Price = ? because it may conflict with the rest of the SQL.
Edit:
Also, you may want to rearrange the textArea_Code.getText() line to be the last value for the prepared statement. Otherwise, the ItemCode value may not be matching up with the order of the ? placeholders (as the moment, it looks like textArea_price.getText() is getting used as the value for ItemCode... which might be dangerous for what gets updated!)
Instead, what about something like this:
PreparedStatement st = db.con.prepareStatement("UPDATE item SET Name = ?, Size = ?, Price = ? WHERE ItemCode = ?");
st.setString(1, textArea_name.getText());
st.setString(2, textArea_size.getText());
st.setString(3, textArea_price.getText());
st.setString(4, textArea_Code.getText());
st.executeUpdate();In this way, all of the ? placeholders correctly match up with the values being set:
Name: st.setString(1, textArea_name.getText());
Size: st.setString(2, textArea_size.getText());
Price: st.setString(3, textArea_price.getText());
ItemCode: st.setString(4, textArea_Code.getText());This is because parameters need to be in the same order as the placeholders (see this documentation for more information.)
What is the output of executeUpdate? Does it return any number of rows updated? If not, then obviously your update clause needs to be, well, updated!
If the executeUpdate does return a positive number, indicating that it updated number of rows, may be your commit policy is not set to auto commit. In that case you may want to commit after executeUpdate.
con.commit();
After getting DB connection set auto commit to true so that DB will be updated without any errors.
connection = DBConnection.getInstance().getConnection();
connection.setAutoCommit(true);
原文出處：sql - update statement in Java - Stack Overflow