mybatis关联查询--自连接 传参

http://xpenxpen.iteye.com/blog/2124198

 

默认及时加载，无论是否用sql引用写结果集，但是延时加载的前提的用sql写结果集

 

http://blog.csdn.net/jbgtwang/article/details/17548829

 

http://cczakai.iteye.com/blog/1276722

<resultMap type="com.demo.sys.entity.Menu" id="menuMap">
    <id property="id" column="id" />
    <result property="name" column="menu_name" />
    <result property="url" column="url" />
    <result property="active" column="isactive" />
    <result property="createDate" column="createdate" />
    <result property="updateDate" column="updatedate" />
    <result property="bak1" column="bak1" />
    <result property="bak2" column="bak2" />
    <result property="bak3" column="bak3" />
    <result property="locale" column="locale" />
    <result property="orderNum" column="ordernum" />
    <association property="module" column="module_id"  resultMap="moduleMap" select="queryModule">
            </association>
</resultMap>

 

 

 

<resultMap type="com.demo.sys.entity.Module" id="moduleMap">
    <id property="id" column="id" />
    <result property="name" column="module_name" />
    <result property="remark" column="remark" />
    <result property="createDate" column="createdate" />
    <result property="updateDate" column="updatedate" />
</resultMap>

 

 

<select id="queryModule" resultMap="moduleMap" >
    select * from sys_module where id =#{id}
</select>

 

  CREATE TABLE teacher(
    t_id INT PRIMARY KEY AUTO_INCREMENT,
    t_name VARCHAR(20)
);
CREATE TABLE class(
    c_id INT PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(20),
    teacher_id INT
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);   

INSERT INTO teacher(t_name) VALUES('teacher1');
INSERT INTO teacher(t_name) VALUES('teacher2');

INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

public class Classes {

    //定义实体类的属性，与class表中的字段对应
    private int id;            //id===>c_id
    private String name;    //name===>c_name
   
    /**
     * class表中有一个teacher_id字段，所以在Classes类中定义一个teacher属性，
     * 用于维护teacher和class之间的一对一关系，通过这个teacher属性就可以知道这个班级是由哪个老师负责的
     */
    private Teacher teacher;




  <!-- 使用resultMap映射实体类和字段之间的一一对应关系 -->
     <resultMap type="me.gacl.domain.Classes" id="ClassResultMap2">
        <id property="id" column="c_id"/>
        <result property="name" column="c_name"/>
        <association property="teacher" column="teacher_id" select="getTeacher"/>
     </resultMap>
    
