Date and DateTime comparisons in JPQL

JPQL doesn't have any explicit date conversion functions like the Oracle TO_DATE, but it does allow for direct comparisons with properly formatted date and datetime strings.

select m
from ClassM m
where m.dtval >= '2008-01-01 23:12:09' and m.dval < '2008-02-01'

This makes dynamic query generation somewhat simpler than having to use and set date parameters within the Query object (my original approach).

Quiet CVS

I should have looked this up ages ago because the CVS directory list often gets in the way of the information that I want to see out of CVS when doing normal CVS operations such as "cvs diff", "cvs update", and "cvs commit".

CVS has a quiet mode - "cvs -q"

cvs -q diff
cvs -q update

Data Conversion Functions in JPQL?

So it appears that there is no equivalent to TO_NUMBER in JPQL which is causing a bit of an inconvenience when trying to query based on numeric criteria on a character column. I don't want to fall back on an Oracle native query.

I need a work-around which will be embedded within a JPQL criteria creation class.

JPQL does provide:

concat(str1, str2)
substring(str, start, length)
length(str)
trim(str)

Using these function it should be possible to produce a workable query.

If this is the query that I want to write:

select a
from ClassA a
where to_number(a.val) > 10 and to_number(a.val) < 300

My work-around could look as follows:

select a
from ClassA a
where substring(concat('000000000000000',trim(a.val)),length(trim(a.val),16)
> '0000000000000010'
and substring(concat('000000000000000',trim(a.val)),length(trim(a.val),16)
< '0000000000000300'

This works because it forces leading zeros in front of the numerical value, forcing the length of the value to 16 digits. The number 16 was arbitrarily chosen. It provides more than enough digits for my purposes, but could be extended if necessary.

The verbosity of the query looks unwieldy, but in reality the query will be auto generated.

There is likely to be a performance hit. The database will have to make several function calls for every evaluation.