CODEWITHSUNDEEP
javahibernatejpa
Dmytro
Dmytro

Reputation: 83

How to use entity field in Hibernate @Formula

Many times I'm using @Formula in my entities. But always it was a simple query or stored procedure with parameter which I can take as filed from table. Now I need to user some property from related object. But I see exception when try to get object from DB. Please see an example below

@Entity
@Table(name = "MINISTRY")
public class Ministry {

    @Id
    @Column(name = "ID")
    private Long id;

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

    // unnecessary code

}

@Entity
@Table(name = "DEPARTMENT")
public class Department {

    @Id
    @Column(name = "ID")
    private Long id;

    @Column(name = "DEP_NAME")
    private String departmentName;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "MINISTRY_ID")
    private Ministry ministry;

    // unnecessary code
}

@Entity
@Table(name = "EMPLOYEE")
public class Employee {

    @Id
    @Column(name = "ID")
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "DEPARTMENT_ID")
    private Department department;

    @Formula("test_package.calc_something(department.ministry.id)")
    private BigDecimal someMetric;

    // unnecessary code

}

How I should use entity prop in @Formula. I don't want to write something like 

select d.ministry.id from Department d ...

Upvotes: 6

Views: 9311

Answers (1)

Tobias Liefke
Tobias Liefke

Reputation: 9022

If you read the JavaDoc of Formula you will see:

The formula has to be a valid SQL fragment

So you will have to use SQL like:

 @Formula("test_package.calc_something("
            + "select DEP.MINISTRY_ID from DEPARTMENT DEP where DEP.ID = DEPARTMENT_ID"
         + ")")
 private BigDecimal someMetric;

The only thing that is modified by Hibernate in the fragment before writing it to SQL: It will add the table alias to your columns (as you can't predict that). I mention that, as only a rudimentary SQL parser is used for that, which will insert the alias at wrong positions for more complex fragments.

A remark about performance: The formula is executed for every Department entity that you load, even if you only want to use the attribute for sorting or filtering (just guessing from the name of the attribute) - unless you use @Basic(fetch = FetchType.LAZY) and turn bytecode instrumentation on (or emulate that with FieldHandled).

Upvotes: 8

Related Questions