Hibernate order by column in detached tables

Question

I have 4 tables. For this question I name them tableA, tableB, tableC, tableD.

tableB looks something like that: id, name, label, some_other_parameters

tableC looks something like that: id, code, some_other_parameters

tableD columns: id, tableA_id, tableB_id, tableC_id, entityVersion

Columns in tableD don't have defined in entity one-to-many, many-to-one association. And it can't be changed. Doesn't matter why but it can't be changed. Definition of column looks like this: @Column(name - "T_tableA_ID") private Long tableAId;

Column definition looks the same for tableB and tableC.

Rows in tableD look like this when inserted:

tableA is not null, tableB is not null, tableC is null

or:

tableA is not null, tableB is null, tableC is not null

I want to get list from tableD in that order: tableB rows sorted by label || ' ' || name asc - (label can be the same for different rows , name is unique) and then tableC rows sorted by code asc

Is it even possible doing that in criteria?

Now I created view for tableD and list from view. When I update or insert rows in tableD I use entity for table. So I have two entities: view (has column display_name and I do order by this column) and table (for inserts and updates). But this solution is not perfect for me. I would prefer use criteria.

I need something like this in criteria:

select * from 
TABLE_D tab_d
where TABLE_A_ID = 1 --example id
order by TABLE_B_ID,
case when TABLE_B_ID is not null then
(select code from TABLE_B where id = tab_d.TABLE_B_ID) 
else
(select label || ' ' || name from TABLE_C where id = tab_d.TABLE_C_ID)
end

Another sql which returns sorted data the way I need:

select tab_d.* from
table_d tab_d
left join table_b tab_b on tab_b.id = tab_d.t_groups_id
left join table_c tab_c on tab_c.id = tab_d.t_users_id
where table_a_id = 10485
order by tab_d.t_groups_id, tab_b.code, tab_c.name || ' ' || tab_c.surname

Possible to create criteria to first or second sql statement?

Answer 1

You can extend org.hibernate.criterion.Order and provide your own implementation which generates SQL for the Order. See the code example

public class OrderBySqlFormula extends Order {
    private String sqlFormula;

    /**
     * Constructor for Order.
     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query
     */
    protected OrderBySqlFormula(String sqlFormula) {
        super(sqlFormula, true);
        this.sqlFormula = sqlFormula;
    }

    public String toString() {
        return sqlFormula;
    }

    public String toSqlString(Criteria criteria, CriteriaQuery criteriaQuery) throws HibernateException {
        return sqlFormula;
    }

    /**
     * Custom order
     *
     * @param sqlFormula an SQL formula that will be appended to the resulting SQL query
     * @return Order
     */
    public static Order sqlFormula(String sqlFormula) {
        return new OrderBySqlFormula(sqlFormula);
    }
}

and then just use your Order

criteria.addOrder(OrderBySqlFormula.sqlFormula("(a + b) desc"));