[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - Re: JPQL:

[negora]

For ethnarch: First of all, excuse my late answer. Also thanks for spending your time trying to help me.

I think that I should have chosen a more generic example, since that one of numbers ending in five was only to get an approximation to my problem in real terms. As you stated it could be solved using a mathematic function even with more efficiency.

My real need is to know if there's a way to compare a column of type numeric against a text string, using the LIKE comparator without tricks like the one which I mentioned above.

Let's think about a comparison which can't be solved using mathematic functions (at least, of my knowledge). let's suppose that I need to get every price which at least has the sequence 23 in it, such as 2394.34, 388231.00, 323.10, 0.235, etc.

I would use this JPQL query:

anonymous wrote : SELECT p FROM Products p WHERE p.price LIKE '%23%'

However this would cause an exception if I used an input parameter instead of typing that expression literally, since EJB needs that both, the parameter and the column, must be of the same type:

anonymous wrote : SELECT p FROM Products p WHERE p.price LIKE ?

Where ? is the String %23% passed as parameter. This causes an error.

So I would need to use the TRIM trick to avoid the exception:

anonymous wrote : SELECT p FROM Products p WHERE TRIM (p.price) LIKE ?

Doing that change I could even pass just the string % as parameter to choose every record, without having to alter the query dynamically (a true problem when they're static, inserted into every entity, as my personal case in this project).

I'm frightened that this trick can't be used in the future, when a new release of JBoss forces the TRIM argument to be a String and can't be a number, as the Sun documentation states.

View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4227030#4227030

Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&p=4227030