【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 configurationPUBLIC "-//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 mapperPUBLIC "-//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;@Overridepublic 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();}}
}

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