How to use Hibernate's @Filter annotation in an @EmbeddedId property?


#1

tl;dr

Can’t make Hibernate filter work with an embedded id property.

Sample project to reproduce the issue here

Some Introduction

Hey there. I’m new to this forum so if I did something wrong I’ll fix it as soon as possible if you guys point out.

I’m creating this question after I tried to fix the problem with help from this post on stackoverflow (many thanks to @Andronicus there).

There is a sample project to reproduce the issue. Link in the first section (can’t put more links in this post)

The following section contains my question copied from stackoverflow.

Actual question

I’m struggling with this query for quite a while.

Suppose the following entity mapping example:

@Entity
class Client {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "client_id")
    @Basic(optional = false)
    private Integer id;

    @Basic(optional = false)
    private String name;

    @OneToMany(mappedBy = "client")
    private List<CarRent> rentHistory;

    // ... getters and setters
}

@Entity
class Car {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "car_id")
    @Basic(optional = false)
    private Integer id;

    @Basic(optional = false)
    private String foo;

    // ... getters and setters
}

@Entity
class CarRent {
    @EmbeddedId
    private CarRentKey carRentKey;

    @MapsId("clientId")
    @ManyToOne()
    @JoinColumn(name = "client_id", nullable = false, insertable = false, updatable = false)
    private Client client;

    @MapsId("carId")
    @ManyToOne()
    @JoinColumn(name = "car_id",  nullable = false, insertable = false, updatable = false)
    private Car car;

    @Basic(optional = false)
    private String bar;

    // ... getters and setters
}

@Embeddable
class CarRentKey {
    private int clientId;
    private int carId;
    @Column(name = "date_due")
    private Date dateDue;

    // ... getters and setters
}

I need to fetch all Clients with theis rentHistory populated with CarRents from a certain date. The following query would work perfectly for me:

from Client cl
    left outer join fetch c.rentHistory as rent with rent.car = c and rent.dateDue = :date

But Hibernate keeps telling me to use a filter when fetching a join in the exception.

I tryed

@Entity
@FilterDef(name="dateDueFilter", parameters= {
    @ParamDef( name="dateDue", type="date" ),
})
@Filters( {
    @Filter(name="dateDueFilter", condition="dateDue = :dateDue"),
})
class CarRent {
    // ...
}

but then when I run my query like:

EntityManager em;
// ...
Session hibernateSession = em.unwrap(Session.class);
hibernateSession.enableFilter("dateDueFilter").setParameter("dateDue", dateDue);
em.createQuery("from Client cl"
    + "left outer join fetch c.rentHistory");
List<Client> clientList = q.getResultList();
// clientList contains CarRent of all dates

The filter is just ignored. Same result with condition="carRentKey.dateDue = :dateDue" and condition="date_due = :dateDue" .

I use filters for other left outer joins on the same query and they work just fine. But this one relation that evolves an embedded parameter I can’t find a way to make it work.

Is it possible? Are there alternatives?

PS: filtering in the where section e.g. from Client cl left outer join fetch c.rentHistory as rent where rent.dateDue is null or rent.dateDue = :date is not an option since my real query has other joins which results get filtered and gets really slow when I do so.


#2

First of all, you can filter a JOIN FETCH collection:

from Client cl
left outer join fetch c.rentHistory as rent with rent.car = c and rent.dateDue = :date

You could use a query like this one:

select cl, cr
from Client cl
join CarRent cr on cr.client = cl and cr.dateDue = :date

Check out this article for more details about joining unrelated entities.

The @Filter annotation is just for filtering a collection in a Parent-Child association, not for expressing LEFT JOIN queries with dynamic joining filtering criteria.


#3

Thank you for the fast response.

Check out this article for more details about joining unrelated entities.

I looked at the article you sent me but I can’t see how to solve the problem yet. It hinted me to upgrade hibernate version for now.

We are on hibernate 4.3.11 for this project but I upgraded my example to 5.4.1 to run some tests.

select cl, cr
from Client cl
join CarRent cr on cr.client = cl and cr.dateDue = :date

On hibernate 5.4.1 I can run this one actually:

select cl, cr
from Client cl
join CarRent cr on cr.client = cl and cr.carRentKey.dateDue = :date

But it’s not quite what I expected.

My original query returned a list of clients filled with the list of rents on that date or an empty list if none.

The alternative would be to query for a Tuple, like in your article, and manually build the structure my method is expected to return.

Is it the only way to do it?


#4

You can always transform a tuple or table-based result set to a parent-child hierarchy. Check out this article for more details.


#5

Wow. Thank you. Didn’t know that was possible.

I think I can adapt my query to join these tables without fetch and then apply a transformer to build my object tree.

That would be much better than relying on a filter indeed.

Will give it a try.

If it works as expected my problem for this specific query would be solved.

But there’s one last thing I’d like to know: is it possible to apply a filter to an embedded property? What would be the correct way to do it?


#6

The @Filter takes a SQL condition, so you can apply it to any DB column you want even if that’s mapped to an Enum property.


#7

Great. Tested it and I think that the strange behaviour I observed was because of misuse of the @Filter annotation as you said.

select distinct(cl)
    from Client cl
    left outer join fetch cl.rentHistory

Is not filtered but the following query is:

from CarRent cr

I’m still working on my new query as proposed but I believe this problem is solved.

Thank you very much for your help.


#8

The @Filter works when fetching entities directly or navigating them, not for queries.


#9

For the sake of completeness the solution I used to solve the problem follows:

org.hibernate.query.Query<Client> q = em.createQuery("select cl, cr " + 
		"from Client cl " + 
		"join CarRent cr on cr.client = cl and cr.carRentKey.dateDue = :date")
		.setParameter("date", dateDue)
		.unwrap(org.hibernate.query.Query.class).setResultTransformer(new BasicTransformerAdapter() {
			@Override
			public List transformList(List list) {
				Map<Serializable, Client> clientMap =
			            new LinkedHashMap<>(list.size());

		        for (Object entityArray: list) {
		            if (Object[].class.isAssignableFrom(entityArray.getClass())) {
		                Client client = null;
		                CarRent carRent = null;
		 
		                Object[] tuples = (Object[]) entityArray;
		                
		                for (Object tuple : tuples) {
	                        em.detach(tuple);
	 
	                        if (tuple instanceof Client) {
	                        	client = (Client) tuple;
	                        }
	                        else if (tuple instanceof CarRent) {
	                        	carRent = (CarRent) tuple;
	                        }
	                        else {
	                            throw new UnsupportedOperationException(
	                                "Tuple " + tuple.getClass() + " is not supported!"
	                            );
	                        }
		                }
		 
		                if (client != null) {
		                    if (!clientMap.containsKey(client.getId())) {
		                    	clientMap.put(client.getId(), client);
		                    	client.setRentHistory(new ArrayList<>());;
		                    }
		                    if (carRent != null) {
		                    	client.getRentHistory().add(carRent);
		                    }
		                }
		            }
		        }
		        return new ArrayList<>(clientMap.values());
			}
		});

List<Client> clientList = q.getResultList();