[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - 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.

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

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