Hibernate标准查询:域对象设置的问题

Question

我有一张表，它模仿下面的列结构。

ID，Harbor_Name，Country，Country_Code，State，State_Code，City

我有一个实体港口如下。

public class Harbor{
   private Long Id;
   private String name;
   private Address address;
   -- getters and setters ---
}

Public class Address{
  private Country country;
  private State state;
  private String city;
  -- getters and setters ---
}

Public class Country{
  private String name;
  private String code;
  -- getters and setters ---
}

Public class State{
  private String name;
  private String code;
  -- getters and setters ---
}

我想从桌子上得到一个国家的名字。我尝试了下面的条件对象，并且遇到了如下所示的SQL异常。

    public Set<State> getStatesByCountry(String countryName) {
    Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(Harbor.class, "harbor");
        criteria.createAlias("harbor.address", "a");
        criteria.createAlias("a.country", "c");
        criteria.createAlias("a.state", "s");
        criteria.setProjection(Projections.distinct(Projections.property("s.name")));
        criteria.add(Restrictions.eq("c.name", countryName).ignoreCase());
        criteria.add(Restrictions.isNotNull("s.name"));
        states.addAll(criteria.list());
}

SQL生成和异常：

select distinct state3_.Harbor_Name as y0_ from HARBOR_DATA this_ where lower(country2_.Harbor_Name)=? and state3_.Harbor_Name is not null

java.sql.SQLSyntaxErrorException: ORA-00904: "STATE3_"."Harbor_Name": invalid identifier

<class name="sample.package.Harbor" table="Harbor_data" mutable="false">
        <id name="Id" column="ID"></id>
        <property name="name" column="Harbor_Name" />
        <component name="address" class="sample.domain.Address">
            <property name="city" column="City" />
            <component name="state" class="sample.domain.State">
                <property name="code" column="State_Code" />
                <property name="name" column="State" />
            </component>
            <component name="country" class="sample.domain.Country">
                <property name="name" column="Country" />
                <property name="code" column="Country_Code" />
            </component>
        </component>
    </class>

Answer 1

这对我有用。

public Set<State> getStatesByCountry(String countryName) {
    Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(Harbor.class, "harbor");
    criteria.setProjection(Projections.distinct(Projections.property("harbor.address.state.name")));
        criteria.add(Restrictions.eq("harbor.address.country.name", countryName).ignoreCase());
        criteria.add(Restrictions.isNotNull("harbor.address.state.name"));
        states.addAll(criteria.list());
}

Answer 2

尝试像这样修改您的标准：

Criteria criteria = this.sessionFactory.getCurrentSession().createCriteria(Harbor.class, "harbor");
        criteria.createAlias("harbor.address", "a");
        criteria.createAlias("a.country", "c");
        criteria.createAlias("a.state", "s");

        //note I've changed projection property to harbor name
        criteria.setProjection(Projections.distinct(Projections.property("harbor.name")));
        criteria.add(Restrictions.eq("c.name", countryName).ignoreCase());
        criteria.add(Restrictions.isNotNull("s.name"));
        states.addAll(criteria.list());
}