SpringBoot Mybatis Insert null Fields

I’m using SpringBoot and MyBatis. When tried to insert/update some rows into a table, I got the fllowing:

org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'remark' cannot be null
### The error may exist in org/chobit/service/mapper/UserMapper.java (best guess)
### The error may involve org.chobit.service.mapper.UserMapper.updateById-Inline
### The error occurred while setting parameters
### SQL: update user set  username=?, `remark`=? where id=?
### Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'remark' cannot be null
; Column 'remark' cannot be null; nested exception is java.sql.SQLIntegrityConstraintViolationException: Column 'remark' cannot be null
	at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:87)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)
	at com.sun.proxy.$Proxy88.update(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67)

org.springframework.dao.DataIntegrityViolationException:

### Error updating database. Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'remark' cannot be null

### The error may exist in org/chobit/service/mapper/UserMapper.java (best guess)

### The error may involve org.chobit.service.mapper.UserMapper.updateById-Inline

### The error occurred while setting parameters

### SQL: update user set username=?, `remark`=? where id=?

### Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'remark' cannot be null

; Column 'remark' cannot be null; nested exception is java.sql.SQLIntegrityConstraintViolationException: Column 'remark' cannot be null

at org.springframework.jdbc.support.SQLExceptionSubclassTranslator.doTranslate(SQLExceptionSubclassTranslator.java:87)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)

at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)

at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:73)

at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:446)

at com.sun.proxy.$Proxy88.update(Unknown Source)

at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:294)

at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:67)

The column remark in the table user was setted to NOT NULL, and the value of the column to insert was null. That’s why I received the error.

This is the update method in class UserMapper:

@Update("update user set username=#{username}, remark=#{remark} where id=#{id}")
boolean update(User user);

1 2	@Update("update user set username=#{username}, remark=#{remark} where id=#{id}") boolean update(User user);

I can fix the error simply by setting an empty string to the field remark in object user. But the same problem exists in some other tables, I have to repeat the operation many times, which seems really ugly.

The best solution is to set default value for all null fields automatically and silently.

PageHelper (the pagination plugin for springboot-mybatis) showed me the way:

add a MyBatis interceptor to obtain the argument instance before executing insert/update operation
set default value for null fields through java reflection

First, let’s define the interceptor:

/**
 * MyBatis interceptor,set default value for null field 
 *
 * @author robin
 */
@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})})
public class MybatisNullInterceptor implements Interceptor {

    private static final Logger logger = LoggerFactory.getLogger(MybatisNullInterceptor.class);

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object parameter = args[1];

        padNullFields(parameter);

        Executor executor = (Executor) invocation.getTarget();
        return executor.update(ms, parameter);
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
    }

}

/**

* MyBatis interceptor,set default value for null field

* @author robin

@Intercepts({@Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})})

public class MybatisNullInterceptor implements Interceptor {

private static final Logger logger = LoggerFactory.getLogger(MybatisNullInterceptor.class);

@Override

public Object intercept(Invocation invocation) throws Throwable {

Object[] args = invocation.getArgs();

MappedStatement ms = (MappedStatement) args[0];

Object parameter = args[1];

padNullFields(parameter);

Executor executor = (Executor) invocation.getTarget();

return executor.update(ms, parameter);

}

@Override

public Object plugin(Object target) {

return Plugin.wrap(target, this);

}

@Override

public void setProperties(Properties properties) {

}

The key is the annotation @Signature：

@Intercepts({
        @Signature(type = Executor.class, method = "update",
                args = {MappedStatement.class, Object.class})
})

@Intercepts({

@Signature(type = Executor.class, method = "update",

args = {MappedStatement.class, Object.class})

})

The annotation specified which class and method to intercept.
The interface org.apache.ibatis.executor.Executordefined all the db operations, including INSERT and UPDATE which are completed by method update.

Second, let’s realise the method of padNullFields:

    private void padNullFields(Object parameter) {
        PropertyDescriptor[] targetPds = BeanUtils.getPropertyDescriptors(parameter.getClass());

        for (PropertyDescriptor pd : targetPds) {
            try {
                padNullField(pd, parameter);
            } catch (Exception e) {
                logger.error("pad null fields failed", e);
            }
        }
    }


    private void padNullField(PropertyDescriptor pd, Object parameter)
            throws InvocationTargetException, IllegalAccessException {
        Method readMethod = pd.getReadMethod();
        Method writeMethod = pd.getWriteMethod();

        if (null == readMethod || null == writeMethod) {
            return;
        }
        if (!Modifier.isPublic(readMethod.getDeclaringClass().getModifiers())) {
            readMethod.setAccessible(true);
        }
        Object value = readMethod.invoke(parameter);
        if (null != value) {
            return;
        }

        if (!Modifier.isPublic(writeMethod.getDeclaringClass().getModifiers())) {
            writeMethod.setAccessible(true);
        }

        Class<?> clazz = pd.getPropertyType();

        setDefaultValue(parameter, clazz, writeMethod);
    }


    /**
     * set default value for null field
     */
    private void setDefaultValue(Object parameter, Class<?> fieldClass, Method writeMethod)
            throws InvocationTargetException, IllegalAccessException {
        if (fieldClass.isAssignableFrom(String.class)) {
            writeMethod.invoke(parameter, "");
        }
        if (fieldClass.isAssignableFrom(LocalDateTime.class)) {
            writeMethod.invoke(parameter, LocalDateTime.now());
        }
    }

