mybatis 一对一association的使用

实际场景应用需求 ，  统计用户某年某月的工资台账，从考勤表中统计出勤天数，反算出日工资   .

 

一 从excel中导入工资台账信息。如  张三 ，2018  01 中国银行 6000元          需要算出出勤日期和每日工资 

 

1、一对一映射

<!--查询 台账记录resultMap结构-->

<resultMap id="wagesResultMap" type="com.imydao.cpsp.wgb.entity.Wages">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <result property="idCard" column="id_card"/>
    <result property="bankCard" column="bank_card"/>
    <result property="bank" column="bank"/>
    <result property="mobile" column="mobile"/>
    <result property="year" column="year"/>
    <result property="month" column="month"/>
    <result property="wages" column="wages"/>
    <result property="engTeamId" column="eng_team_id"/>
    <association property="days" javaType="java.lang.Integer"
                 column="{mobile=mobile,year=year,month=month}" select="signCount"/>
</resultMap>

 2  分页查询工资台账记录sql

 

<select id="selectPageInfo" resultMap="wagesResultMap" parameterType="com.imydao.cpsp.core.common.beans.PageInfo">
    select id, uuid, name, id_card, creator_id, create_time, update_time, status, bank_card,
    bank, mobile, year, month, wages, eng_team_id
    from tb_wages
    where status = 1
    <include refid="whereMapSql"/>
    <if test="whereMap.orderColumns != null">
        order by
        <foreach collection="whereMap.orderColumns" item="orderColumn" index="index"
                 open="" close="" separator=",">
            ${orderColumn} ${orderSort}
        </foreach>
    </if>
    <if test="startRecord != null and pageSize != null">
        limit #{startRecord,jdbcType=INTEGER},#{pageSize,jdbcType=INTEGER}
    </if>
</select>

 

3 查询出勤天数sql

 

<select id="signCount" resultType="java.lang.Integer" parameterType="java.util.Map">
    <include refid="signListSql"/>
</select>
<sql id="signListSql">
     SELECT COUNT(tsr.id) AS days  FROM tb_sign_record tsr LEFT JOIN tb_users tu ON tsr.user_id=tu.id
    WHERE tu.user_status=1 AND tu.telephone_number=#{mobile} AND tsr.sign_in_time IS NOT NULL  AND
    DATE_FORMAT(sign_date,'%Y-%m') =   CONCAT(#{year},'-',IF(#{month}>9,#{month},CONCAT('0',#{month})))
</sql>

 

 

备注： 本文只是mabatis association元素使用的一个小案例。如需深入了解。请参考mybatis官方文档 http://www.mybatis.org/mybatis-3/

 

java分享交流群 110408216