实际场景应用需求 , 统计用户某年某月的工资台账,从考勤表中统计出勤天数,反算出日工资 .
一 从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/