Hibernate Java Criteria查询具有多个集合成员(如标记)的实例

本文介绍了Hibernate Java Criteria查询具有多个集合成员(如标记)的实例的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

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 :)

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

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.

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

与我的问题完全相同，@ Firo提供了Criteria代码，但有一个小问题:仅限
HQL代码:将* ALL *个项目匹配到一个列出具有休眠条件的仅限
HQL代码:休眠:选择集合包含所有指定值的实体
讨论和HQL: https://vladmihalcea .com/sql-query-parent-rows-all-children-match-filtering-criteria

HQL的不错的写法: http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate

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

Exact same question as mine, @Firo offers Criteria code but it has a slight problem: Hibernate Criteria to match against all child collection
HQL code only: Matching *ALL* items in a list with Hibernate criteria
HQL code only: Hibernate: Select entities where collection contains all of the specified valus
Discussion and HQL: https://vladmihalcea.com/sql-query-parent-rows-all-children-match-filtering-criteria

Nice writeup of HQL: http://www.sergiy.ca/how-to-write-many-to-many-search-queries-in-mysql-and-hibernate

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

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

通过打开调试日志记录，我可以看到此生成的SQL(很久以来就因为急切的提取策略).看起来不正确，尤其是"this_.ID = this_.ID"部分-这确实很正确.

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

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

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.

很抱歉，显示的文件出于完整性考虑，为了简洁起见，我跳过了import语句.我可能正在做一些愚蠢的事情，专家可以立即指出，谢谢.

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.

实体解决方案

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

}
}

compare

Hibernate Java Criteria查询具有多个集合成员(如标记)的实例

问题描述

推荐答案