Hibernate 使用错误的表名进行三级继承的 order by 表达式

在我们的项目中，我们有不同类别呈现的不同用户类型。 我们有一个 BaseEntity 类作为@MappedSuperclass。 当我们尝试将用户类与 InheritanceType.JOINED 一起使用时，hibernate 会创建一条我们认为错误的 sql。

基础实体：

@MappedSuperclass
public abstract class BaseEntity implements java.io.Serializable {


private Integer id;

private Date createdDate = new Date();


public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

@Temporal(TemporalType.TIMESTAMP)
@Column(name = "CREATED_DATE", nullable = true)
public Date getCreatedDate() {
    return createdDate;
}

public void setCreatedDate(Date createdDate) {
    this.createdDate = createdDate;
}

}

基础用户

@Entity
@Table(name = "BASE_USER")
@Inheritance(strategy = InheritanceType.JOINED)
@AttributeOverride(name = "id", column = @Column(name = "ID", nullable = false, insertable = false, updatable = false))
public abstract class BaseUser extends BaseEntity{


@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq")
@SequenceGenerator(name = "seq", sequenceName = "USER_SEQ", allocationSize = 1)
public Integer getId() {
    return super.getId();
}

}

User

@Entity
@Table(name = "FIRM_USER")
public class FirmUser extends BaseUser {

private String name;

@Column(name = "name")
public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

}

示例代码

public class HibernateUtil {

private static final SessionFactory sessionFactory;

static {
    try {
        sessionFactory = new AnnotationConfiguration()
                .addAnnotatedClass(FirmUser.class)
                .addAnnotatedClass(BaseUser.class)
                .addAnnotatedClass(BaseEntity.class)
                .configure()
                .buildSessionFactory();
    } catch (Throwable ex) {
        throw new ExceptionInInitializerError(ex);
    }
}

public static Session getSession() throws HibernateException {
    return sessionFactory.openSession();
}

public static void main(String[] args) {
    getSession().save(new FirmUser());

    Query query = getSession().createQuery("select distinct a from FirmUser a ORDER BY a.id");
    query.list();

}
}

对于这个hql

select distinct a from FirmUser a ORDER BY a.id

hibernate创建这个sql

select distinct firmuser0_.ID as ID1_0_,
       firmuser0_1_.CREATED_DATE as CREATED_2_0_,
       firmuser0_.name as name1_1_
from   FIRM_USER firmuser0_ 
       inner join BASE_USER firmuser0_1_ on firmuser0_.ID=firmuser0_1_.ID
order by firmuser0_1_.ID

“按firmuser0_1_.ID 排序”原因

 HSQLDB  : ORDER BY item should be in the SELECT DISTINCT list:

or

 ORACLE : ORA-01791: not a SELECTed expression

但是firmuser0_.ID位于select子句中，我们实际上尝试按FirmUser（firmuser0_）的ID而不是BaseUser（firmuser0_1_）的ID进行排序

如果我们不使用 BaseEntity，它会按预期工作。

为什么hibernate使用连接类来排序，如果它也继承自另一个类？

我复制了你的测试用例，你可以在GitHub https://github.com/vladmihalcea/hibernate-master-class/blob/master/core/src/test/java/com/vladmihalcea/hibernate/masterclass/laboratory/inheritance/InheritanceGroupByTest.java.

肯定存在 Hibernate bug，因为当使用别名时，select 子句使用子类 ID，而 ORDER BY 使用基类 id，由于它不在 select 子句中，因此会抛出异常：

SELECT inheritanc0_.id             AS ID1_0_,
       inheritanc0_1_.created_date AS CREATED_2_0_,
       inheritanc0_.NAME           AS name1_1_
FROM   firm_user inheritanc0_
       INNER JOIN base_user inheritanc0_1_
               ON inheritanc0_.id = inheritanc0_1_.id
ORDER  BY inheritanc0_1_.id 

注意ORDER BY inheritanc0_1_.id，本来应该是ORDER BY inheritanc0_.id反而。

解决方法1：

重写不带别名的查询：

List<FirmUser> result1 = (List<FirmUser>) session.createQuery("from FirmUser order by id").list(); 

正确生成的 SQL：

SELECT inheritanc0_.id             AS ID1_0_,
       inheritanc0_1_.created_date AS CREATED_2_0_,
       inheritanc0_.NAME           AS name1_1_
FROM   firm_user inheritanc0_
       INNER JOIN base_user inheritanc0_1_
               ON inheritanc0_.id = inheritanc0_1_.id
ORDER  BY inheritanc0_1_.id 

解决方法2：

或者也指定 subclass.id，但这会产生子类和子类实体元组的数组：

List<Object[]> result2 = (List<Object[]>) session.createQuery("select distinct a, a.id from FirmUser a order by id").list();

给出以下 SQL：

SELECT DISTINCT inheritanc0_1_.id           AS col_0_0_,
                inheritanc0_1_.id           AS col_1_0_,
                inheritanc0_.id             AS ID1_0_,
                inheritanc0_1_.created_date AS CREATED_2_0_,
                inheritanc0_.NAME           AS name1_1_
FROM   firm_user inheritanc0_
       INNER JOIN base_user inheritanc0_1_
               ON inheritanc0_.id = inheritanc0_1_.id
ORDER  BY inheritanc0_1_.id 

解决方法 3：

与往常一样，本机查询使您可以最终控制任何元组关联：

List<FirmUser> result3 = (List<FirmUser>) session.createSQLQuery(
        "select * " +
        "from FIRM_USER a " +
        "LEFT JOIN BASE_USER b ON a.id = b.id " +
        "order by a.id"
)
.addEntity("a", FirmUser.class)
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.list();

您应该针对此问题填写 Hibernate 问题，因为它的行为不正常。