CODEWITHSUNDEEP
mysqljpaspring-data-jpaspring-datajpql
Tharaka
Tharaka

Reputation: 199

how to create JPQL query for MySql JSON_EXTRACT function by using EntityManager

I have mysql table and there is a column used json data type. i want to execute query with the column as condition With JPQL, I can write it like bellow by using EntityManager.

It's totally working for below(select all product) 

 Query query = em.createQuery("select o from Product o");

and not working for json_extract funcation + entity manager

 Query query = em.createQuery("select o from Product o where json_extract(o.des,'$.org') = 'ABC'");

this it totally not working for me and return below error

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: 
Exception Description: Syntax error parsing [select o from Product o where json_extract(o.des,'$.org') = 'ABC']. 
[41, 85] The expression is not a valid conditional expression.

Caused by: org.eclipse.persistence.exceptions.JPQLException: 
Exception Description: Syntax error parsing [select o from Product o where json_extract(o.des,'$.org') = 'ABC']. 
[41, 85] The expression is not a valid conditional expression.

then, I tried CrudRepository and it worked, but I need to use EntityManager instead of using CrudRepository

public interface ProductRepository extends CrudRepository<Product, String> {

    @Query(value = "select o from Product o where json_extract(o.des,'$.org') = :org")
    List<Product> findProdcutsByOrgJPQL(@Param("org") String org);
}

So, there is no problem with my JPQL. problem with EntityManager.

how can I create a JPQL query for JSON_EXTRACT function by using EntityManager

Upvotes: 2

Views: 5318

Answers (1)

Dherik
Dherik

Reputation: 19060

As Billy said:

JPQL documentation has no such function with that name, so hardly surprising that an error occurs. If you want to ram in random SQL functions into JPQL then you use FUNCTION(...) as per all JPA docs tell you

I found the solution:

Query query = em.createQuery("select o from Product o WHERE FUNCTION('JSON_EXTRACT', o.des, '$.org') = :org");

Upvotes: 5

Related Questions