休眠使用错误的表名按表达式排序，具有三级继承

java orm hibernate hql

2022-09-02 14:04:38

在我们的项目中，我们有不同的类呈现的不同用户类型。我们有一个基实体类作为@MappedSuperclass。当我们尝试将用户类与 InheritType 一起使用时，JOINED 休眠会创建一个我们认为是错误的 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();
}

}

用户

@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

休眠创建此 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:

或

 ORACLE : ORA-01791: not a SELECTed expression

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

如果我们不使用基本实体，它按预期工作。

为什么休眠使用联接的类进行排序，以防它也继承自另一个类？

答案 1

我复制了你的测试用例，你可以在GitHub上找到它。

必须存在一个 Hibernate 错误，因为当您使用别名时，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_.idORDER 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();

您应该为此问题填写一个休眠问题，因为它的行为方式不是应该的。

答案 2