Hibernate Tips: How to Exclude Deactivated Elements from an Association

---

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:

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:

• How to implement a soft delete with Hibernate
• Hibernate Tips: Permanently remove records when using a soft delete
• Best Practices for Many-To-One and One-To-Many Association Mappings
• Ultimate Guide – Association Mappings with JPA and Hibernate