4 Replies Latest reply on Apr 1, 2009 1:48 AM by Zoltan Tiringer

    RESTRICTIONS in EJBQL

    Joaquin Sosa Newbie
      Hi to all.

      I have the following code:

      private static final String EJBQL = "select persona from Persona persona";

      private static final String[] RESTRICTIONS = {
                              "lower(persona.nombre) like concat(lower(#{personaList.persona.nombre}),'%')",
                              "lower(persona.apellido1) like concat(lower(#{personaList.persona.apellido1}),'%')",
                              "lower(persona.apellido2) like concat(lower(#{personaList.persona.apellido2}),'%')",
                              "persona.numExpediente = #{personaList.persona.numExpediente}",
                              "lower(persona.numAfiliacion) like concat(lower(#{personaList.persona.numAfiliacion}),'%')",
                              "persona.datosServicio.colectivo.codigo = #{personaList.persona.datosServicio.colectivo.codigo}",
                              "persona.tipoAfiliacion.codigo = #{personaList.persona.tipoAfiliacion.codigo}"};


      to filter EJBQL by some criteria. The problem is with the type of some fields are BigDecimal, i cannot use the 'like' operator, and to find by this criteria does nothing.

      Can anybody tell me how can i code this sentence???

      persona.datosServicio.colectivo.codigo = #{personaList.persona.datosServicio.colectivo.codigo}"   (BidDecimal)


      Thanks a lot.
                            
        • 1. Re: RESTRICTIONS in EJBQL
          Olivier Thierry Apprentice

          I have the same kind of problem, with some queries that use float parameters. The equal operator in where clauses doesn't work. I thought about wrong Java / SQL type mappings, but could not find a way to solve this. If I remember well (I had the problem a long time ago), I could make it work with some databases (either SQL Server either Oracle, I don't remember), but it doesn't work with HSQLDB for example (the one I use for my unit tests).

          • 2. Re: RESTRICTIONS in EJBQL
            Zoltan Tiringer Newbie

            Indeed, it must be a DB specific issue (probably in Hibernate), because restrictions for BigDecimal work just fine for me with the same syntax that you are using, while connecting to Oracle. What DB are you using? Probably you should check out some Hibernate forums, too...

            • 3. Re: RESTRICTIONS in EJBQL
              Stuart Douglas Master

              Is the underlying database type a double or an arbitrary precision number of some kind?


              At any rate why do you need BigDecimal equality in your queries? Are you sure that these fields should not be integers?


              If you really do need this equality then the way you approach it is to define a small constant Epsilon and write your query like this:


              "abs(persona.datosServicio.colectivo.codigo - #{personaList.persona.datosServicio.colectivo.codigo}) < my_epsilon_constant"
              
              



              What value to use for epsilon depends on the application and the underlying type.

              • 4. Re: RESTRICTIONS in EJBQL
                Zoltan Tiringer Newbie

                Checking again your code, it seems necessary to add to EJBQL the join to datosServicio and colectivo, otherwise you can hardly restrict them (as they seem to be separate entities)