【201】Java8读取JSON树形结构并插入到MySQL数据库表中

我写了一个 maven 项目的 Demo，用来演示 JAVA8 如何读取 JSON 文件树形结构，并将这种树形结构保存到 MySQL 中。

json文件 city.json

{
    "name": "山东省",
    "sub": [
        {
            "name": "青岛市",
            "sub": [
                {"name": "市南区"},
                {"name": "市北区"},
                {"name": "城阳区"},
                {"name": "李沧区"},
            ]
        },
        {
            "name": "济南市",
            "sub": [
                {"name": "市中区"},
                {"name": "历下区"},
                {"name": "天桥区"}
            ]
        },
        {
            "name": "淄博市",
            "sub": [
                {"name": "张店区"},
                {"name": "临淄区"},
            ]
        },
        {
            "name": "枣庄市"
        },
    ]
}

MySQL 数据库的 city 表结构：

CREATE TABLE `city` (
  `id` int(10) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `parent_id` int(10) NOT NULL COMMENT '父级ID',
  `name` varchar(50) NOT NULL COMMENT '名称',
  `del_flag` tinyint(1) NOT NULL COMMENT '0=未删除，1=已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4

pom文件：

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>zhangchao</groupId>
    <artifactId>Java8Json</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding>
        <java.version>1.8</java.version>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <!-- https://mvnrepository.com/artifact/com.google.code.gson/gson -->
        <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.10.1</version>   
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.mysql/mysql-connector-j -->
        <dependency>
            <groupId>com.mysql</groupId>
            <artifactId>mysql-connector-j</artifactId>
            <version>8.3.0</version>
        </dependency>


        <!-- https://mvnrepository.com/artifact/org.mybatis/mybatis -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.15</version>
        </dependency>
    </dependencies>
</project>

mybatis 配置文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/CityMapper.xml"/>
    </mappers>
</configuration>

读取配置文件的类 DBFactory.java

package zhangchao.common.db;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class DBFactory {
    private static SqlSessionFactory sqlSessionFactory = null;

    static {
        String resource = "mybatis-config.xml";
        InputStream inputStream = null;
        try {
            inputStream = Resources.getResourceAsStream(resource);
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    }

    public static SqlSessionFactory getInstance(){
        return sqlSessionFactory;
    }

}

mybatis 的 mapper XML 文件 CityMapper.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="zhangchao.addjsontree.CityMapper">


    <resultMap id="rm" type="zhangchao.addjsontree.City">
        <id property="id" column="c_id"/>
        <result property="parentId" column="parent_id"/>
        <result property="name" column="name"/>
        <result property="delFlag" column="del_flag"/>
    </resultMap>


    <insert id="insert" useGeneratedKeys="true" keyProperty="id">
        INSERT INTO `test`.`city` (
            parent_id,
            `name`,
            del_flag
        )
        VALUES
        (
            #{parentId},
            #{name},
            #{delFlag}
        )
    </insert>
</mapper>

和数据库表对应的实体类 City.java

package zhangchao.addjsontree;

public class City {
    private Integer id;
    private Integer parentId;
    private String name;
    private Integer delFlag;
    

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getParentId() {
        return parentId;
    }

    public void setParentId(Integer parentId) {
        this.parentId = parentId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getDelFlag() {
        return delFlag;
    }

    public void setDelFlag(Integer delFlag) {
        this.delFlag = delFlag;
    }
}

和 JSON 对应的 DTO 文件 CityDto.java

package zhangchao.addjsontree;

import java.util.List;

public class CityDto {
    private Integer id;
    private Integer parentId;
    private String name;
    private List<CityDto> sub;

    @Override
    public String toString() {
        final StringBuffer sb = new StringBuffer("CityDto{");
        sb.append("id=").append(id);
        sb.append(", parentId=").append(parentId);
        sb.append(", name='").append(name).append('\'');
        sb.append(", sub=").append(sub);
        sb.append('}');
        return sb.toString();
    }

    public List<CityDto> getSub() {
        return sub;
    }

    public void setSub(List<CityDto> sub) {
        this.sub = sub;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getParentId() {
        return parentId;
    }

    public void setParentId(Integer parentId) {
        this.parentId = parentId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

}

CityMapper.java

package zhangchao.addjsontree;

public interface CityMapper{

    int insert(City city);
}

整个程序的主方法，演示了具体插入数据库的算法。

先调用 getJSon() 方法，从 JSON 文件中获取 JSON 字符串，然后通过 GSON 转换成 CityDto 对象。

然后使用了树的宽度优先算法遍历树形结构，currentLevel 列表保存当前层节点，nextLevel 列表保存下一层节点。

每次循环把当前层节点插入数据库，并且在插入数据库后，获取数据库 ID（这里把表主键设置成整数自增）。对子节点做是否为空的检查，并且把 NULL 子节点删除掉。把数据库 ID 保存到子节点的 parentId 成员变量中。

把非空子节点加入到 nextLevel 列表中，然后让 currentLevel 引用指向 nextLevel。

package zhangchao.addjsontree;

import com.google.gson.Gson;
import org.apache.ibatis.session.SqlSession;
import zhangchao.common.db.DBFactory;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

/**
 * @author zhangchao 
 */
public class TestAddJsonTree {

    private static String getJSon(){
        File f = new File(
                "E:\\ws\\zc\\Java8Json\\src\\main\\resources\\JsonFile\\city.json"
        );
        StringBuilder sb = new StringBuilder();
        FileInputStream fis = null;
        BufferedReader br = null;
        try {
            fis = new FileInputStream(f);
            br = new BufferedReader(new InputStreamReader(fis, "UTF-8"));
            String str = br.readLine();
            while(str != null) {
                sb.append(str);
                str = br.readLine();
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (fis != null) {
                    fis.close();
                }
                if (br != null) {
                    br.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        String json = sb.toString();
        return json;
    }

    private static void dtoToDomain(CityDto dto, City domain) {
        domain.setName(dto.getName());
        domain.setParentId(dto.getParentId());
        domain.setDelFlag(0);
    }

    public static void main(String[] args) {
        String json = getJSon();
        Gson gson = new Gson();
        CityDto cityDto = gson.fromJson(json, CityDto.class);
        if (cityDto == null) {
            return;
        }
        cityDto.setParentId(-1);
        List<CityDto> currentLevel = new ArrayList<>();
        currentLevel.add(cityDto);
        SqlSession sqlSession = DBFactory.getInstance().openSession(false);
        try {
            CityMapper cityMapper = sqlSession.getMapper(CityMapper.class);
            while (currentLevel != null && !currentLevel.isEmpty()) {
                List<CityDto> nextLevel = new ArrayList<>();
                for (CityDto dto : currentLevel) {
                    City domain = new City();
                    dtoToDomain(dto, domain);
                    cityMapper.insert(domain);
                    List<CityDto> sub = dto.getSub();
                    if (sub != null && !sub.isEmpty()) {
                        for (Iterator<CityDto> iterator = sub.iterator(); iterator.hasNext();) {
                            CityDto item = iterator.next();
                            if (item == null) {
                                iterator.remove();
                            } else {
                                item.setParentId(domain.getId());
                            }
                        }
                        nextLevel.addAll(sub);
                    }
                }
                currentLevel = nextLevel;
            }
            sqlSession.commit();
        } catch (Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        } finally {
            sqlSession.close();
        }
    }
}

最后插入数据库的效果如下：