CODEWITHSUNDEEP
javamybatis
frank
frank

Reputation: 1201

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!

Upvotes: 0

Views: 863

Answers (2)

lance-java
lance-java

Reputation: 27986

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>

Upvotes: 0

lance-java
lance-java

Reputation: 27986

Note: I'm not quite sure what you're doing with dateField == '-999999999-01-01' but that looks like bad practice to me. I'll ignore that

The problem here is that PreparedStatement.setObject(int, Object) does not work for null because the driver doesn't know which type of null to pass to the db. The good news is that PreparedStatement.setObject(int, Object, int sqlType) does support null. We just need to give Mybatis enough information to determine the type so it can call the three args method.

If you include the jdbcType and/or the javaType inside the parameter placeholder, Mybatis can then pass the type to PreparedStatement.setObject(...)

See sqlmap parameters and java.sql.Types

Eg one of the following will work

<update id="updateBeanA">
    update bean_a 
    set date_field = #{dateField,jdbcType=DATE} 
    where id = #{id}
</update>

or

<update id="updateBeanA">
    update bean_a 
    set date_field = #{dateField,javaType=java.time.LocalDate} 
    where id = #{id}
</update>

Upvotes: 0

Related Questions