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(); } }
10 Comments
How would I make a formula column definition in the hbm.xml file?
For example for an entity POJO called Deposit.java :
it would be great to have an equaivalent of @Formula for EclipseLink
@cj Indeed. I have no idea what the EclipseLink / TopLink guys are up to, nowadays.
Is it possible to have yearlySalary updateable.
I mean something like that:
public void setYearlySalary(float yearlySalary) {
this.monthlySalary = yearlySalary/12;
}
There appears to be some logical error in your code. Do you mean?
public void setMonthlySalary(float salary) {
this.monthlySalary ,0 salary;
}
public float getYearlySalary() {
return monthlySalary * 12;
}
Is it possible to use this annotation to auto-generate a date property when I use hibernate to save an object in the database? For example:
In the POJO:
..
@Formula(‘select sysdate from dual’)
private Date date;
…
the method in the DAO:
…
public int insertObject(Object object){
…
return session.save(object);
}
The problem is that the field in the table is not null so if my object doesn’t have set the date field, I receive a constraint violation. Also, I have to take the date from the DB.
Hi,
Could you please Tell Me How To use dynamic values in @Formula query.
I have a case to calculate using the values comming from Client (Dynamic Values).(Ex: database_column_value*user_entered_percentage),Please answer ASAP.
Thanks & Regards
raju.
Does the id column need to be named id? Mine is called primaryKey and I get an invalid column name exception.
perhaps primaryKey is a reserved Hibernate word?