• Post Reply Bookmark Topic Watch Topic
  • New Topic

EJBQL question...what to do with UCASE  RSS feed

 
Sybil Santos-Burgan
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello
I am migrating my EJBs to 2.0 so I am currently rewriting my finders.
I have a finder findByUserGroupName. The data for my UserGroupName could be mixed case. I have a search screen that searches for these names and returns the names back that match the query parameters regardless of case.
So say I have the names "USERgroupNAME and "usERGroupName" if I typed in "use" I would get back the above two id's. In our previous finders we did this by uppercasing the parameter "use" and then wrapping the column in the select in UCASE and using LIKE
It looked like this: UCASE(USER_GROUP_NAME) LIKE ?<upper cased param>
I did this fine in using SQL but now I need to rewrite using EJBQL.
Can someone please help me figure out how to do this now?
I'm using WebSphere 5.0
Thanks in advance!
Sybil Santos-Burgan
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Sybil Santos-Burgan:
I have a finder findByUserGroupName. The data for my UserGroupName could be mixed case. I have a search screen that searches for these names and returns the names back that match the query parameters regardless of case.

You have two options that I know of:
1. Investigate WebShere's EJB-QL extensions. WebLogic added UPPER and LOWER in 8.1 (or 7.0sp<x> . Those functions won't make their debut in standard EJB-QL until EJB 2.1, IIRC. Using EJB-QL extensions, you could either specify that a function-based index be used or uppercase the fields in the search.
2. Add a upper/lower-case version of the same field to your bean, and search on this field instead of the mixed-case one. Whenever the real attribute is set, you must also set the derived one. It takes more space in your DB, but it doesn't involve any container-specific extensions.
I suppose option 3 is to wait for an EJB 2.1 container to ship.
 
Sergiu Truta
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually here's option 3 and this is what I'd do:
I suppose you don't call the entities dirrectly from the client, especially if you are updating to EJB 2.0. So if you have any kind of facade (session facade,message facade,ejbcommand,POJOs) the code should be:
inside the facade object:
findByUserGroupName(String groupName){
....
Collection col = entityLocal.findByUserGroupName(groupName.toUpperCase());
and the EJB-QL inside the entity bean's method should be:
SELECT OBJECT (u) FROM UserEntity AS u WHERE (LOCATE(u.groupName,?1) <> 0)
This LOCATE function is available in EJB-QL from EJB 2.0 and returns a value different from 0 if first parameter is found inside second parameter. It is pretty much like LIKE expressions.
I would advice using this method because if you use extensions of EJB-QL from an application server you are bound to that app server (even that version of the application server).
 
David Harkness
Ranch Hand
Posts: 1646
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by sergiu truta:
SELECT OBJECT (u) FROM UserEntity AS u WHERE (LOCATE(u.groupName,?1) <> 0)

Interesting option. Do you know precisely what this does at the SQL level? I suspect this will require a table scan of that column, but without knowing exactly what it does that's just a guess.
Thanks, though, as I'll remember in the future to check out possible workarounds using the string functions!
Originally posted by sergiu truta:
I would advice using this method because if you use extensions of EJB-QL from an application server you are bound to that app server (even that version of the application server).

While a valid point, and one I've heard on every project on which I've worked, I've never, ever, ever done any of the following on a project:
1. Changed the database
2. Changed the container
3. Changed the language
Granted, these *can* and *do* happen (just not to me luckily!). However, for something this small that would require about an hour to implement differently should you ever switch containers, I think it's pretty safe.
 
Sergiu Truta
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
David,
I haven't either changed the container at any projects I've worked on,but we sometimes used different databases at different clients.
Though, this is not a reason to make the application dependant on the container because you never know when a new client wants to use an app server that it already has a licence for or a free/open source app server. You are right that a function can be easily changed if necessary when changing the container, but imagine having hundreds of functions that need to be changed...and now you will see my point.
Best regards,
Sergiu.
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!