Quantcast

Dynamic SQL: Filter columns returned from collection(Chidl resultMap) in resultMap

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

Dynamic SQL: Filter columns returned from collection(Chidl resultMap) in resultMap

Ladislav Jech
Hi guys,

I have a scenario where i would like to use dynamic SQL parameters inside CHILD select statement.

MASTER result map is defined in simplified version like this:
<resultMap id="BookingResultMap" type="Booking">
<id column="GREC_SEQ_REC" jdbcType="INTEGER" property="id.incomingOfficeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<result column="GEMP_COD_EMP" jdbcType="VARCHAR" property="company" />
<result column="GTTO_SEQ_TTOO" jdbcType="INTEGER" property="clientId" />
   <collection property="dynamicRemarks" ofType="DynamicRemark" 
column="{incomingOfficeId=GREC_SEQ_REC,bookingNumber=SEQ_RESERVA} " select="findRemarksForBooking" />
</resultMap>

MASTER select statement (I ommit the full column list specification):
<select id="getAll" resultMap="BookingResultMap" >
select /*+ FIRST_ROWS(100)*/
<include refid="Booking_Base_Column_List"/>
from RE_T_RE_RESERVA R, RE_T_GE_TTOO T, RE_T_GE_RECEPTIVO RECEP, RE_T_RE_RESERVA_R2 R2
where T.SEQ_TTOO = R.GTTO_SEQ_TTOO
and RECEP.SEQ_REC = NVL(R.SEQ_REC_HBEDS, R.GREC_SEQ_REC)
and R2.SEQ_REC = R.GREC_SEQ_REC
and R2.SEQ_RESERVA = R.SEQ_RESERVA
</select>

For the collection defined in MASTER result map I have following select statement and another (CHILD) result map defined:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark">
<id column="SEQ_REC" jdbcType="INTEGER" property="id.officeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<id column="COD_OBSERVACION" jdbcType="VARCHAR" property="id.remarksCodeId" />
<result column="OBSERVACION" jdbcType="VARCHAR" property="remarks" />
</resultMap>

<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</select>

Now, i would like to filter some columns with Dynamic SQL(the statements above do not reflect it yet), so I will do something like this:

