I'm running the following code:
Criteria crit = dao.getSesion().createCriteria(TAmbitos.class);
crit.add( Restrictions.sqlRestriction("translate(upper(nombre), 'ÁÉÍÓÚ', 'AEIOU') like translate(upper(?),'ÁÉÍÓÚ', 'AEIOU')",
param+"%", Hibernate.STRING));
crit.add(Restrictions.eq("tipoAmbito", "Empresa"));
crit.setFetchMode("TAmbitosByPais", FetchMode.JOIN);
crit.createAlias("TAmbitosByPais", "TAmbitosByPais", CriteriaSpecification.LEFT_JOIN);
crit.add(Restrictions.eq("TAmbitosByPais", ambito));
This is causing an ORA-00918: column ambiguously defined
exception. The criteria has a table TAmbitos
which references itself via TAmbitosByPais
(any given TAmbitos
row has a field TAmbitosByIdPais
which references another TAmbitos
). The resulting SQL is:
SELECT this_.id_ambito AS id1_2_1_, this_.depende_empresa AS depende2_2_1_,
this_.id_zona AS id3_2_1_, this_.pais AS pais2_1_,
this_.id_cls_soc AS id5_2_1_, this_.tipo_ambito AS tipo6_2_1_,
this_.nombre AS nombre2_1_, this_.clave_antigua AS clave8_2_1_,
this_.desactivado AS desactiv9_2_1_,
tambitosby1_.id_ambito AS id1_2_0_,
tambitosby1_.depende_empresa AS depende2_2_0_,
tambitosby1_.id_zona AS id3_2_0_, tambitosby1_.pais AS pais2_0_,
tambitosby1_.id_cls_soc AS id5_2_0_,
tambitosby1_.tipo_ambito AS tipo6_2_0_,
tambitosby1_.nombre AS nombre2_0_,
tambitosby1_.clave_antigua AS clave8_2_0_,
tambitosby1_.desactivado AS desactiv9_2_0_
FROM sac_conf.t_ambitos this_ LEFT OUTER JOIN sac_conf.t_ambitos tambitosby1_
ON this_.pais = tambitosby1_.id_ambito
WHERE TRANSLATE (UPPER (nombre), 'ÁÉÍÓÚ', 'AEIOU') LIKE
TRANSLATE (UPPER (?), 'ÁÉÍÓÚ', 'AEIOU')
AND this_.tipo_ambito = ?
AND this_.pais = ?
As you can see in the WHERE clause, the SQL cannot tell what "nombre" field I am referencing. I can overcome this by adding this_ in the sqlRestriction:
crit.add( Restrictions.sqlRestriction("translate(upper(this_.nombre), 'ÁÉÍÓÚ', 'AEIOU') like translate(upper(?),'ÁÉÍÓÚ', 'AEIOU')",
nombre+"%", Hibernate.STRING));
But I don't know if it is the best solution as I cannot be sure if the SQL will reference always the queried table as this_.There is another way to define the Criteria so "nombre" references TAmbitos.nombre
and not TAmbitosByIdPais.nombre
?
Thanks.
Hibernate Criteria API supports a special {alias}
placeholder for this case:
crit.add( Restrictions.sqlRestriction(
"translate(upper({alias}.nombre), 'ÁÉÍÓÚ', 'AEIOU') like translate(upper(?),'ÁÉÍÓÚ', 'AEIOU')",
param+"%", Hibernate.STRING));