1

I have two MySQL tables with different charset UTF8(table_a) and latin1(table_b) and I would like translate the following native query to JPQL (JPA 2.1):

SELECT a.* FROM table_a a, table_b b WHERE CONVERT(a.id USING latin1) = b.a_id;

I tried the next expressions but are wrong:

@Query("SELECT a FROM tableA a, tableB WHERE FUNCTION('CONVERT', a.id USING latin) = b.aId")

@Query("SELECT a FROM tableA a, tableB WHERE FUNCTION('CONVERT', 'a.id USING latin') = b.aId")

@Query("SELECT a FROM tableA a, tableB WHERE FUNCTION('CONVERT', a.id, 'USING latin') = b.aId")

@Query("SELECT a FROM tableA a, tableB WHERE FUNCTION('CONVERT', a.id 'USING latin') = b.aId")

Is there a way to use MySQL CONVERT function with JPQL?

Improve this question
7
  • I don't think that JPQL allows for the FUNCTION or CONVERT keywords, but you could add nativeQuery = true to your @Query in order to use native MySQL functions. @Query("SELECT a FROM tableA a, tableB WHERE FUNCTION('CONVERT', a.id USING latin) = b.aId", nativeQuery = true) Commented Feb 5, 2020 at 14:45
  • I just read logicbig.com/tutorials/java-ee-tutorial/jpa/… Commented Feb 5, 2020 at 15:13
  • 1
    If you are using vanilla JPQL and Hibernate, the link you shared would work. However, if you are using SpringData, adding nativeQuery = true will solve the problem. See here. By using nativeQuery, you would use your base SQL query. Commented Feb 5, 2020 at 15:26
  • I appreciate your time but I need to use JPQL to simplify the field mapping. TableA is a very complex POJO and the example I gave was a great simplification of the real query. I will try with Criteria. Commented Feb 6, 2020 at 8:04
  • 1
    @darkmnemic It works fine with nativeQuery=true. Thank you. Commented Feb 10, 2020 at 11:31
Related questions
2
Convert Integer to String in Spring Data JPA Query
24
spring data jpa utf-8 encoding not working
0
Spring JPA + CRUD - custom query doesn't allow _ characters?
Load 7 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.