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
• 對于日期類型字段，可能需要額外處理空字符串或默認值的情況