1

in my table items has a json column named tag. keeping data like ["tag1", "tag2"] .

i want to select from this table filter with the given tag.
in mysql command line, json_contains works. 

select * from items where json_contains(tags, '"tag1"');

but how can i using it in Spring JPA? 

@Query(value = "select * from items where json_contains(tags, ?1))", nativeQuery = true)
Page<ItemDO> list(String query, Pageable pageable);

got error

TRACE 21469 --- [           main] o.h.type.descriptor.sql.BasicBinder      : binding parameter [1] as [VARCHAR] - [desc]
WARN 21469 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 1064, SQLState: 42000
ERROR 21469 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') limit 10' at line 1

so how can i using json_contains withing Spring JPA?

GeekLei
  • 991
  • 7
  • 8
  • Possible duplicate of [Spring Boot JPA: how do query a JSON column in a table](https://stackoverflow.com/questions/43917096/spring-boot-jpa-how-do-query-a-json-column-in-a-table) – Teun van der Wijst Nov 21 '18 at 14:46
  • @ Teun van der Wijst but the positional param can't be filled by value when generate sql – GeekLei Nov 22 '18 at 02:42

1 Answers1

2

There are two issues with your implementation:

  • There as an extra parenthesis at the end of SQL query ), look at what logs say. Instead, you should write it as follows:

    @Query(value = "select * from items where json_contains(tags, ?1)", nativeQuery = true)

  • Having done that, you also need to wrap the method parameter (your query variable) inside double quotations, to exactly match what you tried in MySQL command line console. So, you will call the method as follows:

    yourRepository.list("\"tag1\"", PageRequest.of(,10) );

Alternative solution

You may use Spring Data Specifications API to avoid native queries.

Thus, you could do the following:

@Repository
public interface ItemRepository extends JpaRepository<ItemDao, Integer> , JpaSpecificationExecutor<ItemDao> {

    default Page<ItemDao> findItemsByJsonTagValue(String jsonTagValue, Pageable pageable){
       return findAll((root, query, builder) -> {
            final String CONTAINS_FUNCTION = "JSON_CONTAINS";
            final String JSON_COLUMN_NAME = "tags" ;
            final int TRUE_BIT = 1;
            return builder.equal(
                    builder.function(
                            CONTAINS_FUNCTION,
                            String.class,
                            root.<String>get(JSON_COLUMN_NAME),
                            builder.literal(String.format("\"%s\"", jsonTagValue))),TRUE_BIT);
    }, pageable);
}

}

And then somewhere in your code, you would call the method as follows :

itemRepository.findItemsByJsonTagValue("tag1", PageRequest.of(0  ,10));
Elgarni
  • 236
  • 2
  • 5