Issue with join of 2 tables in Ibatis

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Issue with join of 2 tables in Ibatis

deepakl_2000
Hi,

    I have to fetch a list from 2 tables based on below condition.

 

[code]

-------------------

Service class

-------------------

fileParameters.put("Project_Code",projectCode);

fileParameters.put("Files_ID",filePk);

responseFileData = (List)sqlMap.queryForList("getFileResponses",fileParameters);

 

-------------------------------------------------------
query.xml

<parameterMap id="FileDetails" class="java.util.Map" >                                               
                <parameter property="Project_Code" jdbcType="VARCHAR" />
                <parameter property="Files_ID" jdbcType="INT" />
</parameterMap>

                                 

<resultMap id="get_list" class="com.hcl.fpTool.pojo.ResponseFilesDataPOJO">
            <result property="rule_Id" column="Rule_Id"/>           
            <result property="response" column="Response"/>                       
            <result property="remarks" column="Remarks"/>                       
/resultMap>

<select id="getFileResponses" resultClass="com.hcl.fpTool.pojo.ResponseFilesDataPOJO" parameterMap="FileDetails" resultMap="get_list">                   

            select bres.rule_id, bstatic.rule_desc,bres.remarks, bres.response
            from BusinessRule_ResFiles_Data bres, BusinessRule_StaticData bstatic
            where  project_Code = ? and files_id = ?
            and bres.rule_id = bstatic.rule_id and bres.rule_id not like ('%CD%')                                        
 </select>

 

The problem lies here is
1> i need  to select "bstatic.rule_desc"  from my another table "BusinessRule_StaticData"
2>i need to select "bres.rule_id" from my another table "BusinessRule_ResFiles_Data"

 so i have a total select of 4 columns based on a join of 2 tables.

so please help me to correct the "query.xml" file such that i can accomplish the selection of 4 columns and retrieve it back as a list in my service class


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Issue with join of 2 tables in Ibatis

jhking
Your query gets the 4 columns you need from the appropriate tables.
You just need to change your resultmap and ResponseFilesDataPOJO to have all 4 values.

class ResponseFilesDataPOJO{
    private String rule_id;
    private String response;
    private String remarks;
    private String rule_desc;  
// constructor , getters , setters
}
<resultMap id="get_list" class="com.hcl.fpTool.pojo.ResponseFilesDataPOJO">
            <result property="rule_Id" column="Rule_Id"/>          
            <result property="response" column="Response"/>                        
            <result property="remarks" column="Remarks"/>                        
            <result property="rule_desc" column="Rule_desc"/>
/resultMap>


--- [hidden email] wrote:

From: deepakl_2000 <[hidden email]>
To: [hidden email]
Subject: Issue with join of 2 tables in Ibatis
Date: Wed, 2 Dec 2009 09:30:06 -0800 (PST)


Hi,

    I have to fetch a list from 2 tables based on below condition.

 

[code]

-------------------

Service class

-------------------

fileParameters.put("Project_Code",projectCode);

fileParameters.put("Files_ID",filePk);

responseFileData =
(List)sqlMap.queryForList("getFileResponses",fileParameters);

 

-------------------------------------------------------
query.xml

<parameterMap id="FileDetails" class="java.util.Map" >                                              
                <parameter property="Project_Code" jdbcType="VARCHAR" />
                <parameter property="Files_ID" jdbcType="INT" />
</parameterMap>

                                 

<resultMap id="get_list" class="com.hcl.fpTool.pojo.ResponseFilesDataPOJO">
            <result property="rule_Id" column="Rule_Id"/>          
            <result property="response" column="Response"/>                        
            <result property="remarks" column="Remarks"/>                        
/resultMap>

<select id="getFileResponses"
resultClass="com.hcl.fpTool.pojo.ResponseFilesDataPOJO"
parameterMap="FileDetails" resultMap="get_list">                    

            select bres.rule_id, bstatic.rule_desc,bres.remarks,
bres.response
            from BusinessRule_ResFiles_Data bres, BusinessRule_StaticData
