英文:
Multiple conditions in an SQL Delete statement
问题
我正在将Java与MySQL结合用于Web应用程序。我想问一下这个语句是否能正常工作。
String delete = "DELETE FROM `eoms`.`order` WHERE (`employeeID` = '" + eID + "', orderTime = '" + mealName + "', orderDate = '" + curDate + "');";
英文:
I am using java in combination with mysql for a web application. I wanted to ask if this statement would work fine.
String delete="DELETE FROM `eoms`.`order` WHERE (`employeeID` = '"+eID+"', orderTime = '"+mealName+"', orderDate = '"+curDate+"');";
答案1
得分: 5
是的，您的删除查询应该有效，但您应该使用预处理语句：
String delete = "DELETE FROM `eoms`.`order` WHERE employeeID = ? OR orderTime = ? OR orderDate = ?";
PreparedStatement ps = conn.prepareStatement(delete);
ps.setInt(1, eID);
ps.setTime(2, orderTime);
ps.setDate(3, curDate);
int row = ps.executeUpdate();
System.out.println(row + " 行已删除。");
附注：请避免使用保留的 SQL 关键字（如 order）来命名您的表（以及其他数据库对象）。您将永远需要在反引号中转义您的 order 表。此外，似乎您将订单时间和日期存储在单独的列中。这可能不是最佳实践，您应该考虑只使用单个日期时间列来表示订单时间。最后，也许您打算让 WHERE 子句中的所有三个限制同时生效。如果是这样，只需使用 AND 代替 OR 即可。
英文:
Yes, your delete query should work, but you should use a prepared statement:
String delete = "DELETE FROM `eoms`.`order` WHERE employeeID = ? OR orderTime = ? OR orderDate = ?";
PreparedStatement ps = conn.prepareStatement(delete);
ps.setInt(1, eID);
ps.setTime(2, orderTime);
ps.setDate(3, curDate);
int row = ps.executeUpdate();
System.out.println(row + " rows were deleted.");
Side notes: Please avoid naming your tables (and other database objects) using reserved SQL keywords such as order. You will forever have to escape your order table in backticks. Also, it appears that you are storing the order time and date in separate columns. This probably isn't best practice, and you should consider just using a single datetime column for the order. Finally, perhaps you intended for all three restrictions in the WHERE clause to apply simultaneously. If so, then just swap OR with AND.
答案2
得分: 4
你应该使用 and 运算符来组合条件：
String delete = "DELETE FROM `eoms`.`order` WHERE (`employeeID` = '" + eID + "' and orderTime = '" + mealName + "' and orderDate = '" + curDate + "')";
根据建议，为了避免 SQL 注入，应该使用预编译语句来处理参数：
String deleteQuery = "DELETE FROM `eoms`.`order` WHERE employeeID = ? and orderTime = ? and orderDate = ?";
PreparedStatement preparedStmt = conn.prepareStatement(deleteQuery);
preparedStmt.setInt(1, eID);
preparedStmt.setString(2, mealName);
preparedStmt.setString(3, curDate);
preparedStmt.executeUpdate();
conn.close();
英文:
You should combine conditions with and operator
String delete="DELETE FROM `eoms`.`order` WHERE (`employeeID` = '"+eID+"' and orderTime = '"+mealName+"' and orderDate = '"+curDate+"')";
As recommended, use prepare statement for your parameter to avoid a SQL injection
String deleteQuery = "DELETE FROM `eoms`.`order` WHERE employeeID =? and orderTime =? and orderDate=? ";
PreparedStatement preparedStmt = conn.prepareStatement(deleteQuery);
preparedStmt.setInt(1, eID);
preparedStmt.setInt(2, mealName);
preparedStmt.setInt(3, curDate);
preparedStmt.executeUpdate();
conn.close();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论