Hibernate Java Criteria查询具有多个集合成员(如标记)的实例 [英] Hibernate java Criteria query for instances with multiple collection members like tag

查看：117
本文介绍了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，我将走得更远:/

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

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

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

}
}

      推荐答案

      视为与ID属性(而不是父级id属性)进行比较的字符串值，这会导致ClassCaseException.

      Restrictions.eq is for compare property with a value, Restrictions.propertyEq 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.

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

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

      这篇关于Hibernate Java Criteria查询具有多个集合成员(如标记)的实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
Java开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