CODEWITHSUNDEEP
javasqloracle-databasehibernate
Draken
Draken

Reputation: 3189

Hibernate formula not finding column names

having a bit of fun with formulas and this is driving me crazy. We have a bit of code to get the entity via a formula, there is no link in the database and it needs to be done at runtime, hence the @Formula usage. Here is how the class looks:

@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;
}
....
}

And the formulas are set up this way:

@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;
}

Now originally I was using the columns DEFAULT_COST_CENTER_SAP_CODE and DEFAULT_WBS_SAP_CODE directly without the internal select, but was getting the error invalid identifier on both of those columns, so I thought I'd try and use the IDs instead to reference, however that also comes up with invalid identifier. Here is the SQL code that hibernate produces:

2016-02-29 09:07:49,810 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger.logStatement(SqlStatementLogger.java:104) [rw] - select employeeco0_.EMPLOYEE_ID as EMPLOYEE2_33_1_, employeeco0_.COMPANY_ID as COMPANY1_34_1_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_1_, employeeco0_.COMPANY_ID as COMPANY1_34_0_, employeeco0_.EMPLOYEE_ID as EMPLOYEE2_34_0_, employeeco0_.DEFAULT_COST_CENTER_SAP_CODE as DEFAULT7_34_0_, employeeco0_.DEFAULT_WBS_SAP_CODE as DEFAULT8_34_0_, (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 = employeeco0_.EMPLOYEE_ID) and LTRIM((SELECT * FROM (select TECA.DEFAULT_COST_CENTER_SAP_CODE from T_EMPLOYEE_COMPANY_ASSIGNMENT TECA WHERE TECA.EMPLOYEE_ID = employeeco0_.EMPLOYEE_ID) where ROWNUM = 1), '0') = LTRIM(cc.sap_code, '0') order by cc.sap_code DESC) WHERE ROWNUM = 1) as formula2_0_, (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 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 formula3_0_ from T_EMPLOYEE_COMPANY_ASSIGNMENT employeeco0_ where ( employeeco0_.COMPANY_ID in (select c.id from T_COMPANY c where c.is_deleted_in_sap =0)) and employeeco0_.EMPLOYEE_ID=? order by employeeco0_.COMPANY_ID

2016-02-29 09:07:49,811 TRACE org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:83) [rw] - binding parameter [1] as [BIGINT] - 28250

2016-02-29 09:07:49,811 TRACE org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:83) [rw] - binding parameter [1] as [BIGINT] - 28250

2016-02-29 09:07:49,811 TRACE org.hibernate.type.descriptor.sql.BasicBinder.bind(BasicBinder.java:83) [rw] - binding parameter [1] as [BIGINT] - 28250

2016-02-29 09:07:49,817 WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:143) [rw] - SQL Error: 904, SQLState: 42000

2016-02-29 09:07:49,817 WARN org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:143) [rw] - SQL Error: 904, SQLState: 42000

2016-02-29 09:07:49,817 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:144) [rw] - ORA-00904: "EMPLOYEECO0_"."EMPLOYEE_ID": invalid identifier

2016-02-29 09:07:49,817 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions(SqlExceptionHelper.java:144) [rw] - ORA-00904: "EMPLOYEECO0_"."EMPLOYEE_ID": invalid identifier

Can anyone see where the issue is coming from? It all seems rather peculiar to me...

This is being done on an Oracle SQL enviroment and using IntelliJ. Without the formulas, this class works fine

EDIT

I've updated the formula for one function and been testing a little more to see what I can do, I've got it so the following works:

@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;
}

However, once I add this line to the where clause:

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

Or this:

WBS.SAP_CODE = DEFAULT_WBS_SAP_CODE

It fails with the invalid identifier (On the EMPLOYEE_ID and DEFAULT_WBS_SAP_CODE respectively), yet these fields are used in the SQL code that it generates for the properties that it fills in. Testing the SQL generated in the database directly works. The generated working SQL code looks like this:

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_

And the one that fails looks like this:

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_

Or it looks like this:

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_

Yet that works when done in the database (When you replace employeeco0_.EMPLOYEE_ID or DEFAULT_WBS_SAP_CODE with a valid ID). Hope this update helps a bit

Upvotes: 0

Views: 2232

Answers (2)

Draken
Draken

Reputation: 3189

The issue was being caused by the fact Oracle SQL can't reference a column declared in the outer query on the internal query. I have to rethink and redo the query so that I don't need to reference the field DEFAULT_WBS_SAP_CODE or EMPLOYEE_ID inside the internal query

Upvotes: 0

jpprade
jpprade

Reputation: 3664

Before using Formula and hibernate, I would try to run the query directly in the database, to understand what part of the query is wrong.

Looking at your @Formula, I just see the "select *" : you can't not use formula to return fully managed Entity like with @ManyToOne.

You must use @Formula with String or Interger. The Select in the @Formula must have a single column

Moreover formula are fetched with the entity, it can impact the performance badly because of the complexity of the query and in 90% of the time it won't be used. Maybe in your case it might be worth it to not use Formula

Upvotes: 1

Related Questions