一、项目需求
针对将近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种情况:
-
如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
-
如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
-
如果传入的参数是多个的时候,我们就需要把它们封装成一个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)别急,是问题都能解决,一定耐心点,多查多问多学习;