Map<String, Object> parms = new HashMap<String, Object>();
parms.put("name", "abc);

List<Remarks> list = myBatis.selectList("BookingResultMap",params);

But I would like to use the parameter inside the child result map SQL statement, how to do this correctly and is it even supported? My intention is to extend the MASTER select by this:
<select id="getAll" resultMap="BookingResultMap" parameterType="map">

I expect to second select to be required to be extended in similar way if the parameter should be passed from parent(MASTER) to child (CHILD) select by this:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark" parameterType="map">

And then I would like to do something in CHILD select like this:
<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
  <choose>
<when test="name = "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<when test="name != "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<otherwise>
select '','','' from dual
</otherwise>
  </choose>
</select>

Is something like this possible?

Thanks for suggestions.

Ladislav

--
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
|  
Report Content as Inappropriate

Re: Dynamic SQL: Filter columns returned from collection(Chidl resultMap) in resultMap

Ladislav Jech
So, this is not possible, MyBatis doesn't support passing parameters from parent to child SQL statements, but you can pass a class with static method or static field(constant) like following:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.MyBatisDataMapper">
<resultMap id="ParentDataResultMap" type="ParentData">
<id column="PARENT_KEY" jdbcType="VARCHAR" property="parentKey"/>
<result column="PARENT_DATA" jdbcType="VARCHAR" property="parentData"/>
<collection property="childData" ofType="ChildData" javaType="ArrayList"
column="{childKey=CHILD_KEY,parentKey=PARENT_KEY,childData=CHILD_DATA} " select="findChildData"/>
</resultMap>
<resultMap id="ChildDataResultMap" type="ChildData">
<id column="CHILD_KEY" jdbcType="VARCHAR" property="childKey"/>
<id column="PARENT_KEY" jdbcType="VARCHAR" property="parentKey"/>
<result column="CHILD_DATA" jdbcType="VARCHAR" property="childData"/>
</resultMap>

<select id="findChildData" resultMap="ChildDataResultMap">
select * from (values
<!-- THIS DOESN'T WORKk
<if test="filter == 'FIRST'">('CHILD_KEY_1','PARENT_KEY_1','CHILD_DATA_1')</if>
<if test="filter == 'SECOND'">('CHILD_KEY_2','PARENT_KEY_2','CHILD_DATA_2')</if>
-->

<!-- THIS WORKS - but I would like to submit custom dynamic object instead of constant type -->
<if test="@org.Decision@getString() == 'FIRST'">('CHILD_KEY_1','PARENT_KEY_1','CHILD_DATA_1')</if>
<if test="@org.Decision@getString() == 'SECOND'">('CHILD_KEY_2','PARENT_KEY_2','CHILD_DATA_2')</if>
) s(CHILD_KEY,PARENT_KEY,CHILD_DATA)
</select>
<select id="getAllData" resultMap="ParentDataResultMap">
select * from ( values
<if test="filter == 'FIRST'">('PARENT_KEY_1','PARENT_DATA_1','CHILD_KEY_1','CHILD_DATA_1')</if>
<if test="filter == 'SECOND'">('PARENT_KEY_2','PARENT_DATA_2','CHILD_KEY_2','BETA')</if>
) s(PARENT_KEY, PARENT_DATA,CHILD_KEY,CHILD_DATA)
</select>
</mapper>

So using static access is the possible way.

Ladislav

Dne čtvrtek 3. listopadu 2016 14:56:29 UTC+1 Ladislav Jech napsal(a):
Hi guys,

I have a scenario where i would like to use dynamic SQL parameters inside CHILD select statement.

MASTER result map is defined in simplified version like this:
<resultMap id="BookingResultMap" type="Booking">
<id column="GREC_SEQ_REC" jdbcType="INTEGER" property="id.incomingOfficeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<result column="GEMP_COD_EMP" jdbcType="VARCHAR" property="company" />
<result column="GTTO_SEQ_TTOO" jdbcType="INTEGER" property="clientId" />
   <collection property="dynamicRemarks" ofType="DynamicRemark" 
column="{incomingOfficeId=GREC_SEQ_REC,bookingNumber=SEQ_RESERVA} " select="findRemarksForBooking" />
</resultMap>

MASTER select statement (I ommit the full column list specification):
<select id="getAll" resultMap="BookingResultMap" >
select /*+ FIRST_ROWS(100)*/
<include refid="Booking_Base_Column_List"/>
from RE_T_RE_RESERVA R, RE_T_GE_TTOO T, RE_T_GE_RECEPTIVO RECEP, RE_T_RE_RESERVA_R2 R2
where T.SEQ_TTOO = R.GTTO_SEQ_TTOO
and RECEP.SEQ_REC = NVL(R.SEQ_REC_HBEDS, R.GREC_SEQ_REC)
and R2.SEQ_REC = R.GREC_SEQ_REC
and R2.SEQ_RESERVA = R.SEQ_RESERVA
</select>

For the collection defined in MASTER result map I have following select statement and another (CHILD) result map defined:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark">
<id column="SEQ_REC" jdbcType="INTEGER" property="id.officeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<id column="COD_OBSERVACION" jdbcType="VARCHAR" property="id.remarksCodeId" />
<result column="OBSERVACION" jdbcType="VARCHAR" property="remarks" />
</resultMap>

<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</select>

Now, i would like to filter some columns with Dynamic SQL(the statements above do not reflect it yet), so I will do something like this:

Map<String, Object> parms = new HashMap<String, Object>();
parms.put("name", "abc);

List<Remarks> list = myBatis.selectList("BookingResultMap",params);

But I would like to use the parameter inside the child result map SQL statement, how to do this correctly and is it even supported? My intention is to extend the MASTER select by this:
<select id="getAll" resultMap="BookingResultMap" parameterType="map">

I expect to second select to be required to be extended in similar way if the parameter should be passed from parent(MASTER) to child (CHILD) select by this:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark" parameterType="map">

And then I would like to do something in CHILD select like this:
<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
  <choose>
<when test="name = "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<when test="name != "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<otherwise>
select '','','' from dual
</otherwise>
  </choose>
</select>

Is something like this possible?

Thanks for suggestions.

Ladislav

--
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
|  
Report Content as Inappropriate

Re: Dynamic SQL: Filter columns returned from collection(Chidl resultMap) in resultMap

Ladislav Jech
Still this is not solving my solution as I will need dynamic(instance) non static variables to be passed. It looks MyBatis lack such required functionality in the moment.

Ladislav

Dne středa 9. listopadu 2016 11:23:54 UTC+1 Ladislav Jech napsal(a):
So, this is not possible, MyBatis doesn't support passing parameters from parent to child SQL statements, but you can pass a class with static method or static field(constant) like following:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//<a href="http://mybatis.org//DTD" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2F%2FDTD\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHjp-E8UL0TmmS7A0V54yMfbtfEkA&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2F%2FDTD\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHjp-E8UL0TmmS7A0V54yMfbtfEkA&#39;;return true;">mybatis.org//DTD Mapper 3.0//EN" "<a href="http://mybatis.org/dtd/mybatis-3-mapper.dtd" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2Fdtd%2Fmybatis-3-mapper.dtd\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFpJ-PGZSCiEykIm86cAd_bUTvMAQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2Fdtd%2Fmybatis-3-mapper.dtd\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFpJ-PGZSCiEykIm86cAd_bUTvMAQ&#39;;return true;">http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.MyBatisDataMapper">
<resultMap id="ParentDataResultMap" type="ParentData">
<id column="PARENT_KEY" jdbcType="VARCHAR" property="parentKey"/>
<result column="PARENT_DATA" jdbcType="VARCHAR" property="parentData"/>
<collection property="childData" ofType="ChildData" javaType="ArrayList"
column="{childKey=CHILD_KEY,parentKey=PARENT_KEY,childData=CHILD_DATA} " select="findChildData"/>
</resultMap>
<resultMap id="ChildDataResultMap" type="ChildData">
<id column="CHILD_KEY" jdbcType="VARCHAR" property="childKey"/>
<id column="PARENT_KEY" jdbcType="VARCHAR" property="parentKey"/>
<result column="CHILD_DATA" jdbcType="VARCHAR" property="childData"/>
</resultMap>

<select id="findChildData" resultMap="ChildDataResultMap">
select * from (values
<!-- THIS DOESN'T WORKk
<if test="filter == 'FIRST'">('CHILD_KEY_1','PARENT_KEY_1','CHILD_DATA_1')</if>
<if test="filter == 'SECOND'">('CHILD_KEY_2','PARENT_KEY_2','CHILD_DATA_2')</if>
-->

<!-- THIS WORKS - but I would like to submit custom dynamic object instead of constant type -->
<if test="@org.Decision@getString() == 'FIRST'">('CHILD_KEY_1','PARENT_KEY_1','CHILD_DATA_1')</if>
<if test="@org.Decision@getString() == 'SECOND'">('CHILD_KEY_2','PARENT_KEY_2','CHILD_DATA_2')</if>
) s(CHILD_KEY,PARENT_KEY,CHILD_DATA)
</select>
<select id="getAllData" resultMap="ParentDataResultMap">
select * from ( values
<if test="filter == 'FIRST'">('PARENT_KEY_1','PARENT_DATA_1','CHILD_KEY_1','CHILD_DATA_1')</if>
<if test="filter == 'SECOND'">('PARENT_KEY_2','PARENT_DATA_2','CHILD_KEY_2','BETA')</if>
) s(PARENT_KEY, PARENT_DATA,CHILD_KEY,CHILD_DATA)
</select>
</mapper>

So using static access is the possible way.

Ladislav

Dne čtvrtek 3. listopadu 2016 14:56:29 UTC+1 Ladislav Jech napsal(a):
Hi guys,

I have a scenario where i would like to use dynamic SQL parameters inside CHILD select statement.

MASTER result map is defined in simplified version like this:
<resultMap id="BookingResultMap" type="Booking">
<id column="GREC_SEQ_REC" jdbcType="INTEGER" property="id.incomingOfficeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<result column="GEMP_COD_EMP" jdbcType="VARCHAR" property="company" />
<result column="GTTO_SEQ_TTOO" jdbcType="INTEGER" property="clientId" />
   <collection property="dynamicRemarks" ofType="DynamicRemark" 
column="{incomingOfficeId=GREC_SEQ_REC,bookingNumber=SEQ_RESERVA} " select="findRemarksForBooking" />
</resultMap>

MASTER select statement (I ommit the full column list specification):
<select id="getAll" resultMap="BookingResultMap" >
select /*+ FIRST_ROWS(100)*/
<include refid="Booking_Base_Column_List"/>
from RE_T_RE_RESERVA R, RE_T_GE_TTOO T, RE_T_GE_RECEPTIVO RECEP, RE_T_RE_RESERVA_R2 R2
where T.SEQ_TTOO = R.GTTO_SEQ_TTOO
and RECEP.SEQ_REC = NVL(R.SEQ_REC_HBEDS, R.GREC_SEQ_REC)
and R2.SEQ_REC = R.GREC_SEQ_REC
and R2.SEQ_RESERVA = R.SEQ_RESERVA
</select>

For the collection defined in MASTER result map I have following select statement and another (CHILD) result map defined:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark">
<id column="SEQ_REC" jdbcType="INTEGER" property="id.officeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<id column="COD_OBSERVACION" jdbcType="VARCHAR" property="id.remarksCodeId" />
<result column="OBSERVACION" jdbcType="VARCHAR" property="remarks" />
</resultMap>

<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</select>

Now, i would like to filter some columns with Dynamic SQL(the statements above do not reflect it yet), so I will do something like this:

Map<String, Object> parms = new HashMap<String, Object>();
parms.put("name", "abc);

List<Remarks> list = myBatis.selectList("BookingResultMap",params);

But I would like to use the parameter inside the child result map SQL statement, how to do this correctly and is it even supported? My intention is to extend the MASTER select by this:
<select id="getAll" resultMap="BookingResultMap" parameterType="map">

I expect to second select to be required to be extended in similar way if the parameter should be passed from parent(MASTER) to child (CHILD) select by this:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark" parameterType="map">

And then I would like to do something in CHILD select like this:
<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
  <choose>
<when test="name = "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<when test="name != "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<otherwise>
select '','','' from dual
</otherwise>
  </choose>
</select>

Is something like this possible?

Thanks for suggestions.

Ladislav

--
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
|  
Report Content as Inappropriate

Re: Dynamic SQL: Filter columns returned from collection(Chidl resultMap) in resultMap

Iwao AVE!
Hi Ladislav,

Yes, it is not possible to access parent query's parameter from within a nested query.
Your options would be...

1) Instead of a nested select, use a nested result map with a JOINed select.
2) Use multiple result sets [1] if your DB supports it.
3) Add the necessary parameter to the parent query as a constant column (i.e. select *, #{filter} as filter from ...). Then you can pass it via the 'column' attribute of the collection element.
4) Execute these queries separately and merge the results in Java code.


