76570

Hibernate java Criteria query for instances with multiple collection members like tag

Question:

Please help me write a Java Criteria-object query to find all items with collections that have all desired members. Basically, I need to "and" the condition, not "or" it. This is exactly like SO articles and tags: search for articles with tags "java" and "hibernate", the result should only have articles tagged with both tags (more tags are ok). Like this one :)

My entity is called "Solution" and it has a collection of tag entities mapped via a two-column mapping table. I understand from research below that I need a DetachedQuery. The critical routine (see search service impl below) runs but returns no results in the test case.

Research so far - if I knew how to translate HQL to Criteria better I would be farther ahead :/

<ol><li> Exact same question as mine, @Firo offers Criteria code but it has a slight problem: <a href="https://stackoverflow.com/questions/8931950/hibernate-criteria-to-match-against-all-child-collection" rel="nofollow">Hibernate Criteria to match against all child collection</a> </li><li> HQL code only: <a href="https://stackoverflow.com/questions/18426077/matching-all-items-in-a-list-with-hibernate-criteria" rel="nofollow">Matching *ALL* items in a list with Hibernate criteria</a> </li><li> HQL code only: <a href="https://stackoverflow.com/questions/12784960/hibernate-select-entities-where-collection-contains-all-of-the-specified-valus" rel="nofollow">Hibernate: Select entities where collection contains all of the specified valus</a> </li><li> Discussion and HQL: <a href="https://vladmihalcea.com/sql-query-parent-rows-all-children-match-filtering-criteria" rel="nofollow">https://vladmihalcea.com/sql-query-parent-rows-all-children-match-filtering-criteria</a> </li><li> Nice writeup of HQL: <a href="http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate" rel="nofollow">http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate</a> </li></ol>

<strong>EDIT</strong> Thanks to @samabcde I corrected the query method to use Restrictions.eqProperty, no more class cast exception.

By turning on debug logging I can see this generated SQL (long because of the eager fetch policy). It doesn't look right, especially the "this_.ID=this_.ID" part - this is trivially true.

select this_.ID as ID1_39_1_, this_.NAME as NAME2_39_1_, tags2_.SOL_ID as SOL_ID1_38_3_, tag3_.ID as TAG_ID2_38_3_, tag3_.ID as ID1_40_0_, tag3_.NAME as NAME2_40_0_ from SOLUTION this_ left outer join SOL_TAG_MAP tags2_ on this_.ID=tags2_.SOL_ID left outer join TAG tag3_ on tags2_.TAG_ID=tag3_.ID where ? = (select count(t1_.NAME) as y0_ from SOLUTION this_ inner join SOL_TAG_MAP tags3_ on this_.ID=tags3_.SOL_ID inner join TAG t1_ on tags3_.TAG_ID=t1_.ID where this_.ID=this_.ID and t1_.NAME in (?, ?))

And I don't get the expected answer - the query result in the test case (see below) is empty, where I expect it to find 1 row.

Sorry for the length, files shown for completeness altho I skipped the import statements for brevity. I'm probably doing something stupid that an expert can point out instantly, thanks in advance.

<strong>Entity Solution</strong>

@Entity @Table(name = "SOLUTION") public class Solution implements Serializable { private static final long serialVersionUID = 745945642089325612L; @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "ID", nullable = false, updatable = false, columnDefinition = "INT") private Long id; @Column(name = "NAME", nullable = false, columnDefinition = "VARCHAR(100)") private String name; // Fetch eagerly to make serialization easy @ManyToMany(fetch = FetchType.EAGER) @JoinTable(name = SolTagMap.TABLE_NAME, // joinColumns = { @JoinColumn(name = SolTagMap.SOL_ID_COL_NAME) }, // inverseJoinColumns = { @JoinColumn(name = SolTagMap.TAG_ID_COL_NAME) }) private Set<Tag> tags = new HashSet<>(0); public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } public Set<Tag> getTags() { return tags; } public void setTags(Set<Tag> tags) { this.tags = tags; } @Override public String toString() { return this.getClass().getName() + "[id=" + getId() + ", name=" + getName() + ", tags=" + getTags() + "]"; } }

<strong>Entity Map table</strong>

