Hello,

I have a method which roughly looks like this:

The important part is that I pass in a property and want to return a Criterion. Now I would like the method to trim the property and return a NullExpression. Something like:

But this does not work: org.hibernate.QueryException: could not resolve property: rtrim(ltrim(r of ...

I tried:

which works, but I do not like this solution as I do not want to hard code the physical colum name.

Is there a different way of achieving this?

Best regards,
Mo

If you want to check for nulls, I'm not sure why you are applying the trims. nulls will be nulls whether trimmed or not, and a string of whitespace will not become null just because it's trimmed - it will become an empty string.

Otherwise, I'm also not quite sure what you're trying to accomplish. Why not just add Restrictions to the Criteria as you are building it?

Thanks for your reply.


Unfortunately this is not true in Oracle. An empty string becomes null in Oracle. (see also Oracle/PLSQL: Difference between an empty string and a null value

The method itself is a lot more complicated, but I did not want to put the whole source code here, as it is not really relevant to the problem. The basic question is: Is it possible to create a "trim( property ) is null" Criterion and if so, how?

Interesting about the null vs empty string in Oracle, and sounds like fun for you.

It seems like you're looking to find records where column is null, is empty string, or is all whitespace (hence the trimming)... yes? I think that's tricky using Restrictions. Can you just do it in HQL? Then, you'll be able to use the trim() function on the property itself.

If you really have to use Restrictions, I think the sqlRestriction is the way, as you've done. In order to programmatically get the column name to avoid hardcoding it, I think you have to do some gymnastics.

(If you're using Spring, you can get at the Configuration object from the LocalSessionFactoryBean available in your application context. Then, with the configuration object you can do a to get at the column name.)

There's probably a better way. If you or anyone else has one, I'd be curious to hear it.

Yes, that is exactly what I am trying to do.

The reason why I would like to return a Criterion is, that this method is actually part of a bigger recursive algorithm that is walking down an abstract syntax tree. Each node of the syntax tree is creating a criterion that gets added to it's parent. It actually works quite well, the only problem is this "trim" function.


Looks interesting, I will have a look into this, thank you.