Hope this helps,
Iwao

2016-11-10 1:46 GMT+09:00 Ladislav Jech <[hidden email]>:
Still this is not solving my solution as I will need dynamic(instance) non static variables to be passed. It looks MyBatis lack such required functionality in the moment.

Ladislav

Dne středa 9. listopadu 2016 11:23:54 UTC+1 Ladislav Jech napsal(a):
So, this is not possible, MyBatis doesn't support passing parameters from parent to child SQL statements, but you can pass a class with static method or static field(constant) like following:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.MyBatisDataMapper">
<resultMap id="ParentDataResultMap" type="ParentData">
<id column="PARENT_KEY" jdbcType="VARCHAR" property="parentKey"/>
<result column="PARENT_DATA" jdbcType="VARCHAR" property="parentData"/>
<collection property="childData" ofType="ChildData" javaType="ArrayList"
column="{childKey=CHILD_KEY,parentKey=PARENT_KEY,childData=CHILD_DATA} " select="findChildData"/>
</resultMap>
<resultMap id="ChildDataResultMap" type="ChildData">
<id column="CHILD_KEY" jdbcType="VARCHAR" property="childKey"/>
<id column="PARENT_KEY" jdbcType="VARCHAR" property="parentKey"/>
<result column="CHILD_DATA" jdbcType="VARCHAR" property="childData"/>
</resultMap>

<select id="findChildData" resultMap="ChildDataResultMap">
select * from (values
<!-- THIS DOESN'T WORKk
<if test="filter == 'FIRST'">('CHILD_KEY_1','PARENT_KEY_1','CHILD_DATA_1')</if>
<if test="filter == 'SECOND'">('CHILD_KEY_2','PARENT_KEY_2','CHILD_DATA_2')</if>
-->

<!-- THIS WORKS - but I would like to submit custom dynamic object instead of constant type -->
<if test="@org.Decision@getString() == 'FIRST'">('CHILD_KEY_1','PARENT_KEY_1','CHILD_DATA_1')</if>
<if test="@org.Decision@getString() == 'SECOND'">('CHILD_KEY_2','PARENT_KEY_2','CHILD_DATA_2')</if>
) s(CHILD_KEY,PARENT_KEY,CHILD_DATA)
</select>
<select id="getAllData" resultMap="ParentDataResultMap">
select * from ( values
<if test="filter == 'FIRST'">('PARENT_KEY_1','PARENT_DATA_1','CHILD_KEY_1','CHILD_DATA_1')</if>
<if test="filter == 'SECOND'">('PARENT_KEY_2','PARENT_DATA_2','CHILD_KEY_2','BETA')</if>
) s(PARENT_KEY, PARENT_DATA,CHILD_KEY,CHILD_DATA)
</select>
</mapper>

So using static access is the possible way.

Ladislav

Dne čtvrtek 3. listopadu 2016 14:56:29 UTC+1 Ladislav Jech napsal(a):
Hi guys,

I have a scenario where i would like to use dynamic SQL parameters inside CHILD select statement.

MASTER result map is defined in simplified version like this:
<resultMap id="BookingResultMap" type="Booking">
<id column="GREC_SEQ_REC" jdbcType="INTEGER" property="id.incomingOfficeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<result column="GEMP_COD_EMP" jdbcType="VARCHAR" property="company" />
<result column="GTTO_SEQ_TTOO" jdbcType="INTEGER" property="clientId" />
   <collection property="dynamicRemarks" ofType="DynamicRemark" 
column="{incomingOfficeId=GREC_SEQ_REC,bookingNumber=SEQ_RESERVA} " select="findRemarksForBooking" />
</resultMap>

MASTER select statement (I ommit the full column list specification):
<select id="getAll" resultMap="BookingResultMap" >
select /*+ FIRST_ROWS(100)*/
<include refid="Booking_Base_Column_List"/>
from RE_T_RE_RESERVA R, RE_T_GE_TTOO T, RE_T_GE_RECEPTIVO RECEP, RE_T_RE_RESERVA_R2 R2
where T.SEQ_TTOO = R.GTTO_SEQ_TTOO
and RECEP.SEQ_REC = NVL(R.SEQ_REC_HBEDS, R.GREC_SEQ_REC)
and R2.SEQ_REC = R.GREC_SEQ_REC
and R2.SEQ_RESERVA = R.SEQ_RESERVA
</select>

For the collection defined in MASTER result map I have following select statement and another (CHILD) result map defined:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark">
<id column="SEQ_REC" jdbcType="INTEGER" property="id.officeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<id column="COD_OBSERVACION" jdbcType="VARCHAR" property="id.remarksCodeId" />
<result column="OBSERVACION" jdbcType="VARCHAR" property="remarks" />
</resultMap>

<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</select>

Now, i would like to filter some columns with Dynamic SQL(the statements above do not reflect it yet), so I will do something like this:

Map<String, Object> parms = new HashMap<String, Object>();
parms.put("name", "abc);

List<Remarks> list = myBatis.selectList("BookingResultMap",params);

But I would like to use the parameter inside the child result map SQL statement, how to do this correctly and is it even supported? My intention is to extend the MASTER select by this:
<select id="getAll" resultMap="BookingResultMap" parameterType="map">

I expect to second select to be required to be extended in similar way if the parameter should be passed from parent(MASTER) to child (CHILD) select by this:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark" parameterType="map">

And then I would like to do something in CHILD select like this:
<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
  <choose>
<when test="name = "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<when test="name != "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<otherwise>
select '','','' from dual
</otherwise>
  </choose>
</select>

Is something like this possible?

Thanks for suggestions.

Ladislav


--
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
|  
Report Content as Inappropriate

Re: Dynamic SQL: Filter columns returned from collection(Chidl resultMap) in resultMap

Ladislav Jech
Hi Iwao,

thank you for your response.

To be honest, today I found one solution which will allow to get dynamic (mapper instance) data to see and be passed via static method.

I see this still more or less as workaround, but actually it works well.

The core lies in OGNL expression object visible accessible from the nested select #this which is type of DynamicContext and I can get it by following:
@org.Decision@getString(#this)

TIn this object which is type of some Map I can see joined data, so I can additionally extend the query in parent with my virtual/custom column which will keep the dynamic variable, 
and rows which will join into collection query will be visible in child query(collection) via #this object.

Still thank you for your suggestion, I will have a look at all possible options and consider which one looks most clear and easy to work with.

Best regards,

Ladislav

Dne středa 9. listopadu 2016 19:14:30 UTC+1 Iwao AVE! napsal(a):
Hi Ladislav,

Yes, it is not possible to access parent query's parameter from within a nested query.
Your options would be...

1) Instead of a nested select, use a nested result map with a JOINed select.
2) Use multiple result sets [1] if your DB supports it.
3) Add the necessary parameter to the parent query as a constant column (i.e. select *, #{filter} as filter from ...). Then you can pass it via the 'column' attribute of the collection element.
4) Execute these queries separately and merge the results in Java code.

