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;
}
}
But I get:
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: Column name 'idDepartamento' invalid.
It seems that you are trying to create a valid HQL / JPQL statement, but if you read the JavaDoc of Formula
you will see:
The formula has to be a valid SQL fragment
So you can't reference any field from your class, only tables and columns from your database.
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;
}
And I would remove the setters of your formulas, as they will confuse any user of your API. Formula fields are usually read-only.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With