Alternative about LIKE in EJBQL

rafonso Jan 20, 2003 11:30 AM

Hello,

I would like ues clause LIKE in my EJB-QL query. Like this: SELECT OBJECT(sc) FROM StateCity sc WHERE sc.stateCityName LIKE ?1%. But it doesn´t works. Reading other posts, I perceived that it is not possible use LIKE with parameter, only with constants.
So I tried other alternative. I wrote my query this: SELECT OBJECT(sc) FROM StateCity sc WHERE SUBSTRING(sc.stateCityName, 0, LENGTH(?1)) = ?1. It works!
Now, I want your opinion. Is this a acceptable alternative? Or this is a ugly patch?

Thanks,

Rafael U. C. Afonso

1. Re: Alternative about LIKE in EJBQL

canobi Jan 21, 2003 2:58 AM (in response to rafonso)

It was an acceptable workaround up until JBoss 3.0.3 when JBoss-QL support for parameters in "like" statements was added (if I recall correctly). As I understand it, the EJB 2.1 spec also includes this so there is no reason not to use it right now - just define your query in jbosscmp-jdbc.xml and the parametrized "like" query will work.
Actions
2. Re: Alternative about LIKE in EJBQL

rafonso Jan 21, 2003 6:48 AM (in response to rafonso)

Canobi:

I don't want use JBoss-QL because I dont't want be tied only to JBoss. And if I recall correctly, JBoss 3.0.4 supports EJB 2.0. Anyway, if I try a query with WHERE myString LIKE ?1%, I obtain a deploying error.

Thanks,

Rafael U. C. Afonso
Actions
3. Re: Alternative about LIKE in EJBQL

canobi Jan 22, 2003 5:22 AM (in response to rafonso)

Well, if this particular feature becomes available in EJB 2.1 then all of EJB 2.1 compliant containers will support it. Therefore if you use JBossQL right now, it shouldn't matter, in a few months or a year that query will work on any modern container anyway. Don't confuse JBossQL with old JAWS query language - JBossQL is the same as EJBQL with some added features.
Your deployment error if probably due to your query being defined in ejb-jar.xml instead of jbosscmp-jdbc.xml.
Actions
4. Re: Alternative about LIKE in EJBQL

gchini Jan 23, 2003 7:52 AM (in response to rafonso)

Hi,
Why don't you use the LOCATE function??
Your query would be:
SELECT OBJECT(sc) FROM StateCity sc WHERE SUBSTRING
LOCATE(sc.stateCityName, ?1) > 0 --> that is like 'xxx%'
or
SELECT OBJECT(sc) FROM StateCity sc WHERE SUBSTRING
LOCATE(sc.stateCityName, ?1) > 0 --> that is ilke '%xxx%'

Good work
Actions
5. Re: Alternative about LIKE in EJBQL

gchini Jan 23, 2003 7:54 AM (in response to rafonso)

Sorry, obviously the substring function must be delete from the command, I forgot it..
Actions

Go to original post