大于开始时间小于结束时间
注意日期非‘ ’问题
<if test="endTime != null">
AND PREPARATION_TIME <![CDATA[<=]]> #{endTime}
</if>
<if test="startTime != null">
AND PREPARATION_TIME <![CDATA[>=]]> #{startTime}
</if>
模糊查询
<if test="compilationUnit != null and compilationUnit != ''">
AND COMPILATION_UNIT LIKE CONCAT('%', #{compilationUnit},'%')
</if>
使用trim标签,多条件时,逗号处理;多用update
直接使用<set>标签,把逗号放后面,更方便
<update id="update" parameterType="com.bonc.industry.txsp.entity.SpareDevice">
UPDATE
spare_device
<trim prefix="set" suffixOverrides=",">
<if test="spareDevice.spareDeviceId !=null and spareDevice.spareDeviceId !=''">
spare_device_id = #{spareDevice.spareDeviceId},
</if>
<if test="spareDevice.spareDeviceName !=null and spareDevice.spareDeviceName !=''">
spare_device_name = #{spareDevice.spareDeviceName}
</if>
</trim>
WHERE spare_device_id = #{spareDevice.spareDeviceId}
</update>
JavaType、ofType和JdbcType
JavaType、ofType都是用来指定对象类型的,但是JavaType是用来指定pojo中属性的类型,而ofType指定的是映射到list集合属性中pojo的类型。
JdbcType是数据库类型
不等于注意('!=' 正确,'! =' 错误)
<if test="stationId !=null and stationId !=''">
AND station_id != #{stationId}
</if>
and 、or使用注意
and 和 or的优先级是先and ,然后是or,所以涉及or的要用括号括起来
<select id="checkExist" resultType="com.bonc.industry.txsp.entity.Station">
SELECT *
FROM station
<where>
is_deleted=0
<if test="stationId !=null and stationId !=''">
AND station_id != #{stationId}
</if>
AND (station_number = #{stationNumber} OR UPPER(station_name) = UPPER(#{stationName}))
</where>
</select>
批量插入,foreach注意加括号
<insert id="insertBatch">
insert into user(id,name,age,sex,addr)
values
<foreach collection="list" index="index" item="item" separator=",">
(
#{item.id},
#{item.name},
#{item.age},
#{item.sex},
#{item.addr}
)
</foreach>
</insert>