Tuesday, September 6, 2011

A Java PreparedStatement with a SELECT statement and LIKE clause

I want to select site language texts with filter. (I have added a LIKE clause to the SELECT statement.)

For me, the hard part of this example is figuring out how to use wildcard characters with your PreparedStatement parameter. I tried adding it directly to the SQL SELECT statement, then realized I just needed to use the ? character there, and then add the wildcards in the prepStmnt.setString() statement.

Here's a sample Java method that shows this PreparedStatement/SELECT/LIKE combination.
private String getTextList(){
  JSONObject json    = new JSONObject();
  ArrayList>jsonobject> data  = new ArrayList>jsonobject>();
  JSONObject rows    = null; 

  String getTextListSQL  = "SELECT * FROM texts WHERE UPPER(en) LIKE ? ORDER BY en ASC;";
  String enFilter    = "";
  PreparedStatement prepStmnt = null;
  ResultSet rs     = null;
  try {
   enFilter = (request.getParameter("enFilter") != null)? request.getParameter("enFilter"):"Mega";
   prepStmnt = dbConn.prepareStatement(getTextListSQL);
   prepStmnt.setString(1, "%" + enFilter.toUpperCase() + "%");
   rs = prepStmnt.executeQuery();
    rows = new JSONObject();
    rows.put("id", rs.getInt("id"));
    rows.put("en", rs.getString("en"));
    rows.put("ru", rs.getString("ru"));
   json.put("data", data);
  } catch (SQLException e) {
   logger.error("getTextList() | SQLException", e);
  } catch (Exception e) {
   logger.error("getTextList() | Exception", e);
  return json.toString();
That is all ...

1 comment: