mybatis操作Oracle数据库批量插入与更新、运行注意事项、属性含义

一、项目需求

   针对将近300万用户的用电数据进行统计分析,将结果更新保存Oracle数据库。我需要往一个表里面插入数据,数据量总计在500万条左右。一条一条插入的话非常慢,2万条数据近20分钟,后面就想通过foreach来批量插入,参考网上的写法,自己实际操作过程中出现很多问题。

二、项目所遇问题

(1)批量删除语句,执行报错

    原因:使用sql in删除指定数据,条件>1000,需考虑将条件分解拼接;

    解决方案:

    (后台代码调用mybatis xml方法如下):步骤:1、自定义getSumArrayList方法将超过长度的数据进行格式化;

     //Java开发中会出现一种情况,就是使用mybatis 的in语句的时候,in语句里面的数组大小不能大于1000.

      //可以通过以下方法解决:

     //对于一个List ids 大小大于1000的list而言,使用getSumArrayList方法,获取List<List> 对象

     List<List<String>> idParams = getSumArrayList(consNos);

     Map<String, Object> mapNew = new HashMap<>();

     mapNew.put("consNos", idParams );

     userCredentialMapper.deleteByBatch(mapNew);

(后台自定义方法如下):

------------------------------public List<List> getSumArrayList(List list);----------------

/**

  • @author: fz

  • @date: 2019/08/07

  • @param list

  • @description: 以下方法为解决:mybatis xml中使用in的sql长度超过1000的解决办法(getSumArrayList方法)

*/

public List<List> getSumArrayList(List list){

List<List> objectlist = new ArrayList<>();

int iSize = list.size()/1000;

int iCount = list.size()%1000;

for(int i=0;i<=iSize;i++){

List newObjList = new ArrayList<>();

if(i==iSize){

for(int j =i1000;j<i1000+iCount;j++ ){

newObjList.add(list.get(j));

}

}else{

for(int j =i*1000;j<(i+1)*1000;j++ ){

newObjList.add(list.get(j));

}

}

if(newObjList.size()>0){

objectlist.add(newObjList);

}

}

return objectlist;

}


(批量删除:mybatis xml 写法如下):

delete from DFB_USER_CREDENTIAL where cons_no in (‘test’)

or cons_no in

#{cn,jdbcType=VARCHAR}

-------------------------------------------------备注:mybatis xml中的属性含义-------------------------------------

foreach的主要用在构建in条件中,它可以在SQL语句中进行迭代一个集合。

foreach元素的属性主要有 item,index,collection,open,separator,close。

item集合中每一个元素进行迭代时的别名,

index表示在迭代过程中,每次迭代到的位置,

open该语句以什么开始,

separator在每次进行迭代之间以什么符号作为分隔 符,

close以什么结束,

在使用foreach的时候最关键的也是最容易出错的就是collection属性,该属性是必须指定的,但是在不同情况 下,该属性的值是不一样的,

主要有一下3种情况:

  1. 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
    
  2. 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
    
  3. 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了
    

(2)批量插入语句,执行报错问题及解决方案

问题1:Mybatis运行错误:信息: SQLErrorCodes loaded: [DB2, Derby, H2, HDB, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
错误原因:

一般这个错误是由于sql语句的输入参数与数据库表结构字段类型不一致导致。

这个错误很常见,自己排错却很容易忽略细节:

1、parameterType="xxx"有没写错;

2、sql语句中有没什么无效字符,例如:结尾的分号;

3、#{xxx}有没写错,例如:写成了小括号();

自己项目原因:

    我在使用mybatis操作Oracle数据库过程中出现这个报错问题,排查了很久并不是上述罗列的原因!这是因为在搜索他人写好的批量方法时,忽略了方法对应的数据库(sqlserver、mysql、oracle),我使用的是oracle当然写法上和其他数据库不同,从而造成方法执行报错!

   很容易忽略,所以代码不能直接粘过来,结合自己的业务需求,懂原理更得自己写每一行代码!之前反复强调!

---------------------------mybatis操作oracle数据库批量插入问题解决方案及步骤:---------------------------

    为防止超时以及长时间占用数据库连接,采取了批量更新插入,我现在设置的是一次插入50条数据(注:每次所操作的记录list数据近20多万条,因此需要将结果list进行切分,从而避免上述问题);

1.后台代码每50条,与数据库进行一次交互

//由于系统中一次性需要导入2万+条数据,为防止超时以及长时间占用数据库连接,采取了批量更新插入。

// 限制分批条数

int dataLimitNum = 50;

int i = 0;

while (i < insertList.size()) {

int from = i;

int to = i + dataLimitNum;

if (to >= insertList.size()) {

to = insertList.size();

}

List partList = insertList.subList(from, to);

if (partList.isEmpty()) {

break;

}

i = to;

userCredentialMapper.insertByBatch(partList);

}

2.批量插入(尤其注意mybatis操作不同数据库,mybatis xml写法不同!!!不要直接搬代码,很坑)

INSERT ALL

into DFB_USER_CREDENTIAL

ID,

CONS_NO,

WHETHER_OVERDU,

OWE_NUMBER,

LIQUIDATED_DAMAGE,

WHETHER_POWER,

DEFAULT_ELEC,

HISTORY_QFJE,

HISTORY_QFCS,

NOW_QFJE,

NOW_QFCS,

NOW_YSJE,

NOW_SSJE,

YJBJ

#{item.id,jdbcType=VARCHAR},

#{item.consNo,jdbcType=VARCHAR},

#{item.whetherOverdu,jdbcType=VARCHAR},

#{item.oweNumber,jdbcType=DECIMAL},

#{item.liquidatedDamage,jdbcType=FLOAT},

#{item.whetherPower,jdbcType=VARCHAR},

#{item.defaultElec,jdbcType=VARCHAR},

#{item.historyQfje,jdbcType=FLOAT},

#{item.historyQfcs,jdbcType=FLOAT},

#{item.nowQfje,jdbcType=FLOAT},

#{item.nowQfcs,jdbcType=FLOAT},

#{item.nowYsje,jdbcType=FLOAT},

#{item.nowSsje,jdbcType=FLOAT},

#{item.yjbj,jdbcType=FLOAT}

SELECT 1 FROM DUAL

3、批量更新操作类似插入方法再次不做累述
4、java中list中的subList方法
List list = new Arraylist<>();

List subList = list.subList(0, 5);

其中subList(0, 5)取得的是下标为0到4的元素,不包含下标为5的元素.

5、oracle的SQL语句如何insert into 多个values(项目排错过程中在这踩了坑!!!)

稍微熟悉Oracle的都知道,如果我们想一条SQL语句向表中插入多个值的话,如果如下语句

INSERT INTO 某表 VALUES(各个值),VALUES(各个值),…;

这样会报错的,因为oracle是不支持这种写法的,如果多个INSERT INTO VALUEES(各个值);这样以“;”隔开一同执行也是不行的,oracle也是不支持的。

   不过MySQL这两种方式都是支持的。在MySQL中还可以使用如下格式的插入语句:

示例:

insert into persons

(id_p, lastname , firstName, city )

values

(200,‘haha’ , ‘deng’ , ‘shenzhen’),

(201,‘haha2’ , ‘deng’ , ‘GD’),

(202,‘haha3’ , ‘deng’ , ‘Beijing’);

这样就批量插入数据了

   SQL Server也是不支持这两种写法的。

   然而,有时我们在开发过程中,如果是用的oracle数据库的话,有时很需要这种“INSERT INTO  某表” 一次性能插入多个值这样的功能,但oracle又不支持以上两种方式那么我们怎么办?是的可能你很机灵的想到了几种方法,如存储过程,或着INSERT INTO 某表 SELECT * FROM 某个表等,不过声明一下,我们在此不探讨这些方法,而且都有一定的局限性,有时我就要插入指定的几个值,而且一条SQL语句完成,存储过程的话,万不得已,建议大家不要使用,当然这个根据情况而定,可以读读这个博客存储过程的优缺点,分析的很详细。

   那么有没有其他方法呢?答案是肯定有的,也许熟悉oracle数据库的可能知道从oracle9i版本以后,可以使用一种方法即:

“INSERT ALL INTO a表
VALUES(各个值)
INTO a表 VALUES (其它值)
INTO a表 VALUES(其它值) … 再跟一个SELECT 语句”。

后边跟的SELECT 语句我们可以从虚拟表里查如 SELECT 1 FROM DUAL。注意后边跟的SELECT语句可以随意,不过不是把它SELECT出来的内容插入前边的表里,而是起到前边的多个数据每次插入多少行的作用,这个多少行是和后边跟的SELECT语句查出来几条而定的,如后边的跟的SELECT 语句查出了15条记录,那么前边的"INSERT ALL INTO a表 VALUES(各个值1) INTO a表 VALUES (其它值2) INTO a表 VALUES(其它值3)"就会先插入值1对应的各个字段插入15条记录,然后插入值2各个对应的字段15条记录,然后插入值3对应的各个字段15条记录,也就是说有点按列插入的意思。

   我们要的是批量插入多个VALUES这样的一条记录,所以后边的SELECT 语句只要能查出一条记录就行,建议大家后边用SELECT 1 FROM DUAL。

   下边看一个例子,一目了然,一看就会用了:

在这里插入图片描述

OK了,我们实现了没用存储过程用一条SQL语句完成了向oracle的表中插入多个指定的值。也许你会问这有啥用处,告诉你,如果你在一定情况下用到它,作用就大了,避免了写复杂而又没必要写的存储过程,避免了执行多个SQL语句多次连接数据库的开销,当你知道了学到了,遇到问题用的时候就会明白了!

三、总结

(1)先动脑想清楚问题内容及解决方案,再行动;

(2)遇到问题,确定问题解决得大致正确方向,耐心排查原因都能解决,只是耗费时间得不同,解决问题得方式更好更高效;

(3)数据库知识很重要很重要很重要;

(4)解决的问题,遇到的情况及解决方案及时记录备忘,花时间总结,寻找是否有更好的解决方案;

(5)别急,是问题都能解决,一定耐心点,多查多问多学习;

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

George_Z3

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值