• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to write such an EJB-QL

 
Mishra Anshu
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!!
I have to an entity bean mapped to some table (say table1).

If I have to search anything from this bean depending upon a set
of five criterias it is easy to have 5 finders like:--

findByProp1,findByProp2,findByProp3,findByProp4,findByProp5.

But the problem is that I need to have any combination of these five properties (i.e, may be at a time in the search criteria , I will have
prop1,prop4,prop5...and so on..).

So, is there a way in EJB-QL to write such query or I'll have to write

that many queries

Plz. help
 
alzamabar
Ranch Hand
Posts: 379
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Without having a taste of the kind of data, it's difficult to answer propertly. But...You can restrict the number of queries (regardless of the data-type) using relations.

Hint: look at the 'IN' keyword in the 'FROM' part of an EJB-QL. Please note: 'IN' in the 'FROM' is not the same as the 'IN' in the 'WHERE'.

Hope this help,

Marco
 
Alec Lee
Ranch Hand
Posts: 569
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I guess you could consider passing a boolean flag to the ejbql if certain condition should be tested

say your criteria is an int, then the finder should looks like:

findByMyCriteria(int prop1, boolean testProp1, int prop2, boolean testProp2, ....)

And the ejbql should be:

SELECT ... FROM ...
WHERE (prop1 = ... OR testProp1 <> TRUE) AND
(Prop2 = ... OR testProp2 <> TRUE) ...
 
Mishra Anshu
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Lee!!

Looks like I am going to solve it soon..
Will update you about the status..
 
Mishra Anshu
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
But shouldn't it be like that :--

SELECT ... FROM ...
WHERE (prop1 = ... AND testProp1 <> TRUE) AND
(Prop2 = ... AND testProp2 <> TRUE) ...

for example.. suppose I have :--

prop1 = *something* and testProp1 = false , hence it should not be contributing towards the search, which can be attained if we apply AND instead of OR , in

(prop1 = ... AND testProp1 <> TRUE)

Please suggest me if I am wrong
 
Valentin Crettaz
Gold Digger
Sheriff
Posts: 7610
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
As Marco mentioned, it would be very cool if you would come up with some concrete example.

Actually, the query should be like this:

SELECT ... FROM ...
WHERE (prop1 = ... AND testProp1 = TRUE) AND
(Prop2 = ... AND testProp2 = TRUE) ...

which means that you test the property if and only if testPropx is true (and not different from true)...
 
Mishra Anshu
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Valentin,

Let me make it clearer.
I have an object SearchCriteria which has following five setters:-

SearchCriteria.setMobileNo
SearchCriteria .setName
SearchCriteria.setCategory
SearchCriteria .setStatus
SearchCriteria .setSubscriptionPlan

Now, from the gui, a person can select, one or two or three or four or all, these criterias.

So, depnending upon the selection , I'll get through the getters of this class the value or null (if not selected)..

So, how should I write the EJB-QL??

:roll:
 
Mishra Anshu
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is the data not sufficient ?

I had just gone through this link :-

http://forum.java.sun.com/thread.jsp?forum=13&thread=537805&tstart=15&trange=15

According to this, it feels that I am not going to solve it through the EJB-QL.

Please suggest
 
Mishra Anshu
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have just solved the problem.

I have written a finder:--

findBySearchCriteria(String mobileNo, String name, String category, String status, String subscriptionPlan)

Please note that (category , status and subscriptionPlan are int in my DB table, but I have declared here all of them to be string).

My EJB- QL is :--

SELECT o FROM UserMaster o WHERE o.mobileNo LIKE ?1 AND o.userName LIKE ?2 AND o.categoryType LIKE ?3 AND o.userStatusId LIKE ?4 AND o.planId LIKE ?5

------------------------------------------
Now depending upon the search critera, if I get some of the value as null (say for the username....),

then I'll call it as :--

client.findBySearchCriteria(searchCritera.getMobileNo,
"%",

searchCritera.getStatus,
searchCritera.getPlanId,
);
 
Valentin Crettaz
Gold Digger
Sheriff
Posts: 7610
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, that's good but... unfortunately not portable. Maybe you don't even care about portability but I think it is important to be aware of the following:

Section 11.2.7.4 states:

Note that if an input parameter value is null, comparison operations or arithmetic operations involving the input parameter will return in an unknown value. See Section 11.2.9.


And you cannot even use the IS NULL operator since those can only be used in conjunction with CMP or single-valued CMR fields.

So, the solution you have now strongly depends on the way your container works and what it decides to return when doing comparisons with NULL parameter values.
 
Mishra Anshu
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Then,
how to deal with such a problem?
It is a very common one.
Do, you propose to write a BMP with all that JDBC codes to use SQL queries
and get rid of the problem?

Please ask me if you are not clear about the data.
A better and portable (ofcourse) solution is what any body would like to have.
 
Valentin Crettaz
Gold Digger
Sheriff
Posts: 7610
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually, I think that this can be best solved with an ad-hoc finder method such as the following one. The method name must not start with "find" !!



Note: The 1=1 is just to have a true condition for adding the WHERE part without having to check that at least one parameter is not null
[ July 26, 2004: Message edited by: Valentin Crettaz ]
 
Mishra Anshu
Ranch Hand
Posts: 224
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes,
It is really a nice solution. I agree with you...
But...We couldn't use CMP bean, and hence the EJB-QL
couldn't suffice a proper way to solve this particular
problem, we may assume.

So, In such situations, I feel we should go for the
BMP uses and then we have a lot of flexibilties to
use the SQL Query.

Am I right now?
 
Valentin Crettaz
Gold Digger
Sheriff
Posts: 7610
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, you can still have CMP beans and let a dedicated session bean do that work for you. Actually, that's what I would do. What I usually do is that I let a session bean do the work and retrieve only the primary keys of the matching entity beans and then I look up each entity bean using findByPrimaryKey, but it all depends on how the performance looks like on your server. To sum up, you still get to use CMP (if this is what you want) and you can have customized finders in ad-hoc session beans that retrieve entity beans for you instead of interacting directly with the entity bean itself.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic