我写了一个 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();
}
}
}
最后插入数据库的效果如下: