时间戳

首页 > 解决方案 > 带有布尔全文搜索格式问题的spring jdbc准备语句

java - 带有布尔全文搜索格式问题的spring jdbc准备语句

问题描述

我有一个使用布尔全文搜索的 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;

即引号和语音标记'"product manager"'对查询很重要。

当我将此查询作为 spring jdbc 准备语句运行时，我必须做一些工作以正确格式化字符串，因为如果我有'?'，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});

但是查询没有产生正确的结果，结果不是预期的顺序。我只能假设发生这种情况是因为准备好的语句参数中的格式。但是我在添加参数后看不到查询的样子，我该如何调试呢？

标签： javamysqljdbcspring-jdbc

解决方案

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

所以，如果你想'"product manager"'用参数替换文字，那么你?在查询中使用（所以，没有单引号），并设置值"product manager"（同样，没有单引号），作为 Java 字符串文字是"\"product manager\"".

在作为参数传递的值中使用单引号相当于使用 SQL 字符串文字'''"product manager"'''。

推荐阅读