MyBatis 动态 SQL 解决方案

<!-- 动态INSERT语句 -->
<insert id="addDailyFreezeTer" parameterType="com.linyang.pecker.pojo.DailyFreezeTerminalBean">INSERT INTO dailyfreezeter_info<trim prefix="(" suffix=")" suffixOverrides=",">checkId, terminalId, pointId, meterNo, dataDate, saveDate, stepFlag,<if test="day_050601FF != null">day_050601FF,</if><if test="day_050602FF != null">day_050602FF,</if><if test="day_050603FF != null">day_050603FF,</if><if test="day_050604FF != null">day_050604FF,</if><if test="day_050605FF != null">day_050605FF,</if><if test="day_050606FF != null">day_050606FF,</if><if test="day_050607FF != null">day_050607FF,</if><if test="day_050608FF != null">day_050608FF,</if><if test="day_E1008060 != null">day_E1008060,</if><if test="day_E1008061 != null">day_E1008061,</if><if test="day_E1008062 != null">day_E1008062,</if><if test="day_E1008063 != null">day_E1008063,</if><if test="day_040005FF != null">day_040005FF,</if><if test="day_E1008031 != null">day_E1008031,</if></trim>VALUES<trim prefix="(" suffix=")" suffixOverrides=",">#{checkId}, #{terminalId}, #{pointId}, #{meterNo}, #{dataDate}, #{saveDate}, #{stepFlag},<if test="day_050601FF != null">#{day_050601FF},</if><if test="day_050602FF != null">#{day_050602FF},</if><if test="day_050603FF != null">#{day_050603FF},</if><if test="day_050604FF != null">#{day_050604FF},</if><if test="day_050605FF != null">#{day_050605FF},</if><if test="day_050606FF != null">#{day_050606FF},</if><if test="day_050607FF != null">#{day_050607FF},</if><if test="day_050608FF != null">#{day_050608FF},</if><if test="day_E1008060 != null">#{day_E1008060},</if><if test="day_E1008061 != null">#{day_E1008061},</if><if test="day_E1008062 != null">#{day_E1008062},</if><if test="day_E1008063 != null">#{day_E1008063},</if><if test="day_040005FF != null">#{day_040005FF},</if><if test="day_E1008031 != null">#{day_E1008031},</if></trim>
</insert>

实现说明

动态 INSERT 语句

• 使用<trim>标签处理括号和逗号
• 通过<if>判断字段是否为 null，只有非 null 字段才会加入 SQL
• suffixOverrides=","自动处理多余的逗号

<!-- 动态UPDATE语句 -->
<update id="updateDailyFreezeTer" parameterType="com.linyang.pecker.pojo.DailyFreezeTerminalBean">UPDATE dailyfreezeter_info<set><if test="day_050601FF != null">day_050601FF = #{day_050601FF},</if><if test="day_050602FF != null">day_050602FF = #{day_050602FF},</if><if test="day_050603FF != null">day_050603FF = #{day_050603FF},</if><if test="day_050604FF != null">day_050604FF = #{day_050604FF},</if><if test="day_050605FF != null">day_050605FF = #{day_050605FF},</if><if test="day_050606FF != null">day_050606FF = #{day_050606FF},</if><if test="day_050607FF != null">day_050607FF = #{day_050607FF},</if><if test="day_050608FF != null">day_050608FF = #{day_050608FF},</if><if test="day_E1008060 != null">day_E1008060 = #{day_E1008060},</if><if test="day_E1008061 != null">day_E1008061 = #{day_E1008061},</if><if test="day_E1008062 != null">day_E1008062 = #{day_E1008062},</if><if test="day_E1008063 != null">day_E1008063 = #{day_E1008063},</if><if test="day_040005FF != null">day_040005FF = #{day_040005FF},</if><if test="day_E1008031 != null">day_E1008031 = #{day_E1008031},</if><if test="dataDate != null">dataDate = #{dataDate},</if><if test="saveDate != null">saveDate = #{saveDate},</if><if test="stepFlag != null">stepFlag = #{stepFlag},</if></set>WHERE checkId = #{checkId}  <!-- 根据实际主键调整 -->
</update>

动态 UPDATE 语句

• 使用<set>标签自动处理 SET 关键字和逗号
• 同样通过<if>判断字段是否为 null
• 需要确保 WHERE 子句使用正确的主键或唯一标识

注意事项

• 对于必须字段（如 checkId, terminalId 等），建议在 Java 代码中确保其非空
• 如果字段类型是基本数据类型（如 int, long），需要改用包装类（Integer, Long）才能判断 null
• 对于日期类型字段，可能需要额外处理空字符串或默认值的情况