[1] <a href="http://www.mybatis.org/mybatis-3/sqlmap-xml.html#Multiple_ResultSets_for_Collection" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.mybatis.org%2Fmybatis-3%2Fsqlmap-xml.html%23Multiple_ResultSets_for_Collection\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGUECeWPCclZdc40H1sK09SMVX5qQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.mybatis.org%2Fmybatis-3%2Fsqlmap-xml.html%23Multiple_ResultSets_for_Collection\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGUECeWPCclZdc40H1sK09SMVX5qQ&#39;;return true;">http://www.mybatis.org/mybatis-3/sqlmap-xml.html#Multiple_ResultSets_for_Collection

Hope this helps,
Iwao

2016-11-10 1:46 GMT+09:00 Ladislav Jech <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="QXW7r-d7BgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">arche...@...>:
Still this is not solving my solution as I will need dynamic(instance) non static variables to be passed. It looks MyBatis lack such required functionality in the moment.

Ladislav

Dne středa 9. listopadu 2016 11:23:54 UTC+1 Ladislav Jech napsal(a):
So, this is not possible, MyBatis doesn't support passing parameters from parent to child SQL statements, but you can pass a class with static method or static field(constant) like following:
<?xml version="1.0" encoding="UTF-8"?> <!DOCTYPE mapper PUBLIC "-//<a href="http://mybatis.org//DTD" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2F%2FDTD\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHjp-E8UL0TmmS7A0V54yMfbtfEkA&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2F%2FDTD\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHjp-E8UL0TmmS7A0V54yMfbtfEkA&#39;;return true;">mybatis.org//DTD Mapper 3.0//EN" "<a href="http://mybatis.org/dtd/mybatis-3-mapper.dtd" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2Fdtd%2Fmybatis-3-mapper.dtd\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFpJ-PGZSCiEykIm86cAd_bUTvMAQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fmybatis.org%2Fdtd%2Fmybatis-3-mapper.dtd\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFpJ-PGZSCiEykIm86cAd_bUTvMAQ&#39;;return true;">http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="org.MyBatisDataMapper">
<resultMap id="ParentDataResultMap" type="ParentData">
<id column="PARENT_KEY" jdbcType="VARCHAR" property="parentKey"/>
<result column="PARENT_DATA" jdbcType="VARCHAR" property="parentData"/>
<collection property="childData" ofType="ChildData" javaType="ArrayList"
column="{childKey=CHILD_KEY,parentKey=PARENT_KEY,childData=CHILD_DATA} " select="findChildData"/>
</resultMap>
<resultMap id="ChildDataResultMap" type="ChildData">
<id column="CHILD_KEY" jdbcType="VARCHAR" property="childKey"/>
<id column="PARENT_KEY" jdbcType="VARCHAR" property="parentKey"/>
<result column="CHILD_DATA" jdbcType="VARCHAR" property="childData"/>
</resultMap>

