Hibernate公式找不到列名

Question

有一点公式的乐趣，这让我发疯。我们有一些代码通过公式获取实体，数据库中没有链接，需要在运行时完成，因此@formula的用法。下面是课堂的样子：

@Entity
@Table(name="T_EMPLOYEE_COMPANY_ASSIGNMENT"
)

public class EmployeeCompanyAssignment  implements java.io.Serializable {



public EmployeeCompanyAssignment() {
}

@EmbeddedId
@AttributeOverrides( {
    @AttributeOverride(name="employeeId", column=@Column(name="EMPLOYEE_ID", nullable=false, precision=10, scale=0) ), 
    @AttributeOverride(name="companyId", column=@Column(name="COMPANY_ID", nullable=false, precision=10, scale=0) ) } )
public EmployeeCompanyAssignmentId getId() {
    return this.id;
}
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="EMPLOYEE_ID", nullable=false, insertable=false, updatable=false)
public Employee getEmployee() {
    return this.employee;
}

public void setEmployee(Employee employee) {
    this.employee = employee;
}
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="COMPANY_ID", nullable=false, insertable=false, updatable=false)
public Company getCompany() {
    return this.company;
}

public void setCompany(Company company){
    this.company = company;
}
@Column(name="DEFAULT_WBS_SAP_CODE", length=24)
public String getDefaultWbsSapCode() {
    return this.defaultWbsSapCode;
}

public void setDefaultWbsSapCode(String defaultWbsSapCode){
    this.defaultWbsSapCode = defaultWbsSapCode;
}
@Column(name="DEFAULT_COST_CENTER_SAP_CODE", length=10)
public String getDefaultCostCenterSapCode() {
    return this.defaultCostCenterSapCode;
}

public void setDefaultCostCenterSapCode(String defaultCostCenterSapCode){
    this.defaultCostCenterSapCode = defaultCostCenterSapCode;
}
....
}

公式是这样建立的：

@Formula(value = "(SELECT * FROM(" +
        "Select *" +
            " from T_COST_CENTER cc" +
            " where cc.is_blocked_in_sap = 0" +
                " and cc.is_deleted_in_sap = 0" +
                " and cc.company_id in" +
                    " (SELECT TECA.COMPANY_ID" +
                        " FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA" +
                        " WHERE TECA.EMPLOYEE_ID = EMPLOYEE_ID)" +
        " and LTRIM(" +
                "(SELECT *" +
                 " FROM (select TECA.DEFAULT_COST_CENTER_SAP_CODE" +
                         " from T_EMPLOYEE_COMPANY_ASSIGNMENT TECA" +
                        " WHERE TECA.EMPLOYEE_ID = EMPLOYEE_ID)" +
                " where ROWNUM = 1)" +
            ", '0') = LTRIM(cc.sap_code, '0')" +
        " order by cc.sap_code DESC" +
        ")" +
        " WHERE ROWNUM = 1" +
        ")")
public CostCenter getDefaultCostCenter() {
    return defaultCostCenter;
}
public void setDefaultCostCenter(CostCenter defaultCostCenter ) {
    this.defaultCostCenter = defaultCostCenter ;
}

@Formula(value="(" +
        "SELECT *" +
            " FROM (Select wbs.*" +
                " from T_WORK_BREAKDOWN_STRUCTURE wbs" +
                " LEFT OUTER JOIN T_Company c" +
                    " on wbs.company_id = c.id" +
                " where wbs.sap_code in (select TECA.DEFAULT_WBS_SAP_CODE" + //We shouldn't have to do this this, but whenever I try to call the field DEFAULT_WBS_SAP_CODE direct in the formula, it complains it can't find it
                                        " from T_EMPLOYEE_COMPANY_ASSIGNMENT TECA" +
                                        " where TECA.EMPLOYEE_ID = EMPLOYEE_ID)" +
                    " and wbs.is_blocked_in_sap = 0" +
                    " and wbs.is_deleted_in_sap = 0" +
                " order by wbs.sap_code DESC)" +
        " WHERE ROWNUM = 1"+
        ")")
public WorkBreakdownStructure getDefaultWbs() {
    return defaultWbs;
}
public void setDefaultWbs(WorkBreakdownStructure  defaultWbs) {
    this.defaultWbs = defaultWbs;
}

现在，我最初直接使用列DEFAULT\u COST\u CENTER\u SAP\u CODE和DEFAULT\u WBS\u SAP\u CODE，而不使用内部select，但是在这两列上都得到了错误的无效标识符，所以我想我应该尝试使用id来引用，但是这也会产生无效标识符。以下是hibernate生成的SQL代码：

2016-02-29 09:07:49,811TRACEorg.hibernate.type.descriptor.sql.BasicBinder.bind（BasicBinder.java:83）[rw]-绑定参数[1]as[BIGINT]-28250

2016-02-29 09:07:49,811TRACEorg.hibernate.type.descriptor.sql.BasicBinder.bind（BasicBinder.java:83）[rw]-绑定参数[1]as[BIGINT]-28250

2016-02-29 09:07:49,811TRACEorg.hibernate.type.descriptor.sql.BasicBinder.bind（BasicBinder.java:83）[rw]-绑定参数[1]as[BIGINT]-28250

2016-02-29 09:07:49817警告组织。冬眠发动机jdbc。spi。SqlExceptionHelper。logExceptions（SqlExceptionHelper.java:143）[rw]-SQL错误：904，SQLState:42000

2016-02-29 09:07:49817警告组织。冬眠发动机jdbc。spi。SqlExceptionHelper。logExceptions（SqlExceptionHelper.java:143）[rw]-SQL错误：904，SQLState:42000

2016-02-29 09:07:49817错误组织。冬眠发动机jdbc。spi。SqlExceptionHelper。logExceptions（SqlExceptionHelper.java:144）[rw]-ORA-00904:“EMPLOYEECO0”。“员工ID”：无效标识符

2016-02-29 09:07:49817错误组织。冬眠发动机jdbc。spi。SqlExceptionHelper。logExceptions（SqlExceptionHelper.java:144）[rw]-ORA-00904:“EMPLOYEECO0”。“员工ID”：无效标识符

有人能看出问题来自何方吗？这对我来说似乎很奇怪。。。

这是在Oracle SQL环境中使用IntelliJ完成的。如果没有这些公式，这个类可以很好地工作

编辑

我已经更新了一个函数的公式，并一直在测试更多，看看我能做些什么，我已经得到了它，所以以下工作：

@OneToOne
@JoinColumnsOrFormulas({
        @JoinColumnOrFormula(formula=@JoinFormula("(SELECT * FROM (" +
                "SELECT WBS.ID FROM T_WORK_BREAKDOWN_STRUCTURE WBS" +
                " LEFT OUTER JOIN T_COMPANY c" +
                " ON WBS.COMPANY_ID = C.ID" +
                " WHERE"+
                " WBS.IS_BLOCKED_IN_SAP = 0" +
                " AND WBS.IS_DELETED_IN_SAP = 0" +
                " ORDER BY WBS.SAP_CODE DESC)" +
                "WHERE ROWNUM=1)"))
})
public WorkBreakdownStructure getDefaultWbs() {
    return defaultWbs;
}

但是，一旦我将这一行添加到where子句中：

WBS.SAP_CODE IN (SELECT TECA.DEFAULT_WBS_SAP_CODE FROM T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = EMPLOYEE_ID)

或者这个：

WBS.SAP_CODE = DEFAULT_WBS_SAP_CODE

由于标识符无效（分别在员工ID和默认WBS\U SAP\U代码上），它失败，但这些字段在为其填充的属性生成的SQL代码中使用。直接测试数据库中生成的SQL是有效的。生成的工作SQL代码如下所示：

select
    employeeco0_.COMPANY_ID as COMPANY1_34_,
    employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_,
    employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_,
    employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_,
    (SELECT
        * 
    FROM
        (SELECT
            WBS.ID 
        FROM
            T_WORK_BREAKDOWN_STRUCTURE WBS 
        LEFT OUTER JOIN
            T_COMPANY c 
                ON WBS.COMPANY_ID = C.ID 
        WHERE
            WBS.IS_BLOCKED_IN_SAP = 0 
            AND WBS.IS_DELETED_IN_SAP = 0 
        ORDER BY
            WBS.SAP_CODE DESC)
    WHERE
        ROWNUM=1
    ) as formula9_ 
from
    T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_

失败的是这样的：

select
    employeeco0_.COMPANY_ID as COMPANY1_34_,
    employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_,
    employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_,
    employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_,
    (SELECT
        * 
    FROM
        (SELECT
            WBS.ID 
        FROM
            T_WORK_BREAKDOWN_STRUCTURE WBS 
        LEFT OUTER JOIN
            T_COMPANY c 
                ON WBS.COMPANY_ID = C.ID 
        WHERE
            WBS.SAP_CODE IN (
                SELECT
                    TECA.DEFAULT_WBS_SAP_CODE 
                FROM
                    T_EMPLOYEE_COMPANY_ASSIGNMENT TECA 
                WHERE
                    TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID
            ) 
            AND WBS.IS_BLOCKED_IN_SAP = 0 
            AND WBS.IS_DELETED_IN_SAP = 0 
        ORDER BY
            WBS.SAP_CODE DESC)
        WHERE
            ROWNUM=1) as formula9_ 
    from
        T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_

或者它看起来像这样：

select
    employeeco0_.COMPANY_ID as COMPANY1_34_,
    employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_,
    employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_,
    employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_,
    (SELECT
        * 
    FROM
        (SELECT
            WBS.ID 
        FROM
            T_WORK_BREAKDOWN_STRUCTURE WBS 
        LEFT OUTER JOIN
            T_COMPANY c 
                ON WBS.COMPANY_ID = C.ID 
        WHERE
            WBS.SAP_CODE = employeeco0_.DEFAULT_WBS_SAP_CODE 
            AND WBS.IS_BLOCKED_IN_SAP = 0 
            AND WBS.IS_DELETED_IN_SAP = 0 
        ORDER BY
            WBS.SAP_CODE DESC)
    WHERE
        ROWNUM=1
    ) as formula9_ 
from
    T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_

然而，当在数据库中完成时（当您替换employeeco0_。EMPLOYEE_ID或DEFAULT_WBS_SAP_CODE有效ID）。希望这次更新有点帮助

Answer 1

该问题是由于OracleSQL不能引用内部查询外部查询中声明的列引起的。我必须重新思考和重做查询，这样我就不需要引用内部查询中的字段DEFAULT_WBS_SAP_CODE或EMPLOYEE_ID

Answer 2

在使用公式和Hibernate之前，我会尝试直接在数据库中运行查询，以了解查询的哪一部分是错误的。

看看你的@公式，我只看到“选择*”：你不能不使用公式来返回像@ManyToOne一样完全托管的实体。

您必须使用@公式与字符串或Interger。@公式中的选择必须具有单个列

此外，公式是通过实体获取的，由于查询的复杂性，它可能会严重影响性能，并且在90%的时间内它不会被使用。也许在你的情况下，不使用公式是值得的