mybatis使用foreach批量插入

创建表

create table public."match" ( match_date date not null , match_name character varying(20) not null , match_season character varying(10) not null , match_round numeric(2) not null , home_team character varying(30) not null , away_team character varying(30) not null , handicap numeric(3, 2) not null , handicap_greater numeric(4, 3) not null , handicap_less numeric(4, 3) not null , goals numeric(3, 2) not null , goals_greater numeric(4, 3) not null , goals_less numeric(4, 3) not null , corner numeric(4, 2) not null , corner_greater numeric(4, 3) not null , corner_less numeric(4, 3) not null , half_score character varying(20) not null , full_score character varying(20) not null )

创建pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
       <groupId>org.springframework.boot</groupId>
       <artifactId>spring-boot-starter-parent</artifactId>
       <version>2.7.8</version>
       <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.micro</groupId>
    <artifactId>springcloud-database</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>springcloud-database</name>
    <description>Demo project for Spring Boot</description>
    <properties>
       <java.version>11</java.version>
       <spring-cloud.version>2021.0.3</spring-cloud.version>
    </properties>
    <dependencies>
       <dependency>
          <groupId>org.mybatis.spring.boot</groupId>
          <artifactId>mybatis-spring-boot-starter</artifactId>
          <version>2.1.4</version>
       </dependency>
       <dependency>
          <groupId>commons-dbcp</groupId>
          <artifactId>commons-dbcp</artifactId>
          <version>1.4</version>
       </dependency>
       <dependency>
          <groupId>org.postgresql</groupId>
          <artifactId>postgresql</artifactId>
          <version>42.5.4</version>
       </dependency>
       <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-web</artifactId>
       </dependency>
       <dependency>
          <groupId>org.springframework.boot</groupId>
          <artifactId>spring-boot-starter-test</artifactId>
          <scope>test</scope>
       </dependency>
    </dependencies>
    <dependencyManagement>
       <dependencies>
          <dependency>
             <groupId>org.springframework.cloud</groupId>
             <artifactId>spring-cloud-dependencies</artifactId>
             <version>${spring-cloud.version}</version>
             <type>pom</type>
             <scope>import</scope>
          </dependency>
       </dependencies>
    </dependencyManagement>
</project>

创建controller

package com.micro.springclouddatabase.controller;

import com.micro.springclouddatabase.mapper.MatchMapper;
import com.micro.springclouddatabase.model.Match;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
public class DataBaseController {

    private MatchMapper matchMapper;

    @Autowired
    public DataBaseController(MatchMapper matchMapper) {
        this.matchMapper = matchMapper;
    }

    @GetMapping("/match/init")
    public HttpStatus insert() {
        List<Match> list = TestData.getTestData();
        matchMapper.insertMatch(list);
        return HttpStatus.OK;
    }
    @GetMapping("/match/update")
    public HttpStatus update() {
        List<Match> list = TestData.getTestData();
        matchMapper.updateMatch(list);
        return HttpStatus.OK;
    }
}

创建mapper 

package com.micro.springclouddatabase.mapper;

import com.micro.springclouddatabase.model.Match;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface MatchMapper {

    public void insertMatch(List<Match> args);

    public void updateMatch(List<Match> args);
}

创建对象类

package com.micro.springclouddatabase.model;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.time.LocalDateTime;

public class Match {
    private LocalDate matchDate;

    private String matchName;

    private String matchSeason;

    private String matchRound;

    private String homeTeam;

    private String awayTeam;

    private BigDecimal handicap;

    private BigDecimal handicapGreater;

    private BigDecimal handicapLess;

    private BigDecimal goals;

    private BigDecimal goalsGreater;

    private BigDecimal goalsLess;

    private BigDecimal corner;

    private BigDecimal cornerGreater;

    private BigDecimal cornerLess;

    private String halfScore;

    private String fullScore;

    public Match() {

    }

    public Match(LocalDate matchDate, String matchName, String matchSeason, String matchRound,
                 String homeTeam, String awayTeam, BigDecimal handicap, BigDecimal handicapGreater,
                 BigDecimal handicapLess, BigDecimal goals, BigDecimal goalsGreater, BigDecimal goalsLess,
                 BigDecimal corner, BigDecimal cornerGreater, BigDecimal cornerLess, String halfScore, String fullScore) {
        this.matchDate = matchDate;
        this.matchName = matchName;
        this.matchSeason = matchSeason;
        this.matchRound = matchRound;
        this.homeTeam = homeTeam;
        this.awayTeam = awayTeam;
        this.handicap = handicap;
        this.handicapGreater = handicapGreater;
        this.handicapLess = handicapLess;
        this.goals = goals;
        this.goalsGreater = goalsGreater;
        this.goalsLess = goalsLess;
        this.corner = corner;
        this.cornerGreater = cornerGreater;
        this.cornerLess = cornerLess;
        this.halfScore = halfScore;
        this.fullScore = fullScore;
    }


