mapping if else

约定不等于承诺〃 2022-04-11 10:38 54阅读 0赞

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
    <mapper namespace="com.zichen.xhkq.dao.shiro.SysRoleDao">
    	
    	<select id="selectBySchoolIdAndRoleName" parameterType="com.zichen.xhkq.pojo.shiro.SysRole"
    		resultType="com.zichen.xhkq.pojo.shiro.SysRole">
    		SELECT * FROM `sys_role` WHERE `school_id`=#{schoolId} AND
    		`name`=#{name}
    	</select>
    
    	
    	<select id="selectCount" parameterType="com.zichen.xhkq.pojo.shiro.SysRole"
    		resultType="java.lang.Integer">
    		SELECT count(*) FROM `sys_role` WHERE `school_id`=#{schoolId}
    		<if test="name!=null and name!=''">
    			AND `name` LIKE '%${name}%'
    		</if>
    		<choose>
    			<when
    				test="startCreatTime!=null and startCreatTime!='' and endCreatTime!=null and endCreatTime!='' and startCreatTime==endCreatTime">
    				AND `creat_time` >= #{startCreatTime}
    			</when>
    			<otherwise>
    				<if test="startCreatTime!=null and startCreatTime!=''">
    					AND `creat_time` >=#{startCreatTime}
    				</if>
    				<if test="endCreatTime!=null and endCreatTime!=''">
    					AND `creat_time` <=#{endCreatTime}
    				</if>
    			</otherwise>
    		</choose>
    		<if test="creatPerson!=null and creatPerson!=''">
    			AND `creat_person` LIKE '%${creatPerson}%'
    		</if>
    		<if test="available!='3'">
    			AND `available` =#{available}
    		</if>
    		<if test="available=='3'">
    			AND `available` in ('0','1')
    		</if>
    	</select>
    
    	
    	<select id="selectListBySchoolId" parameterType="com.zichen.xhkq.pojo.shiro.SysRole"
    		resultType="com.zichen.xhkq.pojo.shiro.SysRole">
    		SELECT * FROM `sys_role` WHERE `school_id`=#{schoolId}
    	</select>
    
    	
    	<delete id="deleteById" parameterType="com.zichen.xhkq.pojo.shiro.SysRole">
    		DELETE FROM `sys_role`
    		WHERE `id`=#{id}
    	</delete>
    
    	
    	<delete id="deleteBatch">
    		DELETE FROM `sys_role` WHERE `id` in
    		<foreach collection="idList" close=")" open="(" separator=","
    			item="id">
    			#{id}
    		</foreach>
    		;
    	</delete>
    
    	
    	<update id="updateBySysRole" parameterType="com.zichen.xhkq.pojo.shiro.SysRole">
    		UPDATE `sys_role`
    		<set>
    			`creat_time`= NOW(),
    			<if test="name != null">
    				`name`=#{name},
    			</if>
    			<if test="available != null and available !=''">
    				`available`=#{available},
    			</if>
    			<if test="creatPerson != null">
    				`creat_person`=#{creatPerson},
    			</if>
    			<if test="description != null">
    				`description`=#{description},
    			</if>
    		</set>
    		WHERE `id`=#{id}
    	</update>
    
    
    	
    	<insert id="insertSysRole" parameterType="com.zichen.xhkq.pojo.shiro.SysRole">
    		INSERT INTO `sys_role`(
    		`id`,`school_id`,`name`,`available`,`creat_time`,`creat_person`,`description`
    		)
    
    		VALUES(
    		#{id},#{schoolId},#{name},#{available},NOW(),#{creatPerson},#{description}
    		)
    	</insert>
    
    	
    	<select id="selectListConditions" resultType="com.zichen.xhkq.pojo.shiro.SysRole">
    		SELECT * FROM `sys_role` WHERE `school_id`=#{schoolId}
    		<if test="name!=null and name!=''">
    			AND `name` LIKE '%${name}%'
    		</if>
    
    		<choose>
    			<when
    				test="startCreatTime!=null and startCreatTime!='' and endCreatTime!=null and endCreatTime!='' and startCreatTime==endCreatTime">
    				AND `creat_time` >= #{startCreatTime}
    			</when>
    			<otherwise>
    				<if test="startCreatTime!=null and startCreatTime!=''">
    					AND `creat_time` >=#{startCreatTime}
    				</if>
    				<if test="endCreatTime!=null and endCreatTime!=''">
    					AND `creat_time` <=#{endCreatTime}
    				</if>
    			</otherwise>
    		</choose>
    
    
    		<if test="creatPerson!=null and creatPerson!=''">
    			AND `creat_person` LIKE '%${creatPerson}%'
    		</if>
    		<if test="available!='3'">
    			AND `available` =#{available}
    		</if>
    		<if test="available=='3'">
    			AND `available` in ('0','1')
    		</if>
    		LIMIT #{startNum},#{pageSize}
    	</select>
    	
    	<select id="getRoleListBySchoolIdAndAvailable" resultType="com.zichen.xhkq.pojo.shiro.SysRole">
    		SELECT * FROM `sys_role` WHERE `available` = #{roleAvailable} AND
    		`school_id`=#{schoolId}
    	</select>
    
    	
    	<update id="updateRoleAvaliableByRoleId">
    		UPDATE sys_role AS SR SET SR.available = #{available}
    		WHERE SR.id = #{id};
    	</update>
    
    	
    	<select id="getSysRoleByRoleId" parameterType="java.lang.String"
    		resultType="com.zichen.xhkq.pojo.shiro.SysRole">
    		SELECT * FROM sys_role AS SR WHERE SR.id = #{id};
    	</select>
    
    </mapper>