Hibernate Tips: How To Map an Entity to a Query

---

Take your skills to the next level!

The Persistence Hub is the place to be for every Java developer. It gives you access to all my premium video courses, monthly Java Persistence News, monthly coding problems, and regular expert sessions.

Join the Persistence Hub!

---

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:

• Entities or DTOs – When should you use which projection?
• ResultSet Mapping: Constructor Result Mappings
• Hibernate Tips: How to map a view with Hibernate