【Mybatis】INSERT INTO 遇到NULL怎么处理？

本文主要是介绍【Mybatis】INSERT INTO 遇到NULL怎么处理？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

背景-使用Mybatis手写批量插入Insert方法

表中设置都为非空字段，author默认值为sys。

测试核心代码，author字段为null，插入条件怎么写？

List<Article> ls = new ArrayList<>();
Article article = new Article();
article.setTitle("123");
article.setContent("666");
ls.add(article);
try {articleService.insertBatch1(ls);
} catch (Exception e) {e.printStackTrace();
}

MybatisPlus解决方案

自动填充字段

https://baomidou.com/guides/auto-fill-field/#1-定义实体类

• FieldFill.DEFAULT 完全依赖数据库默认值
• FieldFill.INSERT 插入时填充
• FieldFill.UPDATE 更新时填充

FieldFill.DEFAULT 完全依赖数据库默认值

不做任何处理，author为空，报错了

<!-- 批量插入Article -->
<insert id="insertBatch1" parameterType="java.util.List">INSERT INTO article(title, content, author, create_time, update_time)VALUES<foreach item="article" index="index" collection="list"open="(" separator=")," close=")">#{article.title}, #{article.content},#{article.author},NOW(), NOW()</foreach>
</insert>

org.springframework.dao.DataIntegrityViolationException: 
### Error updating database.  Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'author' cannot be null
### The error may exist in file [/Users/SpringBootDemo/SpringBootDemo-web/target/classes/mapper/ArticleMapper.xml]
### The error may involve com.example.sbb.SpringBootDemo.demos.mapper.ArticleMapper.insertBatch1-Inline
### The error occurred while setting parameters
### SQL: INSERT INTO article(title, content, author, create_time, update_time)         VALUES          (               ?, ?,?,             NOW(), NOW()          )
### Cause: java.sql.SQLIntegrityConstraintViolationException: Column 'author' cannot be null
; Column 'author' cannot be null

FieldFill.INSERT 插入时填充

xml的insertBatch1方法没改，插入成功，原理应该是拦截器处理

Mybatis解决方案

if标签处理

<!-- 批量插入Article -->
<insert id="insertBatch2" parameterType="java.util.List">INSERT INTO article(title, content, author, create_time, update_time)VALUES<foreach item="article" index="index" collection="list"open="(" separator=")," close=")">#{article.title}, #{article.content},<if test="article.author != null">#{article.author}</if><if test="article.author == null">0</if>,NOW(), NOW()</foreach>
</insert>

本方法也能成功，依赖动态标签，动态拼接SQL

问题：如果不在工程里面设置默认值？如何直接使用数据库的默认值？

<!-- 批量插入Article -->
<insert id="insertBatch1" parameterType="java.util.List">INSERT INTO article(title, content, author, create_time, update_time)VALUES<foreach item="article" index="index" collection="list"open="(" separator=")," close=")">#{article.title}, #{article.content},IFNULL(#{article.author}, DEFAULT(author)),NOW(), NOW()</foreach></insert>

这篇关于【Mybatis】INSERT INTO 遇到NULL怎么处理？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---