【Mybatis】mybatis可能会用到的语法整合

mybatis可能会用到的语法整合

1.批量插入

<!-- 批量插入 -->
<insert id="batchInsert" parameterType="xxx.xxx.TWeather">
	INSERT INTO `t_weather`
		(
			`area_id`, 
			`forecast`, 
			`observe`, 
			`air`, 
			`index`, 
			`create_time`, 
			`city_name`
		) VALUES 
	<foreach collection ="list" item="weather" index= "index" separator =",">
		(
			#{weather.areaId,jdbcType=VARCHAR}, 
			#{weather.forecast,jdbcType=VARCHAR}, 
			#{weather.observe,jdbcType=VARCHAR}, 
			#{weather.air,jdbcType=VARCHAR}, 
			#{weather.index,jdbcType=VARCHAR}, 
			#{weather.createTime,jdbcType=TIMESTAMP}, 
			#{weather.cityName,jdbcType=VARCHAR}
		)
	</foreach>
</insert>

2.使用in查询

<!-- 使用in查询 -->
<select id="queryByIn" resultType="integer">
	SELECT * FROM `table`
	WHERE
	<if test="idCollection != null and idCollection .size() > 0">
		`id` in
		<foreach item="item" index="index" collection="idCollection"
			 open="(" separator="," close=")">
			#{item,jdbcType=VARCHAR}
		</foreach>
	</if>
</select>

3.判断查询条件值是否为空, 模糊查询, 日期判断,> < 转义

<!-- 判断查询条件值是否为空 -->
<if test="account != null and account != ''">
	AND account = #{account}
</if>
<!-- 判断查询条件值是否为0 在使用if标签进行字符串和数字的比较时，会把字符串转化成数字来进行比较，也就是会把’'转换成0.0D,0 == 0.0D 相等 ，就进不去if里面-->
<if test="(account != null and account != '') or account == 0">
	AND account = #{account}
</if>
<!-- 模糊查询 -->
<if test="area != null and area != ''">
	AND area LIKE CONCAT('%', #{area}, '%')
</if>
<!-- 日期判断,> < 转义 -->
<if test="startDt != null and startDt != ''">
	AND insert_dt &gt;= #{startDt}
</if>
<if test="endDt != null and endDt != ''">
    AND insert_dt &lt;= #{endDt}
</if>