I’m using SpringBoot and MyBatis. When tried to insert/update some rows into a table, I got the fllowing:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
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:
|
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
/** * 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：
|
1 2 3 4 |
@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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
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：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
@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!