|
| 發表者 |
討論內容 |
冷日
(冷日) |
發表時間：2019/5/30 13:40 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]Java PreparedStatement - a SQL UPDATE example
Java PreparedStatement - a SQL UPDATE exampleJava SQL FAQ: Can you provide a Java PreparedStatement example that shows how to use a SQL UPDATE? Sure. I have quite a few examples on this website, just see the "Related" section for those. But for now, here's a short sample method that performs a JDBC SQL UPDATE using a Java PreparedStatement:
// a java preparedstatement example
public static void updateDescriptionAndAuthor (
Connection conn,
String description,
int id,
int seqNum,
String author
)
throws SQLException
{
try
{
// create our java preparedstatement using a sql update query
PreparedStatement ps = conn.prepareStatement(
"UPDATE Messages SET description = ?, author = ? WHERE id = ? AND seq_num = ?");
// set the preparedstatement parameters
ps.setString(1,description);
ps.setString(2,author);
ps.setInt(3,id);
ps.setInt(4,seqNum);
// call executeUpdate to execute our sql update statement
ps.executeUpdate();
ps.close();
}
catch (SQLException se)
{
// log the exception
throw se;
}
}
I'll skip the description of this PreparedStatement UPDATE example for now, but if you have any questions or comments, just leave them below, and I'll be glad to respond to them.
原文出處：Java PreparedStatement - a SQL UPDATE example | alvinalexander.com
|
|
|
|
|
冷日
(冷日) |
發表時間：2019/5/30 13:49 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]update statement in Java
- 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.
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.
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
|
|
|
|
|
冷日
(冷日) |
發表時間：2019/7/29 13:14 |
- Webmaster
- 註冊日: 2008/2/19
- 來自:
- 發表數: 15771
|
- [轉貼]PreparedStatement: How to insert data into multiple tables using JDBC
- PreparedStatement: How to insert data into multiple tables using JDBC
Could somebody tell me whether the first stmt.close(); required in the following JDBC code, for executing two different SQL queries against two different tables?
public class MyService {
private Connection connection = null;
public void save(Book book) {
try {
Class.forName("com.mysql.jdbc.Driver");
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "password");
PreparedStatement stmt = connection.prepareStatement("INSERT INTO PUBLISHER (CODE, PUBLISHER_NAME) VALUES (?, ?)");
stmt.setString(1, book.getPublisher().getCode());
stmt.setString(2, book.getPublisher().getName());
stmt.executeUpdate();
stmt.close(); //1
stmt = connection.prepareStatement("INSERT INTO BOOK (ISBN, BOOK_NAME, PUBLISHER_CODE) VALUES (?, ?, ?)");
stmt.setString(1, book.getIsbn());
stmt.setString(2, book.getName());
stmt.setString(3, book.getPublisher().getCode());
stmt.executeUpdate();
stmt.close(); //2
} catch (ClassNotFoundException e) { e.printStackTrace(); } catch (SQLException e) { e.printStackTrace(); }
finally { connection.close(); }
}
}
In my book, I would always recommend closing resources that have been opened to avoid possible leaks.
A slightly more modern way would be to use try-with-resources:
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydb", "root", "password")) {
try (PreparedStatement stmt = connection.prepareStatement("INSERT INTO PUBLISHER (CODE, PUBLISHER_NAME) VALUES (?, ?)")) {
stmt.setString(1, book.getPublisher().getCode());
stmt.setString(2, book.getPublisher().getName());
stmt.executeUpdate();
}
// stmt is auto closed here, even if SQLException is thrown
try (PreparedStatement stmt = connection.prepareStatement("INSERT INTO BOOK (ISBN, BOOK_NAME, PUBLISHER_CODE) VALUES (?, ?, ?)");
stmt.setString(1, book.getIsbn());
stmt.setString(2, book.getName());
stmt.setString(3, book.getPublisher().getCode());
stmt.executeUpdate();
}
// stmt is auto closed here, even if SQLException is thrown
}
// connection is auto closed here, even if SQLException is thrown
It is a common misconception with statements that closing them releases all that preparing them built. This is wrong. The optimisation that results in the preparation of the statement is performed by the database. It is then stored/cached by the database and usually re-used next time the statement is prepared.
As a result, prepared statements can be closed and prepared as often as you wish - the database will recognise the same statement next time around and recover the cached preparations it made last time - if it wishes to.
In summary - yes, statements should be closed - and no, this does not reduce the effectiveness of your queries.
It is not required but it is recommended. http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#close()
The next line after the first close() in your code assigns a new value to the reference stmt, so the object you use to execute the first insert will be GC'd and closed eventually. It is good practice to go ahead and close it when you know you're done with it. This releases the JDBC resources immediately.
Its good practice to close the Statement handle as it will release the JDBC & Database resources. You can read more about stmt.close() here
I would like to point out that its good to close your Statement object in finally block so that DB resources are released even if an Exception occurs.
Yes, both the stmt.close() methods are necessary. You should always explicitly close the Statement or PreparedStatement object to ensure proper cleanup.
try{
String insertIntoCust = "insert into NORTHWIND_CUSTOMER(CUSTOMER_ID,FIRST_NAME,LAST_NAME,ADDRESS,CITY,STATE,POSTAL_CODE) values(?,?,?,?,?,?)";
pst = connect.prepareStatement(insertIntoCust);
pst.setString(1, txtCustomerId.getText());
pst.setString(2, txtFirstName.getText());
pst.setString(3, txtLastName.getText());
pst.setString(4, txtAddress2.getText());
pst.setString(5, txtCity.getText());
pst.setString(6, txtState.getText());
pst.setString(7, txtPostalCode.getText());
pst.execute();
String insertIntoOrder = "insert into NORTHWIND_ORDER(ORDER_ID,ORDER_DATE) values(?,?)";
pst = connect.prepareStatement(insertIntoOrder);
pst.setString(1, txtOrderId.getText());
pst.setString(2, txtOrderDate.getText());
pst.execute();
JOptionPane.showMessageDialog(null, "Saved");
}catch(Exception e){
JOptionPane.showMessageDialog(null, e);
}
With the code above, I could insert data into multiple tables with one button. The pst.execute need to be inserted in both queries; if not, the query that have the pst.execute is the table that will receive the data.
shareimprove this answer
原文出處：java - PreparedStatement: How to insert data into multiple tables using JDBC - Stack Overflow
|
|
|
|
