You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by Jeff Butler <je...@gmail.com> on 2006/04/19 07:09:11 UTC
Re: Urgent help Needed - Join Query
Wow - it's a week of groupBy questions. Try this:
<resultMap id="userMap" class="user" groupBy="userid">
Also, make your getter/setter for ListDynamicProperties be of the same type
- either List or ArrayList, but not both. Everything else the same.
Jeff Butler
On 4/18/06, rambabu.piridi@wipro.com <ra...@wipro.com> wrote:
>
> Here i am giving the complete information:
>
> I have defined two result maps.
>
>
> <resultMap id="userMap" class="user">
>
> <result property="userid" column="USR_ID"/>
>
> <result property="password" column="USR_PWD"/>
>
> <result property="username" column="USR_NAME"/>
>
> <result property="role" column="ROLE_ID"/>
>
> <result property="code" column="USR_CODE"/>
>
> <result property="email" column="EMAIL_ID"/>
>
> <result property="phoneno" column="PHONE_NUMBER"/>
>
> <result property="description" column="DESCRIPTION"/>
>
> <result property="listDynamicProperties" resultMap="User.userMap1"/>
>
> </resultMap>
>
>
>
> <resultMap id="userMap1" class="dynamicProperty">
>
> <result property="propertyName" column="PROPERTY_NAME"/>
>
> <result property="propertyValue" column="PROPERTY_VALUE"/>
>
> </resultMap>
>
> These are my bean classes:
>
> public class *User* {
>
>
> public User()
> {
>
> }
>
> private List listDynamicProperties;
>
>
> private String userid;
>
> private String username;
>
> private int role;
>
>
> public String getUsername() {
> return username;
> }
>
> public void setUsername(String username) {
> this.username = username;
> }
>
> public int getRole()
> {
> return role;
> }
>
> public void setRole(int role) {
> this.role = role;
> }
>
> public String getUserid() {
> return userid;
> }
>
> public void setUserid(String userid) {
> this.userid = userid;
> }
>
> public List getListDynamicProperties()
> {
> return listDynamicProperties;
>
> }
>
> public void setListDynamicProperties(ArrayList listDynamicProperties1)
> {
> this.listDynamicProperties = listDynamicProperties1;
>
> }
>
> }
> public class *DynamicProperty *{
>
> private String propertyName;
> private String propertyValue;
>
> /*
> * name and value should not be null.
> * If null throw Exception.
> */
> public DynamicProperty()
> {
>
> }
>
> public String getPropertyName() {
> return propertyName;
> }
>
>
> public void setPropertyName(String propertyName) {
> this.propertyName = propertyName;
> }
>
>
> public String getPropertyValue() {
> return propertyValue;
> }
>
>
> public void setPropertyValue(String propertyValue) {
> System.out.println("Setting DynamicProperties");
> this.propertyValue = propertyValue;
> }
>
>
> The data in the data base is:
> *USR_BASIC_INFO* *:*
>
> USR_ID, USR_NAME, ROLE_ID
> 1, xxxx, 200
> 2, yyyy, 300
>
> *USR_EXTENSION_INFO*:
>
> USR_ID, PROPERTY_NAME, PROPERTY_VALUE :
> 1, city, foster city
> 1, state, california
> 2, city, hyd
> 2, state, ap
>
> My select query is:
>
> <select id="select4" resultMap="userMap">
>
> select BASIC.USR_ID, BASIC.USR_NAME, BASIC.ROLE_ID,EXTENSIN.PROPERTY_NAME
> ,EXTENSIN.PROPERTY_VALUE from USR_BASIC_INFO BASIC, USR_EXTENSION_INFO
> EXTENSIN where BASIC.USR_ID=EXTENSIN.USR_ID;
>
> </select>
>
> Now i am getting four results. where i am expecting two results.
>
> i am getting the result as "*4 User objects*" and the data as:
>
> 1,xxxx, 200, city, foster city
>
> 1,xxxx, 200,state, california
> 2,yyyy,300,city, hyd
>
> 2,yyyy,300,state, ap
>
> But i am expecting two User objects. And each User should contain List of
> dynamicProeprties object. Is it problem in Ibatis?.
>
>
>
> ------------------------------
> *From:* Poitras Christian [mailto:Christian.Poitras@ircm.qc.ca]
> *Sent:* Tuesday, April 18, 2006 9:40 PM
>
> *To:* user-java@ibatis.apache.org
> *Subject:* RE: Urgent help Needed - Join Query
>
>
> First, I believe you should make a link between your 2 objects.
>
> In User add a List property that will contain user addresses. With this
> solution it will be easy to populate User and Address List at the same time.
> Also add this in the User resultMap.
> <result property="addressList" column="userId" select="getAddressByUser"
> />
> And the query "getAddressByUser" will contain
> select * from User, Address where User.UserId = Address.UserId and
> User.UserId = #value#
>
> You can also populate the User and it's Addresses in one call.
>
> Christian
> Christian.Poitras@ircm.qc.ca
>
> ------------------------------
> *From:* rambabu.piridi@wipro.com [mailto:rambabu.piridi@wipro.com]
> *Sent:* Tuesday, 18 April 2006 11:22
> *To:* user-java@ibatis.apache.org
> *Subject:* Urgent help Needed - Join Query
>
>
>
> Hi,
>
> I have two tables User and Address. The primary key of the User table
> is also the foreign key of Address table.
>
> It is possible that a user may or may not have address ( Property )
> associated with him and also one user can have more than one address
> (property). I have two Java classes which are useful to set and get the
> values of the data members.
>
>
> public class User {
>
> private String UserId ;
> private String UserName;
> private String Designation;
> private String Address;
>
> public String getAddress() { return Address; }
>
> public String getDesignation() { return Designation; }
>
> public String getUserId() { return UserId; }
>
> public String getUserName() { return UserName; }
>
> public void setAddress(String address) { Address = address; }
>
> public void setDesignation(String designation) { Designation =
> designation; }
>
> public void setUserId(String userId) { UserId = userId; }
>
> public void setUserName(String userName) { UserName = userName; }
> }
>
> public class Address
> {
>
> private String UserId;
> private String City;
> private String State;
>
> public String getCity() { return City; }
>
> public String getState() { return State; }
>
> public String getUserId() { return UserId; }
>
> public void setCity(String city) { City = city; }
>
> public void setState(String state) { State = state; }
>
> public void setUserId(String userId) { UserId = userId; }
>
> }
>
> The following table shows the structure of the table.
>
> User
> ===============
> UserId - PrimaryKey
> UserName
> Designation
> Address
>
>
> Address
> ===============
> UserId - ForeignKey
> City
> State
>
>
> the following table data is an example data.
>
> User
> ======================================================
> UserId UserName Designation
> Address
>
> 1 Abc S.E City,
> New Yark
> 2 Xyz S.E ---
>
> 3 Cde P.M
> ----
>
>
>
> Address
> =======================================================
> UserId PropertyName PropertyName
>
> 1 City New
> Yark
>
> 1 Country America
> 2 --
> ---
> 3 City
> NewYark
>
> 4 City London
>
>
> When I am trying to get the data from both the tables using the following
> Query
>
> select * from User, Address where User.UserId = Address.UserId
>
> the result is
>
> 1, Abc , S.E
> 1, City, New Yark
> 1, Country, America
>
>
> Now my requirement is to get the data from both the tables and store them
> as User Object.
> Can any one specify
>
> 1. IBatis SQL Mappings for the tables.
>
> 2. SQL Qury to get the data from both the
> tables and store it in User Object.
>
>
>
> regards,
>
> rambabu
>
> The information contained in this electronic message and any attachments
> to this message are intended for the exclusive use of the addressee(s) and
> may contain proprietary, confidential or privileged information. If you are
> not the intended recipient, you should not disseminate, distribute or copy
> this e-mail. Please notify the sender immediately and destroy all copies of
> this message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient
> should check this email and any attachments for the presence of viruses. The
> company accepts no liability for any damage caused by any virus transmitted
> by this email.
>
> www.wipro.com
>
> The information contained in this electronic message and any attachments
> to this message are intended for the exclusive use of the addressee(s) and
> may contain proprietary, confidential or privileged information. If you are
> not the intended recipient, you should not disseminate, distribute or copy
> this e-mail. Please notify the sender immediately and destroy all copies of
> this message and any attachments.
>
> WARNING: Computer viruses can be transmitted via email. The recipient
> should check this email and any attachments for the presence of viruses. The
> company accepts no liability for any damage caused by any virus transmitted
> by this email.
>
> www.wipro.com
>