mybatis+mysql/oracle 数据库批量插入，主键自增长

最新推荐文章于 2024-05-16 14:29:54 发布

yangchuanan

最新推荐文章于 2024-05-16 14:29:54 发布

阅读量5.1k

点赞数 1

分类专栏： oracle 数据库 mybatis Springboot mysql

本文链接：https://blog.csdn.net/yangchuanan/article/details/82667447

版权

Springboot 同时被 3 个专栏收录

11 篇文章 0 订阅

订阅专栏

mysql

9 篇文章 0 订阅

订阅专栏

mybatis

5 篇文章 0 订阅

订阅专栏

1 mybatis+oracle

		<!-- https://mvnrepository.com/artifact/com.oracle/ojdbc6 -->
		<dependency>
			<groupId>com.oracle</groupId>
			<artifactId>ojdbc6</artifactId>
			<version>11.2.0.4.0-atlassian-hosted</version>
		</dependency>

1.1 oracle 序列

创建序列

CREATE SEQUENCE SEQ_SMS_SUPPLIER   
INCREMENT BY 1  
NOMAXVALUE  
NOCYCLE  
CACHE 10;

1.2 主键自增长

1.21 使用序列

 <insert id="addSupplier" parameterType="com.pinganfu.GouTong.common.dal.model.SmsSupplierDO" >
  <selectKey resultType="int" keyProperty="id" order="BEFORE">
   SELECT SEQ_SMS_SUPPLIER.nextval FROM dual
  </selectKey>
  INSERT INTO
  t_sms_supplier(id,supplierId,username,password,usestatus,connstatus,suppliername,supplieraddress)
  VALUES(#{id},#{supplierId,jdbcType=VARCHAR},
  #{username,jdbcType=VARCHAR},#{password,jdbcType=VARCHAR},
  #{useStatus,jdbcType=NUMERIC},#{connStatus,jdbcType=NUMERIC},
  #{supplierName,jdbcType=VARCHAR},#{supplierAddress,jdbcType=VARCHAR})
 </insert>

keyProperty是指vo类中的主键属性名称，resultType是指vo类中的主键属性类型，order有两个属性，一个是AFTER是指限制性插入语句，一个是BEFORE是指先执行selectKey标签内的语句

或者，此时<selectKey>标签内的order属性为AFTER

<insert id="addLoginLog" parameterType="map" >
  	insert into t_sms_supplier(ID,MEMBER_ID) values(SEQ_SMS_SUPPLIER.nextval,#{memberId})
  </insert>

详解

<!--Oracle不支持	自增；Oracle使用序列序列来模拟自增；
		每次插入的数据的主键是从序列中拿到的值：如歌获取到这个值?	
	 -->
	 <insert id="addDept" databaseId="oracle" >
	 	<!-- keyProperty:查出的主键封装给JavaBean的哪个属性 
	 		 order="BEFORE":当前SQL在插入之前运行
	 		 		AFTER:当前SQL在插入之后运行
	 		 resultType: 查出数据的返回值类型
	 		 
	 		 BEFORE运行顺序：
	 		 	先运行selectKey查询id的sql；查出id的值封装给javaBean的id属性
	 		 	再运行插入的SQL；就可以取出id属性对应的值
	 		 AFTER：
	 			先 运行插入的SQL（从序列中取出新值作为id）
	 		 	再运行selectKey查询id的sql
	 	-->
	 	<selectKey keyProperty="deptno" order="BEFORE" resultType="Integer">
	 		<!-- 编写查询主键的SQL语句 -->
		 	<!-- BEFORE：-->
				SELECT seq_deptno.nextval FROM dual
			<!-- AFTER:	 
				SELECT seq_deptno.currval FROM dual
			 -->
	 	</selectKey>
	 		<!-- 插入时的主键是从序列中拿到的 -->
			<!-- BEFORE： -->
				insert into dept(deptno,dname,loc) 
				values (#{deptno},#{dname},#{loc})
			<!-- AFTER:	
				insert into dept(deptno,dname,loc) 
				values (seq_deptno.nextval,#{dname},#{loc})
			-->
	</insert>

1.22 使用序列加触发器

创建触发器

create or replace trigger loginlog_trigger
before insert on ap_loginlog
for each row
begin
select loginlog_sequence.nextval into :new.id from dual;
end loginlog_trigger;

然后再sql语句中插入

<insert id="addLoginLog" parameterType="map" >
    insert into ap_loginlog(MEMBER_ID) values(#{memberId})
</insert>

1.3 借助序列插入和不借助序列批量插入

insert into table(...) (select ... from dual) union all (select ... from dual)

方式一

<insert id="addList" parameterType="java.util.List" useGeneratedKeys="false">
        INSERT ALL
        <foreach item="item" index="index" collection="list">
        INTO T_APPLAUD
        (
            ID,
            USER_ID,
            BUSINESS_TYPE,
            PRODUCT_ID,
            CREATE_TIME
        ) VALUES
        (
            #{item.id, jdbcType=NUMERIC},
            #{item.userId, jdbcType=VARCHAR},
            #{item.businessType, jdbcType=VARCHAR},
            #{item.productId, jdbcType=VARCHAR},
            #{item.createdTime, jdbcType=NUMERIC} 
        )
        </foreach>
        SELECT 1 FROM DUAL
    </insert>

方式二

    <!-- 字典分类序号 -->
    <select id = "selectSequence" resultType="long" > 
       SELECT CM_TERMTYPES_SEQUENCE.nextval FROM dual
    </select> 
    
    <!-- 自带序列号 -->
    <insert id="insertTermtypesBatch" parameterType="list" >
        INSERT INTO cm_termtypes
        (TERMTYPEID, PARENTTYPEID,
        TERMTYPECODE,TERMTYPENAME,ITEMSTYPE,ISENABLED,TYPEREMARK,CREATORID,CREATETIME,LASTMODIFYID,LASTMODIFYTIME)
         select cd.* from(
        <foreach collection="list" item="item" index="" separator="union all">
          select
            #{item.termTypeId},
            #{item.parentTypeId},
            #{item.termTypeCode},
            #{item.termTypeName},
            #{item.itemsType},
            #{item.isEnabled},
            #{item.typerEmark},
            #{item.creatorId},
            #{item.creatTime},
            #{item.lastModifyId},
            #{item.lastModifyTime}
           from dual
        </foreach>
        ) cd
    </insert>

    <!-- 自增序列号 -->
    <insert id="insertTreeTermBatch" parameterType="list"  useGeneratedKeys="false">
        INSERT INTO cm_treetermdata
        (   ID, 
            TERMTYPEID,
            PARENTID,
            TREETERMCODE,
            TREETERMNAME,
            ORDERID,
            ISENABLED,
            REMARK,
            CREATORID,
            CREATETIME,
            LASTMODIFYID,
            LASTMODIFYTIME  )
        select CM_TREETERMDATA_SEQUENCE.nextval ID, cd.* from(
        <foreach collection="list" item="item" index="index" close=")" open="(" separator="union">
            select
            #{item.termTypeId},
            #{item.parentId},
            #{item.treeTermCode},
            #{item.treeTermName},
            #{item.orderId},
            #{item.isEnabled},
            #{item.remark},
            #{item.creatorId},
            #{item.creatTime},
            #{item.lastModifyId},
            #{item.lastModifyTime}
            from dual
        </foreach>
         ) cd
    </insert>

若包错mybatis 批量插入数据到oracle报 ”java.sql.SQLException: ORA-00933: SQL 命令未正确结束“ 错误解决方法：

mybatis批量插入oracle时需要显式指定为 useGeneratedKeys="false"，没有指定useGeneratedKeys="false" 也不会报错

1.3 mybatis 插入oracle数据库报“Try setting a different JdbcType for this parameter or a different jdbcTypeForNull”错误

postgreSQL，MySQL，SQLSERVER都支持JdbcType.NULL类型，Oracle，DB2是不支持，适配的时候也因为这个问题导致mybatis报错。

比如，之前配置#{submitDate}，它会在oracle中报错：Error settingnull parameter

更改成#{submitDate,jdbcType=DATE}，注意jdbcType是区分大小写的。

org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #3 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型
### The error may exist in UserMapper.xml
### The error may involve com.queen.mybatis.mapper.UserMapper.addUserMap-Inline
### The error occurred while setting parameters
### SQL: insert into   t_user(id,loginId,userName,role,note)   values(?,?,?,?,?)
### Cause: org.apache.ibatis.type.TypeException: Error setting null for parameter #3 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型
	at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:26)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:154)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:141)
	at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:51)
	at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:52)
	at com.sun.proxy.$Proxy2.addUserMap(Unknown Source)
	at com.queen.mybatis.MyBatisTest.testAdd(MyBatisTest.java:41)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
	at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
	at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
	at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
	at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:263)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:68)
	at org.junit.runners.BlockJUnit4ClassRunner.runChild(BlockJUnit4ClassRunner.java:47)
	at org.junit.runners.ParentRunner$3.run(ParentRunner.java:231)
	at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:60)
	at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:229)
	at org.junit.runners.ParentRunner.access$000(ParentRunner.java:50)
	at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:222)
	at org.junit.runners.ParentRunner.run(ParentRunner.java:300)
	at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:50)
	at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:459)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:675)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:382)
	at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:192)
