Hibernate - Using @Where and @WhereJoinTable Annotations Examples

This tutorial shows you how to use Hibernate's @Where and @WhereJoinTable annotations.

Hibernate has a lot of annotations. One of which is the @Where annotation. It can be used to specify a restriction for fetching entities or collections. There is also another annotation @WhereJoinTable which has a similar functionality. If you want to know how to use the annotations, you can read this tutorial for the usage examples.

Using @Where Annotation

@Where is an annotation that can be put in type (class), field, or method level. It allows you to specify a restriction that's generated while Hibernate needs to fetch an entity or collection. The restriction must be written in native SQL instead of the Hibernate Query Language. It must be passed as the clause parameter of the annotation.

Usage on Type/Class

For example, there is an entity named Category. It has a column named is_deleted whose type is boolean. When fetching categories from the database, we always want to return non-deleted categories (the value of is_deleted is false). As a solution, add a @Where annotation to the class and write is_deleted = false as the clause.

  @Entity
  @Table(name = "categories")
  @Where(clause = "is_deleted = false")
  public class Category {
  
    @Id
    @UuidGenerator(style = UuidGenerator.Style.TIME)
    private UUID id;
  
    private String name;
  
    private Boolean isDeleted;

    // Constructor, getters, and setters
  }

When fetching a category using the Hibernate Query Language (HQL), Hibernate will automatically add the where clause to the query.

  Optional<Category> categoryOptional = this.categoryRepository.findById(UUID.fromString("b81cdd09-6aee-483f-93de-4a5854ff7bdb"));

Below is the generated query.

  select c1_0.id,c1_0.is_deleted,c1_0.name from categories c1_0 where c1_0.id=? and (c1_0.is_deleted = false)
  binding parameter [1] as [UUID] - [b81cdd09-6aee-483f-93de-4a5854ff7bdb]

The same also applies for fetching a list of entities.

  List<Category> categories = this.categoryRepository.findAll();

Here is the generated query.

  select c1_0.id,c1_0.is_deleted,c1_0.name from categories c1_0 where (c1_0.is_deleted = false)

Usage on Field

For example, there is another entity named SubCategory. A category can have one or more sub categories.

  @Entity
  @Table(name = "sub_categories")
  public class SubCategory {
  
    @Id
    @UuidGenerator(style = UuidGenerator.Style.TIME)
    private UUID id;
  
    private String name;
  
    private Boolean isActive;
  }

In the Category class, we can define a one-to-many relationship with the SubCategory class. This enables us to fetch the related sub categories of a category using HQL. However, we only want to include active sub categories. To make Hibernate always add the criterias, add the @Where annotation to the field.

  @Table(name = "categories")
  public class Category {
  
    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "category_id", referencedColumnName="id")
    @Where(clause = "is_active = true")
    private List<SubCategory> subCategories;
  
    // Other fields, constructor, getters, and setters
  }

Then, write a Hibernate query that fetches the sub categories.

  @Query("SELECT c FROM Category c JOIN FETCH subCategories")
  List<Category> findAllWithSubCategories();

When generating the query, Hibernate will include the is_active = true criteria for the sub categories.

  select c1_0.id,c1_0.is_deleted,c1_0.name,s1_0.category_id,s1_0.id,s1_0.is_active,s1_0.name from categories c1_0 join sub_categories s1_0 on c1_0.id=s1_0.category_id and (s1_0.is_active = true)

Using @WhereJoinTable Annotation

There is another annotation @WhereJoinTable which has a similar usage except it's intended for a join table.

For example, there is another entity named Product.

  @Entity
  @Table(name = "products")
  public class Product {
  
    @Id
    @UuidGenerator(style = UuidGenerator.Style.TIME)
    private UUID id;
  
    private String name;
  
    private Boolean isShown;
  }

A category can have many products and a product can be included in multiple categories. In other words, the relationship is many-to-many. In this situation, we usually have a join table that stores the relationship between the two tables. If you want to add a where clause to the join table, you can use the @WhereJoinTable annotation.

  @Entity
  @Table(name = "categories")
  public class Category {
  
    @ManyToMany
    @JoinTable(name = "product_categories")
    @Where(clause = "is_shown = true")
    @WhereJoinTable(clause = "is_active = true")
    List<Product> products;
  }

Below is a Hibernate query for fetching categories along with the products.

  @Query("SELECT c FROM Category c JOIN FETCH products")
  List<Category> findAllWithProducts();

And below is the SQL query generated by Hibernate.

  select c1_0.id,c1_0.is_deleted,c1_0.name,p1_0.category_id,p1_1.id,p1_1.is_shown,p1_1.name from categories c1_0 join (product_categories p1_0 join products p1_1 on p1_1.id=p1_0.products_id) on c1_0.id=p1_0.category_id and (p1_0.is_active = true)  and ( p1_1.is_shown = true) and ( p1_1.is_shown = true)

Summary

In this tutorial, we have learned how to use Hibernate's @Where and @WhereJoinTable. They can be used to add default where clauses for queries generated by HQL. Keep in mind that the where clause will not be added automatically if you use a native query.

The files of this tutorial can be downloaded here.

You can also read about: