Hi Philipp,
I'm spinning my wheels with a WebLogic 7 issue, and their searching
facilities are sorely lacking, so it's nice to have a diversion that I
can actually be successful with. :)
> java.util.Collection findPhonesByUser(UserLocal user)
> SELECT OBJECT(p) FROM User AS u, IN(u.phones) AS p WHERE u = ?1
Man, ya learn something new every day. I didn't know you could pass in a
bean like that, but then again, I always just go through the
relationship accessors in that case.
> I want findUserByPhone, not phonesByUser, as you give me.
Ah okay. First, why not just use the relationship accessors?
Collection phones = user.getPhones();
and
UserLocal user = phone.getUser();
instead of findPhonesByUser and findUserByPhone? Are you just exploring
to learn how to create finders? If so, totally cool, I just figured I'd
point out the easy way first.
> I want:
>
> java.util.Collection findUserByPhone(PhoneLocal phone)
> SELECT OBJECT(u) FROM User AS u WHERE u.getPhone = ?1
You cannot do this as there are many phones, so the accessor is
u.getPhones.
> java.util.Collection findUserByPhoneId(String phoneId)
> SELECT OBJECT(u) FROM User AS u WHERE u.getPhone.getId = ?1
Same thing here, but you're close. This should do the trick.
java.util.Collection findUserByPhoneId(String phoneId)
SELECT OBJECT(u) FROM User AS u, IN(u.phones) AS p WHERE p.id = ?1
Side note: Unfortunately, you can't do any access on the parameter, so
you cannot pass in a PhoneLocal and use "p.id = ?1.id". Similarly, if
you wanted to do a partial match search (in Oracle), you can't do "WHERE
u.name like '?1%'". Instead, you need to do "WHERE u.name like ?1" and
call it as "findUserByName(name + "%")".
Is that closer?
David Harkness
Sony Pictures Digital Networks
(310) 482-4756
-----Original Message-----
From: Philipp W. Kutter [mailto:[EMAIL PROTECTED]
Sent: Tuesday, August 05, 2003 11:55 AM
To: [EMAIL PROTECTED]
Subject: Re: [Xdoclet-user] Using CMR field with EJBQL
Hi, David.
Thanks for the prompt answer!
>>2. Phone and User have a 1:N relationship. Each P has a U, and thus a
>>U
>>
>>
>
>
>
>> also has many Ps that consider them as their U.
>>
>>Now I would like to make a finder method on phones, where I select
>>
>>
>those
>
>
>>(the single one in fact) having a certain user:
>>
>>
>
>[One correction: you'll get many phones for any given user as it is U
>1:N P.]
>
>
Yes, and one user for any given phone, right?
>You cannot search directly on the CMR field like that, but you're
>close. You need to create what would effectively be a join in SQL by
>searching the User bean and relating it to its Phones. There are many
>ways to do this, and some may be easier, but let's continue with your
>example.
>
>I'm adapting the following EJB-QL from a different set of beans, but
>here's a first stab at it. The following finds the Phones related to
>the user (or users) with the given name:
>
> java.util.Collection findPhonesByUserName(java.lang.String name)
> SELECT OBJECT(p) FROM User AS u, IN(u.phones) AS p WHERE u.name = ?1
>
> "phones" is the CMR Collection field in User.
> "name" is a CMP field in User.
>
>
This is exactly the kind of example I got working. I even got it working
using the PhoneLocal directly,
I mean:
java.util.Collection findPhonesByUser(UserLocal user)
SELECT OBJECT(p) FROM User AS u, IN(u.phones) AS p WHERE u = ?1
This is working in my examples. Is there any reason to go for the .name
field, instead of
using the UserLocal directly?
BUT BACK TO MY QUESTION:
I was expressing my question wron. Sorry about that.
I want findUserByPhone, not phonesByUser, as you give me.
I want:
java.util.Collection findUserByPhone(PhoneLocal phone)
SELECT OBJECT(u) FROM User AS u WHERE u.getPhone = ?1
Or, if there is a reason I cannot go over PhoneLocal directly, I want:
java.util.Collection findUserByPhoneId(String phoneId)
SELECT OBJECT(u) FROM User AS u WHERE u.getPhone.getId = ?1
This is what is not working. Why is it not working? How can I do it?
For me it seems like, I cannot do it, since it is not needed to do it.
You always can
get the user by phone by applying getPhone outside of the EJBQL, and
thus it is not supported and nobody gives an example.
Is this understanding right?
Best, Philipp
>In SQL, this would be
>
> select p.* from phone p, user u where p.user_id = u.id and u.name =
>'<value>'
>
>If you were searching by user.id instead of user.name (as you would be
>given your example where you must have found a single User first), you
>could simply use
>
> UserLocal user = UserLocalHome.findByPrimaryKey(id);
> Collection phones = user.getPhones();
>
>David Harkness
>Sony Pictures Digital Networks
>(310) 482-4756
>
>
>-----Original Message-----
>From: Philipp W. Kutter [mailto:[EMAIL PROTECTED]
>Sent: Tuesday, August 05, 2003 11:05 AM
>To: [EMAIL PROTECTED]
>Subject: [Xdoclet-user] Using CMR field with EJBQL
>
>
>Hi, David.
>
>I had a second look on your answer, and I think I can formulate my
>problem based on
>the code proposed by you:
>
>I would like to know why
>
>* @ejb.finder
>* signature = "java.util.Collection findPhoneOfUser(UserLocal
user)"
>* query = "SELECT OBJECT(p) FROM phone p WHERE p.getUser = ?1 "
>
>
>is not working.
>
>In Detail:
>
>
>
>>1. User and Phone are entity beans.
>>2. Phone and User have a 1:N relationship. Each P has a U, and thus a
>>U
>>
>>
>
>
>
>>also has
>> many Ps that consider them as their U.
>>3. The User table has a PK column called id.
>>4. The Phone table has a FK column called user.
>>
>>If those are all correct, then you need to change your xdoclet tags to
>>specify a relationship instead of a field. For example, for User <--
>>1:N
>>--> Phones, here are the tags I use in UserEJB.java:
>>
>> /**
>> * @ejb.interface-method
>> * @ejb.transaction type="Supports"
>> * @ejb.relation
>> * name="User-Phone"
>> * role-name="User-has-many-Phones"
>> * cascade-delete="no"
>> */
>> public abstract Collection getPhones ( ) ;
>> public abstract void setPhones ( Collection phones ) ;
>>
>>And in PhoneEJB.java:
>>
>> /**
>> * @ejb.interface-method
>> * view-type="local"
>> * @ejb.transaction type="Supports"
>> * @ejb.relation
>> * name="User-Phone"
>> * role-name="Phones-have-a-User"
>> * cascade-delete="yes"
>> * @weblogic.column-map
>> * foreign-key-column="user_id"
>> * key-column="id"
>> */
>> public abstract UserLocal getUser ( ) ;
>>
>> /**
>> */
>> public abstract void setUser ( UserLocal user ) ;
>>
>>This is for a bidirectional relationship, so both beans know about and
>>can find each other via relationships and in finder queries. I also
>>added a helper method so I could hook a Phone up to a User given only
>>the User's ID:
>>
>> /**
>> * Attaches this phone to the user with the given ID.
>> *
>> * @param userId unique ID of the owning user
>> */
>> protected void setLocalUserId ( Integer userId )
>> {
>> try {
>> setUser(UserUtil.getLocalHome().findByPrimaryKey(userId));
>> }
>> catch ( NamingException e ) {
>> throw new ServiceException("Failure looking up user home", e);
>> }
>> catch ( FinderException e ) {
>> throw new ServiceException("Failure finding a user", e);
>> }
>> }
>>
>>First of all, is this where you were going with this?
>>
>>
>>
>Yes, this is what I am going to, adapted to your Phone/User example.
>
>Now I would like to make a finder method on phones, where I select
>those
>
>(the single one in fact) having a certain
>user:
>
>* @ejb.finder
>* signature = "java.util.Collection findPhoneOfUser(UserLocal
user)"
>* query = "SELECT OBJECT(p) FROM phone p WHERE p.getUser = ?1 "
>
>
>This is not working in my examples. Why?
>
>Best, and thanks again, Philipp
>
>
>
>
>-------------------------------------------------------
>This SF.Net email sponsored by: Free pre-built ASP.NET sites including
>Data Reports, E-commerce, Portals, and Forums are available now.
>Download today and enter to win an XBOX or Visual Studio .NET.
>http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_0
>1/01
>_______________________________________________
>xdoclet-user mailing list
>[EMAIL PROTECTED]
>https://lists.sourceforge.net/lists/listinfo/xdoclet-user
>
>
-------------------------------------------------------
This SF.Net email sponsored by: Free pre-built ASP.NET sites including
Data Reports, E-commerce, Portals, and Forums are available now.
Download today and enter to win an XBOX or Visual Studio .NET.
http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01
_______________________________________________
xdoclet-user mailing list
[EMAIL PROTECTED]
https://lists.sourceforge.net/lists/listinfo/xdoclet-user
