mybatis自增主键配置
来源:互联网 发布:php判断是否微信浏览器 编辑:程序博客网 时间:2024/05/16 14:04
mybatis进行插入操作时,如果表的主键是自增的,针对不同的数据库相应的操作也不同。基本上经常会遇到的就是 Oracle Sequece 和 Mysql 自增主键,至于其他的手动生成唯一主键的问题在这里就不讨论了,这里主要说明下在mybatis中对于自增主键的配置。
不返回自增主键值
如果考虑到插入数据的主键不作为其他表插入数据的外键使用,那么可以考虑使用这种方式。
Oracle Sequence 配置
<ol class="linenums"><li class="L0"><code><span class="pln"> </span><span class="tag"><sql</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">'TABLE_NAME'</span><span class="tag">></span><span class="pln">TEST_USER</span><span class="tag"></sql></span></code></li><li class="L1"><code><span class="pln"> </span><span class="tag"><sql</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">'TABLE_SEQUENCE'</span><span class="tag">></span><span class="pln">SEQ_TEST_USER_ID.nextval</span><span class="tag"></sql></span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="com"><!-- 注意这里直接调用sequence的nextval函数 --></span></code></li><li class="L4"><code><span class="pln"> </span><span class="tag"><insert</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">"insert"</span><span class="pln"> </span><span class="atn">parameterType</span><span class="pun">=</span><span class="atv">"User"</span><span class="tag">></span></code></li><li class="L5"><code><span class="pln"> insert into </span><span class="tag"><include</span><span class="pln"> </span><span class="atn">refid</span><span class="pun">=</span><span class="atv">"TABLE_NAME"</span><span class="pln"> </span><span class="tag">/></span><span class="pln"> (ID,NAME,AGE)</span></code></li><li class="L6"><code><span class="pln"> values ( </span><span class="tag"><include</span><span class="pln"> </span><span class="atn">refid</span><span class="pun">=</span><span class="atv">"TABLE_SEQUENCE"</span><span class="pln"> </span><span class="tag">/></span><span class="pln"> ,#{name}, #{age} )</span></code></li><li class="L7"><code><span class="pln"> </span><span class="tag"></insert></span></code></li></ol>
当插入语句如上配置时,那么针对如下语句
<ol class="linenums"><li class="L0"><code><span class="pln"> </span><span class="typ">User</span><span class="pln"> user </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">User</span><span class="pun">();</span></code></li><li class="L1"><code><span class="pln"> user</span><span class="pun">.</span><span class="pln">setName</span><span class="pun">(</span><span class="str">"test"</span><span class="pun">);</span></code></li><li class="L2"><code><span class="pln"> user</span><span class="pun">.</span><span class="pln">setAge</span><span class="pun">(</span><span class="lit">24</span><span class="pun">);</span></code></li><li class="L3"><code></code></li><li class="L4"><code><span class="pln"> userMapper</span><span class="pun">.</span><span class="pln">insert</span><span class="pun">(</span><span class="pln">user</span><span class="pun">);</span></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="pln">user</span><span class="pun">.</span><span class="pln">id</span><span class="pun">);</span><span class="pln"> </span><span class="com">// user.id 为空</span></code></li></ol>
user.id
为空,也就是说如上的配置并不能在完成插入操作后将插入时的主键值存放到保存的对象中。Mysql自增主键配置
由于mysql数据库中,可以设置表的主键为自增,所以对于Mysql数据库在mybatis配置插入语句时,不指定插入ID字段即可。主键的自增交由Mysql来管理。
<ol class="linenums"><li class="L0"><code><span class="pln"> </span><span class="tag"><sql</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">'TABLE_NAME'</span><span class="tag">></span><span class="pln">TEST_USER</span><span class="tag"></sql></span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="com"><!-- 注意这里的插入SQL中是没有指明ID字段的! --></span></code></li><li class="L3"><code><span class="pln"> </span><span class="tag"><insert</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">"insert"</span><span class="pln"> </span><span class="atn">parameterType</span><span class="pun">=</span><span class="atv">"User"</span><span class="tag">></span></code></li><li class="L4"><code><span class="pln"> insert into </span><span class="tag"><include</span><span class="pln"> </span><span class="atn">refid</span><span class="pun">=</span><span class="atv">"TABLE_NAME"</span><span class="pln"> </span><span class="tag">/></span><span class="pln"> (NAME,AGE)</span></code></li><li class="L5"><code><span class="pln"> values (#{name}, #{age} )</span></code></li><li class="L6"><code><span class="pln"> </span><span class="tag"></insert></span></code></li></ol>
同样,针对Mysql如此配置mybaits,插入完成后
user.id
为空。
插入后获取自增主键值
上述的情况能满足大部分情况,但有时候我们会遇到类似一对多的那种表结构,在插入多端数据时,需要获取刚刚保存了的一段的主键。那么这个时候,上述的配置就无法满足需要了。为此我们需要使用mybatis提供的<selectKey />
来单独配置针对自增逐渐的处理。
Oracle Sequence 配置
<ol class="linenums"><li class="L0"><code><span class="pln"> </span><span class="tag"><sql</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">'TABLE_NAME'</span><span class="tag">></span><span class="pln">TEST_USER</span><span class="tag"></sql></span></code></li><li class="L1"><code><span class="pln"> </span><span class="tag"><sql</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">'TABLE_SEQUENCE'</span><span class="tag">></span><span class="pln">SEQ_TEST_USER_ID.nextval</span><span class="tag"></sql></span></code></li><li class="L2"><code></code></li><li class="L3"><code><span class="pln"> </span><span class="com"><!-- 注意这里需要先查询自增主键值 --></span></code></li><li class="L4"><code><span class="pln"> </span><span class="tag"><insert</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">"insert"</span><span class="pln"> </span><span class="atn">parameterType</span><span class="pun">=</span><span class="atv">"User"</span><span class="tag">></span></code></li><li class="L5"><code><span class="pln"> </span><span class="tag"><selectKey</span><span class="pln"> </span><span class="atn">keyProperty</span><span class="pun">=</span><span class="atv">"id"</span><span class="pln"> </span><span class="atn">resultType</span><span class="pun">=</span><span class="atv">"int"</span><span class="pln"> </span><span class="atn">order</span><span class="pun">=</span><span class="atv">"BEFORE"</span><span class="tag">></span></code></li><li class="L6"><code><span class="pln"> select </span><span class="tag"><include</span><span class="pln"> </span><span class="atn">refid</span><span class="pun">=</span><span class="atv">"TABLE_SEQUENCE"</span><span class="pln"> </span><span class="tag">/></span><span class="pln"> from dual</span></code></li><li class="L7"><code><span class="pln"> </span><span class="tag"></selectKey></span></code></li><li class="L8"><code><span class="pln"> insert into </span><span class="tag"><include</span><span class="pln"> </span><span class="atn">refid</span><span class="pun">=</span><span class="atv">"TABLE_NAME"</span><span class="pln"> </span><span class="tag">/></span><span class="pln"> (ID,NAME,AGE)</span></code></li><li class="L9"><code><span class="pln"> values ( #{id}, #{name}, #{age} )</span></code></li><li class="L0"><code><span class="pln"> </span><span class="tag"></insert></span></code></li></ol>
当使用了
<selectKey />
后,在实际的插入操作时,mybatis会执行以下两句SQL:<ol class="linenums"><li class="L0"><code><span class="pln"> </span><span class="kwd">select</span><span class="pln"> SEQ_TEST_USER_ID</span><span class="pun">.</span><span class="pln">nextval </span><span class="kwd">from</span><span class="pln"> dual</span><span class="pun">;</span><span class="pln"> </span><span class="com">// 语句1</span></code></li><li class="L1"><code><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="pun">(</span><span class="pln">ID</span><span class="pun">,</span><span class="pln">NAME</span><span class="pun">,</span><span class="pln">AGE</span><span class="pun">)</span><span class="pln"> values </span><span class="pun">(</span><span class="pln"> </span><span class="pun">?,</span><span class="pln"> </span><span class="pun">?,</span><span class="pln"> </span><span class="pun">?</span><span class="pln"> </span><span class="pun">);</span><span class="pln"> </span><span class="com">// 语句2</span></code></li></ol>
在执行插入 语句2 之前,会先执行 语句1 以获取当前的ID值,然后mybatis使用反射调用
User
对象的setId
方法,将语句1 查询出的值保存在User
对象中,然后才执行语句2 这样就保证了执行完插入后<ol class="linenums"><li class="L0"><code><span class="pln"> </span><span class="typ">User</span><span class="pln"> user </span><span class="pun">=</span><span class="pln"> </span><span class="kwd">new</span><span class="pln"> </span><span class="typ">User</span><span class="pun">();</span></code></li><li class="L1"><code><span class="pln"> user</span><span class="pun">.</span><span class="pln">setName</span><span class="pun">(</span><span class="str">"test"</span><span class="pun">);</span></code></li><li class="L2"><code><span class="pln"> user</span><span class="pun">.</span><span class="pln">setAge</span><span class="pun">(</span><span class="lit">24</span><span class="pun">);</span></code></li><li class="L3"><code></code></li><li class="L4"><code><span class="pln"> userMapper</span><span class="pun">.</span><span class="pln">insert</span><span class="pun">(</span><span class="pln">user</span><span class="pun">);</span></code></li><li class="L5"><code><span class="pln"> </span><span class="typ">System</span><span class="pun">.</span><span class="kwd">out</span><span class="pun">.</span><span class="pln">println</span><span class="pun">(</span><span class="pln">user</span><span class="pun">.</span><span class="pln">id</span><span class="pun">);</span><span class="pln"> </span><span class="com">// user.id 不为空</span></code></li></ol>
user.id
是有值的。Mysql自增主键配置
针对于Mysql这种自己维护主键的数据库,可以直接使用以下配置在插入后获取插入主键,
<ol class="linenums"><li class="L0"><code><span class="pln"> </span><span class="tag"><sql</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">'TABLE_NAME'</span><span class="tag">></span><span class="pln">TEST_USER</span><span class="tag"></sql></span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="tag"><insert</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">"insert"</span><span class="pln"> </span><span class="atn">useGeneratedKeys</span><span class="pun">=</span><span class="atv">"true"</span><span class="pln"> </span><span class="atn">keyProperty</span><span class="pun">=</span><span class="atv">"id"</span><span class="pln"> </span><span class="atn">parameterType</span><span class="pun">=</span><span class="atv">"User"</span><span class="tag">></span></code></li><li class="L3"><code><span class="pln"> insert into </span><span class="tag"><include</span><span class="pln"> </span><span class="atn">refid</span><span class="pun">=</span><span class="atv">"TABLE_NAME"</span><span class="pln"> </span><span class="tag">/></span><span class="pln"> ( NAME, AGE )</span></code></li><li class="L4"><code><span class="pln"> values ( #{name}, #{age} )</span></code></li><li class="L5"><code><span class="pln"> </span><span class="tag"></insert></span></code></li></ol>
当然,由于Mysql的自增主键可以通过SQL语句
<ol class="linenums"><li class="L0"><code><span class="pln"> </span><span class="kwd">select</span><span class="pln"> LAST_INSERT_ID</span><span class="pun">();</span></code></li></ol>
来获取的。因此针对Mysql,Mybatis也可配置如下:
<ol class="linenums"><li class="L0"><code><span class="pln"> </span><span class="tag"><sql</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">'TABLE_NAME'</span><span class="tag">></span><span class="pln">TEST_USER</span><span class="tag"></sql></span></code></li><li class="L1"><code></code></li><li class="L2"><code><span class="pln"> </span><span class="com"><!-- 注意这里需要先查询自增主键值 --></span></code></li><li class="L3"><code><span class="pln"> </span><span class="tag"><insert</span><span class="pln"> </span><span class="atn">id</span><span class="pun">=</span><span class="atv">"insert"</span><span class="pln"> </span><span class="atn">parameterType</span><span class="pun">=</span><span class="atv">"User"</span><span class="tag">></span></code></li><li class="L4"><code><span class="pln"> </span><span class="tag"><selectKey</span><span class="pln"> </span><span class="atn">keyProperty</span><span class="pun">=</span><span class="atv">"id"</span><span class="pln"> </span><span class="atn">resultType</span><span class="pun">=</span><span class="atv">"int"</span><span class="pln"> </span><span class="atn">order</span><span class="pun">=</span><span class="atv">"BEFORE"</span><span class="tag">></span></code></li><li class="L5"><code><span class="pln"> SELECT LAST_INSERT_ID()</span></code></li><li class="L6"><code><span class="pln"> </span><span class="tag"></selectKey></span></code></li><li class="L7"><code><span class="pln"> insert into </span><span class="tag"><include</span><span class="pln"> </span><span class="atn">refid</span><span class="pun">=</span><span class="atv">"TABLE_NAME"</span><span class="pln"> </span><span class="tag">/></span><span class="pln"> (ID,NAME,AGE)</span></code></li><li class="L8"><code><span class="pln"> values ( #{id}, #{name}, #{age} )</span></code></li><li class="L9"><code><span class="pln"> </span><span class="tag"></insert></span></code></li></ol>
只不过该中配置需要额外的一条查询SQL!
小结
当数据插入操作不关心插入后数据的主键(唯一标识),那么建议使用 不返回自增主键值 的方式来配置插入语句,这样可以避免额外的SQL开销.
当执行插入操作后需要立即获取插入的自增主键值,比如一次操作中保存一对多这种关系的数据,那么就要使用 插入后获取自增主键值 的方式配置.
- mybatis自增主键配置
- mybatis 自增主键配置
- mybatis自增主键配置
- mybatis 自增主键配置
- mybatis 自增主键配置
- mybatis自增主键配置
- mybatis自增主键配置
- mybatis 自增主键配置
- mybatis 自增主键配置
- mybatis 自增主键配置
- mybatis详解-(8)配置自增主键
- mybatis获取mysql插入的自增主键配置方法
- mybatis获取自增主键
- mybatis自增主键无效
- mybatis+oracle主键自增
- mybatis设置自增主键
- mybatis mysql 主键自增
- mybatis自增主键设置
- AJax请求
- 素数判定方法_Miller-Rabbin测试
- ios开发常见错误
- 16. Dubbo原理解析-集群&容错之router路由服务
- CentOS 7.0 下 源码编译安装PostgreSQL
- mybatis自增主键配置
- FZU Problem 2151 OOXX Game (数学啊)
- Android实现记事本项目完整实例,附源代码
- FTP连接时出现“227 Entering Passive Mode” 的解决方法
- 封装和继承
- 检查代码
- ListView嵌套ScrollView后,设置dividerHeight属性后高度计算
- 17. Dubbo原理解析-集群&容错之负载均衡
- 分享18个常用的网站性能测试工具