Sometimes, we need a calculated column in hibernate; at such a time, the formula feature is used. For this, we will use the @Formula annotation with the field.
The field annotated with the @Formula annotation is a read-only field, and the formula is only applied while using the SELECT operation.
To show how formula works, we will change a Product class and add a field, capitalName, which has no physical column in the product table, as shown in the following code:
Source file: Product.java
@Entity
@Table(name = "product")
public class Product {
@Id
@GeneratedValue
@Column(name = "id")
private long id;
@Column(name = "name")
private String name;
@Formula("UPPER(name)")
private String capitalName;
@Column(name = "price")
private double price;
@ManyToOne
@JoinColumn(name = "category_id")
private Category category;
// Getters and setters
}Now, we will run a code to show how it works:
Criteria criteria = session.createCriteria(Product.class);
List<Product> list = criteria.list();
for(Product product : list){
System.out.println("
Product name: " + product.getName());
System.out.println("Product capital name: " + product.getCapitalName());
}Hibernate: select this_.id as id0_1_, this_.category_id as category4_0_1_, this_.name as name0_1_, this_.price as price0_1_, UPPER(this_.name) as formula0_1_, category2_.id as id1_0_, category2_.created_on as created2_1_0_, category2_.name as name1_0_ from product this_ left outer join category category2_ on this_.category_id=category2_.id Product name: Meeting room table Product capital name: MEETING ROOM TABLE Product name: Metal bookcases Product capital name: METAL BOOKCASES Product name: Lighting Product capital name: LIGHTING Product name: Business envelopes Product capital name: BUSINESS ENVELOPES Product name: Paper clips Product capital name: PAPER CLIPS Product name: Highlighters Product capital name: HIGHLIGHTERS
Hibernate uses the phrase provided in @Formula directly in the SQL query. In the formula, we can use any SQL clause supported by the database.
From the output, we can easily understand that hibernate uses the string which is given in @Formula annotation in a similar way to UPPER(this_.name).
Here, we will take a smaller example to convert a product name to uppercase. Apart from this, we can use all the SQL clauses in the formula. Now, we will use a whole query in the formula.
For this, we will add one more field, named categoryName, in the product class to fetch the category name. Execute the following code:
Source file: Category.java
@Entity
@Table(name = "product")
public class Product {
@Formula("(SELECT c.name FROM category c WHERE c.id=category_id)")
private String categoryName;
// Other fields and getters/setters
}Criteria criteria = session.createCriteria(Product.class);
List<Product> list = criteria.list();
for(Product product : list){
System.out.println("
Product name: " + product.getName());
System.out.println("Category name: " + product.getCategoryName());
}Hibernate: select this_.id as id0_1_, this_.category_id as category4_0_1_, this_.name as name0_1_, this_.price as price0_1_, UPPER(this_.name) as formula0_1_, (SELECT c.name FROM category c WHERE c.id=this_.category_id) as formula1_1_, category2_.id as id1_0_, category2_.created_on as created2_1_0_, category2_.name as name1_0_ from product this_ left outer join category category2_ on this_.category_id=category2_.id Product name: Meeting room table Category name: Furniture Product name: Metal bookcases Category name: Furniture Product name: Lighting Category name: Furniture Product name: Business envelopes Category name: Stationary Product name: Paper clips Category name: Stationary Product name: Highlighters Category name: Stationary
From the output, we understand that hibernate will use our query provided in @Formula as a subquery.