Multiple conditions in an SQL Delete statement

Viewed 639

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+"');";
2 Answers

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.

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