A Java PreparedStatement example with a SQL SELECT statement and LIKE clause

Java/JDBC FAQ: Can you share an example of a PreparedStatement with a SQL SELECT statement and LIKE clause?

Sure. Here’s an example of how to use a JDBC PreparedStatement with a SQL SELECT query when accessing a database. As requested, it uses a SQL SELECT statement with a LIKE clause.

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 ps.setString() statement.

Given that background, here’s a sample Java method that shows this PreparedStatement/SELECT/LIKE combination.

private List getTopics (Connection conn, String searchCriteria)
throws SQLException
{
  List blogs = new LinkedList();
  String query = "SELECT id, text FROM blogs WHERE UPPER(text) LIKE ?";
  try
  {
    // going to do a search using "upper"
    searchCriteria = searchCriteria.toUpperCase();

    // create the preparedstatement and add the criteria
    PreparedStatement ps = conn.prepareStatement(query);
    ps.setString(1, "%" + searchCriteria + "%");

    // process the results
    ResultSet rs = ps.executeQuery();
    while ( rs.next() )
    {
      Blog blog = new Blog();
      blog.setID  ( rs.getInt("id") );
      blog.setText( rs.getString("text") );
      blogs.add(blog);
    }
    rs.close();
    ps.close();
  }
  catch (SQLException se)
  {
    // log exception;
    throw se;
  }
  return blogs;
}  

As you can see, the setup work is a lot like using a PreparedStatement with an INSERT, UPDATE, or DELETE, but in the case of a SQL SELECT statement you process the results you get back from the ResultSet. As mentioned, the trick is making sure your String just says LIKE ?, and then adding the wildcard characters you need (i.e., the % character) to your PreparedStatement parameter.