模块优化之SQL

1、模糊查询

<!--条件-分页查询字典类别信息-->
	<select id="queryByPage" resultType="com.lekai.anysys.entity.vo.SysDictTypeVo">
		<include refid="selectDictTypeVo" />
		where 1 = 1
		<if test="Sys.name != null and Sys.name != ''">
			and `name` like CONCAT('%', #{Sys.name}, '%')
		</if>
		<if test="Sys.type != null and Sys.type != ''">
			and `type` like CONCAT('%', #{Sys.type}, '%')
		</if>
		<if test="Sys.state != null">
			and state = #{Sys.state}
		</if>
		and del_flag = 0
		order by created_time desc
	</select>

concat('%',#{sda},'%') 查询含sda的数据

2、SQL公共部分-sql标签

	<sql id="selectDictTypeVo">
        select id, `name`, `type`, state, created_by, created_time, remarks
		from any_sys_dict_type
    </sql>

引用-include标签

	<select id="selectDictTypeAll" resultMap="SysDictTypeResult">
		<include refid="selectDictTypeVo"/> where del_flag = 0
	</select>

将多次使用的部分提出，在需要使用的地方进行引用。提高代码可读性

3、封装返回集，简化

	<resultMap type="com.lekai.anysys.entity.SysDictTypeEntity" id="SysDictTypeResult">
		<id property="id" column="id"/>
		<result property="name" column="name"/>
		<result property="type" column="type"/>
		<result property="state" column="state"/>
	</resultMap>

	<select id="selectDictTypeAll" resultMap="SysDictTypeResult">
		<include refid="selectDictTypeVo"/> where del_flag = 0
	</select>

将resultMap简化

4、in功能-foreach标签

 	<delete id="deleteDictTypeByIds" parameterType="Integer">
 		delete from any_sys_dict_type where id in
 		<foreach collection="array" item="dictId" open="(" separator="," close=")">
 			#{dictId}
        </foreach>
		and del_flag = 0
 	</delete>

5、更新-set标签

	<update id="updateDictType" parameterType="com.lekai.anysys.entity.SysDictTypeEntity">
 		update any_sys_dict_type
 		<set>
 			<if test="name != null and name != ''">`name` = #{name},</if>
 			<if test="type != null and type != ''">`type` = #{type},</if>
 			<if test="state != null">state = #{state},</if>
 			<if test="remarks != null">remarks = #{remarks},</if>
 			<if test="updatedBy != null and updatedBy != ''">updated_by = #{updatedBy},</if>
 			updated_time = sysdate()
 		</set>
 		where id = #{id}
	</update>

判断选择设置

6、条件新增

 	<insert id="insertDictType" parameterType="com.lekai.anysys.entity.SysDictTypeEntity">
 		insert into any_sys_dict_type (
 			<if test="name != null and name != ''">name,</if>
 			<if test="type != null and type != ''">type,</if>
 			<if test="state != null">state,</if>
 			<if test="remarks != null and remarks != ''">remarks</if>
 		) values (
 			<if test="name != null and name != ''">#{name},</if>
 			<if test="type != null and type != ''">#{type},</if>
 			<if test="state != null">#{state},</if>
 			<if test="remarks != null and remarks != ''">#{remarks}</if>
 		)
	</insert>