使用“喜欢”准备好的声明中的通配符 [英] Using "like" wildcard in prepared statement

问题描述

我正在使用预准备语句来执行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 ，因为从以下代码我将添加'关键字％'？

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，注释+％）或类似的东西。我在网上看到很多帖子但在任何地方都没有好的答案。

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 + "%");

这篇关于使用“喜欢”准备好的声明中的通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！