Java SQL Server: Querying Data with the LIKE Operator

Summary: in this tutorial, you will learn how to query data from an SQL Server table using the LIKE operator from a Java program.

This tutorial begins where the Paginate Data in Java tutorial left off.

We’ll use the LIKE operator to find authors whose first or last names match a search term.

Step 1. Define a new method findByName in the AuthorDB class:

public List<Author> findByName(String term) throws DBException {

        var sql = "SELECT AuthorID, FirstName, LastName, BirthDate " +
                  "FROM Authors " +
                  "WHERE FirstName LIKE ? OR LastName LIKE ?";


        var authors = new ArrayList<Author>();

        try (var statement = connection.prepareStatement(sql)) {


            var searchTerm = "%" + term + "%";
            statement.setString(1, searchTerm);
            statement.setString(2, searchTerm);

            try(var resultset = statement.executeQuery()) {
                while (resultset.next()) {
                    authors.add(createAuthorFrom(resultset));
                }
            }

            return authors;

        } catch (SQLException e) {
            throw new DBException(e.getMessage());
        }
    }Code language: Java (java)

How it works.

First, define the findByName() method that accepts a parameter term used for matching with the first and last names of authors:

public List<Author> findByName(String term) throws DBException {Code language: Java (java)

The findByName() method returns a list of Author objects and throws a DBException if any error occurs while querying data.

Second, construct a SELECT statement that uses the LIKE operator to retrieve authors whose first or last names contain the term:

 var sql = "SELECT AuthorID, FirstName, LastName, BirthDate " +
           "FROM Authors   " +
           "WHERE FirstName LIKE ? OR LastName LIKE ?";Code language: Java (java)

Third, initialize an empty list of Author objects:

var authors = new ArrayList<Author>();Code language: Java (java)

Fourth, create a PreparedStatement object with the SELECT statement by calling the prepareStatement method of the Connection object:

try (var statement = connection.prepareStatement(sql)) {Code language: Java (java)

Fifth, set the values to the statement:

var searchTerm = "%" + term + "%";
statement.setString(1, searchTerm);
statement.setString(2, searchTerm);Code language: Java (java)

Sixth, execute the query, create an Author object from each row of the result set, and add it to the authors list:

try (var resultset = statement.executeQuery()) {
  while (resultset.next()) {
    authors.add(createAuthorFrom(resultset));
  }
}Code language: Java (java)

Seventh, return the authors list:

return authors;Code language: Java (java)

Finally, throw a new DBException if any error occurs while fetching the data:

} catch(SQLException e) {
  throw new DBException(e.getMessage());
}Code language: Java (java)

Step 3. Modify the main() method of the Main class to use the findByName() method of the AuthorDB class:

import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        try (var connection = SQLServerConnection.connect()) {
            var authorDB = new AuthorDB(connection);
            var authors = authorDB.findByName("vid");
            for(var author: authors) {
                System.out.println(author.getAuthorId() + " - " + author.getFirstName() + ", " + author.getLastName());
            }
        } catch (SQLException | DBException e) {
            System.err.println(e.getMessage());
        }
    }
}Code language: Java (java)

In the main() method, we call the findByName() method to find authors whose first or last names contain the term vid:

Step 4. Run the Java program. It’ll show the following output:

16 - David, Hall
29 - David, WrightCode language: Java (java)

Both David, Hall and David, Wright contain the term vid.

Download the project source code

Download the project source code

Summary

• Execute a SELECT statement with the LIKE operator to select data that matches a term.