Caused by: org.apache.ibatis.type.TypeException: Error setting null for parameter #3 with JdbcType OTHER . Try setting a different JdbcType for this parameter or a different jdbcTypeForNull configuration property. Cause: java.sql.SQLException: 无效的列类型
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:45)
	at org.apache.ibatis.scripting.defaults.DefaultParameterHandler.setParameters(DefaultParameterHandler.java:81)
	at org.apache.ibatis.executor.statement.PreparedStatementHandler.parameterize(PreparedStatementHandler.java:80)
	at org.apache.ibatis.executor.statement.RoutingStatementHandler.parameterize(RoutingStatementHandler.java:61)
	at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:74)
	at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:47)
	at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:105)
	at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:71)
	at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:152)
	... 28 more
Caused by: java.sql.SQLException: 无效的列类型
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
	at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:3424)
	at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4197)
	at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4186)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:606)
	at org.apache.ibatis.logging.jdbc.PreparedStatementLogger.invoke(PreparedStatementLogger.java:70)
	at com.sun.proxy.$Proxy4.setNull(Unknown Source)
	at org.apache.ibatis.type.BaseTypeHandler.setParameter(BaseTypeHandler.java:43)
	... 36 more

MyBatis 插入空值时，需要指定JdbcType ，否则会报错。

