Home > Java > javaTutorial > How to use the \'like\' wildcard effectively with prepared statements?

How to use the \'like\' wildcard effectively with prepared statements?

Linda Hamilton
Release: 2024-11-17 14:29:02
Original
826 people have browsed it

How to use the

Utilizing "like" Wildcard in Prepared Statement

Prepared statements offer an effective way to execute SQL queries by preventing SQL injection attacks and enhancing performance. However, the integration of the "like" wildcard with prepared statements can be a bit tricky.

Consider the following query that searches for rows in the "analysis" table based on a keyword:

PreparedStatement pstmt = con.prepareStatement(
      "SELECT * FROM analysis WHERE notes like ?");
pstmt.setString(1, notes);
ResultSet rs = pstmt.executeQuery();
Copy after login

To use the "like" wildcard with this query, you need to include it within the value being bound to the prepared statement parameter. Simply adding "keyword%" to pstmt.setString(1, notes) won't suffice.

Instead, you can implement prefix, suffix, or global matching depending on your needs:

  • Prefix Matching: Prefix matching searches for rows that start with the keyword. To achieve this with your query, use the following code:
notes = notes
    .replace("!", "!!")
    .replace("%", "!%")
    .replace("_", "!_")
    .replace("[", "![");
PreparedStatement pstmt = con.prepareStatement(
        "SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");
Copy after login
  • Suffix Matching: Suffix matching searches for rows that end with the keyword. To implement this, modify the code as follows:
pstmt.setString(1, "%" + notes);
Copy after login
  • Global Matching: Global matching searches for rows that contain the keyword anywhere within the column value. To perform global matching, use the following code:
pstmt.setString(1, "%" + notes + "%");
Copy after login

Remember to escape special characters in the "notes" string using a forward slash () to prevent them from interfering with the "like" wildcard search.

The above is the detailed content of How to use the 'like' wildcard effectively with prepared statements?. For more information, please follow other related articles on the PHP Chinese website!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template