foreach 数据库 MySQL+Oracle

最新推荐文章于 2024-05-16 14:29:54 发布

叫我XiaoNi

最新推荐文章于 2024-05-16 14:29:54 发布

阅读量1.4k

点赞数 1

分类专栏：项目踩坑文章标签： mysql java

本文链接：https://blog.csdn.net/weixin_41793772/article/details/105417605

版权

项目踩坑专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章目录

Mybatis 中 in 用法
扩展
带出的问题
- Update 返回值
- tips
新增-Oracle

Mybatis 中 in 用法

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

foreach 主要属性

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

index：指定一个名字，表示在迭代过程中，每次迭代到的位置（在集合数组情况下：值为当前索引值，当迭代循环的对象是Map类型时，这个值为Map的key）

open：该语句以什么开始（整个循环内容开头的字符串）

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

close：以什么结束（整个循环内容结尾的字符串）

collection：必填，主要分三种情况：

传入的是单参数且参数类型是 List 的时候，collection=“list”
传入的是单参数且参数类型是一个array数组的时候，collection=“array”
传入的参数是多个的时候，我们需要将其封装为一个Map，单参数也可以封装成Map，collection=“key值”

三种写法

单参数 List 类型：

<select id="dynamicForeachTest" resultType="Blog">
           select * from t_blog where id in
        <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
               #{item}       
       </foreach>    
   </select>

单参数 Array 数组类型：

<select id="dynamicForeach2Test" resultType="Blog">
     select * from t_blog where id in
     <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
          #{item}
     </foreach>
 </select>

多参数封装成 Map 类型：

<select id="dynamicForeach3Test" resultType="Blog">
2         select * from t_blog where title like "%"#{title}"%" and id in
3          <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
4               #{item}
5          </foreach>
6 </select>

实战

批量查询：前端输入框中通过 ID 查询，ID 可同时输入多个，比如：1,2,3,5,7；

Controller层中：

String prodIds = prodSceneElement.getProdId();
Map mapProdIds=new HashMap();
if(prodIds!=null){
    //prodIds 为 null ，会报错 NullPointerException
    mapProdIds.put("prodIds",prodIds.split("\\,"));
}
try{
    listProduct = qryProductService.qryProductByProdId(mapProdIds);
}

String.split 函数会返回一个数组，并且当 prodIds 为 null 的时候，会报空指针异常！（所以代码中加了一个判断）

「在这里，没必要再使用一个Map封装，直接把返回的 Array 传到 SQL 中即可」

SQL语句的写法：

<select id="qryProductByProdId" parameterType="java.util.Map" resultMap="ProductMapperResultMap">
    SELECT PROD_ID,PROD_NBR,PROD_NAME FROM PRODUCT
    <if test="prodIds!=null and prodIds!=''">
        where PROD_ID in
        <foreach collection="prodIds" index="prodIds" item="item" open="(" separator="," close=")">
                  #{item}
        </foreach>
    </if>
</select>

直接用 Array 传入

 String prodIds = prodSceneElement.getProdId();
 String[] arrayProdIds=null;

 if(prodIds!=null){
      //直接数组传递
      arrayProdIds=prodIds.split("\\,");
   }
   try{
       listProduct = qryProductService.qryProductByProdId(arrayProdIds);

中间过程的参数传递

public List<Product> qryProductByProdId(String[] arrayProdIds) throws SQLException;

SQL 写法：注意 collection 的值；如何判断传入参数是否为空

<select id="qryProductByProdId" resultMap="ProductMapperResultMap">
    SELECT PROD_ID,PROD_NBR,PROD_NAME FROM PRODUCT
    //判断直接使用 array 判断是否为空！
      <if test="array!=null">
        where PROD_ID in
        <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
      </if>
</select>

扩展

foreach 实现批量插入

int insertList(List<SysUser> userList);//mapper 中的接口方法

SQL:

<insert id="insertList">
   insert into user(id,username,password) values 
   <foreach collection="list" item="user" separator=",">
   (
   #{user.id},#{user.username},#{user.password}
   )
   </foreach>
</insert>

通过 item 指定了循环变量名后，在引用值的时候使用的是“属性·属性”的方式，如上面的 SQL

实战

在重构项目中遇到使用 for 循环插入、更新数据库的代码。。。

果断直接使用 foreach 优化了一波：当前接口少连接数据库6次

//实现类中的代码；mapper 中的代码就是一句，传递数据作用
String prodId = "100##200##300";
        String prodName = "产品10##产品20##产品30";
        String prodNbr="100200300";

        String[] prodIds=prodId.split("##");
        String[] prodNames=prodName.split("##");

        List<Map<String,Object>> list=new ArrayList<>();

        for(int i=0;i<prodIds.length;i++){
            Map<String, Object> inMap = new HashMap<>();
            inMap.put("prodId",prodIds[i]);
            inMap.put("prodName",prodNames[i]);
            inMap.put("prodNbr",prodNbr);
            list.add(inMap);
        }
		
		Map<String, Object> mapUpdate = new HashMap<>();
        mapUpdate.put("test",list);
        bsYdxhRelMapper.insertLocalTest(mapUpdate);

//xml 语句
	  <insert id="insertLocalTest" parameterType="java.util.Map">
        insert into product (prod_id,prod_name,prod_nbr) values
        <foreach collection="test" index="test" item="item" separator="," >
            (#{item.prodId},#{item.prodName},#{item.prodNbr})
        </foreach>
    </insert>

// 直接对应的 SQL 语句：
insert into table
	(id,name,age) values(1,nn,11),(2,mm,22)

foreach 实现批量更新

实战1

//实现类中的代码（测试数据），和上面的插入数据一样
<update id="insertLocalTest" parameterType="java.util.Map">
    <foreach collection="test" index="test" item="item" separator=";">
            update product
            set
            prod_id = #{item.prodId}
            where
            prod_nbr=#{item.prodNbr} and prod_name=#{item.prodName}
    </foreach>
</update>

# 对应的 SQL 执行语句如下：
update product
            set
            prod_id = #{item.prodId}
            where
            prod_nbr=#{item.prodNbr} and prod_name=#{item.prodName};
update product
            set
            prod_id = #{item.prodId}
            where
            prod_nbr=#{item.prodNbr} and prod_name=#{item.prodName}
            
# 是否可以用 case when then  优化？？？下面就是优化

实战2

这个是对上面批量更新代码的改进。为什么会有这个改进？

在具体的项目中，实战 1 的代码可能会运行不通过，会报错。网上百度说MySQL 没有开启批量插入，我按照网上的教程在连接 MySQL 的 URL 加上了 allowMultiQueries=true 还是不行。

现在就是接着上面的分析：使用 case when then 语句写成一条 SQL 语句

<!--
 <foreach collection="maps" index="index" item="item" separator=";">
             update bs_ydxh_rel
             SET
             status_cd=#{item.status}
             WHERE
             ORDER_NBR=#{item.orderNbr} and acc_nbr= #{item.accNbr}
         </foreach>
         /* 在数据库中具体的执行语句是三条。报错
            改为如下的 case when then ,在数据库中只有一条 SQL ，执行成功
          */
          -->
        update bs_ydxh_rel
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="status_cd=case" suffix="end,">
                <foreach collection="maps" item="item" index="index">
                    WHEN ORDER_NBR=#{item.orderNbr} and acc_nbr= #{item.accNbr} THEN #{item.status}
                </foreach>
            </trim>
        </trim>

上面的两个写法在数据库中执行效果一致，但是第一种写法网上说是MySQL默认不支持批量操作，通过执行代码，确实也出错；
第二种写法就是一条 SQL 语句，所以执行成功，在数据库对应的写法如下：

update bs_ydxh_rel set status_cd=case WHEN ORDER_NBR=? and acc_nbr= ? THEN ? WHEN ORDER_NBR=? and acc_nbr= ? THEN ? WHEN ORDER_NBR=? and acc_nbr= ? THEN ? end

foreach 实现批量删除

<delete id="batchDeleteUsers" parameterType="java.util.List">
		delete from mhc_user where id in
		<foreach collection="list" index="index" item="item" open="(" close=")" separator=",">
			#{item}
		</foreach>
	</delete>

以上实战，均是我在项目中使用过的，绝对亲测！大家有好的建议，欢迎交流

------------------------------------------后续添加--------------------------------------------

带出的问题

在这里插入图片描述

本地 MySQL 版本信息和使用引擎。

Update 返回值

update 语句返回值是什么？如下图：我在本地的数据库进行的测试

在这里插入图片描述

当数据一致，不执行 SQL：matched:1 changed:0
当数据不一致，执行 SQL：matched:1 changed:1

但是 Update 通过 Java 代码执行的 update SQL 返回值都是 1。即 Update 返回值是匹配的行，而不是受影响的行。

总结：

Update 返回的值是匹配的行，如果匹配上且数据一致，不会修改；如果数据不一致，才会真正修改。

tips

在项目中使用到了，Mybatis 整合，在 XML 中使用了 foreach 批量更新操作（见上面的 实战2 代码），此时Update 返回的值是这张表的总数据。尽管不满足 case when then 的匹配条件。

-------------------------------------分割线2021.1.19------------------------------------------

新增-Oracle

以上的 foreach 操作都是基于 mysql 数据库，不适用于 Oracle

Oracle-mybatis-foreach

直接给出可运行的 xml 中 foreach 的写法。
参考网上大部分写法，和下面的差不多。

<insert id="insertWhiteNumber" parameterType="java.util.List" useGeneratedKeys="false">
        insert into tmp.INTER_WHITE_NUMBER(telnumber,telecom,industry,idcard,nature)
        select A.* from (
        <foreach collection="list" item="item" index="index" separator="UNION ALL">
            select
            #{item.telnumber,jdbcType=VARCHAR},
            #{item.telecom,jdbcType=VARCHAR},
            #{item.industry,jdbcType=VARCHAR},
            #{item.idcard,jdbcType=VARCHAR},
            #{item.nature,jdbcType=VARCHAR}
            from dual
        </foreach>)A
    </insert>

Oracle 运行注意事项

1.SQL中没有VALUES；

2.标签中的(selece … from dual)；

3.标签中的separator的属性为"UNION ALL"，将查询合并结果集。

解决思路

先 Google 搜索网上方法，直接拿过来用，发现本地代码运行始终报错（并且尝试网上的方法都几乎一致，说明绝大可能是本地的问题）
直接使用链接工具，链接 Oracle 进行操作，先将 sql 能在数据库中跑起来，然后根据 foreach 用法套用在 xml 文件中

Oracle 直接数据库运行的批量查询SQL：

insert all 
into table_name(telnumber, telecom, industry, idcard, nature) values('SRSTugklw47E1aWg', '201', '*科技', '222039149376', '1')
into table_name(telnumber, telecom, industry, idcard, nature) values('SRSTuGCfw4aWg', '2001', '**科技', '220092384372', '1')
SELECT 1 from dual

下面的 SQL 就是 XML 文件中对应的 SQL　执行语句

INSERT INTO tmp.INTER_WHITE_NUMBER ( telnumber, telecom, industry, idcard, nature ) SELECT
A.* 
FROM
	(
	SELECT
		'SRSTuglhCfw47EaWg',
		'2001',
		'上海**科技',
		'22027238493276',
		'1' 
	FROM
		dual UNION ALL
	SELECT
		'SRSllhCfw47EaWg',
		'2001',
		'上海**科技',
		'22022384937276',
		'1' 
	FROM
	dual 
	)A