Hibernate - Formula - invalid Query

I have a question about Hibernate Formula.

Let us assume that I have an entity "garage" in which there are numerous "cars". Now I would like to add a property in the car class that tells me which number of car it is in the garage.

I created this formula on the car-class:

@Formula("(SELECT pos FROM (SELECT rownum AS pos, c.id FROM garage g "
+ "LEFT JOIN car c ON g.id = c.garage_id "
+ "WHERE g.id = GARAGE_ID "
+ "ORDER BY c.date) WHERE id = CAR_ID)")

The entity car has both properties (ID and GARAGE_ID). How can I now incorporate these into the Formula?

Furthermore I have the problem that Hibernate always generates an invalid SQL from it.

Pos becomes query0_.pos. However, this is not valid.

The rule with the SQL fragment inside the @Formula annotation is that any "bare" (unqualified) column reference is going to be qualified by the alias of the entity the formula belongs to.

So, for example, in this @Formula:

@Formula("(select upper(isbn) || ' ' || upper(a.name) from authors a where a.id=author_id)")

The columns which are going to get a qualifier attached to them are isbn and author_id.

So in your example:

• you should add a qualifier to pos to suppress this behavior, but
• you should leave id and garage_id unqualified since they belong to the car entity.

However, there's something that seems rather confusing in your code. Why would you be joining back to the car table here? That doesn't look quite right.