{"id":4277,"date":"2024-07-26T15:03:36","date_gmt":"2024-07-26T08:03:36","guid":{"rendered":"https:\/\/www.sqlservertutorial.net\/?page_id=4277"},"modified":"2024-07-29T07:53:17","modified_gmt":"2024-07-29T00:53:17","slug":"java-sql-server-like","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/java-sql-server\/java-sql-server-like\/","title":{"rendered":"Java SQL Server: Querying Data with the LIKE Operator"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to query data from an SQL Server table using the LIKE<\/code> operator from a Java program.<\/p>\n\n\n\n

This tutorial begins where the Paginate Data in Java tutorial<\/a> left off.<\/p>\n\n\n\n

We’ll use the LIKE<\/a><\/code> operator to find authors whose first or last names match a search term.<\/p>\n\n\n\n

Step 1. Define a new method findByName<\/code> in the AuthorDB<\/code> class:<\/p>\n\n\n

public<\/span> List<Author> findByName<\/span>(String term)<\/span> throws<\/span> DBException <\/span>{\n\n        var<\/span> sql = \"SELECT AuthorID, FirstName, LastName, BirthDate \"<\/span> +\n                  \"FROM Authors \"<\/span> +\n                  \"WHERE FirstName LIKE ? OR LastName LIKE ?\"<\/span>;\n\n\n        var<\/span> authors = new<\/span> ArrayList<Author>();\n\n        try<\/span> (var<\/span> statement = connection.prepareStatement(sql)) {\n\n\n            var<\/span> searchTerm = \"%\"<\/span> + term + \"%\"<\/span>;\n            statement.setString(1<\/span>, searchTerm);\n            statement.setString(2<\/span>, searchTerm);\n\n            try<\/span>(var<\/span> resultset = statement.executeQuery()) {\n                while<\/span> (resultset.next()) {\n                    authors.add(createAuthorFrom(resultset));\n                }\n            }\n\n            return<\/span> authors;\n\n        } catch<\/span> (SQLException e) {\n            throw<\/span> new<\/span> DBException(e.getMessage());\n        }\n    }<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
First, define the findByName()<\/code> method that accepts a parameter term<\/code> used for matching with the first and last names of authors:<\/p>\n\n\n
public<\/span> List<Author> findByName<\/span>(String term)<\/span> throws<\/span> DBException <\/span>{<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
The findByName()<\/code> method returns a list of Author<\/code> objects and throws a DBException<\/code> if any error occurs while querying data.<\/p>\n\n\n\n
Second, construct a SELECT<\/code> statement that uses the LIKE<\/code> operator to retrieve authors whose first or last names contain the term<\/code>:<\/p>\n\n\n
 var<\/span> sql = \"SELECT AuthorID, FirstName, LastName, BirthDate \"<\/span> +\n           \"FROM Authors   \"<\/span> +\n           \"WHERE FirstName LIKE ? OR LastName LIKE ?\"<\/span>;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Third, initialize an empty list of Author<\/code> objects:<\/p>\n\n\n
var<\/span> authors = new<\/span> ArrayList<Author>();<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fourth, create a PreparedStatement<\/code> object with the SELECT<\/code> statement by calling the prepareStatement<\/code> method of the Connection<\/code> object:<\/p>\n\n\n
try<\/span> (var<\/span> statement = connection.prepareStatement(sql)) {<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Fifth, set the values to the statement:<\/p>\n\n\n
var<\/span> searchTerm = \"%\"<\/span> + term + \"%\"<\/span>;\nstatement.setString(1<\/span>, searchTerm);\nstatement.setString(2<\/span>, searchTerm);<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Sixth, execute the query, create an Author<\/code> object from each row of the result set, and add it to the authors<\/code> list:<\/p>\n\n\n
try<\/span> (var<\/span> resultset = statement.executeQuery()) {\n  while<\/span> (resultset.next()) {\n    authors.add(createAuthorFrom(resultset));\n  }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Seventh, return the authors<\/code> list:<\/p>\n\n\n
return<\/span> authors;<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, throw a new DBException<\/code> if any error occurs while fetching the data:<\/p>\n\n\n
} catch<\/span>(SQLException e) {\n  throw<\/span> new<\/span> DBException(e.getMessage());\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Modify the main()<\/code> method of the Main<\/code> class to use the findByName()<\/code> method of the AuthorDB<\/code> class:<\/p>\n\n\n
import<\/span> java.sql.SQLException;\n\npublic<\/span> class<\/span> Main<\/span> <\/span>{\n    public<\/span> static<\/span> void<\/span> main<\/span>(String[] args)<\/span> <\/span>{\n        try<\/span> (var<\/span> connection = SQLServerConnection.connect()) {\n            var<\/span> authorDB = new<\/span> AuthorDB(connection);\n            var<\/span> authors = authorDB.findByName(\"vid\"<\/span>);\n            for<\/span>(var<\/span> author: authors) {\n                System.out.println(author.getAuthorId() + \" - \"<\/span> + author.getFirstName() + \", \"<\/span> + author.getLastName());\n            }\n        } catch<\/span> (SQLException | DBException e) {\n            System.err.println(e.getMessage());\n        }\n    }\n}<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
In the main()<\/code> method, we call the findByName()<\/code> method to find authors whose first or last names contain the term vid<\/code>:<\/p>\n\n\n\n
Step 4. Run the Java program. It’ll show the following output:<\/p>\n\n\n
16<\/span> - David, Hall\n29<\/span> - David, Wright<\/code><\/span>Code language:<\/span> Java<\/span> (<\/span>java<\/span>)<\/span><\/small><\/pre>\n\n\n
Both David, Hall<\/code> and David, Wright<\/code> contain the term vid<\/code>.<\/p>\n\n\n\n
Download the project source code #<\/a><\/h2>\n\n\n\n
Download the project source code<\/a><\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
    \n
  • Execute a SELECT<\/code> statement with the LIKE<\/code> operator to select data that matches a term.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t