不同于MYSQL,Oracle数据库插入数据时不能够实现ID的自动增长,常用的解决办法为为数据库表序列,这里介绍另一种解决办法,其中需要使用到ORACLE函数:nvl()。
通过搜索可知,ORACLE提供了nvl(st1,str2)和nvl2(boolean,str1,str2)函数
其中nvl(st1,str2)表示在str1 == null的时候选择返回值str2
nvl2(boolean,str1,str2),boolean == true return str1 else return str2
SELECT NVL(MAX(ID), 1) as id FROM TH_DATA_DATATYPE
在MYBATIS中的使用:
<selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
SELECT NVL(MAX(ID), 1) as id FROM TH_DATA_DATATYPE
</selectKey>
完整代码:
<insert id="insertSelective" parameterType="com.tianheng.spring.database.model.ThDataDataType">
<selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
SELECT NVL(MAX(ID), 1) as id FROM TH_DATA_DATATYPE
</selectKey>
insert into TH_DATA_DATATYPE
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id != null">
ID,
</if>
<if test="name != null">
NAME,
</if>
<if test="code != null">
CODE,
</if>
<if test="driver != null">
DRIVER,
</if>
<if test="createby != null">
CREATEBY,
</if>
<if test="createdate != null">
CREATEDATE,
</if>
<if test="updateby != null">
UPDATEBY,
</if>
<if test="updatedate != null">
UPDATEDATE,
</if>
<if test="status != null">
STATUS,
</if>
<if test="order != null">
ORDER,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="id != null">
#{id,jdbcType=DECIMAL},
</if>
<if test="name != null">
#{name,jdbcType=VARCHAR},
</if>
<if test="code != null">
#{code,jdbcType=VARCHAR},
</if>
<if test="driver != null">
#{driver,jdbcType=VARCHAR},
</if>
<if test="createby != null">
#{createby,jdbcType=DECIMAL},
</if>
<if test="createdate != null">
#{createdate,jdbcType=TIMESTAMP},
</if>
<if test="updateby != null">
#{updateby,jdbcType=DECIMAL},
</if>
<if test="updatedate != null">
#{updatedate,jdbcType=TIMESTAMP},
</if>
<if test="status != null">
#{status,jdbcType=VARCHAR},
</if>
<if test="order != null">
#{order,jdbcType=DECIMAL},
</if>
</trim>
</insert>