service

public void batchInsert(List<Prop> propList) throws Exception {
        propMapper.insertAll(propList);
    }

mapper

@InsertProvider(type = PropProvider.class, method = "insertAll")
         void insertAll(@Param("list") List<Prop> props);

provider

public String insertAll(Map map) {
        List<Prop> props = (List<Prop>) map.get("list");
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO t_game_prop ");
        sb.append("(game_id,currency_id,propid,propname,price) ");
        sb.append("VALUES ");
        MessageFormat mf = new MessageFormat("(#'{'list[{0}].game_id},#'{'list[{0}].currency_id},#'{'list[{0}].propid},#'{'list[{0}].propname},#'{'list[{0}].price})");
        for (int i = 0; i < props.size(); i++) {
            sb.append(mf.format(new Object[]{i}));
            if (i < props.size() - 1) {
                sb.append(",");
            }
        }
        return sb.toString();
    }

/**
     * excel导入
     * @return
     */
    @Transactional
    public BaseResult upload(MultipartFile file,HttpServletRequest request){
        boolean success = true;
        String msg = "";
        BaseResult result = new BaseResult();
        HSSFWorkbook hssfWorkbook = null;
        try {
            InputStream is = file.getInputStream();
            hssfWorkbook = new HSSFWorkbook(is);
            List<Prop> propList = new ArrayList<Prop>();
            StringBuffer buffer = new StringBuffer();
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                for (int row = 1; row <= hssfSheet.getLastRowNum(); row++) {
                    HSSFRow hssfRow = hssfSheet.getRow(row);
                    if (hssfRow == null) {
                        buffer.append(row+1 + ",");
                        continue;
                    }
                    Prop prop = new Prop();
                    HSSFCell cell = hssfRow.getCell(0);
                    if (cell == null) {
                        buffer.append(row+1 + ",");
                        continue;
                    }
                    String propid = StringUtils.trimAllWhitespace(cell.getStringCellValue());
                    int i = propMapper.isExistsPropid(propid);
                    if (i != 0) {
                        buffer.append(row+1 + ",");
                        continue;
                    } else {
                        cell = hssfRow.getCell(4);
                        if (cell == null){
                            buffer.append(row+1 + ",");
                            continue;
                        }
                        String bundle = StringUtils.trimAllWhitespace(cell.getStringCellValue());
                        Integer id = gameMapper.isExistsBundle(bundle);
                        //如果游戏存在，不插入游戏，只插入道具
                        if (id != null) {
                            prop.setGame_id(id);
                            prop.setCurrency_id(1);
                            prop.setPropid(propid);
                            cell = hssfRow.getCell(1);
                            if (cell == null){
                                buffer.append(row+1 + ",");
                                continue;
                            }
                            prop.setPropname(StringUtils.trimAllWhitespace(cell.getStringCellValue()));
                            cell = hssfRow.getCell(2);
                            if (cell == null){
                                buffer.append(row+1 + ",");
                                continue;
                            }
                            prop.setPrice(new BigDecimal(cell.getNumericCellValue()));
                            propList.add(prop);
                        }else{
                            //如果游戏不存在，先插入游戏，再插入道具
                            Game game = new Game();
                            cell = hssfRow.getCell(3);
                            if (cell == null){
                                buffer.append(row+1 + ",");
                                continue;
                            }
                            String gameName = StringUtils.trimAllWhitespace(cell.getStringCellValue());
                            game.setName(gameName);
                            game.setPinyin(HanyuPinyinHelper.getPinyinString(gameName));
                            game.setForshort(HanyuPinyinHelper.getFirstLettersLo(gameName));
                            game.setBundle(bundle);
                            game.setCreate_time(new Date());
                            int x = gameMapper.insertGameGetId(game);
                            if (x != -1) {
                                prop.setGame_id(game.getId());
                                prop.setCurrency_id(1);
                                prop.setPropid(propid);
                                cell = hssfRow.getCell(1);
                                if (cell == null){
                                    buffer.append(row+1 + ",");
                                    continue;
                                }
                                prop.setPropname(StringUtils.trimAllWhitespace(cell.getStringCellValue()));
                                cell = hssfRow.getCell(2);
                                if (cell == null){
                                    buffer.append(row+1 + ",");
                                    continue;
                                }
                                prop.setPrice(new BigDecimal(cell.getNumericCellValue()));
                                propList.add(prop);
                            }

                        }
                    }
                }
            }

            HttpSession session = request.getSession();
            session.setAttribute("propList",propList);
            session.setMaxInactiveInterval(300);
            if (propList.size() > 0) {
                if (StringUtil.isNotEmpty(buffer.toString())) {
                    msg = "总共有" + propList.size() + "条数据可导入，其中第" + buffer.toString().substring(0, buffer.length() - 1) + "行发生错误";
                } else {
                    msg = "总共有" + propList.size() + "条数据";
                }
            } else {
                success = false;
                if (buffer != null) {
                    msg = "数据为空或者账号格式全部错误";
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            success = false;
            msg = Global.SERVER_EXCEPTION;
        } finally {
            if (hssfWorkbook != null) {
                try {
                    hssfWorkbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            result.setSuccess(success);
            result.setMsg(msg);
            return result;
        }

    }
/**
     * 导入
     *
     * @return
     * @throws Exception
     */
    @PostMapping("/import")
    public BaseResult importExcel(HttpSession session) throws Exception {
        boolean success = true;
        String msg = "导入成功!";
        try {
            List<Prop> propList = (List<Prop>)session.getAttribute("propList");
            propService.batchInsert(propList);
        } catch (Exception e) {
            success = false;
            msg = "导入失败,可能导入的数据已存在!";
        }
        BaseResult result = new BaseResult();
        result.setSuccess(success);
        result.setMsg(msg);
        return result;

    }

@Insert("Insert Into t_game(name,pinyin,forshort,bundle,create_time) Values(#{game.name},#{game.pinyin},#{game.forshort},#{game.bundle},#{game.create_time})")
    @SelectKey(before = false, keyProperty = "game.id", resultType = Integer.class, statementType = StatementType.STATEMENT, statement = "Select last_insert_id() As id")
     int insertGameGetId(@Param("game")Game game);