bstatic
            where  project_Code = ? and files_id = ?
            and bres.rule_id = bstatic.rule_id and bres.rule_id not like
('%CD%')                                        
 </select>

 

The problem lies here is
1> i need  to select "bstatic.rule_desc"  from my another table
"BusinessRule_StaticData"
2>i need to select "bres.rule_id" from my another table
"BusinessRule_ResFiles_Data"

 so i have a total select of 4 columns based on a join of 2 tables.

so please help me to correct the "query.xml" file such that i can accomplish
the selection of 4 columns and retrieve it back as a list in my service
class



--
View this message in context: http://old.nabble.com/Issue-with-join-of-2-tables-in-Ibatis-tp26612861p26612861.html
Sent from the iBATIS - Dev mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Issue with join of 2 tables in Ibatis

deepakl_2000
Hi jhking,
           Im sorry to say that "rule_desc" is not a property of ResponseFilesDataPOJO.it is a property of some other class,please dont get confused...

1> bstatic.rule_desc is coming from  another table "BusinessRule_StaticData bstatic"
2> bres.rule_id,bres.remarks,bres.response is coming from another table " BusinessRule_ResFiles_Data bres"

Then i perform a join as shown below.

            select bres.rule_id, bstatic.rule_desc,bres.remarks,bres.response
            from BusinessRule_ResFiles_Data bres, BusinessRule_StaticData bstatic
            where  project_Code = ? and files_id = ?
            and bres.rule_id = bstatic.rule_id and bres.rule_id not like ('%CD%')  

public class StaticDataPOJO {
        private String rule_Desc;
            //getters and setters
}


class ResponseFilesDataPOJO{
    private String rule_id;
    private String response;
    private String remarks;
    // constructor , getters , setters
}

please help me now.....
how should i change my query.xml file to get the correct fetch.....could you please modify my code below and let me know how to proceed

-------------------------------------------------------
query.xml

<parameterMap id="FileDetails" class="java.util.Map" >                                               
                <parameter property="Project_Code" jdbcType="VARCHAR" />
                <parameter property="Files_ID" jdbcType="INT" />
</parameterMap>

                                 

<resultMap id="get_list" class="com.hcl.fpTool.pojo.ResponseFilesDataPOJO">
            <result property="rule_Id" column="Rule_Id"/>           
            <result property="response" column="Response"/>                       
            <result property="remarks" column="Remarks"/>                       
/resultMap>

<select id="getFileResponses"  resultClass="com.hcl.fpTool.pojo.ResponseFilesDataPOJO" parameterMap="FileDetails" resultMap="get_list">                   
           select bres.rule_id, bstatic.rule_desc,bres.remarks, bres.response
           from BusinessRule_ResFiles_Data bres, BusinessRule_StaticData bstatic
           where  project_Code = ? and files_id = ?
           and bres.rule_id = bstatic.rule_id and bres.rule_id not like ('%CD%')                                        
</select>


Please need help forum people..im stuck with this for quite a while some time....
i will be highly thankful to you guys if could help me with this.....

Thanks in advance for your quick replies...



jhking wrote
Your query gets the 4 columns you need from the appropriate tables.
You just need to change your resultmap and ResponseFilesDataPOJO to have all 4 values.

class ResponseFilesDataPOJO{
    private String rule_id;
    private String response;
    private String remarks;
    private String rule_desc;  
// constructor , getters , setters
}
<resultMap id="get_list" class="com.hcl.fpTool.pojo.ResponseFilesDataPOJO">
            <result property="rule_Id" column="Rule_Id"/>           
            <result property="response" column="Response"/>                       
            <result property="remarks" column="Remarks"/>                       
            <result property="rule_desc" column="Rule_desc"/>
/resultMap>


--- deepakl_2000@yahoo.com wrote:

From: deepakl_2000 <deepakl_2000@yahoo.com>
To: dev@ibatis.apache.org
Subject: Issue with join of 2 tables in Ibatis
Date: Wed, 2 Dec 2009 09:30:06 -0800 (PST)


Hi,

    I have to fetch a list from 2 tables based on below condition.

 

