阿里云服务器
mybatis集合映射话不多说直接上代码了,对mybatis有基础的同学会有很大帮助 可以理解成栏目和文章
//collection 一对多集合映射
<!--查询异常考勤记录sql 封装resultMap结构--> <resultMap id="signListResult" type="com.imydao.cpsp.wgb.crudparams.SignParams"> <result property="userId" column="userId"/> <result property="userName" column="userName"/> <result property="engineeringId" column="engineeringId"/> <result property="years" column="years"/> <result property="months" column="months"/> <result property="days" column="days"/> <result property="attendance" column="attendance"/> <result property="notClock" column="notClock"/> <collection property="content" ofType="java.util.Date" javaType="ArrayList" column="{userId=userId,years=years,months=months,attendance=attendance}" select="selectAbnormal" /> </resultMap>
//分页查询所有主数据 即栏目 注意 返回结果集是resultMap=signListResult 映射
<select id="getAbSignList" resultMap="signListResult" parameterType="com.imydao.cpsp.core.common.beans.PageInfo"> SELECT tsr.user_id userId, tu.name userName, tsr.org_id engineeringId, YEAR (tsr.sign_date) years, CONCAT('0',MONTH (tsr.sign_date)) months , COUNT(tsr.id) days ,tsr.attendance attendance , tsr.not_clock notClock FROM tb_sign_record AS tsr,tb_users AS tu WHERE tsr.user_id=tu.id AND tsr.status=1 AND tsr.sign_in_time IS NOT NULL GROUP BY years ,months,userId HAVING 1=1 <include refid="whereMapSql"/> <if test="startRecord != null and pageSize != null"> limit #{startRecord,jdbcType=INTEGER},#{pageSize,jdbcType=INTEGER} </if> </select>
//根据主数据的参数查询栏目下所有文章、
<select id="selectAbnormal" resultType="java.util.Date" parameterType="java.util.Map"> SELECT setdate FROM tb_calendar WHERE YEAR =#{years} AND MONTH=#{months} AND setdate <= (SELECT MAX(sign_date) FROM tb_sign_record WHERE YEAR(sign_date)=#{years} AND MONTH(sign_date)=#{months}) AND setdate NOT IN (SELECT sign_date FROM tb_sign_record WHERE user_id=#{userId} AND YEAR(sign_date)=#{years} AND MONTH(sign_date)=#{months} ) <if test="attendance==1"> AND STATUS=0 </if> </select>