This tutorial shows you how to use Hibernate's @HQL and @SQL annotations.
Hibernate added some annotations in version 6.3. Two of them are @HQL and @SQL. Those annotations can be used to simplify how to define queries. You just need to declare methods according to the convention and define the query using one of those two annotations. Then, Hibernate will generate the implementation of the methods. That's similar to Spring Data JPA's @Query annotation. Below are the usage examples.
Using @HQL Annotation
For example, there's an entity named Post as shown below.
@Table(name = "posts", uniqueConstraints = {
@UniqueConstraint(name = "uq_posts_slug", columnNames = {"slug"})
})
public class Post {
@Id
@UuidGenerator
private UUID id;
private String slug;
private String title;
private String content;
}There's a case to find a post by using its slug. With the annotation, you can have the query that you want by declaring a method in an abstract class or an interface without writing the implementation. In the example below we create an interface. Then, declare a method that's annotated with @HQL. To use the annotation, you have to pass an HQL query as the value. The query can optionally contain some parameters using :parameterName syntax. The parameters of the HQL query must match the parameters of the method. You can use any name for the method since there is no convention for it.
public interface PostRepositoryHql {
@HQL("from Post WHERE slug = :slug")
Post fetchPostBySlug(String slug);
// other methods
}It also supports queries that return a list of records.
public interface PostRepositoryHql {
@HQL("from Post WHERE title LIKE ?1")
List<Post> fetchPostsByTitleLike(String title);
// other methods
}Not only SELECT queries, you can write other queries supported by HQL such as UPDATE and DELETE.
public interface PostRepositoryHql {
@HQL("UPDATE Post p SET p.counter = p.counter + 1 WHERE slug = :slug")
int incrementPostCounterBySlug(String slug);
@HQL("DELETE FROM Post p WHERE p.slug = :slug")
int deletePostBySlug(String slug);
// other methods
}When the code is built, Hibernate generates a class file whose name is the interface or abstract class' name added with _ suffix. It contains the implementation of all methods annotated with @HQL. The generated methods are static. If you read the generated code, there should be EntityManager as the first parameter of the generated methods in addition to the parameters that you write.
@StaticMetamodel(PostRepositoryHql.class)
@Generated("org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
public abstract class PostRepositoryHql_ {
/**
* Execute the query {@value #DELETE_POST_BY_SLUG_String}.
*
* @see com.woolha.hibernate6.example.repository.PostRepositoryHql#deletePostBySlug(String)
**/
public static int deletePostBySlug(@Nonnull EntityManager entityManager, String slug) {
return entityManager.createQuery(DELETE_POST_BY_SLUG_String)
.setParameter("slug", slug)
.executeUpdate();
}
/**
* Execute the query {@value #FETCH_POST_BY_SLUG_String}.
*
* @see com.woolha.hibernate6.example.repository.PostRepositoryHql#fetchPostBySlug(String)
**/
public static Post fetchPostBySlug(@Nonnull EntityManager entityManager, String slug) {
return entityManager.createQuery(FETCH_POST_BY_SLUG_String, Post.class)
.setParameter("slug", slug)
.getSingleResult();
}
/**
* Execute the query {@value #INCREMENT_POST_COUNTER_BY_SLUG_String}.
*
* @see com.woolha.hibernate6.example.repository.PostRepositoryHql#incrementPostCounterBySlug(String)
**/
public static int incrementPostCounterBySlug(@Nonnull EntityManager entityManager, String slug) {
return entityManager.createQuery(INCREMENT_POST_COUNTER_BY_SLUG_String)
.setParameter("slug", slug)
.executeUpdate();
}
/**
* Execute the query {@value #FETCH_POSTS_BY_TITLE_LIKE_String}.
*
* @see com.woolha.hibernate6.example.repository.PostRepositoryHql#fetchPostsByTitleLike(String)
**/
public static List fetchPostsByTitleLike(@Nonnull EntityManager entityManager, String title) {
return entityManager.createQuery(FETCH_POSTS_BY_TITLE_LIKE_String, Post.class)
.setParameter(1, title)
.getResultList();
}
static final String DELETE_POST_BY_SLUG_String = "DELETE FROM Post p WHERE p.slug = :slug";
static final String FETCH_POST_BY_SLUG_String = "from Post WHERE slug = :slug";
static final String INCREMENT_POST_COUNTER_BY_SLUG_String = "UPDATE Post p SET p.counter = p.counter + 1 WHERE slug = :slug";
static final String FETCH_POSTS_BY_TITLE_LIKE_String = "from Post WHERE title LIKE ?1";
}As you can see on the generated class, Hibernate adds EntityManager as the first parameter of the generated methods. Therefore, you have to pass an EntityManager instance to call the generated methods.
@RequiredArgsConstructor
public class PostService {
private final EntityManager entityManager;
public PostDto fetchPostBySlug(String slug) {
Post post = PostRepositoryHql_.fetchPostBySlug(entityManager, slug);
return this.mapToPostDto(post);
}
public List<PostDto> fetchPostsByTitleLike(String title) {
List<Post> posts = PostRepositoryHql_.fetchPostsByTitleLike(entityManager, "%" + title + "%");
return posts.stream()
.map(this::mapToPostDto)
.collect(Collectors.toList());
}
@Transactional
public int incrementPostCounterBySlug(String slug) {
int result = PostRepositoryHql_.incrementPostCounterBySlug(entityManager, slug);
return result;
}
@Transactional
public int deletePostBySlug(String slug) {
int result = PostRepositoryHql_.deletePostBySlug(entityManager, slug);
return result;
}
private PostDto mapToPostDto(Post post) {
return PostDto.builder()
.id(post.getId())
.slug(post.getSlug())
.title(post.getTitle())
.content(post.getContent())
.build();
}
}Below is an invalid example where a parameter in the HQL query doesn't exist in the method.
public interface PostRepositoryHql {
@HQL("from Post WHERE slug = :s")
Post fetchPostBySlug(String slug);
}If you try to run the application, you'll get the following error.
error: missing method parameter for query parameter :s (add a parameter 'java.lang.String s' to 'fetchPostBySlug')
@HQL("from Post WHERE slug = :s")Below is another invalid example where a parameter of the method doesn't present in the HQL query.
public interface PostRepositoryHql {
@HQL("from Post WHERE slug = :slug")
Post fetchPostBySlug(String slug, String title);
}And here's the error.
error: missing query parameter for 'title' (no parameter named :title or ?2)
@HQL("from Post WHERE slug = :slug")Instead of creating an interface, you can also create an abstract class.
public abstract class PostRepositoryHqlAlt {
@HQL("from Post WHERE slug = :slug")
abstract Post fetchPostBySlug(String slug);
@HQL("from Post WHERE title LIKE ?1")
abstract List fetchPostsByTitleLike(String title);
@HQL("UPDATE Post p SET p.counter = p.counter + 1 WHERE slug = :slug")
abstract int incrementPostCounterBySlug(String slug);
@HQL("DELETE FROM Post p WHERE p.slug = :slug")
abstract int deletePostBySlug(String slug);
}If you want to use the same EntityManager for all of the generated methods in the class, you can declare a method without implementation that returns an EntityManager instance in the interface or abstract class.
public interface PostRepositoryHqlWithEntityManager {
@HQL("from Post WHERE slug = :slug")
Post fetchPostBySlug(String slug);
@HQL("from Post WHERE title LIKE ?1")
List<Post> fetchPostsByTitleLike(String title);
@HQL("UPDATE Post p SET p.counter = p.counter + 1 WHERE slug = :slug")
int incrementPostCounterBySlug(String slug);
@HQL("DELETE FROM Post p WHERE p.slug = :slug")
int deletePostBySlug(String slug);
EntityManager entityManager();
}The class generated by Hibernate will now have a constructor with one parameter whose type is EntityManager. Since you have to set the EntityManager by calling the constructor, the generated methods are not static.
@StaticMetamodel(PostRepositoryHqlWithEntityManager.class)
@Generated("org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
public class PostRepositoryHqlWithEntityManager_ implements PostRepositoryHqlWithEntityManager {
/**
* Execute the query {@value #DELETE_POST_BY_SLUG_String}.
*
* @see com.woolha.hibernate6.example.repository.PostRepositoryHqlWithEntityManager#deletePostBySlug(String)
**/
@Override
public int deletePostBySlug(String slug) {
return entityManager.createQuery(DELETE_POST_BY_SLUG_String)
.setParameter("slug", slug)
.executeUpdate();
}
private final @Nonnull EntityManager entityManager;
public PostRepositoryHqlWithEntityManager_(@Nonnull EntityManager entityManager) {
this.entityManager = entityManager;
}
public @Nonnull EntityManager entityManager() {
return entityManager;
}
/**
* Execute the query {@value #FETCH_POST_BY_SLUG_String}.
*
* @see com.woolha.hibernate6.example.repository.PostRepositoryHqlWithEntityManager#fetchPostBySlug(String)
**/
@Override
public Post fetchPostBySlug(String slug) {
return entityManager.createQuery(FETCH_POST_BY_SLUG_String, Post.class)
.setParameter("slug", slug)
.getSingleResult();
}
/**
* Execute the query {@value #INCREMENT_POST_COUNTER_BY_SLUG_String}.
*
* @see com.woolha.hibernate6.example.repository.PostRepositoryHqlWithEntityManager#incrementPostCounterBySlug(String)
**/
@Override
public int incrementPostCounterBySlug(String slug) {
return entityManager.createQuery(INCREMENT_POST_COUNTER_BY_SLUG_String)
.setParameter("slug", slug)
.executeUpdate();
}
/**
* Execute the query {@value #FETCH_POSTS_BY_TITLE_LIKE_String}.
*
* @see com.woolha.hibernate6.example.repository.PostRepositoryHqlWithEntityManager#fetchPostsByTitleLike(String)
**/
@Override
public List<Post> fetchPostsByTitleLike(String title) {
return entityManager.createQuery(FETCH_POSTS_BY_TITLE_LIKE_String, Post.class)
.setParameter(1, title)
.getResultList();
}
static final String DELETE_POST_BY_SLUG_String = "DELETE FROM Post p WHERE p.slug = :slug";
static final String FETCH_POST_BY_SLUG_String = "from Post WHERE slug = :slug";
static final String INCREMENT_POST_COUNTER_BY_SLUG_String = "UPDATE Post p SET p.counter = p.counter + 1 WHERE slug = :slug";
static final String FETCH_POSTS_BY_TITLE_LIKE_String = "from Post WHERE title LIKE ?1";
}Below is the example of how to create the repository instance by passing an EntityManager and how to call the generated methods.
public class PostService2 {
private final PostRepositoryHqlWithEntityManager_ postRepository;
public PostService2(EntityManager entityManager) {
this.postRepository = new PostRepositoryHqlWithEntityManager_(entityManager);
}
public PostDto fetchPostBySlug(String slug) {
Post post = this.postRepository.fetchPostBySlug(slug);
return this.mapToPostDto(post);
}
public List<PostDto> fetchPostsByTitleLike(String title) {
List<Post> posts = this.postRepository.fetchPostsByTitleLike("%" + title + "%");
return posts.stream()
.map(this::mapToPostDto)
.collect(Collectors.toList());
}
@Transactional
public int incrementPostCounterBySlug(String slug) {
int result = this.postRepository.incrementPostCounterBySlug(slug);
return result;
}
@Transactional
public int deletePostBySlug(String slug) {
int result = this.postRepository.deletePostBySlug(slug);
return result;
}
private PostDto mapToPostDto(Post post) {
return PostDto.builder()
.id(post.getId())
.slug(post.getSlug())
.title(post.getTitle())
.content(post.getContent())
.build();
}
}Using @SQL Annotation
The @SQL annotation has the similar functionality to the @HQL annotation.The difference is you have to write queries in the plain SQL format. How to use the @SQL annotation is very similar to the @HQL annotation. You need to create an interface or abstract class with several methods annotated by @SQL. Then, Hibernate will generate the implementation of the methods.
public interface PostRepositorySql {
@SQL("SELECT * FROM posts WHERE slug = :slug")
Post fetchPostBySlug(String slug);
@SQL("SELECT * FROM posts WHERE title LIKE ?1")
List<Post> fetchPostsByTitleLike(String title);
@SQL("UPDATE posts p SET counter = counter + 1 WHERE slug = :slug")
int incrementPostCounterBySlug(String slug);
@SQL("DELETE FROM posts p WHERE p.slug = :slug")
int deletePostBySlug(String slug);
}Below is the generated code.
@StaticMetamodel(PostRepositorySql.class)
public abstract class PostRepositorySql_ {
static final String DELETE_POST_BY_SLUG_String = "DELETE FROM posts p WHERE p.slug = :slug";
static final String FETCH_POST_BY_SLUG_String = "SELECT * FROM posts WHERE slug = :slug";
static final String INCREMENT_POST_COUNTER_BY_SLUG_String = "UPDATE posts p SET counter = counter + 1 WHERE slug = :slug";
static final String FETCH_POSTS_BY_TITLE_LIKE_String = "SELECT * FROM posts WHERE title LIKE ?1";
public PostRepositorySql_() {
}
public static int deletePostBySlug(@Nonnull EntityManager entityManager, String slug) {
return entityManager.createNativeQuery("DELETE FROM posts p WHERE p.slug = :slug").setParameter("slug", slug).executeUpdate();
}
public static Post fetchPostBySlug(@Nonnull EntityManager entityManager, String slug) {
return (Post)entityManager.createNativeQuery("SELECT * FROM posts WHERE slug = :slug", Post.class).setParameter("slug", slug).getSingleResult();
}
public static int incrementPostCounterBySlug(@Nonnull EntityManager entityManager, String slug) {
return entityManager.createNativeQuery("UPDATE posts p SET counter = counter + 1 WHERE slug = :slug").setParameter("slug", slug).executeUpdate();
}
public static List<Post> fetchPostsByTitleLike(@Nonnull EntityManager entityManager, String title) {
return entityManager.createNativeQuery("SELECT * FROM posts WHERE title LIKE ?1", Post.class).setParameter(1, title).getResultList();
}
}Like using the @SQL annotation, you can also use an abstract class instead. It's also possible to declare a method that returns the EntityManager in the interface or abstract class which causes the generated class to have a constructor with EntityManager as the parameter and the generated methods become non-static.
Summary
Hibernate's @HQL and @SQL can be used to define custom queries in HQL and SQL syntax respectively. You only need to declare methods without the implementation and add one of the annotations. The parameters of a method annotated with @HQL or @SQL must match the parameters of the annotation's query. If you do it correctly, Hibernate will generate the methods which can be called from your code.
You can also read about:
