Stack Exchange
spring jdbc prepared statement with boolean fulltext search formatting issues
I have an sql query that uses boolean fulltext search:
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;

i.e. the quotation and speech marks in '"product manager"' is important to the query.
When I run this query as a spring jdbc prepared statement, I have to do some work arounds to get the string formatted correctly since if I have '?', jdbc doesnt recognise this as a parameter:
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});

But the correct result isnt produced from the query, the results are not in the expected order. I can only assume that this happens because of the formatting in the prepared statement parameters. But I can't see what the query looks like after adding the parameters, how can I debug this?

You need to remove the single quotes from your value. Single quotes in SQL delimit a string literal in the query string. When you set a parameter value, you only need to set the string value without the single quotes.
So, if you want to replace the literal '"product manager"' with a parameter, then you use ? in your query (so, without single quotes), and set the value "product manager" (again, without single quotes), which as a Java string literal is "\"product manager\"".
Using single quotes in the value passed as a parameter would be the equivalent of using the SQL String literal '''"product manager"'''.
