select and inner join with two identical tables

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

select and inner join with two identical tables

Fabrizio Starred
I have this select:

Select
 a
.cod1,
 a
.cod2,
 b1
.description,
 b2
.description
from a
inner join
 b
as b1
on
 a
.cod1 = b1.cod
inner join
 b
as b2
on
 a
.cod2 = b2.cod

This is my conceptual bean:

Bean A
{
 property b1
;
 property b2
;
}


The select above works on myBatis goes without error, but I can't map my bean properly with the resultMap.

I have tried with:

<association property="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

But property b1 and b2 are null at result.
How can I solve this?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: select and inner join with two identical tables

Ray Sprinkle

You must name the columns differently.  Use can use he same mappings if you use the prefix attribute of the resultmap.

On Jan 19, 2015 6:40 AM, "Fabrizio Starred" <[hidden email]> wrote:
I have this select:

Select
 a
.cod1,
 a
.cod2,
 b1
.description,
 b2
.description
from a
inner join
 b
as b1
on
 a
.cod1 = b1.cod
inner join
 b
as b2
on
 a
.cod2 = b2.cod

This is my conceptual bean:

Bean A
{
 property b1
;
 property b2
;
}


The select above works on myBatis goes without error, but I can't map my bean properly with the resultMap.

I have tried with:

<association property="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

But property b1 and b2 are null at result.
How can I solve this?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: select and inner join with two identical tables

Fabrizio Starred
Sorry, I have tried with 

<association property="b1"
 
columnPrefix="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
columnPrefix="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

