RESTRICTIONS in EJBQL

[jsosa]

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.
                      

[chawax]

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

[ztiringer]

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...

[swd847]

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.

[ztiringer]

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)