mybatis方法参数是list的批量插入

你的名字 2022-12-15 11:30 141阅读 0赞

## 要想批量插入，首先要知道forEach标签: ##

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了，当然单参数也可以封装成map

## PersonDaoImpl ##

/** * Created by 李柏霖 * 2020/10/15 11:13 */
    
    package com.lbl.dao;
    
    import com.lbl.domain.Person;
    
    import java.util.List;
    
    public interface IPersonDao { 
        void savePersonList(List<Person> person);
    }

## IPersonService ##

/** * Created by 李柏霖 * 2020/10/15 11:07 */
    
    package com.lbl.service;
    
    import com.lbl.domain.Person;
    
    import java.util.List;
    
    public interface IPersonService { 
        void savePersonList(List<Person> person);
    }

## IPersonDao.xml ##

<?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.lbl.dao.IPersonDao">
        <insert id="savePersonList" parameterType="list">
            insert into person(name,money) values
            <foreach collection="list" index="index" item="Person" open="" close="" separator=",">
                (#{Person.name},#{Person.money})
            </foreach>
        </insert>
    </mapper>

不写open="" close=""进而的参数。

在(\#\{Person.name\},\#\{Person.money\})参数外加上()

就可以达到(?,?),(?,?),(?,?)的效果

## applicationContext.xml ##

<?xml version="1.0" encoding="UTF-8"?>
    <beans xmlns="http://www.springframework.org/schema/beans" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:context="http://www.springframework.org/schema/context" xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx" xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd http://www.springframework.org/schema/aop https://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
    
        
        <context:component-scan base-package="com.lbl">
            
            <context:exclude-filter type="annotation" expression="org.springframework.stereotype.Controller"/>
        </context:component-scan>
    
        
        <bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
            <property name="jdbcUrl" value="jdbc:mysql://47.115.79.211:3308/ssm"></property>
            <property name="driverClass" value="com.mysql.jdbc.Driver"></property>
            <property name="user" value="root"></property>
            <property name="password" value="admin123"></property>
        </bean>
        
        <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
            <property name="dataSource" ref="dataSource"/>
            
            <property name="typeAliasesPackage" value="com.lbl.domain"/>
        </bean>
        
        <bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
            <property name="basePackage" value="com.lbl.dao"/>
            <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
        </bean>
    
    
        
        
        <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
            <property name="dataSource" ref="dataSource"/>
        </bean>
    
        <tx:advice id="txAdvice" transaction-manager="transactionManager">
            <tx:attributes>
                <tx:method name="find*" read-only="true"/>
                <tx:method name="*" isolation="DEFAULT"/>
            </tx:attributes>
        </tx:advice>
    
        
        <aop:config>
            <aop:pointcut id="service" expression="execution(* com.lbl.service.impl.*ServiceImpl.*(..))"/>
            <aop:advisor advice-ref="txAdvice" pointcut-ref="service"/>
        </aop:config>
    
    
    </beans>

## log4j.properties ##

# Set root category priority to INFO and its only appender to CONSOLE.
    #log4j.rootCategory=INFO, CONSOLE            debug   info   warn error fatal
    log4j.rootCategory=debug, CONSOLE, LOGFILE
    
    # Set the enterprise logger category to FATAL and its only appender to CONSOLE.
    log4j.logger.org.apache.axis.enterprise=FATAL, CONSOLE
    
    # CONSOLE is set to be a ConsoleAppender using a PatternLayout.
    log4j.appender.CONSOLE=org.apache.log4j.ConsoleAppender
    log4j.appender.CONSOLE.layout=org.apache.log4j.PatternLayout
    log4j.appender.CONSOLE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n
    
    # LOGFILE is set to be a File appender using a PatternLayout.
    log4j.appender.LOGFILE=org.apache.log4j.FileAppender
    log4j.appender.LOGFILE.File=F:/ssmTest/log4j/ssm2.log
    log4j.appender.LOGFILE.Append=true
    log4j.appender.LOGFILE.layout=org.apache.log4j.PatternLayout
    log4j.appender.LOGFILE.layout.ConversionPattern=%d{ISO8601} %-6r [%15.15t] %-5p %30.30c %x - %m\n

## transactionTest ##

/** * Created by 李柏霖 * 2020/10/16 11:22 */
    
    package com.lbl.transaction;
    
    import com.lbl.domain.Person;
    import com.lbl.service.IPersonService;
    import org.junit.Test;
    import org.junit.runner.RunWith;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.test.context.ContextConfiguration;
    import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
    
    import java.util.ArrayList;
    import java.util.List;
    
    @RunWith(SpringJUnit4ClassRunner.class)
    @ContextConfiguration("classpath:applicationContext.xml")
    public class transactionTest { 
        @Autowired
        IPersonService service;
        @Test
        public void test03(){ 
            List<Person> personList = new ArrayList<>();
            personList.add(new Person("jack",100.00));
            personList.add(new Person("rose",200.00));
            personList.add(new Person("tony",300.00));
            service.savePersonList(personList);
    
        }
    
    }

## sql执行结果： ##

![在这里插入图片描述][20201016152227151.png_pic_center]

使用批量插入执行的SQL语句应该等价于：

`insert into person(name,money) values (?,?) , (?,?) , (?,?)`

[20201016152227151.png_pic_center]: /images/20221123/76c816e6553e438aac335d21bcebb90f.png