private void padNullFields(Object parameter) {

PropertyDescriptor[] targetPds = BeanUtils.getPropertyDescriptors(parameter.getClass());

for (PropertyDescriptor pd : targetPds) {

try {

padNullField(pd, parameter);

} catch (Exception e) {

logger.error("pad null fields failed", e);

}

private void padNullField(PropertyDescriptor pd, Object parameter)

throws InvocationTargetException, IllegalAccessException {

Method readMethod = pd.getReadMethod();

Method writeMethod = pd.getWriteMethod();

if (null == readMethod || null == writeMethod) {

return;

}

if (!Modifier.isPublic(readMethod.getDeclaringClass().getModifiers())) {

readMethod.setAccessible(true);

}

Object value = readMethod.invoke(parameter);

if (null != value) {

return;

}

if (!Modifier.isPublic(writeMethod.getDeclaringClass().getModifiers())) {

writeMethod.setAccessible(true);

}

Class<?> clazz = pd.getPropertyType();

setDefaultValue(parameter, clazz, writeMethod);

}

/**

* set default value for null field

private void setDefaultValue(Object parameter, Class<?> fieldClass, Method writeMethod)

throws InvocationTargetException, IllegalAccessException {

if (fieldClass.isAssignableFrom(String.class)) {

writeMethod.invoke(parameter, "");

}

if (fieldClass.isAssignableFrom(LocalDateTime.class)) {

writeMethod.invoke(parameter, LocalDateTime.now());

}

I used java reflection to set default value for null fields. In my project, I only need to set default value for fields with types of String and LocalDateTime. If you want to add more, please change the method setDefaultValue.

The last work is letting the interceptor work in springboot, all we need to do is to add the interceptor into sqlSessionFactory：

@Configuration
public class MybatisExtConfigure {

    @Autowired
    private List<SqlSessionFactory> sqlSessionFactoryList;


    @PostConstruct
    public void addInterceptor() {
        MybatisNullInterceptor interceptor = new MybatisNullInterceptor();
        for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {
            org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();
            if (!containsInterceptor(configuration, interceptor)) {
                configuration.addInterceptor(interceptor);
            }
        }
    }

    private boolean containsInterceptor(org.apache.ibatis.session.Configuration configuration, Interceptor interceptor) {
        try {
            return configuration.getInterceptors().contains(interceptor);
        } catch (Exception e) {
            return false;
        }
    }
}

@Configuration

public class MybatisExtConfigure {

@Autowired

private List<SqlSessionFactory> sqlSessionFactoryList;

@PostConstruct

public void addInterceptor() {

MybatisNullInterceptor interceptor = new MybatisNullInterceptor();

for (SqlSessionFactory sqlSessionFactory : sqlSessionFactoryList) {

org.apache.ibatis.session.Configuration configuration = sqlSessionFactory.getConfiguration();

if (!containsInterceptor(configuration, interceptor)) {

configuration.addInterceptor(interceptor);

}

private boolean containsInterceptor(org.apache.ibatis.session.Configuration configuration, Interceptor interceptor) {

try {

return configuration.getInterceptors().contains(interceptor);

} catch (Exception e) {

return false;

}

After adding all the code into my project and rerunning test case, all the Exceptions disappeared.

End!

Chobit

A coder's notebook

SpringBoot Mybatis Insert null Fields