Version: Next

Mybatis操作Oracle的一些坑与总结

联表删除

需求：
有两个表，BusinessProcessStatus和MioInfoLst，一条BusinessProcessStatus对应多条MioinfoLst，用老师和学生举例子
学生和老师，一个老师有一堆学生，老师自己有个tid，学生除了自己的id之外还有一个tid，用来标记自己的老师是谁
BusinessProcessStatus自己有个BID，MioInfoLst除了自己的id之外还有个BID用来指明自己所属的BusinessProcessStatus
之所以不在老师这一端弄一个StudentID来标记学生，而是用学生存老师tid的方式，是因为这样方便，道理如同让一堆学生记住自己的老师比让老师记住一堆学生容易
当一个老师被删除了，那么这个老师所属的学生应当自己把自己删除了，不然就成了孤儿
对照：BusinessProcessStatus被删除时，所属的MioInfoLst应该把自己删除掉
问题：
当要删除一个BusinessProcessStatus记录时，需要取出它的主键BID，然后把MioInfoLst表中所有BID指向这条BusinessProcessStatus的记录全删了
对于Mysql：delete from MioInfoLst,BusinessProcessStatus where BusinessProcessStatus.ID = MioInfoLst.BID就行了
对于Oracle：很不幸，Oracle不支持这种语法，就很蛋疼
解决：
在键表时，外键后面跟上on delete cascade，这样当主表记录被删除时，对应子表的记录自动就被删除了，delete语句直接可以这样写
delete from BusinessProcessStatus where credentialNum = #{credentialNum}

CREATE TABLE BusinessProcessStatus (
ID NUMBER PRIMARY KEY,
BusinessName VARCHAR2(50),
BusinessStatus VARCHAR2(50),
BusinessMessage VARCHAR2(500),
ClaimNo VARCHAR2(20) NOT NULL,
BusinessKeyNo VARCHAR2(20) NOT NULL,
PersonGUID VARCHAR2(255),
NAME VARCHAR2(60),
SaveTime DATE NOT NULL,
credentialType VARCHAR2(2) NOT NULL,    
credentialNum VARCHAR2(22) NOT NULL
)

CREATE TABLE MioInfoLst(
ID NUMBER PRIMARY KEY,
MioFlag VARCHAR2(2) NOT NULL,
MioAmnt NUMBER NOT NULL,
PlnmioDate DATE NOT NULL,
MioDate DATE,
BankAccNo VARCHAR2(30) NOT NULL,
BankCode VARCHAR2(50) NOT NULL,
AccCustName VARCHAR2(200) NOT NULL,
BID NUMBER NOT NULL,
CONSTRAINT BusID FOREIGN KEY (BID) REFERENCES BusinessProcessStatus(ID) 
on delete cascade
)

自增主键

需求：
在执行insert时，表里有个自增主键，我们不关心主键的值，希望在插入时，数据库自动算出新的值往里插就行了
对于Mysql：建表的时候在主键后面写个auto_increament就行了
对于Oracle：很不幸，Oracle它不会

解决：在Oracle中可以用序列或者触发器，我就试了下序列，触发器去自己查吧

序列的建立

CREATE SEQUENCE MioInfoLstAutoIncrement 
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;  
minvalue 1
maxvalue 999999999999999999999999999
start with 1
increment by 1
nocache;

序列的删除

drop SEQUENCE MioInfoLstAutoIncrement

通过序列获得完成自增主键

获取主键的下一个值

select MioInfoLstAutoIncrement.nextval from dual

dual是个Oracle的虚拟表，也不用你自己建，反正这么用就对了，想知道原理去查

warning

只要你执行了这个命令，序列值就会增加，不要看它是个select语句，执行完值会增加！

获取主键的当前值

select MioInfoLstAutoIncrement.currval from dual

warning

当你新建了一个序列，必须执行一次上面那个nextval命令，不然currval就报错，最好手动跑一次nextval，不然可能上Mybatis直接疯狂报错，令你一脸懵逼

在插入命令中使用序列实现自增主键

在MySQL的insert语句中，自增主键的位置直接写个null就行了
在Oracle中，要使用序列名.nextval来获取自增主键值

INSERT INTO
                MioInfoLst (
                ID,
                MioFlag,
                MioAmnt,
                PlnmioDate,
                MioDate,
                BankAccNo,
                BankCode,
                AccCustName,
                BID)
values (
                MioInfoLstAutoIncrement.NEXTVAL,
                #{item.MioFlag},
                #{item.MioAmnt},
                to_date(#{item.MioDate},'yyyy/mm/dd HH24:MI:SS'),
                to_date(#{item.PlnmioDate},'yyyy/mm/dd HH24:MI:SS'),
                #{item.BankAccNo},
                #{item.BankCode},
                #{item.AccCustName},
                #{item.BID}
       );

自增主键并返回主键值实现多表联合插入

需求：
按照我们的对照小例子，假设现在让你往数据库里存老师和学生，那你要解决一些问题
首先你插入了一个老师，这老师自己有个tid
接下来你要插入学生，这些学生是上面那个老师的学生，所以这些学生的tid字段的值，就是你刚才插入的那个老师的tid值
问题：
想象一下，你写了一条SQL，把老师插进数据库了，老师的tid是自增主键，自动生成的，在你的Java程序中这个tid根本没出现
接下来你该插学生了，你需要通过Java程序把上面那个老师的tid传到插入学生的SQL语句里，填充学生的tid字段
问题就是这个tid就没出现在Java程序里，就是插入老师的时候自己生成的，如何把这个值捣鼓出来，好让我们把这个tid传给学生，完成联合插入
解决：我们需要一种方法，在SQL中自动生成主键值的同时，还要把这个主键值返回到Java程序中，这样我们就能再用这个主键值
Mybatis提供了一些方法，这里介绍selectKey标签，还有一些其他方法，感兴趣去查

selectKey标签

<selectKey keyProperty="ID" resultType="int" order="BEFORE">
     select BusinessProcessStatusAutoIncrement.nextval from dual
</selectKey>

还是用序列查询出下一个主键值
keyProperty：你查出来的下一个主键值，给它起个名字，在insert语句的values中，你就可以用#{名字}拿到这个主键值了，我这里就keyProperty="ID"，然后#{ID}
resultType：查出来的主键是个啥类型，一般就int
如何在Java程序中拿到这个值？
- 在执行这条Mapper命令时，传入参数使用的实体类对象，比如老师你就传个Teacher的对象teacher
- 实体类对象自己有个id属性，比如Teacher就有个tid属性
- 当执行完selectKey，Mybatis会把新的主键值，通过反射，自动set到你之前传入的那个实体类对象的ID值里
- 在Java程序里，当你执行完Mapper那一句数据库操作后，就可以用之前传入的那个实体类对象直接取出新主键值了

// 调用Mapper执行数据库操作，传入了一个businessProcessStatusDto
dao.save("webServiceMioMapper.saveBusinessProcessStatus", businessProcessStatusDto);
// 紧接着，从这个businessProcessStatusDto中直接拿ID，就是上一个数据库操作中生成的新ID值了
int nextBid = businessProcessStatusDto.getID();
// ... 省略一些东西
//接下来你可以在要插入的从表数据中，设置这个id值，好让从表的数据指向刚刚插入的父表记录
mioInfoLstDto.setBID(nextBid); 

怕你不明白，再写个对照的例子，编的，跑不了
// 用Mapper插一个Teacher
teacherMapper.saveTeacher(teacher);
// 直接从这个teacher拿id，拿到的时新的自增id
int tid = teacher.getId();
// 然后你该插入学生了，你得给学生设置老师，就设置刚才这个老师
student1.setTeacherId(tid);
student2.setTeacherId(tid);
// ... 可以弄一堆学生，只要设置了teacherId，它们就有老师了
// 接下来你就可以往数据库插入这些学生了
// 首先你需要把这些学生弄成一个List
List<Student> studentList = new ArrayList<>();
studentList.add(student1);
studentList.add(student2);
// 调用Mapper,直接把List扔进去
studentMapper.saveStudents(studentList);

<insert id="saveBusinessProcessStatus" parameterType="com.lz.controller.webservicedto.BusinessProcessStatusDto">
        <selectKey keyProperty="ID" resultType="int" order="BEFORE">
            select BusinessProcessStatusAutoIncrement.nextval from dual
        </selectKey>
    INSERT INTO
    BusinessProcessStatus
    (ID,
    BUSINESSNAME,
    BUSINESSSTATUS,
    BUSINESSMESSAGE,
    CLAIMNO,
    BUSINESSKEYNO,
    PERSONGUID,
    NAME,SAVETIME,
    CREDENTIALTYPE,
    CREDENTIALNUM)

        VALUES (
            #{ID},
            #{BusinessName},
            #{BusinessStatus},
            #{BusinessMessage},
            #{ClaimNo},
            #{BusinessKeyNo},
            #{PersonGUID},
            #{NAME},
            to_date(#{SaveTime},'yyyy/mm/dd HH24:MI:SS'),
            #{credentialType},
            #{credentialNum}
            )
    </insert>

批量插入

需求：
还是上面我们的对照例子：一次存一个老师，和老师的一堆学生
存学生传进的是一个学生的List，那我们就得写个Insert，把这个List里的学生都遍历了给存进去
问题：
MySQL和Oracle写法又不一样：
MySQL正常的多条插入 inset into 某表 values ([列值], [列值]), ([列值], [列值]), ([列值], [列值]), ...
Oracle正常的多条插入写法：
insert all
into <tableName>[(<table_column1>,<table_column2>...)] values([<column_value1>,<column_value2>...])
[ into <tableName>[(<table_column1>,<table_column2>...)] values([<column_value1>,<column_value2>...])]...
select <table_value1>[,<table_value2>...] from dual;
insert into <tableName>[(<table_column1>,<table_column2>...)]
select [<column_value1>,<column_value2>...] from dual
[ union select [<column_value1>,<column_value2>...] from dual ]...
insert into <tableName1>[(<table_column1>,<table_column2>...)]
select [<column_value1>,<column_value2>...] from <tableName2> [where [...]]
union [ select [<column_value1>,<column_value2>...] from <tableName2> [where [...] ]]
反正看着挺蛋疼的！
然后用Mybatis遍历，还要用到foreach标签来循环，写出来就蛋疼的很蛋疼的很
解决：
MySQL就用foreach标签写
collection：你传进去的那个List的名字，要对应
item：在SQL语句里，遍历出来的每一个集合中元素的临时名字，相当于for循环的那个i
separator：循环和循环之间的分割符，写个,，就拼成了(一堆参数), (一堆参数)这样
<insert id="insertBatch" >
insert into person
values
<foreach collection="list" item="item" index="index" separator=",">
(null,#{item.name},#{item.sex},#{item.address})
</foreach>
</insert>
Oracle不能用上面这个写法，因为他和Mysql语法不一样。然后上面列了3中Oracle的多条插入语法，你可以自己拼foreach，反正挺蛋疼的，你也可以上网去查别人的，我比较懒，我就在这里写一种自己看的懂的写法
直接把整个Insert语句用foreach遍历，用begin和end;套起来，弄成一个存储过程
把MioInfoLst当成学生表Student就对了，现在要插入一堆学生，传进去的列表就叫list
// 这是个HashMap，里面放了了个列表MioInfoLstDtoList，键叫list
mioInfoLstMap.put("list", MioInfoLstDtoList);

dao.save("webServiceMioMapper.saveMioInfoLst", mioInfoLstMap);
<insert id="saveMioInfoLst">
begin
<foreach collection="list" item="item" index="index">
INSERT INTO
MioInfoLst (
ID,
MioFlag,
MioAmnt,
PlnmioDate,
MioDate,
BankAccNo,
BankCode,
AccCustName,
BID)
values (
MioInfoLstAutoIncrement.NEXTVAL,
#{item.MioFlag},
#{item.MioAmnt},
to_date(#{item.MioDate},'yyyy/mm/dd HH24:MI:SS'),
to_date(#{item.PlnmioDate},'yyyy/mm/dd HH24:MI:SS'),
#{item.BankAccNo},
#{item.BankCode},
#{item.AccCustName},
#{item.BID}
);
</foreach>
end;
</insert>
warning
这种写法，要特别注意;，很严格，如代码所示，首先Insert语句后面的;不能少，平时写单条insert;没了没事，这里没了直接凉凉；其次，end后面有个;，不写也凉凉
这个写法相当于你的列表里有几个元素，最后就跑几个正常的单次insert语句

Mybatis操作Oracle的一些坑与总结#

联表删除#

自增主键#

序列的建立#

序列的删除#

通过序列获得完成自增主键#

warning

warning

在插入命令中使用序列实现自增主键#

自增主键并返回主键值实现多表联合插入#

selectKey标签#

批量插入#

warning

Mybatis操作Oracle的一些坑与总结

联表删除

自增主键

序列的建立

序列的删除

通过序列获得完成自增主键

在插入命令中使用序列实现自增主键

自增主键并返回主键值实现多表联合插入

selectKey标签

批量插入