mybatis 批量新增跟修改

╰半夏微凉° 2022-04-12 12:18 297阅读 0赞

<update id="delStudentAttendanceBatch"  parameterType="java.util.List">    
        <foreach collection="studentAttendanceList" item="item" index="index" open="" close="" separator=";">  
            UPDATE emp\_student\_attendance  
            <set>  
                stat = 0   
            </set>  
            WHERE stat = 1   
            AND student\_id = \#\{item.studentId\}  
            AND class\_id = \#\{item.classId\}  
        </foreach>        
    </update>

<insert id="addStudentAttendanceBatch" parameterType="java.util.List">  
        INSERT INTO emp\_student\_attendance  
        (id, student\_id,  
        class\_id,class\_course\_arrange\_id,employee\_id,school\_id,attendance\_status,attendence\_time,  
        consume\_hour\_counts,remark,create\_time,last\_update\_time,stat)  
        values  
        <foreach collection="studentAttendanceList" item="studentAttendance"  
            index="index" separator=",">  
            (  
            \#\{studentAttendance.id,jdbcType=VARCHAR\},  
            \#\{studentAttendance.studentId,jdbcType=VARCHAR\},  
            \#\{studentAttendance.classId,jdbcType=VARCHAR\},  
            \#\{studentAttendance.classCourseArrangeId,jdbcType=VARCHAR\},  
            \#\{studentAttendance.employeeId,jdbcType=VARCHAR\},  
            \#\{studentAttendance.schoolId,jdbcType=VARCHAR\},  
            \#\{studentAttendance.attendanceStatus,jdbcType=INTEGER\},  
            \#\{studentAttendance.attendenceTime,jdbcType=TIMESTAMP\},  
            \#\{studentAttendance.consumeHourCounts,jdbcType=DOUBLE\},  
            \#\{studentAttendance.remark,jdbcType=VARCHAR\},  
            \#\{studentAttendance.createTime,jdbcType=TIMESTAMP\},  
            \#\{studentAttendance.lastUpdateTime,jdbcType=TIMESTAMP\},  
            \#\{studentAttendance.stat,jdbcType=INTEGER\}  
            )  
        </foreach>  
    </insert>

<select id="findListCount" resultType="java.lang.Integer">  
        SELECT  
        count(1)

FROM  
        app\_class a  
        LEFT JOIN app\_lesson\_school b ON a.school\_id = b.school\_id  
        AND a.lession\_id = b.lesson\_id  
        LEFT JOIN app\_school c ON a.school\_id=c.id  
        LEFT JOIN common\_employee d ON a.teacher\_emoloyee\_id =d.id  
        LEFT JOIN (SELECT \* FROM app\_dictionary e WHERE e.parent\_id IN (SELECT  
        f.id FROM app\_dictionary f WHERE f.param\_name='attend\_class\_times' ))  
        g ON g.param\_value=a.attend\_class\_times  
        WHERE a.stat=1 AND b.stat=1 AND c.stat=1 AND d.stat=1  
        <include refid="where\_conditions"></include>          
    </select>      
      
    <sql id="where\_conditions">  
        <if test="cla.schoolId  == null and cla.schoolIdList !=null"  >  
        AND a.school\_id in  
      
          <foreach collection="cla.schoolIdList" item="item" open="(" close=")" separator="," >  
                    \#\{item\}  
            </foreach>  
        </if>  
            <if test="cla.schoolId  != null and cla.schoolIdList ==null"  >  
        AND a.school\_id =\#\{cla.schoolId\}  
        </if>  
        <if test="cla.gradeId != '' and cla.gradeId  != null">  
            AND a.grade\_id = \#\{cla.gradeId\}  
        </if>  
        <if test="cla.season != '' and cla.season  != null">  
            AND a.season = \#\{cla.season\}  
        </if>  
    </sql>