How to use mysql "json_contains" with JPA Specification

Question

I want use JPA Specification to query my data. The column is annotated by @Type(type = "json"). And I generated predicate with those code:

 cb.like(root.get<String>("exampleColumnName"), "%queryParam%")

finally,I got the sql like this and it cant get anyting form mysql:

select * from XXX where exampleColumnName like '"%queryParam%"'

But the except sql is

select * from XXX where exampleColumnName like '%queryParam%'

I trace the code to com.vladmihalcea.hibernate.type.json.internal.JsonTypeDescriptor and found the "unwrap" method is different with org.hibernate.type.descriptor.java.StringTypeDescriptor.

I want to generate a LikePredicate about the column annotated by @Type(type = "json");

Answer 1

Finally，I found a method “function” can do what I want

return Specification { root, _, cb ->
            val predicate = cb.conjunction()
            val expressions = predicate.expressions

            if (!queryForm.areaCode.isNullOrBlank()) {
                expressions.add(
                    cb.equal(
                        cb.function(
                            "JSON_CONTAINS",
                            String::class.java,
                            root.get<String>("areaCodeList"),
                            cb.literal(jacksonObjectMapper().writeValueAsString(arrayOf(queryForm.areaCode))),
                            cb.literal('$')
                        ), "1"
                    )
                )
            }
            predicate
        }