JPQL: "LIKE" comparison between a numeric column and a Strin

[negora]

Good morning:

I'm still a rookie with the Java Persistence API and the Java Persistence Query Language, and have a doubt about the implementation of the LIKE operator. On SQL, I use this operator to compare not only text strings between the each other but also a numeric value to a text pattern. I've checked and proved that I can do exactly the same in JPQL. Something like this:

SELECT s FROM Student s WHERE s.age LIKE '%5'

Being "s.age" a column of type Integer, I would get every student whose age ends in a 5. For example: 5, 15, 25, 35... Everything is OK.

I need to do exactly the same but in a case in which the string to compare with is not specified literally but with an input parameter which is of type String:

SELECT s FROM Student s WHERE s.age LIKE :age

However, this returns a failure because it expects the named parameter "age" to be of the same type that the column, thus an Integer. Is there any way to avoid that?

By now I've found a workaround which I didn't expect to work, sincerely... I wrapped the "a.age" column with the function TRIM in order to force the left term to be a String (meaning an implicit casting). I didn't think that it could work, because the documentation from Sun states that the parameter of TRIM must be of type String. However, this works:

SELECT s FROM Student s WHERE TRIM (s.age) LIKE :age

This also works using CONCAT, but not with UPPER or LOWER. Is there a "cleaner" way to achieve the same target?

I don't know if this is a problem from the implementation of JBoss or it's caused by the Java EE specifications. Just in case that it's a known issue, I'm using JBoss v. 4.2.2 GA. I would love using the version 5.x, but we began this project prior to the release of that version and still are messed up with lots of stuff.

Thank you very much for your help.

[ethnarch]

I would go look this up for you normally but I can't find the documentation on hibernate right now.

I think HQL might have a mod operator and I think this is what you actually want to use. I can't verify it it really exists though.

The mod operator returns the remainder so 5 mod 2 = 1 and 3 mod 2 = 1
So you would look for anything (s.age mod 10)=:age to find any ages that end in a certain number.

Most databases that I know of have some form of the mod operator for instance sql server
http://www.tek-tips.com/viewthread.cfm?qid=1213610&page=10

and oracle
http://www.techonthenet.com/oracle/functions/mod.php

If it does exist then you would be able to do something like this

SELECT s FROM Student s WHERE mod(s.age,10)=:age

I suspect that this would be faster than a string comparison and conversion however, I am not sure either.

Actually I think i found some documentation half way down this page
http://doc.javanb.com/hibernate-reference-3-2-4-sp1-en/queryhql.html

Any function or operator defined by EJB-QL 3.0: substring(), trim(), lower(), upper(), length(), locate(), abs(), sqrt(), bit_length(), mod()

There is always more than one way to do things but hopefully this helps you.

[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:

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:

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:

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.

[ceefour]

Thanks for your pointer to CONCAT.

I also have the same need but that works with JPA 2.0 and Hibernate 5.0.2:

SELECT p FROM MatchProfile p WHERE CONCAT(p.id, '') = :keyword

Works wonders. I think it works with LIKE too.