Use dynamic column names in Spring Data JPA

Question

I am trying to use dynamic column names and values in a query in Spring Data JPA

interface CustomFooRepository {
    fun findByValueIgnoreCase(query: String, type: RequestType): List<Foo>
}

class CustomFooRepositoryImpl(
    @PersistenceContext private val entityManager: EntityManager
) : CustomFooRepository {

    override fun findByValueIgnoreCase(query: String, type: RequestType): List<Foo> {
        val column = when (type) {
            RequestType.first_name -> "first_name"
            RequestType.last_name -> "last_name"
            RequestType.email -> "email"
        }

        val sql = """
            SELECT 
                *,
                SIMILARITY(:column, :query) AS score
            FROM foo
            WHERE :column iLIKE %:query%
            ORDER BY score DESC NULLS LAST, :column
            LIMIT 20;
        """

        val constructedQuery = entityManager.createNativeQuery(sql, Foo::class.java)
        constructedQuery.setParameter("column", column)
        constructedQuery.setParameter("query", query)
        return constructedQuery.resultList as List<Foo>
    }
}


interface FooRepository : JpaRepository<Foo, Long>, CustomFooRepository

But this gives me

org.springframework.dao.InvalidDataAccessResourceUsageException: No argument for named parameter ':query%'

Why?

Answer 1

You can not concatenate % inside the query string. It must be concatenate outside.

The best solution is to add a new parameter:

constructedQuery.setParameter("queryWildcard", "%" + query + "%")

So your final sql literal would be:

val sql = """
        SELECT 
            *,
            SIMILARITY(:column, :query) AS score
        FROM foo
        WHERE :column iLIKE :queryWildcard
        ORDER BY score DESC NULLS LAST, :column
        LIMIT 20;
    """