Hibernate - Using @SQLOrder Annotation Examples

This tutorial shows you how to use Hibernate's @SQLOrder annotation.

When fetching an entity using Hibernate, you may also want to fetch related collections defined in the entity. A collection itself can consist of more than one items. In some cases, the order of the items matters. As a result, it would be better if we can fetch the collection items in a desired order. For that purpose, you can use JPA's @OrderBy column. However, if you need to write the order expression using native SQL syntax, you can use Hibernate's @SQLOrder, which was introduced in Hibernate 6.3

Using @SQLOrder Annotation

For example, there is an entity named SubCategory.

  @Entity
  @Table(name = "sub_categories")
  public class SubCategory {
  
    @Id
    @UuidGenerator
    private UUID id;
  
    private String name;
  
    private int popularity;
  
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name ="category_id", nullable = false)
    private Category category;
  }

There is another entity named Category. A category can have multiple sub categories and a sub category must belong to a category. In other words, the relationship between category and sub category is one-to-many. When a category is fetched, we also want to fetch its sub categories.

  @Entity
  @Table(name = "categories")
  public class Category {
  
    @Id
    @UuidGenerator
    private UUID id;
  
    private String name;
  
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "category")
    private List<SubCategory> subCategories;
  }

There is a requirement that the fetched sub categories must be ordered by the value of the productsCount field. In the database, the column name is products_count. To fulfill the requirement, you can add @SQLOrder annotation to the collection field. On the annotation, you have to pass the order expression in native SQL format.

  @Entity
  @Table(name = "categories")
  public class Category {

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "category")
    @SQLOrder("products_count desc")
    private List<SubCategory> subCategories;
  
    // other fields
  }

Below is the generated query.

  select c1_0.id,c1_0.name,sc1_0.category_id,sc1_0.id,sc1_0.name,sc1_0.products_count
    from categories c1_0 join sub_categories sc1_0
    on c1_0.id=sc1_0.category_id
    order by sc1_0.products_count desc

The SQL syntax may depend on the database that you use. It's also allowed to use multiple columns (e.g. @SQLOrder("products_count, other_column") and SQL functions (.e.g. @SQLOrder("char_length(name)")).

You're not allowed to define both @SQLOrder and @OrderBy annotations. If you do that, you'll get the following error.

  Caused by: org.hibernate.AnnotationException: Collection 'com.woolha.hibernate6.example.model.Category.subCategories' is annotated both '@jakarta.persistence.OrderBy' and '@org.hibernate.annotations.OrderBy'

Summary

If you use Hibernate 6.3 or above, you can use the @SQLOrder annotation to define the order of a collection when it's fetched. It requires you to write the order expression in native SQL format which can be useful if you want to write syntax or functions not supported by HQL/JPQL. If possible, using the @OrderBy annotation is a more preferred way since you can write the expression in HQL/JPQL, which has a better compatibility with different database platforms.

You can also read about:

• How to use Hibernate's @HQL and @SQL annotations.
• How to use Hibernate's @Find annotation.
• How to use Hibernate's @SoftDelete annotation.