加入标准很好......但如果我想添加一个条件？ [英] Criteria with join is fine... but if I want to add a condition?

问题描述

我有以下架构：

I've the following schema:

TABLE EMPLOYEE           TABLE COUNTRY
---------------         ---------------
id                 |------> id
name               |        country_name
country_id   ------|        country_code

如果我想检索属于某个国家/地区的所有雇员，我使用如下标准：

If I want to retrieve all the employees belonging to a country I use a criteria like this:

Criteria crit =  getSession().createCriteria(Employee.class);
crit.add(Restrictions.eq("country.id", countryId));
List<Employee> employees = crit.list();

我的员工实体通过这种方式引用国家：

My employee entity refers to country in this way:

@Entity
public class Employee {
    private static final long serialVersionUID = 1L;

    @Id
    private Integer id;

    @ManyToOne
    @JoinColumn(name="country_id")
    private Country country;

    // Other stuff here 
}

精细！

问题是如果我不知道countryId，但我有country_code（例如UK）。我应该如何更改上面的标准，以便我可以加入表格并在国家代码上添加限制？

The problem is if I don't know the countryId, but I have the country_code instead(i.g. "UK"). How should I change the criteria above so that I can join the tables and add the restriction on the country code?

推荐答案

     Criteria criteria =  getSession().createCriteria(Employee.class,"e");
     criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
     criteria.createAlias("e.country", "c");        
     Criterion codeId= Restrictions.eq("c.id", yourCountryId);
     Criterion codeCriterion= Restrictions.eq("c.countryCode", yourCountryCode);
     criteria.add(Restrictions.or(codeId, codeCriterion));

     List<Employee> employees = criteria.list();

或

OR

    Criteria criteria =  getSession().createCriteria(Employee.class);
    Criterion codeId = Restrictions.eq("country.id", yourCountryId);
    Criterion codeCriterion = Restrictions.eq("country.countryCode", yourCountryCode);
    criteria.add(Restrictions.or(codeId, codeCriterion));

    List<Employee> employees = crit.list()

这篇关于加入标准很好......但如果我想添加一个条件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！