Hibernate Derived Properties – @Formula Annotation

In Hibernate a derived property (also called a calculated property) is a read-only property whose value is calculated at fetch time using SQL expressions.

Example: For an employee entity with properties such as an id and the employee name also a monthlySalary property, you might also want to have a yearlySalary which is not necessarily stored in the database.

package net.ozar.exp.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="EMPLOYEE")
public class Employee implements java.io.Serializable {

    private static final long serialVersionUID = -7311873726885796936L;

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

    @Column(name="FIRST_NAME", length=31)
    private String firstName;

    @Column(name="LAST_NAME", length=31)
    private String lastName;

    @Column(name="MONTHLY_SALARY")
    private float monthlySalary;

    public Employee() {
    }

    // getters and setters
        // ...

    public float getMonthlySalary() {
        return monthlySalary;
    }

    public void setMonthlySalary(float monthlySalary) {
        this.monthlySalary = monthlySalary;
    }

     /* This artificial property - as I call it - is a kind of a calculated property, but not with Hibernate derived property support - not just yet */
    public float getYearlySalary() {
        return this.monthlySalary * 12;
    }

}

The above example gives us a simple calculation in memory for just screen output without Hibernate’s derived property or the @Formula support.  Now take a moment to reflect that we need all the employees whose yearly salary average is above $5000. Then what? In this case, you might wanna make use of Hibernate’s derived property feature.

As introduced above, a derived, or calculated, property is a read only property which its value is calculated at fetch time using SQL expressions. For example a Product class might have a price and a calculated final price which is the price including VAT. The first (not so good) solution could be something like that:

[wp_ad_camp_1]

To define a derived property in Hibernate, we use the @Formula annotation or the <formula> tag in the hbm.xml definition file.

package net.ozar.exp.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

import org.hibernate.annotations.Formula;

@Entity
@Table(name="EMPLOYEE")
public class Employee implements java.io.Serializable {

    private static final long serialVersionUID = -7311873726885796936L;

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

    @Column(name="FIRST_NAME", length=31)
    private String firstName;

    @Column(name="LAST_NAME", length=31)
    private String lastName;

    @Column(name="MONTHLY_SALARY")
    private float monthlySalary;

    @Formula("MONTHLY_SALARY*12")
        private float yearlySalary;

    public Employee() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getFirstName() {
        return firstName;
    }

    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public float getMonthlySalary() {
        return monthlySalary;
    }

    public void setMonthlySalary(float monthlySalary) {
        this.monthlySalary = monthlySalary;
    }

    public float getYearlySalary() {
        return yearlySalary;
    }

}

In the above example the value of the @Formula annotation is actually an SQL expression which refers to the “MONTHLY_SALARY” column in the database and not the Hibernate property monthlySalary.

Please note that the @Formula annotation is specific to Hibernate and is not present in other JPA implementations – at least not yet.

Now a more complex example as also a better practice:

@Formula("(select min(l.creation_date) from Logs l where l.customer_id = id)")
private Date firstLoginDate;

In this example, the query is a native SQL query and id (as the parameter) is the id of the current entity.

@OneToMany(mappedBy="user",cascade=CascadeType.ALL, fetch=FetchType.LAZY)
@BatchSize(size=100)
private Set<Log> logs = new HashSet<Log>();

@Formula("(select min(l.creation_date) from logs l where l.user_id = id)")
private Date firstLoginDate;

@Formula("(select max(l.creation_date) from logs l where l.user_id = id)")
private Date lastLoginDate;

@Formula("(select coalesce(extract ('day' from age(max(l.creation_date))), 9999) from logs l where l.user_id = id)")
private int daysSinceLastLogin;

// ...

public Date getFirstLoginDate() {
  return firstLoginDate;
}

public Date getLastLoginDate() {
  return lastLoginDate;
}

public int getDaysSinceLastLogin() {
   return daysSinceLastLagin;
}

And the log entity would be something like:

@Entity
@Table(name = "LOGS")
public class Log {
 //  ...
  @NotNull
  @Column(name="CREATION_DATE")
  private Date creationDate;

  @ManyToOne
  @JoinColumn(name="USER_ID")
  private User user;

  @PrePersist 
  public void prePersist()  {
    creationDate = new Date();
  }
}