excel批量插入数据库

excel批量插入数据库

使用hutool中的excel工具类

导入依赖

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>4.1.2</version>
</dependency>

package com.zgh.exam.controller;

import com.zgh.exam.service.FileInService;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

@Api(tags = "用户信息批量导入")
@RestController
@RequestMapping("/exam/v1/file")
public class FileInController {
    @Autowired
    private FileInService fileInService;

    @ApiOperation("excel批量导入数据库")
    @PostMapping("/import")
    public String importExcel(MultipartFile file) throws IOException {
        return fileInService.excelIn(file);
    }
}

package com.zgh.exam.service;

import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
public interface FileInService {
    /**
     * excel导入数据库
     * @param file
     * @throws IOException
     */
    String excelIn(MultipartFile file) throws IOException;
}

package com.zgh.exam.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.collection.ListUtil;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import com.zgh.exam.dto.UserDto;
import com.zgh.exam.service.FileInService;
import com.zgh.exam.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import java.io.IOException;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.List;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;

@Service
@Slf4j
public class FileInServiceImpl implements FileInService {
    @Autowired
    private UserService userService;

    @Override
    public String excelIn(MultipartFile file) throws IOException {
        //请求进入时间
        long start = System.currentTimeMillis();

        //文件处理成io流
        InputStream in = file.getInputStream();
        //io流给ExcelReader
        ExcelReader excelReader = ExcelUtil.getReader(in);

        //忽略第一行头(第一行是中文的情况),直接读取表的内容
        List<List<Object>> list = excelReader.read(1);
        List<UserDto> dtoList = CollUtil.newArrayList();
        //获取所有的List<UserDto>
        for (List<Object> row : list) {
            UserDto userDto = new UserDto();
            //excel内属性
            userDto.setId(row.get(0).toString());
            userDto.setUserName(row.get(1).toString());
            userDto.setJobNum(row.get(2).toString());
            userDto.setGender((byte) ("男".equals(row.get(3)) ? 1 : 0));
            userDto.setEmail(row.get(4).toString());
            userDto.setZiCompany(row.get(5).toString());
            userDto.setPositionx(row.get(6).toString());
            userDto.setDescx(row.get(7).toString());
            //表须有属性
            userDto.setDelete((byte) 0);
            userDto.setCreateId("系统默认");
            userDto.setUpdateId("系统默认");
            userDto.setCreateTime(LocalDateTime.now());
            userDto.setUpdateTime(LocalDateTime.now());
            dtoList.add(userDto);
            //****类似一一对应****
        }

        //excel数据读取完毕，准备入库
        long inStart = System.currentTimeMillis();

        //===============================多线程 进行 批量插入============================================
        List<List<UserDto>> lists = ListUtil.split(dtoList, 1000);
        for (List<UserDto> userDtoList : lists) {
            ExecutorService es = Executors.newFixedThreadPool(16);
            es.execute(new Runnable() {
                @Override
                public void run() {
                    userService.batchInsert(userDtoList);
                }
            });
        }
        //==================================多线程 进行 批量插入======================================================

        long inEnd = System.currentTimeMillis();
        long inTime = inEnd - inStart;
        log.info(" 插入耗费时间：{}", inTime);

        //请求结束时间
        long end = System.currentTimeMillis();
        long allTime = end - start;
        log.info(" 总耗费时间：{}", allTime);
        String res = "插入耗费时间:" + inTime + "  ,总耗费时间:" + allTime;
        return res;
    }
}

package com.zgh.exam.service;

import com.zgh.exam.dto.UserDto;

import java.util.List;

public interface UserService {

    /**
     * 批量插入
     *
     * @param list
     * @return
     */
    int batchInsert(List<UserDto> list);
}

package com.zgh.exam.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.zgh.exam.dao.UserMapper;
import com.zgh.exam.dto.UserDto;
import com.zgh.exam.entity.User;
import com.zgh.exam.service.UserService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

@Service
public class UserServiceImpl implements UserService {

    @Resource
    private UserMapper userMapper;

    /**
     * 批量插入
     *
     * @param list
     * @return
     */
    @Override
    public int batchInsert(List<UserDto> list) {
        List<User> userList = BeanUtil.copyToList(list, User.class);
        return userMapper.batchInsert(userList);
    }

}

package com.zgh.exam.dao;

import com.zgh.exam.entity.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

@Mapper
public interface UserMapper {

    /**
     * 批量插入
     *
     * @param list
     * @return
     */
    int batchInsert(@Param("list") List<User> list);

}

<?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.zgh.exam.dao.UserMapper">
    <resultMap id="BaseResultMap" type="com.zgh.exam.entity.User">
        <id column="id" jdbcType="VARCHAR" property="id"/>
        <result column="user_name" jdbcType="VARCHAR" property="userName"/>
        <result column="job_num" jdbcType="VARCHAR" property="jobNum"/>
        <result column="gender" jdbcType="TINYINT" property="gender"/>
        <result column="email" jdbcType="VARCHAR" property="email"/>
        <result column="zi_company" jdbcType="VARCHAR" property="ziCompany"/>
        <result column="positionx" jdbcType="VARCHAR" property="positionx"/>
        <result column="descx" jdbcType="VARCHAR" property="descx"/>
        <result column="is_delete" jdbcType="TINYINT" property="delete"/>
        <result column="create_id" jdbcType="VARCHAR" property="createId"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_id" jdbcType="VARCHAR" property="updateId"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
    </resultMap>
    <sql id="Base_Column_List">
        id,
        user_name,
        job_num,
        gender,
        email,
        zi_company,
        positionx,
        descx,
        is_delete,
        create_id,
        create_time,
        update_id,
        update_time
    </sql>
    <insert id="batchInsert">
        insert into user
                (id, user_name, job_num, gender, email, zi_company, positionx, descx, is_delete,
                 create_id, create_time, update_id, update_time)
                values
        <foreach collection="list" item="item" separator=",">
            (#{item.id,jdbcType=VARCHAR}, #{item.userName,jdbcType=VARCHAR}, #{item.jobNum,jdbcType=VARCHAR},
             #{item.gender,jdbcType=TINYINT}, #{item.email,jdbcType=VARCHAR}, #{item.ziCompany,jdbcType=VARCHAR},
             #{item.positionx,jdbcType=VARCHAR}, #{item.descx,jdbcType=VARCHAR}, #{item.delete,jdbcType=TINYINT},
             #{item.createId,jdbcType=VARCHAR}, #{item.createTime,jdbcType=TIMESTAMP},
             #{item.updateId,jdbcType=VARCHAR},
             #{item.updateTime,jdbcType=TIMESTAMP})
        </foreach>
    </insert>
</mapper>