2

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.

Averroes
  • 4,168
  • 6
  • 50
  • 63

2 Answers2

5

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));
axtavt
  • 239,438
  • 41
  • 511
  • 482
  • 2
    In case you're wondering, like I did: Yes, you have to literally type `{alias}` this is not {yourTableAlias} or {myTableAlias} it is a special placeholder like axtavt just explained. – egallardo May 04 '13 at 04:30
  • for my case, I had done `Restrictions.sqlRestriction("id in ...")` and `id` needed to be replaced with `{alias}.id` – Kevin Meredith Jul 25 '14 at 18:44
2

Documentation to the rescue:

Apply a constraint expressed in SQL. Any occurrences of {alias} will be replaced by the table alias.

(emphasis mine)

JB Nizet
  • 678,734
  • 91
  • 1,224
  • 1,255
  • Looking at the `Restrictions` [docs](http://docs.jboss.org/hibernate/orm/3.3/api/org/hibernate/criterion/Restrictions.html#sqlRestriction(java.lang.String)), I don't think that `SQLQuery` can be used in `Restriction.sqlQuery(String, ...)`. Can a `SQLQuery` be `toString`'d to be a valid argument? – Kevin Meredith Jul 25 '14 at 18:25