[code]

-------------------

Service class

-------------------

fileParameters.put("Project_Code",projectCode);

fileParameters.put("Files_ID",filePk);

responseFileData =
(List)sqlMap.queryForList("getFileResponses",fileParameters);

 

-------------------------------------------------------
query.xml

<parameterMap id="FileDetails" class="java.util.Map" >                                               
                <parameter property="Project_Code" jdbcType="VARCHAR" />
                <parameter property="Files_ID" jdbcType="INT" />
</parameterMap>

                                 

<resultMap id="get_list" class="com.hcl.fpTool.pojo.ResponseFilesDataPOJO">
            <result property="rule_Id" column="Rule_Id"/>           
            <result property="response" column="Response"/>                       
            <result property="remarks" column="Remarks"/>                       
/resultMap>

<select id="getFileResponses"
resultClass="com.hcl.fpTool.pojo.ResponseFilesDataPOJO"
parameterMap="FileDetails" resultMap="get_list">                   

            select bres.rule_id, bstatic.rule_desc,bres.remarks,
bres.response
            from BusinessRule_ResFiles_Data bres, BusinessRule_StaticData
bstatic
            where  project_Code = ? and files_id = ?
            and bres.rule_id = bstatic.rule_id and bres.rule_id not like
('%CD%')                                        
 </select>

 

The problem lies here is
1> i need  to select "bstatic.rule_desc"  from my another table
"BusinessRule_StaticData"
2>i need to select "bres.rule_id" from my another table
"BusinessRule_ResFiles_Data"

 so i have a total select of 4 columns based on a join of 2 tables.

so please help me to correct the "query.xml" file such that i can accomplish
the selection of 4 columns and retrieve it back as a list in my service
class



--
View this message in context: http://old.nabble.com/Issue-with-join-of-2-tables-in-Ibatis-tp26612861p26612861.html
Sent from the iBATIS - Dev mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@ibatis.apache.org
For additional commands, e-mail: dev-help@ibatis.apache.org




---------------------------------------------------------------------
To unsubscribe, e-mail: dev-unsubscribe@ibatis.apache.org
For additional commands, e-mail: dev-help@ibatis.apache.org
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Issue with join of 2 tables in Ibatis

jhking
Yes, someone in this discussion is confused...
I said to change the ResponseFilesDataPOJO class to have that attribute.
You can't have a single select populate 2 resultmaps so if you want the extra column(s) you'll need them in the class.
If, for some reason, you can't change ResponseFilesDataPOJO then you'll need another class with all the column values you want back or write two queries (and have a 2nd class to get the results of the 2nd query).
It would seem add a new property to the existing class would be easiest all around.
deepakl_2000 wrote:
Hi jhking,
           Im sorry to say that "rule_desc" is not a property of
ResponseFilesDataPOJO.it is a property of some other class,please dont get
confused...

1> bstatic.rule_desc is coming from  another table "BusinessRule_StaticData
bstatic"
2> bres.rule_id,bres.remarks,bres.response is coming from another table "
BusinessRule_ResFiles_Data bres"

Then i perform a join as shown below.

            select bres.rule_id,
bstatic.rule_desc,bres.remarks,bres.response 
            from BusinessRule_ResFiles_Data bres, BusinessRule_StaticData
bstatic
            where  project_Code = ? and files_id = ?
            and bres.rule_id = bstatic.rule_id and bres.rule_id not like
('%CD%')   

public class StaticDataPOJO {
	private String rule_Desc;
            //getters and setters
}


class ResponseFilesDataPOJO{
    private String rule_id;
    private String response;
    private String remarks;
    // constructor , getters , setters
}

please help me now.....
how should i change my query.xml file to get the correct fetch.....could you
please modify my code below and let me know how to proceed

-------------------------------------------------------
query.xml

<parameterMap id="FileDetails" class="java.util.Map" >                                               
                <parameter property="Project_Code" jdbcType="VARCHAR" />
                <parameter property="Files_ID" jdbcType="INT" />
</parameterMap>

                                 

