How to search a string including a single quote by using SQL WHERE clause

I want to search a string which including a single quote by using SQL WHERE clause. For example, I want to search "Tom's house" and the SQL clause should be "location = 'Tom's house'". Can anybody开发者_如何学运维 help me to format the SQL clause?

If you're doing the query literally, you can double up the ' so it's Tom''s house as SQLMenance said.

The better general solution, though, is to use parameterized queries via java.sql.PreparedStatement, e.g.:

PreparedStatement ps;
ResultSet rs;

// Create the prepared statement
ps = con.prepareStatement("SELECT x FROM table WHERE location = '?'");

// Set the parameter -- we don't have to quote it, the SQL classes do that
ps.setString(1, "Tom's House");

// Do it
rs = ps.executeQuery();

Not only does this solve the quoting problem for you, but more importantly it helps protect you from SQL injection attacks on your database. Never include any user-supplied data "as is" in a query via string concatenation or the like, either use parameterized queries or make absolutely sure you're escaping the data correctly beforehand.

double the single quote Tom''s house

Usage of prepared statement API avoids such issues.