解决方法一：指定JdbcType

<insert id="addUserMap" parameterType="java.util.Map">
	<selectKey keyProperty="id" order="BEFORE" resultType="Integer">
		select SEQ_T_USER_ID.nextval from dual
	</selectKey>
	insert into
	t_user(
		id,
		loginId,
		userName,
		role,
		note)
	values(
		#{id},
		#{loginId,jdbcType=VARCHAR},
		#{userName,jdbcType=VARCHAR},
		#{role,jdbcType=VARCHAR},
		#{note,jdbcType=VARCHAR}
	)
</insert>

解决方法二：

MyBatis-config.xml 中设置当JDBC类型为空值时，要指定的值得，默认为OTHER，指定为NULL（注意是大写的NULL）。

     <!– 配置设置 –>  
           <settings>  
              <!– 配置全局性 cache 的 ( 开 / 关) default:true –>  
              <setting name=“cacheEnabled” value=“true”/>  
                
             <!– 是否使用 懒加载 关联对象  同 hibernate中的延迟加载 一样  default:true –>  
              <setting name=“lazyLoadingEnabled” value=“true”/>  
                
             <!– [当对象使用延迟加载时 属性的加载取决于能被引用到的那些延迟属性,否则,按需加载(需要的是时候才去加载)] –>  
              <setting name=“aggressiveLazyLoading” value=“true”/>  
             
             <!– 是否允许单条sql 返回多个数据集  (取决于驱动的兼容性) default:true –>  
             <setting name=“multipleResultSetsEnabled” value=“true”/>               

             <!– 是否可以使用列的别名 (取决于驱动的兼容性) default:true–>  
             <setting name=“useColumnLabel” value=“true”/>                 

             <!–允许JDBC 生成主键。需要驱动器支持。如果设为了true，这个设置将强制使用被生成的主键，有一些驱动器不兼容不过仍然可以执行。  default:false–>  
             <setting name=“useGeneratedKeys” value=“false”/>                 

             <!–指定 MyBatis 如何自动映射 数据基表的列 NONE：不隐射　PARTIAL:部分  FULL:全部–>  
             <setting name=“autoMappingBehavior” value=“PARTIAL”/>  
               
             <!– 这是默认的执行类型   
                SIMPLE :简单    
                REUSE:执行器可能重复使用prepared statements 语句   
                BATCH:执行器可以重复执行语句和批量更新  
             –>  
             <setting name=“defaultExecutorType” value=“SIMPLE”/>                 

             <!– 设置驱动等待数据响应的超时数  默认没有设置–>  
             <setting name=“defaultStatementTimeout” value=“25000″/>  
             
             <!– [是否启用 行内嵌套语句  defaut:false] –>  
             <setting name=“safeRowBoundsEnabled” value=“false”/>                

            <!– [是否 启用  数据中 A_column 自动映射 到 java类中驼峰命名的属性 default:fasle] –>  
             <setting name=“mapUnderscoreToCamelCase” value=“false”/>  
               
            <!– 设置本地缓存范围 session:就会有数据的共享  statement:语句范围 (这样就不会有数据的共享 ) defalut:session –>  
             <setting name=“localCacheScope” value=“SESSION”/>  
             
             <!– 设置但JDBC类型为空时,某些驱动程序 要指定值,default:OTHER –>  
            <setting name=“jdbcTypeForNull” value=“DEFAULT”/>  
               
            <!– 设置触发延迟加载的方法  –>  
             <setting name=“lazyLoadTriggerMethods” value=“equals,clone,hashCode,toString”/>  
               
      </settings>

