CODEWITHSUNDEEP
javasqlmybatisdynamic-sql
Robert
Robert

Reputation: 65

MyBatis update if a field in the source object is not null

Using MyBatis annotation and dynamic SQL, I was trying to update a field of a table only if the corresponding field in the source object is not null. For example, if #{srcField} is not null, set field=#{srcField}. I do not care if the field in DB is null or not.

However, I failed to come up with a correct dynamic SQL.

I tried two dynamic sql approaches. Approach 1 can be taken by my program, but it failed to check if the specific field in the source object is null. Approach 2 causes some run-time error.

My DAO object with setter/getter (source object to update the table):

@Data
public class CityDO {
  private Long id;
  private String name;
  private String description;
}

Mapper using annotation and dynamic sql. I was trying to update the description field, only if record.description is not null.

Approach 1, it failed the test #{description}!=null, even if record.description==null. Each time the produced sql include SET description = ?.

  @Update({
      "<script>",
        "update city",
        "<set>",
          "<if test='#{description} != null'>description = #{description,jdbcType=VARCHAR},</if>",
        "</set>",
      "where name = #{name,jdbcType=VARCHAR}",
      "</script>"
  })
  @Override
  int update(CityDO record);

Approach 2, the only difference is I added jdbcType in the test condition. The exception is Caused by: org.apache.ibatis.reflection.ReflectionException: There is no getter for property named 'VARCHAR' in 'class com.mycode.CityDO'

"<if test='#{description,jdbcType=VARCHAR} != null'>description = #{description,jdbcType=VARCHAR},</if>",

I appreciate if someone can tell me how to make it working, and if there is a reference solution (even an xml-based one is fine).

Upvotes: 1

Views: 5314

Answers (1)

ave
ave

Reputation: 3594

The following should work.

<if test='description != null'>

The value of test attribute is evaluated by OGNL while #{} is a MyBatis way of referencing parameter/property. And, in your case, jdbcTypes may be unnecessary (there is no harm, though).

Upvotes: 0

Related Questions