休眠:@Formula查询字段使用另一个@Formula字段 [英] Hibernate: @Formula query field using another @Formula field

问题描述

我希望字段idEmpresa在WHERE语句中使用idDepartamento(这是另一个@Formula字段)成为@Formula字段.

I want field idEmpresa to be a @Formula field using idDepartamento (that is another @Formula field) in WHERE statement.

@Entity
public class CfgUsuario {

    //More fields
    private String idDepartamento;
    private String idEmpresa;


    @Formula("(SELECT ud.IdDepartamento FROM UsuarioDpto ud WHERE ud.PorDefecto = 'S' AND ud.IdUsuario = idUsuario)")
    public String getIdDepartamento() {
        return idDepartamento;
    }

    public void setIdDepartamento(String idDepartamento) {
        this.idDepartamento = idDepartamento;
    }

    @Formula("(SELECT d.IdEmpresa FROM Departamento d WHERE d.IdDepartamento = idDepartamento)")
    public String getIdEmpresa() {
        return idEmpresa;
    }

    public void setIdEmpresa(String idEmpresa) {
        this.idEmpresa = idEmpresa;
    }

}

但是我得到了

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException:列名"idDepartamento"无效.

javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Column name 'idDepartamento' invalid.

推荐答案

似乎您正在尝试创建有效的 HQL / JPQL 语句，但是如果您阅读Formula的JavaDoc，您将看到:

It seems that you are trying to create a valid HQL / JPQL statement, but if you read the JavaDoc of Formula you will see:

该公式必须是有效的 SQL 片段

因此，您不能引用类中的 any 字段，而只能引用数据库中的表和列.

So you can't reference any field from your class, only tables and columns from your database.

但是您可以将Formula更改为使用JOIN:

But you can change your Formula to use a JOIN instead:

@Formula("(SELECT ud.IdDepartamento FROM UsuarioDpto ud WHERE ud.PorDefecto = 'S' AND ud.IdUsuario = idUsuario)")
public String getIdDepartamento() {
    return idDepartamento;
}

@Formula("(SELECT d.IdEmpresa FROM Departamento d JOIN UsuarioDpto ud ON d.IdDepartamento = uid.idDepartamento WHERE ud.PorDefecto = 'S' AND ud.IdUsuario = idUsuario)")
public String getIdEmpresa() {
    return idEmpresa;
}

我将删除您公式的设置器，因为它们会混淆您API的任何用户.公式字段通常是只读的.

And I would remove the setters of your formulas, as they will confuse any user of your API. Formula fields are usually read-only.

这篇关于休眠:@Formula查询字段使用另一个@Formula字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！