springboot

application.yml

mybatis-plus:
  configuration:
    jdbc-type-for-null: 'null' #注意：单引号

解决方法三：

查看mp-starter-源码，可以发现，第119行有一个configurationCustomizers，可以修改configuration

自定义一个，配上就完工

    @Bean
    public ConfigurationCustomizer configurationCustomizer(){
        return new MybatisPlusCustomizers();
    }

    class MybatisPlusCustomizers implements ConfigurationCustomizer {

        @Override
        public void customize(org.apache.ibatis.session.Configuration configuration) {
            configuration.setJdbcTypeForNull(JdbcType.NULL);
        }

解决方法四：

第一步：把可更新为空的 javabean 属性前加上注解：@TableField(el = "username, jdbcType=VARCHAR")

@Email
@TableField(el = "email, jdbcType=VARCHAR")
private String email;

第二步：使用updateAllColumnById方法，而不是updateById. 如：

this.baseMapper.updateAllColumnById(user);

2 mybatis+mysql

批量插入

	<insert id="insertTermtypesBatch">
		INSERT INTO common_termtypes
		(TERM_TYPE_ID, PARENT_TYPE_ID,
		TERM_TYPE_CODE,TERM_TYPE_NAME,ITEMS_TYPE,IS_ENABLED,TYPER_EMARK,CREATOR_ID,CREAT_TIME,LAST_MODIFY_ID,LAST_MODIFY_TIME)
		VALUES
		<foreach collection="list" item="item" separator=",">
			(#{item.termTypeId}, #{item.parentTypeId},
			#{item.termTypeCode},#{item.termTypeName},#{item.itemsType},#{item.isEnabled},#{item.typerEmark},#{item.creatorId},#{item.creatTime}
			,#{item.lastModifyId},#{item.lastModifyTime})
		</foreach>
	</insert>

单条插入

<!-- 	public void addDept(Dept dept);
			mysql支持自增主键；自增主键的获取，mybatis也是利用statement.getGeneratedKeys()
			要想前台获取添加的数字，添加两个属性
			useGeneratedKeys="true": 使用自增主键获取主键值
			keyProperty: 对应的主键属性，也就是mybatis获取到主键值以后，将这个值封装给javaBean的哪个属性
	 -->
	<insert id="addDept" useGeneratedKeys="true" keyProperty="deptno" databaseId="mysql" >
		insert into dept(dname,loc) values (#{dname},#{loc});
	</insert>

mysql 获取自增主键的下一个值

select AUTO_INCREMENT from INFORMATION_SCHEMA.TABLES where TABLE_NAME='表名'

yangchuanan

关注

1
点赞
踩
10

收藏

觉得还不错? 一键收藏
1
评论
mybatis+mysql/oracle 数据库批量插入，主键自增长

1 mybatis+oracle &lt;!-- https://mvnrepository.com/artifact/com.oracle/ojdbc6 --&gt; &lt;dependency&gt; &lt;groupId&gt;com.oracle&lt;/groupId&gt; &lt;artifactId&gt;ojdbc6&lt;/artifactId&gt...
复制链接

扫一扫