<select id="findChildData" resultMap="ChildDataResultMap">
select * from (values
<!-- THIS DOESN'T WORKk
<if test="filter == 'FIRST'">('CHILD_KEY_1','PARENT_KEY_1','CHILD_DATA_1')</if>
<if test="filter == 'SECOND'">('CHILD_KEY_2','PARENT_KEY_2','CHILD_DATA_2')</if>
-->

<!-- THIS WORKS - but I would like to submit custom dynamic object instead of constant type -->
<if test="@org.Decision@getString() == 'FIRST'">('CHILD_KEY_1','PARENT_KEY_1','CHILD_DATA_1')</if>
<if test="@org.Decision@getString() == 'SECOND'">('CHILD_KEY_2','PARENT_KEY_2','CHILD_DATA_2')</if>
) s(CHILD_KEY,PARENT_KEY,CHILD_DATA)
</select>
<select id="getAllData" resultMap="ParentDataResultMap">
select * from ( values
<if test="filter == 'FIRST'">('PARENT_KEY_1','PARENT_DATA_1','CHILD_KEY_1','CHILD_DATA_1')</if>
<if test="filter == 'SECOND'">('PARENT_KEY_2','PARENT_DATA_2','CHILD_KEY_2','BETA')</if>
) s(PARENT_KEY, PARENT_DATA,CHILD_KEY,CHILD_DATA)
</select>
</mapper>

So using static access is the possible way.

Ladislav

Dne čtvrtek 3. listopadu 2016 14:56:29 UTC+1 Ladislav Jech napsal(a):
Hi guys,

I have a scenario where i would like to use dynamic SQL parameters inside CHILD select statement.

MASTER result map is defined in simplified version like this:
<resultMap id="BookingResultMap" type="Booking">
<id column="GREC_SEQ_REC" jdbcType="INTEGER" property="id.incomingOfficeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<result column="GEMP_COD_EMP" jdbcType="VARCHAR" property="company" />
<result column="GTTO_SEQ_TTOO" jdbcType="INTEGER" property="clientId" />
   <collection property="dynamicRemarks" ofType="DynamicRemark" 
column="{incomingOfficeId=GREC_SEQ_REC,bookingNumber=SEQ_RESERVA} " select="findRemarksForBooking" />
</resultMap>

MASTER select statement (I ommit the full column list specification):
<select id="getAll" resultMap="BookingResultMap" >
select /*+ FIRST_ROWS(100)*/
<include refid="Booking_Base_Column_List"/>
from RE_T_RE_RESERVA R, RE_T_GE_TTOO T, RE_T_GE_RECEPTIVO RECEP, RE_T_RE_RESERVA_R2 R2
where T.SEQ_TTOO = R.GTTO_SEQ_TTOO
and RECEP.SEQ_REC = NVL(R.SEQ_REC_HBEDS, R.GREC_SEQ_REC)
and R2.SEQ_REC = R.GREC_SEQ_REC
and R2.SEQ_RESERVA = R.SEQ_RESERVA
</select>

For the collection defined in MASTER result map I have following select statement and another (CHILD) result map defined:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark">
<id column="SEQ_REC" jdbcType="INTEGER" property="id.officeId" />
<id column="SEQ_RESERVA" jdbcType="INTEGER" property="id.bookingNumber" />
<id column="COD_OBSERVACION" jdbcType="VARCHAR" property="id.remarksCodeId" />
<result column="OBSERVACION" jdbcType="VARCHAR" property="remarks" />
</resultMap>

<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</select>

Now, i would like to filter some columns with Dynamic SQL(the statements above do not reflect it yet), so I will do something like this:

Map<String, Object> parms = new HashMap<String, Object>();
parms.put("name", "abc);

List<Remarks> list = myBatis.selectList("BookingResultMap",params);

But I would like to use the parameter inside the child result map SQL statement, how to do this correctly and is it even supported? My intention is to extend the MASTER select by this:
<select id="getAll" resultMap="BookingResultMap" parameterType="map">

I expect to second select to be required to be extended in similar way if the parameter should be passed from parent(MASTER) to child (CHILD) select by this:
<resultMap id="BookingDynamicRemarksResultMap" type="BookingDynamicRemark" parameterType="map">

And then I would like to do something in CHILD select like this:
<select id="findRemarksForBooking" resultMap="BookingDynamicRemarksResultMap">
  <choose>
<when test="name = "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<when test="name != "JohnDoe">
select SEQ_REC,SEQ_RESERVA,COD_OBSERVACION, OBSERVACION
     from RE_T_RE_OBSERVACION_RE
where SEQ_REC = #{incomingOfficeId}
and SEQ_RESERVA = #{bookingNumber}
</when>
<otherwise>
select '','','' from dual
</otherwise>
  </choose>
</select>

Is something like this possible?

Thanks for suggestions.

Ladislav


--
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.
Loading...