Resolve the mybatis plus field is null or an empty string cannot be saved to the database

Background

mybatis and plus are integrated in the project. Today, during a routine addition, deletion and modification check in the background, when the field value is null, this field will not be saved to the database

Solution

Add to the field

@TableField(strategy = FieldStrategy.IGNORED)

strategy field update insertion policy attribute description:

IGNORED (0): "Ignore judgment", all fields are updated and inserted

NOT_NULL (1): "Non-NULL judgment", only non-NULL values are updated and inserted

NOT_EMPTY (2): "Non-null judgment", only updating and inserting non-NULL values and non-null strings

The other one can be configured globally and has not been practiced by hand.

Supplement: Mybatis query data part of the field is displayed as null, how to turn into an empty string ("")

1. First define an handler to turn the field null into an empty string ("")

2. In Mapper. xml, add the typeHandler attribute to the fields that may be empty to specify the full path of the processed handler class.

CustomStringTypeHandler.java

package com.wang.common.mybatis.handler;
import org.apache.ibatis.executor.result.ResultMapException;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * @Name: CustomStringTypeHandler
 * @Desc:  Customize mybatis Handling class, setting the null Returns an empty string ' ) 
 * @Author: Administrator
 * @Date: 2019-09-03 18:20
 */
@MappedTypes({String.class})
@MappedJdbcTypes(JdbcType.VARCHAR)
public class CustomStringTypeHandler extends BaseTypeHandler<String> {
  @Override
  public String getResult(ResultSet rs, String columnName) {
    String result;
    try {
      result = getNullableResult(rs, columnName);
    } catch (Exception e) {
      throw new ResultMapException("Error attempting to get column '" + columnName + "' from result set. Cause: " + e, e);
    }
    return result;
  }
  @Override
  public void setNonNullParameter(PreparedStatement ps, int i, String parameter, JdbcType jdbcType)
      throws SQLException {
    ps.setString(i, parameter);
  }
  @Override
  public String getNullableResult(ResultSet rs, String columnName)
      throws SQLException {
    return rs.getString(columnName) == null? "" : rs.getString(columnName);
  }
  @Override
  public String getNullableResult(ResultSet rs, int columnIndex)
      throws SQLException {
    return rs.getString(columnIndex) == null? "" : rs.getString(columnIndex);
  }
  @Override
  public String getNullableResult(CallableStatement cs, int columnIndex)
      throws SQLException {
    return cs.getString(columnIndex) == null? "" : cs.getString(columnIndex);
  }
}

Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wang.sss.fw.mapper.BusinessTripMapper">
 <resultMap id="BaseResultMap" type="com.wang.sss.fw.pojo.BusinessTrip">
  <result column="REQUEST_ID" jdbcType="VARCHAR" property="requestId" />
  <result column="JOB_NUMBER" jdbcType="VARCHAR" property="jobNumber" />
  <result column="REQUEST_DATE" jdbcType="VARCHAR" property="requestDate" />
  <result column="DEPARTMENT" jdbcType="VARCHAR" property="department" />
  <result column="BUSINESS_DAYS" jdbcType="VARCHAR" property="businessDays"/>
  <result column="CFD" jdbcType="VARCHAR" property="cfd" />
  <result column="MDD" jdbcType="VARCHAR" property="mdd" />
  <result column="START_TIME" jdbcType="VARCHAR" property="startTime" />
  <result column="END_TIME" jdbcType="VARCHAR" property="endTime" />
  <result column="REASON" jdbcType="VARCHAR" property="reason" typeHandler="com.wang.common.mybatis.handler.CustomStringTypeHandler"/>
  <result column="REMARK" jdbcType="VARCHAR" property="remark" typeHandler="com.wang.common.mybatis.handler.CustomStringTypeHandler"/>
 </resultMap>
</mapper> 

No typeHandler attribute is added, and the previous query results are processed:

BusinessTrip(requestId=11925, jobNumber=5721, requestDate=2019-05-06, department=57, businessDays=21, cfd= Shanghai , mdd= Nanjing , startTime=2019-05-06 13:36, endTime=2019-05-07 13:36, reason=null, remark=null)

Add the typeHandler attribute, and the result of processing: (reason and remark fields become empty strings)

BusinessTrip(requestId=11925, jobNumber=5721, requestDate=2019-05-06, department=57, businessDays=21, cfd= Shanghai , mdd= Nanjing , startTime=2019-05-06 13:36, endTime=2019-05-07 13:36, reason=, remark=)