使用“喜欢"准备好的语句中的通配符 | 准备好的语句中的通配符

本文介绍了使用“喜欢"准备好的语句中的通配符的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我正在使用准备好的语句来执行 mysql 数据库查询.我想实现基于各种关键字的搜索功能.

I am using prepared statements to execute mysql database queries. And I want to implement a search functionality based on a keyword of sorts.

为此我需要使用 LIKE 关键字，我知道的就这么多.而且我之前也使用过准备好的语句，但是我不知道如何将它与 LIKE 一起使用，因为从以下代码中我将在哪里添加 'keyword%'?

For that I need to use LIKE keyword, that much I know. And I have also used prepared statements before, but I do not know how to use it with LIKE because from the following code where would I add the 'keyword%'?

我可以在 pstmt.setString(1, notes) 中直接使用它作为 (1, notes+"%") 或类似的东西.我在网络上看到了很多关于此的帖子，但在任何地方都没有好的答案.

Can I directly use it in the pstmt.setString(1, notes) as (1, notes+"%") or something like that. I see a lot of posts on this on the web but no good answer anywhere.

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

推荐答案

您需要在值本身中进行设置，而不是在准备好的语句 SQL 字符串中进行设置.

You need to set it in the value itself, not in the prepared statement SQL string.

所以，这应该用于前缀匹配:

So, this should do for a prefix-match:

notes = notes
    .replace("!", "!!")
    .replace("%", "!%")
    .replace("_", "!_")
    .replace("[", "![");
PreparedStatement pstmt = con.prepareStatement(
        "SELECT * FROM analysis WHERE notes LIKE ? ESCAPE '!'");
pstmt.setString(1, notes + "%");

或后缀匹配:

pstmt.setString(1, "%" + notes);

或全局匹配:

pstmt.setString(1, "%" + notes + "%");