Hibernate Tips: How to Exclude Deactivated Elements from an Association

By Thorben Janssen

Association Mapping, 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:

This Hibernate Tip was inspired by a question I answered on Stack Overflow. The user had to map a legacy database that stored an active_from and an active_to date for each record. When he mapped any association between these records, he only wanted to include the records that are currently active.

Solution:

Quite a lot of systems that need to keep historical data or that have to schedule future updates use an active_from and an active_to date to activate or deactivate a database record. You can easily do that with JPA as long as you always use your own queries to fetch your entities.

If you want to limit the elements of an association to the currently activate ones, you need to use Hibernate’s proprietary @Where annotation. It allows you to provide an SQL snippet which will be added to the SQL statement that initializes the association.

Let’s take a look at an example.

An online bookstore keeps a history of the prices of all books and schedules price updates for the future. You can model that with a Book and a BookPrice entity.

The BookPrice entity

There’s nothing special about the mapping of the BookPrice entity. The id attribute models a generated primary key, and the price attribute persists the price of the book. The active_from and active_to attributes define when the record is active and used by the business logic. I modeled them as attributes of type LocalDate. And the book attribute defines the owning side of a many-to-one association.

@Entity
public class BookPrice {

	@Id
	@GeneratedValue
	private Long id;
	
	private BigDecimal price;
	
	private LocalDate active_from;
	
	private LocalDate active_to;
	
	@ManyToOne
	private Book book;

	...
	
}

The Book entity

The mapping of the Book entity is more interesting. Similar to the BookPrice entity, it maps the primary key to the id attribute and tells Hibernate to generate unique primary key values. The title and isbn attributes store additional information about the book. The isbn is also declared as a natural id, which can be used to load the entity via Hibernate’s Session interface.

The most interesting part of this mapping is the price attribute. It models the referencing side of the association to the BookPrice entity. Normally, this List would contain all past, the current and all future prices of the Book. You can limit it to the currently active price with Hibernate’s @Where annotation and a small SQL snippet that compares the current date and time with the values of the active_from and active_to columns.

@Entity
public class Book {

	@Id
	@GeneratedValue
	private Long id;

	private String title;

	@NaturalId
	private String isbn;
	
	@OneToMany(mappedBy = "book")
	@Where(clause = "active_from <= now() AND active_to > now()")
	private List<BookPrice> price;

	...
	
}

When you use this mapping, Hibernate adds the SQL snippet provided by the @Where annotation to all queries that initializes the price attribute of the Book entity.

18:53:20,498 DEBUG [org.hibernate.SQL] - 
    select
        book0_.id as id1_0_0_,
        book0_.isbn as isbn2_0_0_,
        book0_.title as title3_0_0_ 
    from
        Book book0_ 
    where
        book0_.id=?
18:53:20,597 DEBUG [org.hibernate.SQL] - 
    select
        price0_.book_id as book_id5_1_0_,
        price0_.id as id1_1_0_,
        price0_.id as id1_1_1_,
        price0_.active_from as active_f2_1_1_,
        price0_.active_to as active_t3_1_1_,
        price0_.book_id as book_id5_1_1_,
        price0_.price as price4_1_1_ 
    from
        BookPrice price0_ 
    where
        (
            price0_.active_from <= now() AND price0_.active_to > now()
        ) 
        and price0_.book_id=?

Learn more:

If you want to learn more about association mappings and Hibernate’s @Where annotation, you might enjoy the following article:

 

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 as a paperback, ebook or PDF.

Tags

Association Mapping, 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.

Books and Courses

Coaching and Consulting

Leave a Repl​​​​​y

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.

  1. In the where annotation is there any way by which the query can be used as db independent, since it uses native SQL which is db specific.

    1. No, you can only provide a native SQL snippet. So, you need to make sure that it works on all databases you intend to use …

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