how to handle date column with null when insert or update in mybatis

I have a java bean BeanA:

public class BeanA {
    
    private Integer id;
    private String dateField;
    // other fields
}

And mysql table bean_a which contains date column date_field.

the corresponding mybatis sql looks like:

<insert ..>
insert into bean_a(date_field, ..) values
(
<if test="dateField != null and dateField == ''">null,</if>
<if test="dateField != null and dateField != ''">#{dateField},</if>
...
)
</insert>

<update ..>
update bean_a
<set>
<if test="dateField != null && dateField == ''">date_field = null,</if>
<if test="dateField != null && dateField != ''">date_field = #{dateField},</if>
</set>
where id = #{id}
</update>

For date field, it includes two cases:

1. user clear its value on UI, in this case we should insert/update null to table
2. BeanA beanA = new BeanA();set other fields exclude dateField;then call dao to update BeanA, in this case, we should update table without modifying date_field because the dateField is null.

Is the above a general practice? Is there better practice(because it requires two if check for each insert/update, which maybe repetiton)? Thanks!

I don't fully understand the usage of -999999999-01-01 but I'm guessing you use this value to represent a date that has not been set, and should not be updated? Personally I think this type of logic is a bit hacky, perhaps you could avoid it with extra isXSet() methods on your bean?

It's a bit of boilerplate... but you could have MyBean and MyBeanDelta where the delta represents changes/updates.

Eg

public class MyBean {
   private Long id;
   private String name;
   private LocalDate date;

   // getters & setters
}

public class MyBeanDelta {
   private final Long id;
   private final Map<String, Object> fields = new LinkedHashMap<>();

   // constructor

   public Long getId() { return id; }

   public void setName(String) { fields.put("name", name); }
   public boolean isNameSet() { return fields.contains("name"); }
   public String getName() { return (String) fields.get("name"); }

   public void setDate(LocalDate date) { fields.put("date", date); }
   public boolean isDateSet() { return fields.contains("date"); }
   public LocalDate getDate() { return (LocalDate) fields.get("date"); }
}

Then you could do

@Mapper
public interface MyBeanMapper
   void insert(MyBean bean);
   void update(MyBeanDelta delta);
}

And

<insert id="insert">
   insert into my_bean (id, name, date)
   values (
     #{id}, 
     #{name,javaType=java.lang.String}, 
     #{date,javaType=java.time.LocalDate}
   )
</insert>
<update id="update">
   update my_bean
   <set>
      <if test=#{nameSet}>name = #{name,javaType=java.lang.String},</if>
      <if test=#{dateSet}>date = #{date,javaType=java.time.LocalDate},</if>
   </set>
   where id = #{id}
</update>