MySQL Java deleting row based on two string columns

Question

This is the line

PreparedStatement delete = con.prepareStatement("DELETE FROM " + tableName +" WHERE first LIKE " + firstName + " AND last LIKE " + lastName);

I want to check if both the first name and the last name match the query before i delete the row. 'first' and 'last' are both columns in my table. I am getting this error message

org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such column: Hendrix)

In this case Hendrix is the value for lastName.

Why does last LIKE ____ not work in this case? Is there another keyword i should use if i am checking multiple strings?

Answer 1

In Java, you can escape quotes with \:

PreparedStatement delete = con.prepareStatement("DELETE FROM " + tableName + " WHERE first = \"" + firstName + "\" AND last = \"" + lastName + "\"");

Of course, i find that a bit hard to read, so I prefer to mix my single and double quotes rather than escape them:

PreparedStatement delete = con.prepareStatement('DELETE FROM ' + tableName + ' WHERE first = "' + firstName + '" AND last = "' + lastName + '"');

As for your question on LIKE. Try reading up on the difference between LIKE and =. There is a lot of information out there on it. In short...LIKE is for use with wildcards.

Answer 2

If you used the prepared statement the way it is intended, with placeholders for the values and then binding the values afterwards with setString you wouldn't have this problem. Try this instead:

PreparedStatement delete = con.prepareStatement("DELETE FROM " + tableName +" WHERE first LIKE ? AND last LIKE ?");
delete.setString(1, "%" + firstName + "%");
delete.setString(2, "%" + lastName + "%");
delete.executeUpdate();

Note that to use LIKE you need to include the % in the value being bound. If you want to compare exact values, you would use this instead:

PreparedStatement delete = con.prepareStatement("DELETE FROM " + tableName +" WHERE first = ? AND last = ?");
delete.setString(1, firstName);
delete.setString(2, lastName);
delete.executeUpdate();