【MyBatis】mybatis对oracle批量操作（增删改查）

Mateo-520

已于 2022-07-29 12:01:16 修改

阅读量2k

点赞数

分类专栏： 4 框架(SpringBoot+SpringCloud) 文章标签： oracle 数据库 mybatis sql

于 2021-10-07 14:09:39 首次发布

本文链接：https://blog.csdn.net/m0_62676056/article/details/120635616

版权

4 框架(SpringBoot+SpringCloud) 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

XML：
<insert id="autoFullPass" parameterType="java.util.List" useGeneratedKeys="false">
INSERT ALL
<foreach collection="listAutoData" item="item" index="index">
into SATISFACTION_SURVEY
(PROJECT_NO, EVALUATE, EMPLOYEE_NO, SUBMIT_TIME, as_side)
values
(#{item.projectNo}, #{evaluate}, #{item.employeeNo}, sysdate, 0)
</foreach>
SELECT 1 FROM DUAL
</insert>

复制代码
补充说明：1、foreach外包了一层sql，解决核心；2、注意foreach里面的属性配置，没有一般的begin、end、分隔符等；

3、useGeneratedKeys="false" 笔者也不清楚是不是必须加这句，大家可以不加试下

（2）后面又发现的一种方法，很好理解也很符合oracle

DAO：

int insertCircle(@Param("year")int year, @Param("quarter")int quarter,
@Param("monthAry")int[] monthAry, @Param("weekAry")int[] weekAry);
复制代码
//为了大家理解，特别附上service的代码：
public int insertCircle(int year, int quarter) {
int beginMonth = quarter * 3 - 2;
int[] monthAry = {beginMonth, beginMonth + 1, beginMonth + 2};
int[] weekAry = {1, 2, 3, 4};
return testDao.insertCircle(year, quarter, monthAry, weekAry);
}
复制代码
上XML代码前先介绍一下在oracle里面，批量插入语句的sql样式是这样的

INSERT INTO TABLE_TEST (ID,NAME,PASSWORD,USERAGE)
SELECT 'id1','zhangsan','123',20 FROM DUAL
UNION ALL SELECT 'id2','lisi','321',30 FROM DUAL
UNION ALL SELECT 'id3','wang','123456',40 FROM DUAL
有了这个模板，我们就很容易结合Mybatis标签，拼接出数据库可以执行sql

=====>补充一个简单的例子，方便copy（于2018-04-02日补充）

复制代码
<insert id="createExamRecord" parameterType="java.util.List">
insert into CAIM_EXAM
(ID, OPEN_ID, QUESTION_ID)
<foreach collection="randomQus" item="item" index="index" separator="UNION ALL">
SELECT
sys_guid(),
#{openId},
#{item.questionId}
FROM dual
</foreach>
</insert>
复制代码
补：上面 openId 为另一个string的入参，item.questionId 为遍历对象的一个属性值

下面是一个之前的例子，不是很直白

XML：

复制代码
<insert id="insertCircle" parameterType="java.util.List">
INSERT INTO SALES_WEEKLY
(ID, SUB_TYPE,
DISTRICT_CODE, REGION_CODE, SUB_AREA_CODE,
YEAR, QUARTER,
MONTH, WEEK,
CREATE_USER, CREATE_TIME, WEEK_TYPE)
<foreach collection="monthAry" item="item" index="index" separator="UNION ALL">
<foreach collection="weekAry" item="val" index="seq" separator="UNION ALL">
SELECT
sys_guid() id,9 SUB_TYPE,
12 DISTRICT_CODE,12345 REGION_CODE,66666 SUB_AREA_CODE,
#{year} YEAR,#{quarter} QUARTER,
#{item} MONTH,
#{val} WEEK,
'ZZ123' CREATE_USER, sysdate CREATE_TIME, 6 WEEK_TYPE
FROM dual
</foreach>
</foreach>
</insert>
复制代码
补充说明：这个insert语句是没有values字段的；

笔者这个sql嵌套了双层foreach，实现了一次生成一个季度12周的12条不同记录，是参考Mybatis如何实现多重循环的参考例子

有一点需要说明，foreach里面的字段，最好用别名和数据库字段对应一下，

2、批量删除

DAO：

int deleteByListSon(@Param("listSon")List<String> listSon);
XML：

复制代码
<delete id="deleteByListSon" parameterType="java.util.List">
delete from WECHAT_MENU where id in
<foreach collection="listSon" item="listSon" index="index" separator="," open="(" close=")">
#{listSon}
</foreach>
</delete>
复制代码
补充说明：这个没啥好说的，基本sql格式，比较简单

3、批量更新

（1）DAO：

int changeOnlineByListSon(@Param("listSon")List<String> listSon, @Param("deleted")int deleted);
XML：

复制代码
<update id="changeOnlineByListSon">
update WECHAT_MENU
<set>
<if test="deleted == 0" >
DELETED = 2,
</if>
<if test="deleted == 2" >
DELETED = 0,
</if>
PUBLISH = 0,
UPDATE_DATE = sysdate,
</set>
where ID in
<foreach collection="listSon" item="item" index="index" separator="," open="(" close=")">
#{item}
</foreach>
</update>
复制代码
补充说明：这种还算简单的，是list<string>类型，foreach比较容易，笔者这个sql功能在于切换某菜单的上、下线状态，所以有deleted（没用status有原因、勿喷）这个值（0：上线；2下线）

（2）DAO：

public int saveRegionKpi(@Param("updateToDB")List<HwRegionWeekly> updateToDB, @Param("employeeNo")String employeeNo);
XML：

复制代码
<update id="updateRole" parameterType="java.util.List">
<foreach collection="updateToDB" item="record" index="index" open="begin" close=";end;" separator=";">
update SSM_ROLE
<set>
<if test="record.updateUser != null">
UPDATE_USER = #{employeeNo},
</if>
<if test="record.updateTime != null">
UPDATE_TIME = sysdate,
</if>
<if test="record.annualHosKpi != null">
ANNUAL_HOS_KPI = #{record.annualHosKpi,jdbcType=DECIMAL},
</if>
<if test="record.ytdPlanHos != null">
YTD_PLAN_HOS = #{record.ytdPlanHos,jdbcType=DECIMAL},
</if>
<if test="record.status == 1">
STATUS = 1,
SUBMIT_USER = #{employeeNo},
SUBMIT_TIME =
sysdate,
</if>
<if test="record.manageMeeting != null">
MANAGE_MEETING =
#{record.manageMeeting,jdbcType=DECIMAL},
</if>
<if test="record.remark != null">
REMARK = #{record.remark}
</if>
</set>
where ID = #{record.id}
</foreach>
</update>
复制代码
补充说明：首先，注意比较和（1）的区别，（1）里面所有更新的行中，每个字段更改的值都是一样的；

其次，（2）更新的多行里面。同样的字段也可能更新不同的值，其实（2）就是多条并行执行的更新语句，只不过用了mybatis的特性，

最后，主要还是要注意，（2）这种情况，foreach标签的正确写法

4、批量查询

DAO：

List<SatisfactionSurvey> listJointData(@Param("listProNo")List<Project> listProNo);
XML：

复制代码
<select id="listJointData" resultMap="BaseMap">
select r.project_no as PROJECT_NO, r.party_a_manager_no as employee_no, 0 as as_side
from requirement r
where r.project_no in
<foreach collection="listProNo" index="index" item="val" separator="," open="(" close=")">
#{val.projectNo}
</foreach>
</select>
复制代码
补充说明：这种没啥好说的，主要是foreach里面的配置，也是拼接出数据库能识别的sql语句

（特别新加）关于集合和数组的foreach的配置区别，笔者（2017/10/31）亲自遇到了一个例子，DAO层代码如下

public List<HWList> getAdxSub(@Param("listAdxSub")String[] listAdxSub);//最简单的字符数组
and c.sub_area_code in
<foreach collection="listAdxSub" index="index" item="val" separator="," open="(" close=")">
#{val}
</foreach>
数组的foreach配置，并不像网上好多说的那样，collection要配成array（反而这样会直接报错），而是和集合的一模一样，只是取值的方式，有不同于集合的方法（如上===）

只是在config里面的配置，一开始前后多加了一对双引号：ADXSubarea = “60109,60110,60111,60116“；导致split()处理后的数据异常，导致错误（配置文件里面的都是默认string类型的，不需要用“”包起来）

==》》以上方法Mybatis会帮我们进行sql注入拦截，Mybatis如果采用#{xxx}的形式设置参数，Mybatis会进行sql注入的过滤。如果采用的是${xxx}，Mybatis不会进行sql注入过滤，而是直接将参入的内容输出为sql语句。

5、总结比较

（1）、批量操作，重点在foreach的配置，不熟悉的伙伴可以先多去了解一下mybatis foreach标签的属性和用法

（2）、上面的例子还掺杂了其他很多，可以学习和借鉴的点，仔细看，多发现（以上例子都是，从已上线的项目中抽出来的，绝对是通过测试的）

（3）、以上的例子传入mybatis的参数，基本都是list集合（项目上用到的也几乎全是list），实际情况也可能是map，这里特别附上一例（map的批量查询，加UNION ALL）

附：批量查询--map参数

DAO：

复制代码
List<ShowEvaluateList> getListShowInfo(@Param("allProNo")Map<String, List<String>> allProNo, @Param("limit")int limit, @Param("page")int page,
@Param("beginDate")String beginDate, @Param("endDate")String endDate,
@Param("keyProName")String keyProName, @Param("keyPartyA")String keyPartyA, @Param("keyPartyB")String keyPartyB);//（后面的参数都是条件过滤和分页用的<貌似分页功能没实现>）

Map<String, List<String>> allNoEva = new HashMap<>();
allNoEva.put("A", allNoEvaA);
allNoEva.put("B", allNoEvaB);

//说明：allNoEvaA、allNoEvaB都是List<String>类型的集合（也就是做为甲方和乙方时，未评价的项目的projectNo的集合）
复制代码
XML：

复制代码
<select id="getListShowInfo" resultMap="ListShowMap">

<foreach collection="allProNo.keys" index="k" item="ent" separator="UNION ALL">
select row_number() over(order by 1) as rowIndex,
r.project_name as PRO_NAME, r.project_no as PRO_NO,
<choose>
<when test='ent == "A"'>
r.party_a_manager as PER, r.party_a_manager_no as PER_ID,
p.actual_online as ACTUAL_END, 0 as WHICH_PARTY
</when>
<otherwise>
r.party_b_manager as PER, r.party_b_manager_no as PER_ID,
p.actual_online as ACTUAL_END, 1 as WHICH_PARTY
</otherwise>
</choose>
FROM requirement r
left join project p on p.project_no = r.project_no
where (p.status = 3 or p.status = 4)
<include refid="filterSql"></include>
<choose>
<when test="allProNo[ent].size() != 0">
and r.project_no in
<foreach collection="allProNo[ent]" index="v" item="val" separator="," open="(" close=")">
#{val}
</foreach>
</when>
<otherwise>
and r.project_no = 'sssssssssssss'
</otherwise>
</choose>
</foreach>

</select>
复制代码
补充说明：（1）主要是参考map参数，在mybatis里面怎么循环取健和取值

（2）map的话要结合union all使用（关于和union的区别，貌似是union all不去重，union会去重且有些情况会排序取出的数据顺序）

最后，特别加上条件过滤用的sql：<include refid="filterSql"></include>，以供参考，如下：

复制代码
<sql id="filterSql">
and 1 = 1
<if test="beginDate != null and beginDate != ''">
and p.ACTUAL_ONLINE > to_date(#{beginDate}, 'YYYY/MM/DD')
</if>
<if test="endDate != null and endDate != ''">
and to_date(#{endDate}, 'YYYY/MM/DD') > p.ACTUAL_ONLINE
</if>
<if test="keyProName != null and keyProName != ''">
and p.name like '%${keyProName}%'
</if>
<if test="keyPartyA != null and keyPartyA != ''">
and r.party_a_manager like '%${keyPartyA}%'
</if>
<if test="keyPartyB != null and keyPartyB != ''">
and r.party_b_manager like '%${keyPartyB}%'
</if>
</sql>
复制代码