spring jdbc准备的语句存在布尔全文搜索格式问题

Question

我有一个使用布尔全文搜索的sql查询：

SELECT
    *,
    MATCH (job_title) AGAINST ('"product manager"' in boolean mode) AS title_relevance_exact,
    MATCH (job_description) AGAINST ('"product manager"' in boolean mode) AS description_relevance_exact,
    MATCH (job_title) AGAINST ('product manager' in boolean mode) AS title_relevance_part,
    MATCH (job_description) AGAINST ('product manager' in boolean mode) AS description_relevance_part
FROM
    jobs
WHERE
    MATCH(job_title, job_description) AGAINST ('product manager') AND
    date_posted >= now() - INTERVAL 30 DAY
ORDER BY
    job_title LIKE 'product manager' DESC,
    title_relevance_exact DESC,
    description_relevance_exact DESC,
    title_relevance_part DESC,
    description_relevance_part DESC
LIMIT 300;

即，“产品经理”中的引号和语音标记对查询很重要。

当我以spring jdbc预备语句的形式运行这个查询时，我必须做一些工作来正确格式化字符串，因为如果我有< code > '？'，jdbc不会将它识别为参数:

String queryPrepared =
                "SELECT\n" +
                "   *,\n" +
                "   MATCH (job_title) AGAINST (? in boolean mode) AS title_relevance_exact,\n" +
                "   MATCH (job_description) AGAINST (? in boolean mode) AS description_relevance_exact,\n" +
                "   MATCH (job_title) AGAINST (? in boolean mode) AS title_relevance_part,\n" +
                "   MATCH (job_description) AGAINST (? in boolean mode) AS description_relevance_part\n" +
                "FROM\n" +
                "   jobs\n" +
                "WHERE\n" +
                "   MATCH(job_title, job_description) AGAINST (?) AND\n" +
                "   date_posted >= now() - INTERVAL 30 DAY\n" +
                "ORDER BY\n" +
                "   job_title LIKE ? DESC,\n" +
                "   title_relevance_exact DESC,\n" +
                "   description_relevance_exact DESC,\n" +
                "   title_relevance_part DESC,\n" +
                "   description_relevance_part DESC\n" +
                "LIMIT 500;";
        String param1 = String.format("'\"%1$s\"'", searchValue);
        String param2 = String.format("'%1$s'", searchValue);

        List<Map<String, Object>> resultPrepared = jdbcTemplate.queryForList(queryPrepared,
                new Object[] {param1, param1, param2, param2, param2, param2});

但是查询没有生成正确的结果，结果的顺序也不符合预期。我只能假设这是由于prepared语句参数中的格式设置造成的。但我看不到添加参数后的查询是什么样子，我如何调试它？

Answer 1

您需要从值中删除单引号。SQL中的单引号在查询字符串中分隔字符串文字。设置参数值时，只需设置不带单引号的字符串值。

因此，如果要将文本“产品管理器”替换为参数，则在查询中使用 ？（因此，不带单引号），并设置值“product manager”（同样，不带单引号），作为 Java 字符串文本，该值是“\”product manager\”。

在作为参数传递的值中使用单引号相当于使用SQL字符串文字""产品经理"""。