but property is still null :(


On Monday, January 19, 2015 at 1:13:47 PM UTC+1, rds wrote:

You must name the columns differently.  Use can use he same mappings if you use the prefix attribute of the resultmap.

On Jan 19, 2015 6:40 AM, "Fabrizio Starred" <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="BYHkiBCieagJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">fabrizio...@...> wrote:
I have this select:

Select
 a
.cod1,
 a
.cod2,
 b1
.description,
 b2
.description
from a
inner join
 b
as b1
on
 a
.cod1 = b1.cod
inner join
 b
as b2
on
 a
.cod2 = b2.cod

This is my conceptual bean:

Bean A
{
 property b1
;
 property b2
;
}


The select above works on myBatis goes without error, but I can't map my bean properly with the resultMap.

I have tried with:

<association property="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

But property b1 and b2 are null at result.
How can I solve this?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="BYHkiBCieagJ" rel="nofollow" onmousedown="this.href='javascript:';return true;" onclick="this.href='javascript:';return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: select and inner join with two identical tables

Fabrizio Starred
After restarting the server, I've got another problem:

The properties are now filled but with b2 has the same value of b1, that is not true in database.

Maybe I have to play with column and foreignColumn in same way?

On Monday, January 19, 2015 at 2:21:36 PM UTC+1, Fabrizio Starred wrote:
Sorry, I have tried with 

<association property="b1"
 
columnPrefix="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
columnPrefix="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

but property is still null :(


On Monday, January 19, 2015 at 1:13:47 PM UTC+1, rds wrote:

You must name the columns differently.  Use can use he same mappings if you use the prefix attribute of the resultmap.

On Jan 19, 2015 6:40 AM, "Fabrizio Starred" <[hidden email]> wrote:
I have this select:

Select
 a
.cod1,
 a
.cod2,
 b1
.description,
 b2
.description
from a
inner join
 b
as b1
on
 a
.cod1 = b1.cod
inner join
 b
as b2
on
 a
.cod2 = b2.cod

This is my conceptual bean:

Bean A
{
 property b1
;
 property b2
;
}


The select above works on myBatis goes without error, but I can't map my bean properly with the resultMap.

I have tried with:

<association property="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

But property b1 and b2 are null at result.
How can I solve this?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: select and inner join with two identical tables

Fabrizio Starred
In reply to this post by Fabrizio Starred
After restarting the server, I've got another problem:

The properties are now filled but with b2 has the same value of b1, that is not true in database.

Maybe I have to play with column and foreignColumn in some way?

On Monday, January 19, 2015 at 2:21:36 PM UTC+1, Fabrizio Starred wrote:
Sorry, I have tried with 

<association property="b1"
 
columnPrefix="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
columnPrefix="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

but property is still null :(


On Monday, January 19, 2015 at 1:13:47 PM UTC+1, rds wrote:

You must name the columns differently.  Use can use he same mappings if you use the prefix attribute of the resultmap.

On Jan 19, 2015 6:40 AM, "Fabrizio Starred" <[hidden email]> wrote:
I have this select:

Select
 a
.cod1,
 a
.cod2,
 b1
.description,
 b2
.description
from a
inner join
 b
as b1
on
 a
.cod1 = b1.cod
inner join
 b
as b2
on
 a
.cod2 = b2.cod

This is my conceptual bean:

Bean A
{
 property b1
;
 property b2
;
}


The select above works on myBatis goes without error, but I can't map my bean properly with the resultMap.

I have tried with:

<association property="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

But property b1 and b2 are null at result.
How can I solve this?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" rel="nofollow" target="_blank" onmousedown="this.href='https://groups.google.com/d/optout';return true;" onclick="this.href='https://groups.google.com/d/optout';return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: select and inner join with two identical tables

Ray Sprinkle
In reply to this post by Fabrizio Starred
The table alias is not returned in a JDBC result.   You need something
along the lines of:

 >         Select
 >           a.cod1,
 >           a.cod2,
 >           b1.description as b1_description,
 >           b2.description as b2_description
 >         from a
 >         inner join
 >           b asb1
 >         on
 >           a.cod1 =b1.cod
 >         inner join
 >           b asb2
 >         on
 >           a.cod2 =b2.cod

<associationproperty="b1"
  columnPrefix="b1_"
  javaType="package.B"
  resultMap="package.B.BMapper.BaseResultMap"/>

On 1/19/2015 8:21 AM, Fabrizio Starred wrote:

> Sorry, I have tried with
>
> |
> <associationproperty="b1"
> columnPrefix="b1"
> javaType="package.B"
> resultMap="package.B.BMapper.BaseResultMap"/>
>
> <associationproperty="b2"
> columnPrefix="b2"
> javaType="package.B"
> resultMap="package.B.BMapper.BaseResultMap"/>
> |
>
> but property is still null :(
>
>
> On Monday, January 19, 2015 at 1:13:47 PM UTC+1, rds wrote:
>
>     You must name the columns differently.  Use can use he same mappings
>     if you use the prefix attribute of the resultmap.
>
>     On Jan 19, 2015 6:40 AM, "Fabrizio Starred" <[hidden email]
>     <javascript:>> wrote:
>
>         I have this select:
>
>         |
>         Select
>           a.cod1,
>           a.cod2,
>           b1.description,
>           b2.description
>         froma
>         inner join
>           b asb1
>         on
>           a.cod1 =b1.cod
>         inner join
>           b asb2
>         on
>           a.cod2 =b2.cod
>         |
>
>         This is my conceptual bean:
>
>         |
>         BeanA
>         {
>           property b1;
>           property b2;
>         }
>         |
>
>
>         The select above works on myBatis goes without error, but I
>         can't map my bean properly with the resultMap.
>
>         I have tried with:
>
>         |
>         <associationproperty="b1"
>         javaType="package.B"
>         resultMap="package.B.BMapper.BaseResultMap"/>
>
>         <associationproperty="b2"
>         javaType="package.B"
>         resultMap="package.B.BMapper.BaseResultMap"/>
>         |
>
>         But property b1 and b2 are null at result.
>         How can I solve this?
>
>         --
>         You received this message because you are subscribed to the
>         Google Groups "mybatis-user" group.
>         To unsubscribe from this group and stop receiving emails from
>         it, send an email to [hidden email] <javascript:>.
>         For more options, visit https://groups.google.com/d/optout
>         <https://groups.google.com/d/optout>.
>
> --
> You received this message because you are subscribed to the Google
> Groups "mybatis-user" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to [hidden email]
> <mailto:[hidden email]>.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: select and inner join with two identical tables

Fabrizio Starred
In reply to this post by Fabrizio Starred
I'll try to expose data in a more verbose way:

These are my two tables:

----------
Person
----------
id | name | skill1_id | skill2_id | skill3_id
1 John M C L


----------
Skill
----------
id | desc
M Mathematics
C Chemistry
L Language


This is the select:

select
 name
,
 sk_a
.id as skill1_id,
 sk_a
.desc as skill1_desc,
 sk_b
.id as skill2_id,
 sk_b
.desc as skill2_desc,
from
 
Person
inner join
 
Skill as sk_a
on
 
Person.skill1_id = sk_a.id
inner join
 
Skill as sk_b
on
 
Person.skill2_id = sk_b.id




this is the result on mybatis trace

John | M | Mathematics | C | Chemistry


This is exactly how it should be.

Now this is my custom PersonExt bean

class PersonExt extend Person
{
 
Skill skill1;
 
Skill skill2;
 
 
// get and set methods for each bean's property
}



and Skill bean

class Skill
{
 id
;
 desc
;
 
 
// get and set methods for each bean's property
}


Finally, this is my mapper:

<resultMap id="PersonCustomMap" type="model.PersonExt" extends="dao.PersonMapper.BaseResultMap">
 
 
<association property="skill1"
 
columnPrefix="skill1_"
 
javaType="model.Skill"
 
resultMap="dao.SkillMapper.BaseResultMap" >
 
<id property="id" column="skill1_id" />
 
<result property="desc" column="skill1_desc" />
 
</association>
 
 
<association property="skill2"
 
columnPrefix="skill2_"
 
javaType="model.Skill"
 
resultMap="dao.SkillMapper.BaseResultMap" >
 
<id property="id" column="skill2_id" />
 
<result property="desc" column="skill2_desc" />
 
</association>
 
</resultMap>



Either skill1 and skill2 properties of PersonExt bean are null.

Where am I wrong ?

Thank you

On Monday, January 19, 2015 at 12:39:59 PM UTC+1, Fabrizio Starred wrote:
I have this select:

Select
 a
.cod1,
 a
.cod2,
 b1
.description,
 b2
.description
from a
inner join
 b
as b1
on
 a
.cod1 = b1.cod
inner join
 b
as b2
on
 a
.cod2 = b2.cod

This is my conceptual bean:

Bean A
{
 property b1
;
 property b2
;
}


The select above works on myBatis goes without error, but I can't map my bean properly with the resultMap.

I have tried with:

<association property="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

But property b1 and b2 are null at result.
How can I solve this?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: select and inner join with two identical tables

Fabrizio Starred
In reply to this post by Fabrizio Starred
Ok, I have finally solved because I figured out how columnPrefix really works.

the alias name of the column in the select be composed with "yourprefix"_"real column name" of the table.

I insted used a fake column name so the mapper didn't wrap it.

Thanks :)

On Monday, January 19, 2015 at 12:39:59 PM UTC+1, Fabrizio Starred wrote:
I have this select:

Select
 a
.cod1,
 a
.cod2,
 b1
.description,
 b2
.description
from a
inner join
 b
as b1
on
 a
.cod1 = b1.cod
inner join
 b
as b2
on
 a
.cod2 = b2.cod

This is my conceptual bean:

Bean A
{
 property b1
;
 property b2
;
}


The select above works on myBatis goes without error, but I can't map my bean properly with the resultMap.

I have tried with:

<association property="b1"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />
 
<association property="b2"
 
javaType="package.B"
 
resultMap="package.B.BMapper.BaseResultMap" />

But property b1 and b2 are null at result.
How can I solve this?

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.