Hibernate Tips: How To Map an Entity to a Query

By Thorben Janssen

Mapping

Hibernate Tips is a series of posts in which I describe a quick and easy solution for common Hibernate questions. If you have a question for a future Hibernate Tip, please post a comment below.

Question:

Does Hibernate allow me to map an entity to a query instead of a database table? If yes, how can I do that?

Solution:

Yes, Hibernate can map a read-only entity to an SQL query. But before we dive into the mapping, let’s first discuss if you should use it.

When should you use this mapping

Instead of mapping an entity to an SQL query, you could also:

  1. Execute a query and map the result to a DTO.
  2. Create a database view and map it to an entity.

Depending on your use case, both options offer certain benefits.

As I have shown in a previous article, DTO projections are faster than entity mappings, but they don’t support any managed associations to other entities. So, if you can execute additional queries to retrieve the required information, a query with a DTO projection is the better choice.

If you want to implement write operations, then a database view might be a better choice. You can easily map a view to an entity. Depending on your RDBMS and the query that defines the view, you can then use that entity to implement your write operations.

To sum it up, you should only map an entity to an SQL query, if you need to implement a read-only use case that forces you to traverse managed associations to other entities.

How to implement the mapping

You can use Hibernate’s @Subselect annotation to map an entity to an SQL query. In the following code snippet, I use this annotation to select the id, the title and the number of reviews of a Book and map them to the BookSummary entity.

Before you use this mapping, you need to be aware of two side effects:

  1. You can’t use this entity to perform any write operations. Hibernate would try to execute the operation on the SQL statement provided by the @Subselect annotation. You should, therefore, annotate the entity with @Immutable, use the field-based access strategy and omit all setter methods.
  2. Hibernate doesn’t know which database tables are used by the SQL statement configured in the @Subselect annotation. You can provide this information by annotating the entity with @Synchronize. That enables Hibernate to flush pending state transitions on the Book and Review entities before selecting a BookSummary entity.
@Entity
@Subselect(
	"SELECT b.id, b.title, count(r) as numreviews "
	+ "FROM Book b LEFT JOIN Review r ON b.id = r.book_id "
	+ "GROUP BY b.id, b.title")
@Synchronize({"book", "review"})
@Immutable
public class BookSummary {

	@Id
	private Long id;

	private String title;

	private int numReviews;

	@OneToMany(mappedBy = "book")
	private Set<Review> reviews;

	public String getTitle() {
		return title;
	}

	public int getNumReviews() {
		return numReviews;
	}

	public Set<Review> getReviews() {
		return reviews;
	}

	public Long getId() {
		return id;
	}
}

As you can see in the following log statements, instead of a database table name Hibernate now uses the provided SQL statement as a subselect in the FROM clause.

07:50:43,136 DEBUG [org.hibernate.SQL] - 
    select
        booksummar0_.id as id1_0_0_,
        booksummar0_.numReviews as numRevie2_0_0_,
        booksummar0_.title as title3_0_0_ 
    from
        ( SELECT
            b.id,
            b.title,
            count(r) as numreviews 
        FROM
            Book b 
        LEFT JOIN
            Review r 
                ON b.id = r.book_id 
        GROUP BY
            b.id,
            b.title ) booksummar0_ 
    where
        booksummar0_.id=?

Learn more:

If you want to learn more about handling and mapping of query results, you might enjoy reading the following articles:

 

Hibernate Tips Book







Get more recipes like this one in my new book Hibernate Tips: More than 70 solutions to common Hibernate problems.

It gives you more than 70 ready-to-use recipes for topics like basic and advanced mappings, logging, Java 8 support, caching, and statically and dynamically defined queries.

Get it now!



Tags

Mapping


About the author

Thorben is an independent consultant, international speaker, and trainer specialized in solving Java persistence problems with JPA and Hibernate.
He is also the author of Amazon’s bestselling book Hibernate Tips - More than 70 solutions to common Hibernate problems.

Leave a Reply

Your email address will not be published. Required fields are marked

This site uses Akismet to reduce spam. Learn how your comment data is processed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}