@Entity @IdClass(SolTagMapKey.class) @Table(name = SolTagMap.TABLE_NAME) public class SolTagMap implements Serializable { // Define constants so names can be reused in many-many annotation. /* package */ static final String TABLE_NAME = "SOL_TAG_MAP"; /* package */ static final String SOL_ID_COL_NAME = "SOL_ID"; /* package */ static final String TAG_ID_COL_NAME = "TAG_ID"; private static final long serialVersionUID = -7814665924253912856L; @Embeddable public static class SolTagMapKey implements Serializable { private static final long serialVersionUID = -503957020456645384L; private Long solId; private Long tagId; @Override public boolean equals(Object that) { if (that == null) return false; if (!(that instanceof SolTagMapKey)) return false; SolTagMapKey thatPK = (SolTagMapKey) that; return Objects.equals(solId, thatPK.solId) && Objects.equals(tagId, thatPK.tagId); } @Override public int hashCode() { return Objects.hash(solId, tagId); } @Override public String toString() { return this.getClass().getName() + "[solId=" + solId + ", tagId=" + tagId + "]"; } } @Id @Column(name = SolTagMap.SOL_ID_COL_NAME, nullable = false, updatable = false, columnDefinition = "INT") private Long solId; @Id @Column(name = SolTagMap.TAG_ID_COL_NAME, nullable = false, updatable = false, columnDefinition = "INT") private Long tagId; public Long getSolId() { return solId; } public void setSolId(Long solId) { this.solId = solId; } public Long getTagId() { return tagId; } public void setTagId(Long tagId) { this.tagId = tagId; } @Override public boolean equals(Object that) { if (that == null) return false; if (!(that instanceof SolTagMap)) return false; SolTagMap thatObj = (SolTagMap) that; return Objects.equals(solId, thatObj.solId) && Objects.equals(tagId, thatObj.tagId); } @Override public int hashCode() { return Objects.hash(solId, tagId); } @Override public String toString() { return this.getClass().getName() + "[solId=" + solId + ", tagId=" + tagId + "]"; } }

<strong>Entity Tag</strong>

@Entity @Table(name = "TAG") public class Tag implements Serializable { private static final long serialVersionUID = -288462280366502647L; @Id @GeneratedValue(strategy = GenerationType.AUTO) @Column(name = "ID", nullable = false, updatable = false, columnDefinition = "INT") private Long id; @Column(name = "NAME", nullable = false, columnDefinition = "VARCHAR(100)") private String name; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public String getName() { return name; } public void setName(String name) { this.name = name; } @Override public boolean equals(Object that) { if (that == null) return false; if (!(that instanceof Tag)) return false; Tag thatObj = (Tag) that; return Objects.equals(id, thatObj.id); } @Override public int hashCode() { return Objects.hash(id, name); } @Override public String toString() { return this.getClass().getName() + "[id=" + id + ", name=" + name + "]"; } }

<strong>Search Service implementation</strong>

@Service("simpleSolutionSearchService") @Transactional public class SimpleSolutionSearchServiceImpl implements SimpleSolutionSearchService { @Autowired private SessionFactory sessionFactory; // This works fine public List<Solution> findSolutions() { Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Solution.class); // Hibernate should coalesce the results, yielding only solutions criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); return criteria.list(); } // This throws public List<Solution> findSolutionsWithTags(String[] requiredTags) { final String parentAlias = "sol"; final String collFieldAlias = "t"; final String tagValueField = collFieldAlias + ".name"; DetachedCriteria subquery = DetachedCriteria.forClass(Solution.class) .add(Restrictions.eqProperty("id", parentAlias + ".id")) .createAlias("tags", collFieldAlias) // .add(Restrictions.in(tagValueField, requiredTags)) // .setProjection(Projections.count(tagValueField)); Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Solution.class, parentAlias) .add(Subqueries.eq((long) requiredTags.length, subquery)); criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); return criteria.list(); } }

<strong>Solution repository</strong>

public interface SimpleSolutionRepository extends CrudRepository<Solution, Long> { }

<strong>Tag repository</strong>

public interface SimpleTagRepository extends CrudRepository<Tag, Long> { }

<strong>Test case</strong>

@RunWith(SpringRunner.class) @SpringBootTest public class SolutionServiceTest { private static final Logger logger = LoggerFactory.getLogger(MethodHandles.lookup().lookupClass()); @Autowired private SimpleSolutionRepository solutionRepository; @Autowired private SimpleTagRepository tagRepository; @Autowired private SimpleSolutionSearchService searchService; @Test public void testRepositories() throws Exception { final String tagName1 = "tag name 1"; final String tagName2 = "tag name 2"; Tag t1 = new Tag(); t1.setName(tagName1); t1 = tagRepository.save(t1); Assert.assertNotNull(t1.getId()); logger.info("Created tag {}", t1); Tag t2 = new Tag(); t2.setName(tagName2); t2 = tagRepository.save(t2); Assert.assertNotNull(t2.getId()); logger.info("Created tag {}", t2); Solution s1 = new Solution(); s1.setName("solution one tag"); s1.getTags().add(t1); s1 = solutionRepository.save(s1); Assert.assertNotNull(s1.getId()); logger.info("Created solution {}", s1); Solution s2 = new Solution(); s2.setName("solution two tags"); s2.getTags().add(t1); s2.getTags().add(t2); s2 = solutionRepository.save(s2); Assert.assertNotNull(s2.getId()); logger.info("Created solution {}", s1); List<Solution> sols = searchService.findSolutions(); Assert.assertTrue(sols.size() == 2); for (Solution s : sols) logger.info("Found solution {}", s); String[] searchTags = { tagName1, tagName2 }; List<Solution> taggedSols = searchService.findSolutionsWithTags(searchTags); // EXPECT ONE OBJECT BUT GET ZERO Assert.assertTrue(taggedSols.size() == 1); } }

Answer1:

<a href="https://docs.jboss.org/hibernate/orm/5.2/javadocs/org/hibernate/criterion/Restrictions.html#eq-java.lang.String-java.lang.Object-" rel="nofollow">Restrictions.eq</a> is for compare property with a value, <a href="https://docs.jboss.org/hibernate/orm/5.2/javadocs/org/hibernate/criterion/Restrictions.html#eqProperty-java.lang.String-java.lang.String-" rel="nofollow">Restrictions.propertyEq</a> compare property with another property. Hence the code consider parentAlias + ".id" as a String value to compare with the ID property, instead of parent id property and this cause ClassCaseException.

For problem of no record found,where this_.ID=this_.ID tells the reason. Hibernate considers the id property in the subquery to reference the parent query Solution, instead of the subquery Solution. Alias should be provided to the subquery to distinguish the id property in this case.

public List<Solution> findSolutionsWithTags(String[] requiredTags) { final String parentAlias = "sol"; final String childAlias = "subSol"; final String collFieldAlias = "t"; final String tagValueField = collFieldAlias + ".name"; DetachedCriteria subquery = DetachedCriteria.forClass(Solution.class, childAlias) // Throws ClassCastException; apparently sol.id isn't replaced with an ID value? // Problem should be due to following line //.add(Restrictions.eq("id", parentAlias + ".id")) // Use eqProperty instead .add(Restrictions.eqProperty(childAlias + ".id", parentAlias + ".id")) .createAlias("tags", collFieldAlias) // .add(Restrictions.in(tagValueField, requiredTags)) // .setProjection(Projections.count(tagValueField)); Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Solution.class, parentAlias) .add(Subqueries.eq((long) requiredTags.length, subquery)); criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY); return criteria.list(); }

Recommend

  • Show comma in html only if the variable has value using jquery?
  • Returning an xml as string, but when I print it, all start tags are ignored PHP
  • multiple and dynamic audio players applied for a dictionary in HTML/JavaScript
  • Convert Xml with a lot of levels to one DataTable
  • Properly align columns for rows that end early VBA/Excel
  • How can I hide extra divs with an ellipsis?
  • How to read CDATA from an XML file in Node Sax
  • Can I use a third party Javascript library in GWT
  • Target the XNA framework in ordinary Visual Studio 2012 project
  • How to debug using superdev mode?
  • Inline R code in YAML for rmarkdown doesn't run
  • Problem while Building a Setup Project for a windows Service?
  • Alternative To body {overflow:scroll;} That Will Prevent Page Jostling/Wriggling?
  • How can I sort a a table with VBA with given text condition?
  • With Hadoop, can I create a tasktracker on a machine that isn't running a datanode?
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Listbox within Listbox and scrolling trouble in Windows Phone 7 Silverlight
  • Disable Enter in editText android
  • How to use an array of arrays with array_map(…) in PHP?
  • All Classes Conforming to Protocol Inherit Default Implementation
  • Is there a javascript serializer for JSON.Net?
  • Javascript simulate pressing enter in input box
  • Read text file and split every line in MSBuild
  • Fetching methods from BroadcastReceiver to update UI
  • Excel - Autoshape get it's name from cell (value)
  • Check if a string to interpolate provides expected placeholders
  • How to check if every primary key value is being referenced as foreign key in another table
  • RestKit - RKRequestDelegate does not exist
  • Traverse Array and Display in markup
  • python regex in pyparsing