    public String getMatchName() {
        return matchName;
    }

    public void setMatchName(String matchName) {
        this.matchName = matchName;
    }

    public String getMatchSeason() {
        return matchSeason;
    }

    public void setMatchSeason(String matchSeason) {
        this.matchSeason = matchSeason;
    }

    public String getMatchRound() {
        return matchRound;
    }

    public void setMatchRound(String matchRound) {
        this.matchRound = matchRound;
    }

    public String getHomeTeam() {
        return homeTeam;
    }

    public void setHomeTeam(String homeTeam) {
        this.homeTeam = homeTeam;
    }

    public String getAwayTeam() {
        return awayTeam;
    }

    public void setAwayTeam(String awayTeam) {
        this.awayTeam = awayTeam;
    }

    public BigDecimal getHandicap() {
        return handicap;
    }

    public void setHandicap(BigDecimal handicap) {
        this.handicap = handicap;
    }

    public BigDecimal getHandicapGreater() {
        return handicapGreater;
    }

    public void setHandicapGreater(BigDecimal handicapGreater) {
        this.handicapGreater = handicapGreater;
    }

    public BigDecimal getHandicapLess() {
        return handicapLess;
    }

    public void setHandicapLess(BigDecimal handicapLess) {
        this.handicapLess = handicapLess;
    }

    public BigDecimal getGoals() {
        return goals;
    }

    public void setGoals(BigDecimal goals) {
        this.goals = goals;
    }

    public BigDecimal getGoalsGreater() {
        return goalsGreater;
    }

    public void setGoalsGreater(BigDecimal goalsGreater) {
        this.goalsGreater = goalsGreater;
    }

    public BigDecimal getGoalsLess() {
        return goalsLess;
    }

    public void setGoalsLess(BigDecimal goalsLess) {
        this.goalsLess = goalsLess;
    }

    public BigDecimal getCorner() {
        return corner;
    }

    public void setCorner(BigDecimal corner) {
        this.corner = corner;
    }

    public BigDecimal getCornerGreater() {
        return cornerGreater;
    }

    public void setCornerGreater(BigDecimal cornerGreater) {
        this.cornerGreater = cornerGreater;
    }

    public BigDecimal getCornerLess() {
        return cornerLess;
    }

    public void setCornerLess(BigDecimal cornerLess) {
        this.cornerLess = cornerLess;
    }

    public String getHalfScore() {
        return halfScore;
    }

    public void setHalfScore(String halfScore) {
        this.halfScore = halfScore;
    }

    public String getFullScore() {
        return fullScore;
    }

    public void setFullScore(String fullScore) {
        this.fullScore = fullScore;
    }
}

创建插入SQL 和 更新SQL<?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.micro.springclouddatabase.mapper.MatchMapper">
    <insert id="insertMatch" parameterType="com.micro.springclouddatabase.model.Match">
        INSERT INTO match (
            match_date
            , match_name
            , match_season
            , match_round
            , home_team
            , away_team
            , handicap
            , handicap_greater
            , handicap_less
            , goals
            , goals_greater
            , goals_less
            , corner
            , corner_greater
            , corner_less
            , half_score
            , full_score
        ) VALUES
        <foreach collection="list" item="item" separator=",">
        (
            #{item.matchDate}
            , #{item.matchName}
            , #{item.matchSeason}
            , to_number(#{item.matchRound}, '99')
            , #{item.homeTeam}
            , #{item.awayTeam}
            , #{item.handicap}
            , #{item.handicapGreater}
            , #{item.handicapLess}
            , #{item.goals}
            , #{item.goalsGreater}
            , #{item.goalsLess}
            , #{item.corner}
            , #{item.cornerGreater}
            , #{item.cornerLess}
            , #{item.halfScore}
            , #{item.fullScore}
        )
        </foreach>
    </insert>

    <update id="updateMatch" parameterType="java.util.List">
        <foreach collection="list" item="item" separator=";">
            update match
                <set>
                    half_score = #{item.halfScore}
                    , full_score = #{item.fullScore}
                </set>
            WHERE
            match_date = #{item.matchDate}
            AND match_name = #{item.matchName}
            AND home_team = #{item.homeTeam}
            AND away_team = #{item.awayTeam}
        </foreach>
    </update>
</mapper>