ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeff Butler" <jeffgbut...@gmail.com>
Subject Re: Urgent help Needed - Join Query
Date Wed, 19 Apr 2006 05:09:11 GMT
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 <rambabu.piridi@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
>

Mime
View raw message