获取json文件的批量插入数据库

思路

首先呢,我们得先获取这个文件,解析文件,将json字符串转成jsonArray对象.由于我的json文件格式是乱的,所以是一一获取它的字段.然后批量插入.下面就带大家一一讲解过程.

这是我的json文件

框架

框架随意,我这边用的是SpringBoot+Mybatis-plus,需要的包我都放在下面了

<parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.1.6.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.cqbys</groupId>
    <artifactId>logistics</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <!--mybatis-plus-starter-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        </dependency>
        <!--druid数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.12</version>
        </dependency>
        <!--druid数据源 end-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.6</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
             <artifactId>druid</artifactId>
            <version>1.1.10</version>
         </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>fastjson</artifactId>
            <version>1.2.37</version>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
        <!--mybatis-plus-starter-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.2.0</version>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.6</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

1.这里我用了工具类

package com.cqbys.utils;


import lombok.Data;

/**
 * @author 卡卡罗特
 */
@Data
public class Result {
    private int code;		//返回的代码，0表示成功，其他表示失败
    private String msg;		//成功或失败时返回的错误信息
    private Object data;	//成功时返回的数据信息

    public Result(int code, String msg, Object data){
        this.code = code;
        this.msg = msg;
        this.data = data;
    }

    public Result(ResultStatusCode resultStatusCode, Object data){
        this(resultStatusCode.getCode(), resultStatusCode.getMsg(), data);
    }

    public Result(int code, String msg){
        this(code, msg, null);
    }

    public Result(ResultStatusCode resultStatusCode){
        this(resultStatusCode, null);
    }


}

package com.cqbys.utils;


public enum ResultStatusCode {
    OK(200, "成功"),
    BAD_REQUEST(400, "参数解析失败"),
    METHOD_NOT_ALLOWED(405, "不支持当前请求方法"),
    SYSTEM_ERR(500, "服务器运行异常"),
    EXIST_USER(10001,"用户已存在"),
    NO_INFO(10002,"用户不存在，请注册"),
    NO_EXIST_INFO(10003,"已注册成功，但未填写宝宝信息"),
    NO_PASS(10004,"密码错误，请重试"),
    NO_OK(2001,"失败"),
    EXPIER(2002,"链接无效，已过期"),
    NO_EMAIL(2003,"该邮箱未注册"),
    NO_PIC(2004,"卡片已发完"),
    UPDATE(2005,"升级"),
    NO_UPDATE(2006,"不升级"),
    SHARE_TRUE(2007,"分享成功"),
    SHARE_FALSE(2008,"分享失败"),
    SUB_ERROR(2009,"提交失败");

    private int code;
    private String msg;

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    private ResultStatusCode(int code, String msg) {
        this.code = code;
        this.msg = msg;
    }

}

2.先写实体类

package com.cqbys.domain;

import lombok.Data;
import lombok.ToString;

import java.io.Serializable;

/**
 * @author 卡卡罗特
 */
@Data
@ToString
public class Logistics implements Serializable {

    private Long id;
    private int MMSI;
    private String TIME;
    private int LONGITUDE;
    private int LATITUDE;
    private int COG;
    private int SOG;
    private int HEADING;
    private int ROT;
    private int NAVSTAT;
    private int IMO;
    private String NAME;
    private String CALLSIGN;
    private int TYPE;
    private int A;
    private int B;
    private int C;
    private int D;
    private int DRAUGHT;
    private String DEST;
    private int ETA;
}

3.批量插入的方法

package com.cqbys.mapper;

import com.cqbys.domain.Logistics;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * @author 卡卡罗特
 */
@Component
public interface LogisticsMapper {

    void batchInsert(@Param("list") List<Logistics> list);
}

4.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.cqbys.mapper.LogisticsMapper">
    <insert id="batchInsert" useGeneratedKeys="true">
        insert into Logistics (MMSI,TIME,LONGITUDE,LATITUDE,COG,SOG,HEADING,ROT,NAVSTAT,IMO,NAME,CALLSIGN,TYPE,A,B,C,D,DRAUGHT,DEST,ETA) values
        <foreach collection="list" separator="," item="Logistics" >
            (
            #{Logistics.MMSI},
            #{Logistics.TIME},
            #{Logistics.LONGITUDE},
            #{Logistics.LATITUDE},
            #{Logistics.COG},
            #{Logistics.SOG},
            #{Logistics.HEADING},
            #{Logistics.ROT},
            #{Logistics.NAVSTAT},
            #{Logistics.IMO},
            #{Logistics.NAME},
            #{Logistics.CALLSIGN},
            #{Logistics.TYPE},
            #{Logistics.A},
            #{Logistics.B},
            #{Logistics.C},
            #{Logistics.D},
            #{Logistics.DRAUGHT},
            #{Logistics.DEST},
            #{Logistics.ETA}
            )
        </foreach>
    </insert>
