Question 20948 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Native Query not working in Spring Data JPA. What could I be doing wrong?

For some reasons my query is running perfectly in MySQL WorkBench but when added to the repository I am getting syntax error

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 ‘as e1, profile_history as e2 WHERE e2.profile_id =
e1.profile_id and’ at line 1.

@Query(value = "( SELECT e2.* "
            + " FROM (select ph1.profile_id, max(ph1.last_updated_on) as last_updated_on2 "
            + "     FROM profile_history as ph1 "
            + "     GROUP BY ph1.profile_id ) as e1, profile_history as e2 "
            + " WHERE e2.profile_id = e1.profile_id and e2.last_updated_on = e1.last_updated_on2 )", nativeQuery = true)
    Page<ProfileHistory> getAllProfileHistoryByLastestRow(Pageable paging);

Honestly expecting to get a db hit without any error and retrieve results.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I figured out the issue after fighting for 02 days. The jdbc driver is not interpreting the query correctly. I had to escape the columns and tables in the native query. Normally I shouldn't have to do this.

    @Query(value = " SELECT `e2`.`*` "
            + " FROM (select `eph1`.`employee_id`, max(`eph1`.`last_updated_on`) as `last_updated_on2` "
            + "     FROM `employee_profile_history` `eph1` "
            + "     GROUP BY `eph1`.`employee_id` ) `e1`, `employee_profile_history` `e2` "
            + " WHERE (`e2`.`employee_id` = `e1`.`employee_id` and `e2`.`last_updated_on` = `e1`.`last_updated_on2` )",
            countQuery = "SELECT distinct count(*) FROM `employee_profile_history`",
            nativeQuery = true)

  2. 0

    Maybe you need to specify dialect in the properties file. Don’t know your MySQL version, so can’t give you concrete example, but it should be something like this:

    spring.jpa.database-platform=org.hibernate.dialect.MySQL5InnoDBDialect
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search