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）别急，是问题都能解决，一定耐心点，多查多问多学习；