SQL语句中公共字段的自动填充方法
<p><span><strong>1. 前言</strong></span></p>
<p>
我们在设计数据库的时候一定会带上新增、更新的时间、操作者等审计信息。 之所以带这些信息是因为假如有一天公司的数据库被人为删了，尽管可能有数据库备份可以恢复数据。但是我们仍然需要追踪到这个事是谁干的，在什么时间干的，具体干了哪些事等等，方便定责和修补。但是我们变更每条数据都要去显式变更这些信息就十分繁琐，我们希望无感知的来处理这些信息。</p>
<p>
<span><strong>2. 通用方式</strong></span></p>
<p>
那么有什么好的解决思路呢？在Spring Data框架中提供@CreatedBy和@LastModifiedBy来捕捉谁创建或修改的实体以及@CreatedDate和@LastModifiedDate来捕捉合适创建或修改了实体。如果你使用相关的框架就可以使用这些特性。那么其实我们知道国内Spring Data JDBC、Spring Data JPA并不是主流，主流的是Mybatis。那么我们有哪些选择？</p>
<p>
<strong>2.1 开发Mybatis审计插件</strong></p>
<p>
如果你使用了原生的Mybatis可以编写一个审计插件来实现这些功能。我在之前讲解过Mybatis插件的教程，并不是非常难的事。如果你想拿来就用，其实GitHub上提供了很多可供选择的Mybatis审计组件，本来我打算手写一个，但是确实人家写的好。你可以通过关键词Mybatis Audit来搜索到它们选择一款最适合你的。</p>
<p>
<strong>2.2 Mybatis Plus 自动填充</strong></p>
<p>
如果你使用了Mybatis Plus，可以借助于其自动填充功能来实现。</p>
<blockquote>
<p>
基于 Mybatis Plus 3.3.0</p>
</blockquote>
<p>
只需要实现MetaObjectHandler接口：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterjava" id="highlighter_151818">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
<div class="line number13 index12 alt2">
13</div>
<div class="line number14 index13 alt1">
14</div>
<div class="line number15 index14 alt2">
15</div>
<div class="line number16 index15 alt1">
16</div>
<div class="line number17 index16 alt2">
17</div>
<div class="line number18 index17 alt1">
18</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="java color1">@Component</code>
</div>
<div class="line number2 index1 alt1">
<code class="java keyword">public</code> <code class="java keyword">class</code> <code class="java plain">MybatisAuditHandler </code><code class="java keyword">implements</code> <code class="java plain">MetaObjectHandler {</code>
</div>
<div class="line number3 index2 alt2">
<code class="java spaces"> </code><code class="java color1">@Override</code>
</div>
<div class="line number4 index3 alt1">
<code class="java spaces"> </code><code class="java keyword">public</code> <code class="java keyword">void</code> <code class="java plain">insertFill(MetaObject metaObject) {</code>
</div>
<div class="line number5 index4 alt2">
<code class="java spaces"> </code><code class="java comments">// 声明自动填充字段的逻辑。</code>
</div>
<div class="line number6 index5 alt1">
<code class="java spaces"> </code><code class="java plain">String userId = AuthHolder.getCurrentUserId();</code>
</div>
<div class="line number7 index6 alt2">
<code class="java spaces"> </code><code class="java keyword">this</code><code class="java plain">.strictInsertFill(metaObject,</code><code class="java string">"creator"</code><code class="java plain">,String.</code><code class="java keyword">class</code><code class="java plain">, userId);</code>
</div>
<div class="line number8 index7 alt1">
<code class="java spaces"> </code><code class="java keyword">this</code><code class="java plain">.strictInsertFill(metaObject,</code><code class="java string">"createTime"</code><code class="java plain">, LocalDateTime.</code><code class="java keyword">class</code><code class="java plain">,LocalDateTime.now());</code>
</div>
<div class="line number9 index8 alt2">
<code class="java spaces"> </code><code class="java plain">}</code>
</div>
<div class="line number10 index9 alt1">
</div>
<div class="line number11 index10 alt2">
<code class="java spaces"> </code><code class="java color1">@Override</code>
</div>
<div class="line number12 index11 alt1">
<code class="java spaces"> </code><code class="java keyword">public</code> <code class="java keyword">void</code> <code class="java plain">updateFill(MetaObject metaObject) {</code>
</div>
<div class="line number13 index12 alt2">
<code class="java spaces"> </code><code class="java comments">// 声明自动填充字段的逻辑。</code>
</div>
<div class="line number14 index13 alt1">
<code class="java spaces"> </code><code class="java plain">String userId = AuthHolder.getCurrentUserId();</code>
</div>
<div class="line number15 index14 alt2">
<code class="java spaces"> </code><code class="java keyword">this</code><code class="java plain">.strictUpdateFill(metaObject,</code><code class="java string">"updater"</code><code class="java plain">,String.</code><code class="java keyword">class</code><code class="java plain">,userId);</code>
</div>
<div class="line number16 index15 alt1">
<code class="java spaces"> </code><code class="java keyword">this</code><code class="java plain">.strictUpdateFill(metaObject,</code><code class="java string">"updateTime"</code><code class="java plain">, LocalDateTime.</code><code class="java keyword">class</code><code class="java plain">,LocalDateTime.now());</code>
</div>
<div class="line number17 index16 alt2">
<code class="java spaces"> </code><code class="java plain">}</code>
</div>
<div class="line number18 index17 alt1">
<code class="java plain">}</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
然后我们扩展一下Mybatis Plus的Model<T>把公共审计字段放进去并声明对应的填充策略：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterjava" id="highlighter_280337">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="java keyword">public</code> <code class="java keyword">abstract</code> <code class="java keyword">class</code> <code class="java plain">BaseEntity<T </code><code class="java keyword">extends</code> <code class="java plain">Model<?>> </code><code class="java keyword">extends</code> <code class="java plain">Model<T> {</code>
</div>
<div class="line number2 index1 alt1">
</div>
<div class="line number3 index2 alt2">
<code class="java spaces"> </code><code class="java color1">@TableField</code><code class="java plain">(fill = FieldFill.INSERT)</code>
</div>
<div class="line number4 index3 alt1">
<code class="java spaces"> </code><code class="java keyword">private</code> <code class="java plain">String creator;</code>
</div>
<div class="line number5 index4 alt2">
<code class="java spaces"> </code><code class="java color1">@TableField</code><code class="java plain">(fill = FieldFill.INSERT)</code>
</div>
<div class="line number6 index5 alt1">
<code class="java spaces"> </code><code class="java keyword">private</code> <code class="java plain">LocalDateTime addTime;</code>
</div>
<div class="line number7 index6 alt2">
<code class="java spaces"> </code><code class="java color1">@TableField</code><code class="java plain">(fill = FieldFill.UPDATE)</code>
</div>
<div class="line number8 index7 alt1">
<code class="java spaces"> </code><code class="java keyword">private</code> <code class="java plain">String updater;</code>
</div>
<div class="line number9 index8 alt2">
<code class="java spaces"> </code><code class="java color1">@TableField</code><code class="java plain">(fill = FieldFill.UPDATE)</code>
</div>
<div class="line number10 index9 alt1">
<code class="java spaces"> </code><code class="java keyword">private</code> <code class="java plain">LocalDateTime updateTime;</code>
</div>
<div class="line number11 index10 alt2">
<code class="java plain">}</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
最后我们的实体类不再直接继承Model<T>改为上面的BaseEntity<T>：</p>
<div class="jb51code">
<div>
<div class="syntaxhighlighterjava" id="highlighter_346693">
<div class="toolbar">
<span>?</span>
</div>
<table border="0" cellpadding="0" cellspacing="0"><tbody><tr>
<td class="gutter">
<div class="line number1 index0 alt2">
1</div>
<div class="line number2 index1 alt1">
2</div>
<div class="line number3 index2 alt2">
3</div>
<div class="line number4 index3 alt1">
4</div>
<div class="line number5 index4 alt2">
5</div>
<div class="line number6 index5 alt1">
6</div>
<div class="line number7 index6 alt2">
7</div>
<div class="line number8 index7 alt1">
8</div>
<div class="line number9 index8 alt2">
9</div>
<div class="line number10 index9 alt1">
10</div>
<div class="line number11 index10 alt2">
11</div>
<div class="line number12 index11 alt1">
12</div>
</td>
<td class="code">
<div class="container">
<div class="line number1 index0 alt2">
<code class="java color1">@Data</code>
</div>
<div class="line number2 index1 alt1">
<code class="java color1">@EqualsAndHashCode</code><code class="java plain">(callSuper = </code><code class="java keyword">false</code><code class="java plain">)</code>
</div>
<div class="line number3 index2 alt2">
<code class="java keyword">public</code> <code class="java keyword">class</code> <code class="java plain">UserInfo </code><code class="java keyword">extends</code> <code class="java plain">BaseEntity<UserInfo> {</code>
</div>
<div class="line number4 index3 alt1">
<code class="java spaces"> </code><code class="java color1">@TableId</code><code class="java plain">(value = </code><code class="java string">"user_id"</code><code class="java plain">, type = IdType.ASSIGN_ID)</code>
</div>
<div class="line number5 index4 alt2">
<code class="java spaces"> </code><code class="java keyword">private</code> <code class="java plain">String userId;</code>
</div>
<div class="line number6 index5 alt1">
<code class="java spaces"> </code><code class="java keyword">private</code> <code class="java plain">String username;</code>
</div>
<div class="line number7 index6 alt2">
</div>
<div class="line number8 index7 alt1">
<code class="java spaces"> </code><code class="java color1">@Override</code>
</div>
<div class="line number9 index8 alt2">
<code class="java spaces"> </code><code class="java keyword">protected</code> <code class="java plain">Serializable pkVal() {</code>
</div>
<div class="line number10 index9 alt1">
<code class="java spaces"> </code><code class="java keyword">return</code> <code class="java keyword">this</code><code class="java plain">.userId;</code>
</div>
<div class="line number11 index10 alt2">
<code class="java spaces"> </code><code class="java plain">}</code>
</div>
<div class="line number12 index11 alt1">
<code class="java plain">}</code>
</div>
</div>
</td>
</tr></tbody></table>
</div>
</div>
<div class="codetool" id="codetool">
<div class="code_n">
<textarea></textarea>
</div>
</div>
</div>
<p>
这样我们就不用再关心这几个公共字段了，当然你可以根据需要添加更多你需要填充的字段。</p>
<p>
<span><strong>3. 总结</strong></span></p>
<p>
今天我们SQL审计中的一些公共字段的自动填充的常用方案进行了一些介绍，特别对Mybatis Plus提供的功能进行了介绍相信能够帮助你简化一些样板代码的编写。但是SQL审计并不仅仅这么简单，根据你的业务的不同会有不同的设计。如果设计的更加精细化的话，会通过镜像或探针的方式采集所有数据库的访问流量，并基于SQL语法、语义的解析技术，记录下数据库的所有访问和操作行为。有空可以从网上获取相关的资料进行了解。</p>
<p>
到此这篇关于SQL语句中公共字段的自动填充方法的文章就介绍到这了,更多相关SQL语句公共字段自动填充内容请搜索以前的文章或继续浏览下面的相关文章希望大家以后多多支持！</p>
<p>
原文链接：https://www.cnblogs.com/felordcn/p/13752898.html</p>
頁:
[1]