This tutorial shows you how to use Hibernate's @HQLSelect and @SQLSelect annotations.
In version 6.2, Hibernate added some new annotations. Two of them are @HQLSelect and @SQLSelect. They can be used to set the default query when an entity is fetched by ID. In this tutorial, I'm going to explain the usages of those two annotations.
Using @HQLSelect Annotation
@HQLSelect is an annotation for specifying a custom query to be used when Hibernate fetches an entity or collection by ID. The query must be written in HQL/JPQL syntax. This can be useful if you want to use a custom criteria. Conditions that use the custom query include:
- When using
Session.getorSession.findto fetch an entity. - When an association to an entity is fetched lazily.
- When a collection is fetched lazily
The query must have exactly one parameter whose type is jakarta.persistence.Id. You can include other fields as the criteria, but you cannot have any additional parameter. That makes sense because Hibernate will only pass one argument when running the query which is the ID. In addition, it must return a single value whose type matches the type of the entity or collection element.
Fetch Entity by ID
For example, there is an entity named Item. When fetched by ID, we only want to return the record if the value of isActive is true and the value of isHidden is false. The solution is to add an @HQLSelect annotation to specify the custom criteria.
@Entity
@Table(name = "items")
@HQLSelect(query = "SELECT i FROM Item i WHERE i.id = :id AND i.isActive = TRUE AND i.isHidden = FALSE")
public class Item {
@Id
@UuidGenerator
private UUID id;
private String name;
private Boolean isActive;
private Boolean isHidden;
private Category category;
}Below is the code that fetches the Item record by the ID.
@Service
public class ItemService {
private final EntityManager entityManager;
public ItemService(EntityManager entityManager) {
this.entityManager = entityManager;
}
public ItemDto getById(UUID id) {
Item item = this.entityManager.find(Item.class, id);
if (item == null) {
throw new RuntimeException("Not found");
}
ItemDto itemDto = ItemDto.builder()
.id(item.getId())
.name(item.getName())
.isActive(item.getIsActive())
.isHidden(item.getIsHidden())
.build();
return itemDto;
}
}As you can see from the output below, the query defined in the @HQLSelect annotation is used by Hibernate.
select i1_0.id,i1_0.category_id,i1_0.is_active,i1_0.is_hidden,i1_0.name from items i1_0 where i1_0.id=? and i1_0.is_active=true and i1_0.is_hidden=falseFetch Entity Association Lazily
There is another entity named Category which has a boolean field named isVisible. An Item belongs to a Category. When fetching an Item, we want to lazily fetch the Category. But we only want to return the Category if the isVisible value is true.
@Entity
@Table(name = "categories")
@HQLSelect(query = "SELECT c FROM Category c WHERE c.id = :id AND c.isVisible = TRUE")
public class Category {
@Id
@UuidGenerator
private UUID id;
private String name;
private Boolean isVisible;
}Then, define the relationship in the Item class.
public class Item {
// Other fields
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "category_id", nullable = false)
private Category category;
}Let's assume we have a variable named item which is a fetched Item record. When accessing the getCategory() method, Hibernate will fetch the Category record by its ID using the query defined in the @HQLSelect annotation.
Category category = item.getCategory();Below is the generated query.
select c1_0.id,c1_0.is_visible,c1_0.name from categories c1_0 where c1_0.id=? and c1_0.is_visible=trueFetch Entity Collection Lazily
We have another entity named Variant which has a boolean column named isAvailable. An Item can have multiple Variants. When fetching an Item, we want to fetch its Variants whose isAvailable value is true.
@Entity
@Table(name = "variants")
public class Variant {
@Id
@UuidGenerator
private UUID id;
private String name;
private Boolean isAvailable;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "item_id", nullable = false)
private Item item;
}Then, define the relationship in the Item class.
public class Item {
// Other fields
@OneToMany(fetch = FetchType.LAZY, mappedBy = "item")
@HQLSelect(query = "FROM Variant v WHERE v.id = :id AND v.isAvailable = TRUE")
private List<Variant> variants;
}Below is the generated query for fetching the variants.
select v1_0.id,v1_0.is_available,v1_0.item_id,v1_0.name from variants v1_0 where v1_0.id=? and v1_0.is_available=trueAnother thing that you need to know is the query cannot have multiple selections. Below is the error that you'll get if you define a query with multiple selections.
org.hibernate.query.IllegalQueryOperationException: Query defined multiple selections, return cannot be typed (other that Object[] or Tuple)Using @SQLSelect Annotation
There is another annotation @SQLSelect, which has a similar functionality. The main difference is the query must be written as a native query. Like the @HQLSelect annotation, it works for the following conditions.
- When using
Session.getorSession.findto fetch an entity. - When an association to an entity is fetched lazily.
- When a collection is fetched lazily
For the number of parameters, the query must have one for each column of
- primary key (for an entity)
- foreign key (for a collection)
Fetch Entity by ID
Below is the same criteria for fetching the Item by ID using the @SQLSelect annotation.
@Entity
@Table(name = "items")
@SQLSelect(sql = "SELECT * FROM items i WHERE i.id = :id AND i.is_active = TRUE AND i.is_hidden = FALSE")
public class Item {
}Fetch Entity Association Lazily
Below is the same criteria for fetching the Category of an Item using the @SQLSelect annotation.
@Entity
@Table(name = "categories")
@SQLSelect(sql = "SELECT * FROM categories c WHERE c.id = :id AND c.is_visible = TRUE")
public class Category {
@Id
@UuidGenerator
private UUID id;
private String name;
private Boolean isVisible;
}Fetch Entity Collection Lazily
Below is the same criteria for fetching the list of Variations of an Item with the @SQLSelect annotation.
public class Item {
// Other fields
@OneToMany(fetch = FetchType.LAZY, mappedBy = "item")
@SQLSelect(sql = "SELECT * FROM variants v WHERE v.id = :id AND v.is_available = TRUE")
private List<Variant> variants;
}Summary
The @HqlSelect and @SqlSelect annotations can be used to set the query to be used when an entity is fetched by ID, replacing the default query generated by Hibernate. You have to make sure that the custom query has the correct number of parameters. Keep in mind that the annotations only have effect on the conditions mentioned above. In addition, it doesn't have any effect on native queries.
You can also read about:
