Re: Urgent help Needed - Join Query

Jeff Butler Tue, 18 Apr 2006 22:09:35 -0700

Wow - it's a week of groupBy questions. Try this:

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, [EMAIL PROTECTED] <[EMAIL PROTECTED] > 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:[EMAIL PROTECTED]]
Sent: Tuesday, April 18, 2006 9:40 PM

To: [email protected]

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

[EMAIL PROTECTED]

From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, 18 April 2006 11:22
To: [email protected]
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

Re: Urgent help Needed - Join Query

Reply via email to