javaspringhibernatejpa

Use entity's value in query with Hibernates @Formula annotation

Given the following db structure:

enter image description here

And having the following mapping for this structure:

@Entity
@Table(name = "a")
class A {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "id", updatable = false, nullable = false)
  private int aId;

  @Column(name = "title")
  private String title;

  @Formula("(SELECT COUNT(*) FROM b WHERE b.a_id = aId)")
  private Integer count;
}

My aim is to get the count of all references to A from B (where aId in the query is the value of the current entity).

But I get following Error:

Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause 
java.sql.SQLException: Unknown column 'a0_.aId' in 'where clause'
Solution

  • As Simon mentioned you need to use the name of the column, not the attribute name. In your example above this would be: @Formula("(SELECT COUNT(*) FROM b WHERE b.a_id = id)")