Request sleep criteria for a collection table?

Question

I have the following Entity

@Entity @Table(name = "rule") public class Rule implements Serializable { @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "rule_id") private Long id; @ElementCollection(targetClass = Action.class) @CollectionTable(name = "rule_action", joinColumns = @JoinColumn(name = "rule_id")) @Enumerated(value = EnumType.STRING) @Column(name = "action") private Set<Action> actions; //After editing as per jbrookover suggestion adding a new mapping @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY) @JoinColumn(name = "rule_id") private Set<RuleAction> ruleActions; } 

My next action

 public enum Action { PHONE, EMAIL, POSTAL,PHONE_OR_EMAIL, SMS; } 

I want to get a list of rules with a specific set of actions I'm trying this

  DetachedCriteria criteria = DetachedCriteria.forClass(Rule.class,"rule"); criteria = criteria.createAlias("rule.actions", "action"); criteria.add(Restrictions.in("action.name",actionSet)); return getHibernateTemplate().findByCriteria(criteria); 

But getting org.hibernate.MappingException: collection was not an association: exception ..

EDIT Therefore, after the tutorial from jbrookover, I tried to switch to the wrapper class for Action named RuleAction and was able to establish the oneToMany relationship. I also changed the query as follows

  Set<Action> act = new HashSet<Action>(); act.add(Action.EMAIL); act.add(Action.POSTAL); DetachedCriteria criteria = DetachedCriteria.forClass(Rule.class); criteria.add(Restrictions.eq(SUPPORT_LANG, Language.valueOf("EN"))) .createCriteria("ruleActions").add(Restrictions.in("action",act)); return getHibernateTemplate().findByCriteria(criteria); 

But that returns me the whole rule that has EMAIL or POSTAL, but I want the whole rule with EMAIL and POSTAL and . Help me change the request.

Answer 1

Sorry what you are trying to do, in particular, is not supported in Hibernate. See Frequently Asked Questions:

http://community.jboss.org/wiki/HibernateFAQ-AdvancedProblems#Im_getting_orghibernateMappingException_collection_was_not_an_association_when_I_try_to_join_a_collection_of_components_with_Criteria_queries

I was also very unhappy with this. However, as you can see, they tried to fix it, failed to do it, and put it in the community to handle it. You have several options:

• Use HQL to run the query.
• Rewrite the collection association as an actual entity class with a single Enum field.

You can do something like this:

  @Entity public class ActionWrapper { public Action action; } 

Then update your associations and request the Rule have Set<ActionWrapper> . There are other workarounds, but you essentially cannot use Criteria and @ElementCollection together.

Update

To limit the query further, to make sure that you get rules that match BOTH actions, you need to run a subquery and perform a connection of the 'and' of the agreed values. Something like this should work:

  Criteria subCriteria = criteria.createCriteria("ruleActions"); Disjunction and = Restrictions.conjunction(); for (Action a : act) and.add(Restrictions.eq("action", a) subCriteria.add(and); 

At the end you can find duplicate results. This is a common occurrence and can be eliminated by adding the following:

  criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); 

I can’t talk about the effectiveness of this code - HQL may be better in the long run. However, I did something similar in other projects and did not encounter any problems.

Answer 2

Another solution without wrapping your listings and using ResultTransformers is the following:

  @ElementCollection(fetch = FetchType.EAGER) @Enumerated(value = EnumType.STRING) @Column(name = " action") @JoinTable(name = "rule_action", joinColumns = { @JoinColumn(name = "rule_id") }) private Set<Action> actions; 

Query:

 DetachedCriteria criteria = DetachedCriteria.forClass(Rule.class,"rule"); criteria = criteria.createAlias("rule.actions", "action"); criteria.add(Restrictions.in("action." + CollectionPropertyNames.COLLECTION_ELEMENTS, actionSet)); return getHibernateTemplate().findByCriteria(criteria); 

What worked for me with Hibernate 4.1.6.Final. I found it here .

Answer 3

Requesting criteria looks great. Suppose we have tables:

 rule(id, name,...) action(id, name,..) rule_action(id, rule_id, action_id,...) -- (or rule_id+action_id as a composite pkey) 

The display should be as follows:

 public class Rule { ... @ManyToMany(mappedBy = "rule") private Set<Action> actions; ... } public class Action { ... @JoinTable( name="rule_action", joinColumns={@JoinColumn(name="action_id")}, inverseJoinColumns={@JoinColumn(name="rule_id")} ) private Set<Rule> rules; ... } 

This way your request will work.