要想批量插入,首先要知道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">
<!-- 设置扫描包,包下设置注解@Service @Repository @Component @AutoWried-->
<context:component-scan base-package="com.lbl">
<!-- 由于springmvc的controller是由springmvc来扫描,需要将controller排除在外-->
<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>
<!-- session工厂-->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
<property name="dataSource" ref="dataSource"/>
<!-- com.wzx.domain.Person person-->
<property name="typeAliasesPackage" value="com.lbl.domain"/>
</bean>
<!-- IPersonDao.xml IPersonDao.java-->
<bean id="mapperScanner" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
<property name="basePackage" value="com.lbl.dao"/>
<property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
</bean>
<!--配置Spring框架声明式事务管理-->
<!--配置事务管理器-->
<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增强-->
<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执行结果:
使用批量插入执行的SQL语句应该等价于:
insert into person(name,money) values (?,?) , (?,?) , (?,?)