     <select id="getTeacher" parameterType="int" resultType="me.gacl.domain.Teacher">
        SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
     </select>
     ============（从 sql结果集中找，压入参数）

 

=======================

多个参数

 

column
   
数据库的列名或者列标签别名。与传递给resultSet.getString(columnName)的参数名称相同。

注意： 在处理组合键时，您可以使用column= “{prop1=col1,prop2=col2}”这样的语法，设置多个列名传入到嵌套查询语句。这就会把prop1和prop2设置到目标嵌套选择语句的参数对象中。

 

<resultMap id="testMap"  type="xxx" >
        <id property="id" column="id" jdbcType="DECIMAL" />
        <result property="startDateStr" column="startDateStr"  jdbcType="VARCHAR" />
        <result property="endDateStr" column="endDateStr"  jdbcType="VARCHAR" />
        <!-- 关联查询传入多值，column当map使用 -->
        <association property="actualWorkTime" column="{reqId=id,endDate=endDateStr,startDate=startDateStr}" select="selectById"></association>

</resultMap>

说明: reqId对应map的key,id对应map的value,但取值来自testMap的id column

<select id="selectById" parameterType="java.util.Map" resultType="java.lang.Double">

select * from Table_XXX

where reqId=#{reqId}

and DATE_FORMAT(endDate,'%Y-%m-%d') <= DATE_FORMAT(#{endDate},'%Y-%m-%d')

and DATE_FORMAT(startDate,'%Y-%m-%d') >= DATE_FORMAT(#{startDate},'%Y-%m-%d')

</select>

==============

只有一个时，是母查询结果集中的key压入作为参数，有多个时，采用key-value

key为子中的key,value为母中这个value作为key的vale值，最终用子中的key，占位即的目中结果集传入的值作为参数

 

例子：

 

  public class TbCusFirmChgVo extends BaseConditionVo  implements Serializable{

    /**

* 

*/

private static final long serialVersionUID = -4284635533192116683L;

 

 

 

private Date chk_start;

private String money;

 

private TbCusFirmChg tbCusFirmChg;//关联关系在resultmap中是用属性指定的所以这个属性不用指定@COLUMN

@Column(name = "CUSTOMER_KEY")

    private BigDecimal customerKey;

 

    @Column(name = "MARKET_KEY")

    private BigDecimal marketKey;

 

    }

 

 

<resultMap id="BaseResultMapVo" type="com.esteel.account.beanVo.TbCusFirmChgVo" >
    <!--
      WARNING - @mbggenerated
    -->
    <id column="CUSTOMER_KEY" property="customerKey" jdbcType="DECIMAL" />
    <result column="IS_REC_SMS" property="isRecSms" jdbcType="CHAR" />
    <result column="KF_CUSTOMER_KEY" property="kfCustomerKey" jdbcType="DECIMAL" />
    <result column="CUS_RANK_NUM" property="cusRankNum" jdbcType="DECIMAL" />
    <result column="SET_USER_NUM" property="setUserNum" jdbcType="DECIMAL" />
    <association property="tbCusFirmChg" column="KF_CUSTOMER_KEY" javaType="com.esteel.account.bean.TbCusFirmChg" select="getCue">
    </association>
  </resultMap>

 

 

<select id="getTbCusFirmChgAry" parameterType="map" resultMap="BaseResultMapVo">
  select C.* from TB_CUS_FIRM_CHG C
  where 1=1
  <if test="sCustomerID!=null and sCustomerID!=''">
   and lower(C.CUSTOMER_ID) like '%'||${sCustomerID}||'%'  "
  </if>
   <if test="sCustomerName!=null and sCustomerName!=''">
   and lower(C.CUSTOMER_NAME) like '%'||${sCustomerName}||'%'
  </if>
   <if test="sDB!=null and sDB!=''">
   and C.TRADE_MAN_NAME like '%'||${sDB}||'%' 
  </if>
  <if test="sMarketCode!=null and sMarketCode!=''">
    and C.MARKET_KEY=${sMarketCode}
  </if>
  <if test="sSheng!=null and sSheng!=''">
    and C.PROVINCE_AREA_KEY = ${sSheng}
  </if>
  <if test="sShi!=null and sShi!=''">
   and C.CITY_AREA_KEY = ${sShi}
  </if>
  </select>

 

 

 <select id="getCue" resultMap="BaseResultMap">
    select KF.*  from TB_CUS_FIRM_CHG  KF where  KF.CUSTOMER_KEY=#{KF_CUSTOMER_KEY}
  </select>

 

 

public PageInfo<TbCusFirmChgVo> getTbCusFirmChg(Map<String, Object> param) {
        // TODO Auto-generated method stub
        String pageNum = (String) param.get("pageNum");
        String pageSize = (String) param.get("pageSize");
        PageHelper.startPage(Integer.valueOf(pageNum), Integer.valueOf(pageSize));
        PageHelper.orderBy(param.get("orderField")+" "+param.get("orderDirection"));

      //默认是即时查询，这句话就把子查询关联查出
        List<TbCusFirmChgVo> list = tbCusFirmChgMapper.getTbCusFirmChgAry(param);
        TbCusFirmChg t= list.get(0).getTbCusFirmChg();
        PageInfo<TbCusFirmChgVo> listInfo = new PageInfo<TbCusFirmChgVo>(list);
        return listInfo;

    }