java - Hibernate java Criteria 查询具有多个集合成员(如标签)的实例

Question

请帮助我编写一个 Java Criteria-object 查询，以查找所有包含包含所有所需成员的集合的项目。基本上，我需要“和”条件，而不是“或”它。这与 SO 文章和标签完全一样:搜索带有“java”和“hibernate”标签的文章，结果应该只有带有这两个标签的文章(更多标签也可以)。就像这个:)

我的实体称为“解决方案”，它有一组通过两列映射表映射的标记实体。我从下面的研究中了解到我需要一个 DetachedQuery。关键例程(请参阅下面的搜索服务实现)运行但在测试用例中未返回任何结果。

到目前为止的研究——如果我知道如何更好地将 HQL 转换为 Criteria，我会走得更远:/

1. 与我的问题完全相同，@Firo 提供了 Criteria 代码，但它有一个小问题:Hibernate Criteria to match against all child collection
2. 仅 HQL 代码:Matching *ALL* items in a list with Hibernate criteria
3. 仅 HQL 代码:Hibernate: Select entities where collection contains all of the specified valus
4. 讨论和 HQL:https://vladmihalcea.com/sql-query-parent-rows-all-children-match-filtering-criteria
5. HQL 的好文章:http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate

编辑 感谢@samabcde，我更正了查询方法以使用 Restrictions.eqProperty，不再有类转换异常。

通过打开调试日志记录，我可以看到这个生成的 SQL(由于急切获取策略，所以很长)。它看起来不对，尤其是“this_.ID=this_.ID”部分 - 这是微不足道的事实。

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 (?, ?))         

而且我没有得到预期的答案 - 测试用例中的查询结果(见下文)是空的，我希望它能找到 1 行。

抱歉，为了完整起见，文件的长度很长，但为了简洁起见，我跳过了导入语句。我可能正在做一些专家可以立即指出的愚蠢行为，在此先感谢。

实体解决方案

@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() + "]";
}

}

实体映射表

@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 + "]";
}

}

实体标签

@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 + "]";
}

}

搜索服务实现

@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();
}

}

解决方案库

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

标签库

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

测试用例

@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);

}
}

Answer 1

Restrictions.eq用于将属性与值进行比较，Restrictions.propertyEq将属性(property)与另一属性(property)进行比较。因此，代码将 parentAlias + ".id" 视为字符串值以与 ID 属性而不是父 id 属性进行比较，这会导致 ClassCaseException。

对于找不到记录的问题，where this_.ID=this_.ID说明原因。 Hibernate 认为子查询中的 id 属性引用父查询 Solution，而不是子查询 Solution。在这种情况下，应向子查询提供别名以区分 id 属性。

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();
}