Hibernate Criteria导致ORA-00918：列含糊不清

问题描述

  Criteria crit = dao.getSesion（）。createCriteria（TAmbitos.class） ; 
 crit.add（Restrictions.sqlRestriction（translate（upper（nombre），'ÁÉÍÓÚ'，'AEIOU'）like translate（upper（？），'ÁÉÍÓÚ'，'AEIOU'），
参数+％，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））; 
  

这导致 ORA-00918：列不明确定义异常。该标准有一个表 TAmbitos ，它通过 TAmbitosByPais 引用它（任何给定 TAmbitos row有一个引用另一个 TAmbitos 的字段 TAmbitosByIdPais ）。结果SQL是：

  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 
 WHL TRANSLATE（UPPER（nombre），'ÁÉÍÓÚ'，'AEIOU'）LIKE 
 TRANSLATE（UPPER（？），'ÁÉÍÓÚ'，'AEIOU'）
 AND this_.tipo_ambito =？ 
 AND this_.pais =？ 
  

正如您在WHERE子句中看到的那样，SQL无法知道我所引用的nombre字段。我可以通过在sqlRestriction中添加this_来解决这个问题：

  crit.add（Restrictions.sqlRestriction（translate（upper（this_。 （'），'ÁÉÍÓÚ'，'AEIOU'），
 nombre +％，Hibernate.STRING））; 
  

但我不知道这是否是最好的解决方案，因为我无法确定SQL是否会引用总是作为this_查询表。还有另一种方法来定义标准，以便nombre引用 TAmbitos.nombre 而不是 TAmbitosByIdPais.nombre ？

谢谢。

解决方案

API为这种情况支持一个特殊的 {别名} 占位符：

  crit .add（Restrictions.sqlRestriction（
translate（upper（{alias} .nombre），'ÁÉÍÓÚ'，'AEIOU'）like translate（upper（？），'ÁÉÍÓÚ'，'AEIOU'），
 param +％，Hibernate.STRING））; 
  

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));

这篇关于Hibernate Criteria导致ORA-00918：列含糊不清的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！