zwwcn

Just another WordPress.com site

Hibernate one to many filtering

Recently I need to do an hiberate one to many filtering, but it’s not as easy as I thought.

Firstly, hibernate criteria doesn’t allow you join the same association twice, which mean you can’t have code like this:


finalCriteria.createAlias("a.b","b1", JoinType.LEFT_OUTER_JOIN)

.createAlias("a.b","b2", JoinType.LEFT_OUTER_JOIN);

Two workarounds are mentioned on https://hibernate.atlassian.net/browse/HHH-879, I tried use detachedCriteria but got the same error.

The second solution works for me:


Criterion  criterion  = Restrictions.sqlRestriction(" ((exists (select tc.OBJECT_ID from OP_TICKET_CATEGORY_OPERATOR tc where tc.TICKET_CATEGORY_ID = this_.OBJECT_ID AND tc.OPERATOR_ID ="+selectedResolver.getObjID()+" AND tc.OPERATOR_TYPE='RE')) AND (exists (select tc.OBJECT_ID from OP_TICKET_CATEGORY_OPERATOR tc where tc.TICKET_CATEGORY_ID = this_.OBJECT_ID AND tc.OPERATOR_ID ="+selectedOpener.getObjID()+" AND tc.OPERATOR_TYPE='OP')))" );
finalCriteria.add(criterion);

 

Entities:


OP_TICKET_CATEGORY

@OneToMany(mappedBy="ticketCategory",fetch=FetchType.LAZY,orphanRemoval=true)
private List<TicketCategoryOperator> ticketCategoryOperators;

------------------------------------------------

OP_TICKET_CATEGORY_OPERATOR

@ManyToOne
@JoinColumn(name = "TICKET_CATEGORY_ID",nullable = true)
private TicketCategory ticketCategory;

@ManyToOne
@JoinColumn(name = "OPERATOR_ID",nullable = true)
private Operator operator;

TicketCategoryOperatorType type = TicketCategoryOperatorType.OPENER;

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: