Mybatis笔记 -- 点点滴滴

大于开始时间小于结束时间

注意日期非‘ ’问题

<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>