</mapper>

5.controller层

package com.cqbys.controller;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cqbys.domain.Logistics;
import com.cqbys.service.ILogisticsService;
import com.cqbys.utils.Result;
import com.cqbys.utils.ResultStatusCode;
import org.apache.commons.io.FileUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author 卡卡罗特
 */
@RestController
@RequestMapping("/LogisticsController")
public class LogisticsController {

    @Autowired
    private ILogisticsService logisticsService;

    /**
     * 解析文件批量插入到数据库中去
     * @throws IOException
     */
    @GetMapping("/readJsonData")
    public Result readJsonData() throws IOException {
        ClassPathResource resource = new ClassPathResource("templates/data.json");
        File file = resource.getFile();
        String jsonString = FileUtils.readFileToString(file);
        JSONArray objects = JSONArray.parseArray(jsonString);
        JSONArray jsonArray = objects.getJSONArray(1);
        List<Logistics> list = new ArrayList<>();
        for (int i = 0; i < jsonArray.size(); i++) {
            Logistics logistics = new Logistics();
            JSONObject jo = jsonArray.getJSONObject(i);
            if (jo.get("MMSI") != null){
                logistics.setMMSI(Integer.valueOf(jo.get("MMSI").toString()));
            }
            if (jo.get("TIME") != null){
                logistics.setTIME(jo.get("TIME").toString());
            }
            if (jo.get("LONGITUDE") != null){
                logistics.setLONGITUDE(Integer.valueOf(jo.get("LONGITUDE").toString()));
            }
            if (jo.get("LATITUDE") != null){
                logistics.setLATITUDE(Integer.valueOf(jo.get("LATITUDE").toString()));
            }
            if (jo.get("COG") != null){
                logistics.setCOG(Integer.valueOf(jo.get("COG").toString()));
            }
            if (jo.get("SOG") != null){
                logistics.setSOG(Integer.valueOf(jo.get("SOG").toString()));
            }
            if (jo.get("HEADING") != null){
                logistics.setHEADING(Integer.valueOf(jo.get("HEADING").toString()));
            }
            if (jo.get("ROT") != null){
                logistics.setROT(Integer.valueOf(jo.get("ROT").toString()));
            }
            if (jo.get("NAVSTAT") != null){
                logistics.setNAVSTAT(Integer.valueOf(jo.get("NAVSTAT").toString()));
            }
            if (jo.get("IMO") != null){
                logistics.setIMO(Integer.valueOf(jo.get("IMO").toString()));
            }
            if (jo.get("NAME") != null){
                logistics.setNAME(jo.get("NAME").toString());
            }
            if (jo.get("CALLSIGN") != null){
                logistics.setCALLSIGN(jo.get("CALLSIGN").toString());
            }
            if (jo.get("TYPE") != null){
                logistics.setTYPE(Integer.valueOf(jo.get("TYPE").toString()));
            }
            if (jo.get("A") != null){
                logistics.setA(Integer.valueOf(jo.get("A").toString()));
            }
            if (jo.get("B") != null){
                logistics.setB(Integer.valueOf(jo.get("B").toString()));
            }
            if (jo.get("C") != null){
                logistics.setC(Integer.valueOf(jo.get("C").toString()));
            }
            if (jo.get("D") != null){
                logistics.setD(Integer.valueOf(jo.get("D").toString()));
            }
            if (jo.get("DRAUGHT") != null){
                logistics.setDRAUGHT(Integer.valueOf(jo.get("DRAUGHT").toString()));
            }
            if (jo.get("DEST") != null){
                logistics.setDEST(jo.get("DEST").toString());
            }
            list.add(logistics);
        }
        int index = 0;
        int count = 500;
        while (true) {
            if (list.size() - index <= count) {
                List<Logistics> l1 = new ArrayList<>();
                for (int i = index; i < list.size(); i++) {
                    l1.add(list.get(i));
                }
                this.logisticsService.batchInsert(l1);
                break;
            } else {
                List<Logistics> l2 = new ArrayList<>();
                for (int i = index; i < (index + count); i++) {
                    l2.add(list.get(index));
                }
                this.logisticsService.batchInsert(l2);
                index = index + count;
            }
        }
        Result result=new Result(ResultStatusCode.OK,list);
        return result;
    }
}

6.配置yml

server:
  port: 8761
  servlet:
    context-path: /
spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/Logistics?useUnicode=true&characterEncoding=utf-8&useSSL=true&serverTimezone=UTC
    driver-class-name: com.mysql.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource

7.测试

把项目跑起来,我这边用的是postman测试的

批量插入成功