CODEWITHSUNDEEP
javamysqlspringhibernatejpa
HopeKing
HopeKing

Reputation: 3503

@Formula - Native Query Syntax Error in Hibernate

I have a One-Many Relationship between FullProduct and PartValue (One Full Product has many PartValue)

I am trying to use the below @Formula annotation - the syntax seems to be right, since I ran it in command line and gives me the correct result.

@Formula("select sum(pv.total_value) from part_value pv inner join full_product fp on fp.full_product_id=pv.full_product_id")
private Float totalAmount;

However, when I run it in Spring JPA/Hibernate, i get the following error

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'select sum(pv.total_value) from part_value pv inner join full_product fp on fp.full_product_id=pv.full_product_id' at line 1

There is nothing else in the error log, that seems to point to the right direction.

I am quite confused - If the SQL syntax in command line is running fine, why is Spring/Hibernate throwing up this error. Can someone help ?

Thanks for any pointers.

Upvotes: 3

Views: 6327

Answers (1)

StanislavL
StanislavL

Reputation: 57381

Try to add ( and ) for the subquery.

@Formula("(select sum(pv.total_value) from part_value pv inner join full_product fp on fp.full_product_id=pv.full_product_id)")
private Float totalAmount;

because it's translated into something like

select
  entityAlias1.field1,
  entityAlias1.field2,
  ...
  (select sum(pv.total_value) from part_value pv inner join full_product fp on fp.full_product_id=pv.full_product_id),
  ...
from the_entity_table entityAlias1
...

without the () the syntax is wrong.

Upvotes: 7

Related Questions