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;

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

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

但是查询没有产生正确的结果，结果不是预期的顺序。我只能假设这是由于准备好的语句参数中的格式化造成的。但是我看不到添加参数后的查询是什么样子的，我怎么调试呢？

Answer 1

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

因此，如果希望用参数替换文字'"product manager"'，那么可以在查询中使用? (因此，不带单引号)，并设置值"product manager" (同样，不带单引号)，它作为Java string文字是"\"product manager\""。

在作为参数传递的值中使用单引号等同于使用SQL String文字'''"product manager"'''。