<resultMap id="get_list" class="com.hcl.fpTool.pojo.ResponseFilesDataPOJO">
            <result property="rule_Id" column="Rule_Id"/>           
            <result property="response" column="Response"/>                        
            <result property="remarks" column="Remarks"/>                        
/resultMap>

<select id="getFileResponses" 
resultClass="com.hcl.fpTool.pojo.ResponseFilesDataPOJO"
parameterMap="FileDetails" resultMap="get_list">                    
           select bres.rule_id, bstatic.rule_desc,bres.remarks,
bres.response 
           from BusinessRule_ResFiles_Data bres, BusinessRule_StaticData
bstatic
           where  project_Code = ? and files_id = ?
           and bres.rule_id = bstatic.rule_id and bres.rule_id not like
('%CD%')                                        
</select>


Please need help forum people..im stuck with this for quite a while some
time....
i will be highly thankful to you guys if could help me with this.....

Thanks in advance for your quick replies...

:confused:


jhking wrote:
  
Your query gets the 4 columns you need from the appropriate tables.
You just need to change your resultmap and ResponseFilesDataPOJO to have
all 4 values.

class ResponseFilesDataPOJO{
    private String rule_id;
    private String response;
    private String remarks;
    private String rule_desc;   
// constructor , getters , setters
}
<resultMap id="get_list"
class="com.hcl.fpTool.pojo.ResponseFilesDataPOJO">
            <result property="rule_Id" column="Rule_Id"/>           
            <result property="response" column="Response"/>                        
            <result property="remarks" column="Remarks"/>                        
            <result property="rule_desc" column="Rule_desc"/>
/resultMap>


--- [hidden email] wrote:

From: deepakl_2000 [hidden email]
To: [hidden email]
Subject: Issue with join of 2 tables in Ibatis
Date: Wed, 2 Dec 2009 09:30:06 -0800 (PST)


Hi,

    I have to fetch a list from 2 tables based on below condition.

 

[code]

-------------------

Service class

-------------------

fileParameters.put("Project_Code",projectCode);

fileParameters.put("Files_ID",filePk);

responseFileData =
(List)sqlMap.queryForList("getFileResponses",fileParameters);

 

-------------------------------------------------------
query.xml

<parameterMap id="FileDetails" class="java.util.Map" >                                               
                <parameter property="Project_Code" jdbcType="VARCHAR" />
                <parameter property="Files_ID" jdbcType="INT" />
</parameterMap>

                                 

<resultMap id="get_list"
class="com.hcl.fpTool.pojo.ResponseFilesDataPOJO">
            <result property="rule_Id" column="Rule_Id"/>           
            <result property="response" column="Response"/>                        
            <result property="remarks" column="Remarks"/>                        
/resultMap>

<select id="getFileResponses"
resultClass="com.hcl.fpTool.pojo.ResponseFilesDataPOJO"
parameterMap="FileDetails" resultMap="get_list">                    

            select bres.rule_id, bstatic.rule_desc,bres.remarks,
bres.response 
            from BusinessRule_ResFiles_Data bres, BusinessRule_StaticData
bstatic
            where  project_Code = ? and files_id = ?
            and bres.rule_id = bstatic.rule_id and bres.rule_id not like
('%CD%')                                        
 </select>

 

The problem lies here is 
1> i need  to select "bstatic.rule_desc"  from my another table
"BusinessRule_StaticData"
2>i need to select "bres.rule_id" from my another table
"BusinessRule_ResFiles_Data"

 so i have a total select of 4 columns based on a join of 2 tables.

so please help me to correct the "query.xml" file such that i can
accomplish
the selection of 4 columns and retrieve it back as a list in my service
class



-- 
View this message in context:
http://old.nabble.com/Issue-with-join-of-2-tables-in-Ibatis-tp26612861p26612861.html
Sent from the iBATIS - Dev mailing list archive at Nabble.com.


---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]




---------------------------------------------------------------------
To unsubscribe, e-mail: [hidden email]
For additional commands, e-mail: [hidden email]



    

  

Loading...