@Formula - Native Query Syntax Error in Hibernate

Question

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.

StanislavL · Accepted Answer · 2017-06-16 14:10:15Z

7

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.

answered Jun 16, 2017 at 14:10

StanislavL

57.5k9 gold badges75 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

HopeKing Over a year ago

That was perfect and fast ! Just for my understanding, did you manually type out the translation or was it generated using some tool?

StanislavL Over a year ago

It's manually typed. But you can switch show_sql to true and see real SQL query fired.

HopeKing Over a year ago

Yes, am aware of that. Curious because that your translation was perfectly formed :). Thanks again.

Collectives™ on Stack Overflow

@Formula - Native Query Syntax Error in Hibernate

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related