Invalid JSON text in argument 2 - json_contains using Spring JPA
up vote
0
down vote
favorite
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?
mysql sql json spring spring-data-jpa
add a comment |
up vote
0
down vote
favorite
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?
mysql sql json spring spring-data-jpa
Possible duplicate of Spring Boot JPA: how do query a JSON column in a table
– Teun van der Wijst
Nov 21 at 14:46
@ Teun van der Wijst but the positional param can't be filled by value when generate sql
– GeekLei
Nov 22 at 2:42
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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?
mysql sql json spring spring-data-jpa
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?
mysql sql json spring spring-data-jpa
mysql sql json spring spring-data-jpa
edited Nov 21 at 14:43
Billy Frost
1,72788
1,72788
asked Nov 21 at 13:15
GeekLei
90155
90155
Possible duplicate of Spring Boot JPA: how do query a JSON column in a table
– Teun van der Wijst
Nov 21 at 14:46
@ Teun van der Wijst but the positional param can't be filled by value when generate sql
– GeekLei
Nov 22 at 2:42
add a comment |
Possible duplicate of Spring Boot JPA: how do query a JSON column in a table
– Teun van der Wijst
Nov 21 at 14:46
@ Teun van der Wijst but the positional param can't be filled by value when generate sql
– GeekLei
Nov 22 at 2:42
Possible duplicate of Spring Boot JPA: how do query a JSON column in a table
– Teun van der Wijst
Nov 21 at 14:46
Possible duplicate of Spring Boot JPA: how do query a JSON column in a table
– Teun van der Wijst
Nov 21 at 14:46
@ Teun van der Wijst but the positional param can't be filled by value when generate sql
– GeekLei
Nov 22 at 2:42
@ Teun van der Wijst but the positional param can't be filled by value when generate sql
– GeekLei
Nov 22 at 2:42
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
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));
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
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));
add a comment |
up vote
0
down vote
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));
add a comment |
up vote
0
down vote
up vote
0
down vote
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));
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));
answered Nov 25 at 21:27
Elg
3113
3113
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53412879%2finvalid-json-text-in-argument-2-json-contains-using-spring-jpa%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Possible duplicate of Spring Boot JPA: how do query a JSON column in a table
– Teun van der Wijst
Nov 21 at 14:46
@ Teun van der Wijst but the positional param can't be filled by value when generate sql
– GeekLei
Nov 22 at 2:42