How to concatenate a character to a SQL string in a preparedstatemenet

When I concatenate a character namely tid to this string in Java. I will get where sc.CategoryCode = C But I actually need where sc.CategoryCode = 'C' when I add a single quote both on the start and on the end side of tid like where sc.CategoryCode = '" + tid + "'"; I'll get where sc.CategoryCode = 'C" And it seems very strange. Thanks Jack

String str = "select sc.* from SubCategory sc"
           + "  where sc.CategoryCode = " + tid;
Jon Skeet
people
quotationmark

You shouldn't build up your SQL like this - you should use parameterized SQL instead:

// TODO: Closing the statement etc
String sql = "select sc.* from SubCategory sc where sc.CategoryCode = ?";
PreparedStatement statement = conn.prepareStatement(sql);
statement.setString(tid); // Or statement.setString(String.valueOf(tid))
ResultSet results = statement.executeQuery();

Reasons for using parameterized SQL:

  • Avoiding SQL injection attacks
  • Avoiding unnecessary (and potentially problematic) conversions, particularly with dates/times
  • Keeping your code (the SQL) separate from the data (the parameters), which aids readability

people

See more on this question at Stackoverflow