Hibernate Derived Properties – @Formula Annotation

HibernateIn 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.

[adsenseyu1]

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;
	}

}

Please note that 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 just yet.

A more complex example:

@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();
  }
}

[adsenseyu2]

Be Sociable, Share!

10 thoughts on “Hibernate Derived Properties – @Formula Annotation

    • For example for an entity POJO called Deposit.java :

      public class Deposit implements Serializable {
      
          private Long depositId;
          private Client client;
          private BigDecimal balance;
          private BigDecimal depositValue;
      
          public Deposit() {
          }
      
          public BigDecimal getBalance() {
              return balance;
          }
      
          public void setBalance(BigDecimal balance) {
              this.balance = balance;
          }
      
          public Client getClient() {
              return client;
          }
      
          public void setClient(Client client) {
              this.client = client;
          }
      
          public Long getDepositId() {
              return depositId;
          }
      
          public void setDepositId(Long depositId) {
              this.depositId = depositId;
          }
      }
      
      <hibernate-mapping>
          <class name="net.ozar.demoapp.entity.Deposit" table="DEPOSIT">
              <id name="depositId" type="java.lang.Long">
                  <column name="DEPOSIT_ID" />
                  <generator class="identity" />
              </id>
              <many-to-one name="client" class="net.ozar.demoapp.entity.Client" lazy="false" fetch="select">
                  <column name="CLIENT_ID" />
              </many-to-one>
             
              <property name="balance" type="big_decimal">
                  <column name="BALANCE" precision="20" scale="2" />
              </property>
              
              <property name="depositValue" 
                formula="( SELECT IFNULL((SELECT d.NIGHTLY_RATE FROM DEPOSIT d WHERE d.ACCOUNT_ID = ACCOUNT_ID),1)*NOMINAL_RATE)" />
              
          </class>
      </hibernate-mapping>
      
  1. 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;
      }

  2. 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.

  3. 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.

  4. Does the id column need to be named id? Mine is called primaryKey and I get an invalid column name exception.

Leave a Reply

Your email address will not be published. Required fields are marked *


*