有关Mybatis-Plus常用功能之前有做过一篇总结:
一、什么是SQL注入器
我们在使用Mybatis-Plus时,dao层都会去继承BaseMapper接口,这样就可以用BaseMapper接口所有的方法,
BaseMapper中每一个方法其实就是一个SQL注入器
在Mybatis-Plus的核心(core)包下,提供的默认可注入方法有这些:
那如果我们想自定义SQL注入器呢,我们该如何去做?
比如在Mybatis-Plus中调用updateById方法进行数据更新默认情况下是不能更新空值字段的。
而在实际开发过程中,往往会遇到需要将字段值更新为空值的情况。
那如何让Mybatis-Plus支持空值更新呢?
如果仅是想实现支持更新空值字段并不需要我们自定义SQL注入器,因为Mybatis-Plus提供了几个扩展SQL注入器。
二、内置扩展SQL注入器有哪些?
1、自带扩展SQL注入器
Mybatis-Plus 扩展SQL注入器在扩展包下,为我们提供了可扩展的可注入方法:
AlwaysUpdateSomeColumnById
: 根据id更新字段(全量更新不忽略null字段),updateById默认会自动忽略实体中null值字段。
InsertBatchSomeColumn
: 真实批量插入,saveBatch其实是伪批量插入。
LogicDeleteBatchByIds
: 逻辑删除增加填充功能,比如删除的时候填充更新时间、更新人。
Upsert
: 插入一条数据(选择字段插入)。
2、SQL注入器全局配置
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-java"><span style="color:#75715e">@Component</span>
<span style="color:#f92672">public</span> <span style="color:#f92672">class</span> <span style="color:#a6e22e">MySqlInjector</span> <span style="color:#f92672">extends</span> <span style="color:#a6e22e">DefaultSqlInjector</span> {
<span style="color:#75715e">@Override</span>
<span style="color:#f92672">public</span> List<AbstractMethod> <span style="color:#a6e22e">getMethodList</span><span style="color:#f8f8f2">(Class<?> mapperClass, TableInfo tableInfo)</span> {
List<AbstractMethod> methodList = <span style="color:#e6db74">super</span>.getMethodList(mapperClass, tableInfo);
<span style="color:#75715e">/**
* 把两个扩展内置扩展SQL注入器注入
*/</span>
methodList.add(<span style="color:#f92672">new</span> <span style="color:#a6e22e">InsertBatchSomeColumn</span>(i -> i.getFieldFill() != FieldFill.UPDATE));
methodList.add(<span style="color:#f92672">new</span> <span style="color:#a6e22e">AlwaysUpdateSomeColumnById</span>(i -> i.getFieldFill() != FieldFill.INSERT));
<span style="color:#f92672">return</span> methodList;
}
}
</code></span></span>
3、自定义Mapper
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-java"><span style="color:#f92672">public</span> <span style="color:#f92672">interface</span> <span style="color:#a6e22e">MyBaseMapper</span><T> <span style="color:#f92672">extends</span> <span style="color:#a6e22e">BaseMapper</span><T> {
<span style="color:#75715e">/**
* 全字段更新,不会忽略null值
*
* <span style="color:#808080">@param</span> entity 实体对象
*/</span>
<span style="color:#e6db74">int</span> <span style="color:#a6e22e">alwaysUpdateSomeColumnById</span><span style="color:#f8f8f2">(T entity)</span>;
<span style="color:#75715e">/**
* 全量插入,等价于insert
*
* <span style="color:#808080">@param</span> entityList 实体集合
*/</span>
<span style="color:#e6db74">int</span> <span style="color:#a6e22e">insertBatchSomeColumn</span><span style="color:#f8f8f2">(List<T> entityList)</span>;
}
</code></span></span>
三、扩展SQL注入器示例测试
1、用户表
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-sql"><span style="color:#f92672">CREATE</span> <span style="color:#f92672">TABLE</span> `<span style="color:#f92672">user</span>` (
`id` <span style="color:#e6db74">int</span> unsigned AUTO_INCREMENT COMMENT <span style="color:#e6db74">'主键'</span>,
`username` <span style="color:#e6db74">varchar</span>(<span style="color:#ae81ff">128</span>) COMMENT <span style="color:#e6db74">'用户名'</span>,
`phone` <span style="color:#e6db74">varchar</span>(<span style="color:#ae81ff">32</span>) COMMENT <span style="color:#e6db74">'手机号'</span>,
`sex` <span style="color:#e6db74">char</span>(<span style="color:#ae81ff">1</span>) COMMENT <span style="color:#e6db74">'性别'</span>,
`create_time` datetime COMMENT <span style="color:#e6db74">'创建时间'</span>,
`update_time` datetime COMMENT <span style="color:#e6db74">'更新时间'</span>,
`deleted` tinyint <span style="color:#f92672">DEFAULT</span> <span style="color:#e6db74">'0'</span> COMMENT <span style="color:#e6db74">'1、删除 0、未删除'</span>,
<span style="color:#f92672">PRIMARY</span> KEY (`id`)
) ENGINE<span style="color:#ab5656">=</span>InnoDB AUTO_INCREMENT<span style="color:#ab5656">=</span><span style="color:#ae81ff">1</span>
</code></span></span>
2、创建对应实体
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-java"><span style="color:#75715e">@Data</span>
<span style="color:#75715e">@Accessors(chain = true)</span>
<span style="color:#75715e">@TableName("user")</span>
<span style="color:#f92672">public</span> <span style="color:#f92672">class</span> <span style="color:#a6e22e">UserDO</span> <span style="color:#f92672">implements</span> <span style="color:#a6e22e">Serializable</span> {
<span style="color:#f92672">private</span> <span style="color:#f92672">static</span> <span style="color:#f92672">final</span> <span style="color:#e6db74">long</span> <span style="color:#e6db74">serialVersionUID</span> <span style="color:#ab5656">=</span> <span style="color:#ae81ff">1L</span>;
<span style="color:#75715e">@TableId(value = "id", type = IdType.AUTO)</span>
<span style="color:#f92672">private</span> Integer id;
<span style="color:#75715e">/**
* 用户名
*/</span>
<span style="color:#75715e">@TableField("username")</span>
<span style="color:#f92672">private</span> String username;
<span style="color:#75715e">/**
* 手机号
*/</span>
<span style="color:#75715e">@TableField("phone")</span>
<span style="color:#f92672">private</span> String phone;
<span style="color:#75715e">/**
* 性别
*/</span>
<span style="color:#75715e">@TableField("sex")</span>
<span style="color:#f92672">private</span> String sex;
<span style="color:#75715e">/**
* 创建时间
*/</span>
<span style="color:#75715e">@TableField(value = "create_time",fill = FieldFill.INSERT)</span>
<span style="color:#f92672">private</span> LocalDateTime createTime;
<span style="color:#75715e">/**
* 更新时间
*/</span>
<span style="color:#75715e">@TableField(value = "update_time",fill = FieldFill.INSERT_UPDATE)</span>
<span style="color:#f92672">private</span> LocalDateTime updateTime;
<span style="color:#75715e">/**
* 1、删除 0、未删除
*/</span>
<span style="color:#75715e">@TableField(value = "deleted",fill = FieldFill.INSERT)</span>
<span style="color:#f92672">private</span> Integer deleted;
}
</code></span></span>
其它有关代码这里就不粘贴了,具体看项目源码。
我们自定义的Mapper不再继承BaseMapper而是继承MyBaseMapper
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-java"> <span style="color:#75715e">/**
* 通用mapper接口,以后创建其他mapper接口时,不再继承BaseMapper,而是继承MyBaseMapper
*/</span>
<span style="color:#75715e">@Mapper</span>
<span style="color:#f92672">public</span> <span style="color:#f92672">interface</span> <span style="color:#a6e22e">UserMapper</span> <span style="color:#f92672">extends</span> <span style="color:#a6e22e">MyBaseMapper</span><UserDO> {
}
</code></span></span>
3、测试代码
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-java"><span style="color:#75715e">@SpringBootTest</span>
<span style="color:#75715e">@RunWith(SpringRunner.class)</span>
<span style="color:#75715e">@ComponentScan("com.jincou.mybatisplus.dao")</span>
<span style="color:#f92672">public</span> <span style="color:#f92672">class</span> <span style="color:#a6e22e">SqlInjectorTest</span> {
<span style="color:#75715e">@Autowired</span>
<span style="color:#f92672">private</span> UserMapper mapper;
<span style="color:#75715e">@Test</span>
<span style="color:#f92672">public</span> <span style="color:#f92672">void</span> <span style="color:#a6e22e">alwaysUpdateSomeColumnById</span><span style="color:#f8f8f2">()</span> {
<span style="color:#e6db74">UserDO</span> <span style="color:#e6db74">user</span> <span style="color:#ab5656">=</span> <span style="color:#f92672">new</span> <span style="color:#a6e22e">UserDO</span>();
user.setUsername(<span style="color:#e6db74">"小小"</span>);
user.setPhone(<span style="color:#ae81ff">null</span>);
user.setSex(<span style="color:#e6db74">"女"</span>);
user.setId(<span style="color:#ae81ff">1</span>);
mapper.alwaysUpdateSomeColumnById(user);
}
<span style="color:#75715e">@Test</span>
<span style="color:#f92672">public</span> <span style="color:#f92672">void</span> <span style="color:#a6e22e">insertBatchSomeColumn</span><span style="color:#f8f8f2">()</span> {
<span style="color:#e6db74">UserDO</span> <span style="color:#e6db74">user</span> <span style="color:#ab5656">=</span> <span style="color:#f92672">new</span> <span style="color:#a6e22e">UserDO</span>();
user.setUsername(<span style="color:#e6db74">"zhangsan"</span>);
user.setPhone(<span style="color:#e6db74">"13811111111"</span>);
user.setSex(<span style="color:#e6db74">"女"</span>);
<span style="color:#e6db74">UserDO</span> <span style="color:#e6db74">user1</span> <span style="color:#ab5656">=</span> <span style="color:#f92672">new</span> <span style="color:#a6e22e">UserDO</span>();
user1.setUsername(<span style="color:#e6db74">"lisi"</span>);
user1.setPhone(<span style="color:#e6db74">"13822222222"</span>);
user1.setSex(<span style="color:#e6db74">"男"</span>);
ArrayList<UserDO> userDOS = Lists.newArrayList(user, user1);
mapper.insertBatchSomeColumn(userDOS);
}
}
</code></span></span>
运行结果
alwaysUpdateSomeColumnById方法
insertBatchSomeColumn方法
成功!
四、如何自定义SQL注入器?
在实际开发过程中,当Mybatis-Plus自带的一些SQL注入器不满足我们的条件时,我们就需要自定义SQL注入器,整个流程也非常简单
这里我们以一个很简单的findAll方法为例进行学习。
在MyBaseMapper中添加findAll方法
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-java"><span style="color:#f92672">public</span> <span style="color:#f92672">interface</span> <span style="color:#a6e22e">MyBaseMapper</span><T> <span style="color:#f92672">extends</span> <span style="color:#a6e22e">BaseMapper</span><T> {
<span style="color:#75715e">/**
* 查询所有用户
*/</span>
List<T> <span style="color:#a6e22e">findAll</span><span style="color:#f8f8f2">()</span>;
}
</code></span></span>
2、编写FindAll SQL注入器
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-java"><span style="color:#f92672">public</span> <span style="color:#f92672">class</span> <span style="color:#a6e22e">FindAll</span> <span style="color:#f92672">extends</span> <span style="color:#a6e22e">AbstractMethod</span> {
<span style="color:#f92672">public</span> <span style="color:#a6e22e">FindAll</span><span style="color:#f8f8f2">()</span> {
<span style="color:#e6db74">super</span>(<span style="color:#e6db74">"findAll"</span>);
}
<span style="color:#f92672">public</span> <span style="color:#a6e22e">FindAll</span><span style="color:#f8f8f2">(String methodName)</span> {
<span style="color:#e6db74">super</span>(methodName);
}
<span style="color:#75715e">@Override</span>
<span style="color:#f92672">public</span> MappedStatement <span style="color:#a6e22e">injectMappedStatement</span><span style="color:#f8f8f2">(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo)</span> {
<span style="color:#75715e">/* 执行 SQL ,动态 SQL 参考类 SqlMethod */</span>
<span style="color:#e6db74">String</span> <span style="color:#e6db74">sql</span> <span style="color:#ab5656">=</span> <span style="color:#e6db74">"select * from "</span> + tableInfo.getTableName();
<span style="color:#e6db74">SqlSource</span> <span style="color:#e6db74">sqlSource</span> <span style="color:#ab5656">=</span> languageDriver.createSqlSource(configuration, sql, modelClass);
<span style="color:#f92672">return</span> <span style="color:#e6db74">this</span>.addSelectMappedStatementForTable(mapperClass, sqlSource, tableInfo);
}
}
</code></span></span>
3、注册到Spring容器
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-java"><span style="color:#75715e">@Component</span>
<span style="color:#f92672">public</span> <span style="color:#f92672">class</span> <span style="color:#a6e22e">MySqlInjector</span> <span style="color:#f92672">extends</span> <span style="color:#a6e22e">DefaultSqlInjector</span> {
<span style="color:#75715e">@Override</span>
<span style="color:#f92672">public</span> List<AbstractMethod> <span style="color:#a6e22e">getMethodList</span><span style="color:#f8f8f2">(Class<?> mapperClass, TableInfo tableInfo)</span> {
List<AbstractMethod> methodList = <span style="color:#e6db74">super</span>.getMethodList(mapperClass, tableInfo);
<span style="color:#75715e">/**
* 自定义SQL注入器注入
*/</span>
methodList.add(<span style="color:#f92672">new</span> <span style="color:#a6e22e">FindAll</span>());
<span style="color:#f92672">return</span> methodList;
}
}
</code></span></span>
4、测试
<span style="color:#4b4b4b"><span style="background-color:#ffffff"><code class="language-java"> <span style="color:#75715e">@Test</span>
<span style="color:#f92672">public</span> <span style="color:#f92672">void</span> <span style="color:#a6e22e">findAll</span><span style="color:#f8f8f2">()</span> {
List<UserDO> userDOS = mapper.findAll();
}
</code></span></span>
成功!
补充
项目地址: GitHub - yudiandemingzi/spring-boot-study: springboot学习项目