一、地区表结构
DROP TABLE IF EXISTS `pro_area`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `pro_area` (
`area_id` bigint(30) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`area_code` varchar(64) NOT NULL COMMENT '地区编号',
`parent_code` varchar(64) NOT NULL COMMENT '父分类编号 一级地区父地区编号=-1',
`area_name` varchar(100) NOT NULL COMMENT '地区名称',
`area_state` int(2) DEFAULT NULL COMMENT '状态:1:未启用 2:已启用 9:删除',
`create_time` timestamp NULL DEFAULT NULL COMMENT '创建时间',
`update_time` timestamp NULL DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY (`area_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3424 DEFAULT CHARSET=utf8 COMMENT='地区表';
二、导入的excel表结构
三、导入本地数据到库
public class MyBatisTest {
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
/**
* 3、导入区县
* @throws Exception
*/
@Test
public void insertCountyBatch()throws Exception{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
InputStream inp = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\地址1.xlsx"));
Workbook workbook=new XSSFWorkbook(inp);
Sheet sheet = workbook.getSheetAt(0);
Map<String,String> currentMap = new HashMap<>();
for(int i=sheet.getFirstRowNum();i<=sheet.getLastRowNum();i++) {
Row currRow = sheet.getRow(i);
Cell currentCell = currRow.getCell(2);
Cell parentCell = currRow.getCell(1);
Cell ancestorCell = currRow.getCell(0);
if(currentCell.getCellType()!=Cell.CELL_TYPE_BLANK) {
currentMap.put(ancestorCell.getStringCellValue()+"_"+parentCell.getStringCellValue()+"-"+currentCell.getStringCellValue(), currentCell.getStringCellValue());
}
}
List<ProArea> list = new ArrayList<>();
Iterator it = currentMap.entrySet().iterator();
ProArea proArea = null;
while(it.hasNext()) {
Map.Entry<String, String> node = (Entry<String, String>) it.next();
proArea = new ProArea();
proArea.setAreaCode(UUID.randomUUID().toString());
proArea.setAreaName(node.getValue());
proArea.setAreaState(2);
proArea.setCreateTime(new Date(System.currentTimeMillis()));
//查询parentCode
Map<String,String> params = new HashMap<>();
String ancestorName = node.getKey().substring(0, node.getKey().indexOf("_"));
String parantName = node.getKey().substring(node.getKey().indexOf("_")+1, node.getKey().indexOf("-"));
params.put("parentName", parantName);
params.put("ancestorName", ancestorName);
String parentCode = openSession.selectOne("com.lee.poi.AreaMapper.selectAreaCodeByPNameAndAName",params);
proArea.setParentCode(parentCode);
list.add(proArea);
}
Integer insertCountyRes = openSession.insert("com.lee.poi.AreaMapper.insertAreaBatch", list);
//关闭连接
openSession.commit();
openSession.close();
}
/**
* 2、导入市
*/
@Test
public void insertCityBatch()throws Exception {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
InputStream inp = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\地址1.xlsx"));
Workbook workbook=new XSSFWorkbook(inp);
Sheet sheet = workbook.getSheetAt(0);
Map<String,String> currentMap = new HashMap<>();
for(int i=sheet.getFirstRowNum();i<=sheet.getLastRowNum();i++) {
Row currRow = sheet.getRow(i);
Cell currentCell = currRow.getCell(1);
Cell parentCell = currRow.getCell(0);
if(currentCell.getCellType()!=Cell.CELL_TYPE_BLANK) {
currentMap.put(parentCell.getStringCellValue()+"-"+currentCell.getStringCellValue(), currentCell.getStringCellValue());
}
}
List<ProArea> list = new ArrayList<>();
Iterator it = currentMap.entrySet().iterator();
ProArea proArea = null;
while(it.hasNext()) {
Map.Entry<String, String> node = (Entry<String, String>) it.next();
proArea = new ProArea();
proArea.setAreaCode(UUID.randomUUID().toString());
proArea.setAreaName(node.getValue());
proArea.setAreaState(2);
proArea.setCreateTime(new Date(System.currentTimeMillis()));
//查询parentCode
String parentCode = openSession.selectOne("com.lee.poi.AreaMapper.selectAreaCodeByName",node.getKey().substring(0, node.getKey().indexOf("-")));
proArea.setParentCode(parentCode);
list.add(proArea);
}
Integer insertCityRes = openSession.insert("com.lee.poi.AreaMapper.insertAreaBatch", list);
//关闭连接
openSession.commit();
openSession.close();
}
/**
* 1、导入省
*/
@Test
public void insertProvinceBatch()throws Exception {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
InputStream inp = new FileInputStream(new File("C:\\Users\\Administrator\\Desktop\\地址1.xlsx"));
Workbook workbook=new XSSFWorkbook(inp);
Sheet sheet = workbook.getSheetAt(0);
Map<String,String> currentMap = new HashMap<>();
for(int i=sheet.getFirstRowNum();i<=sheet.getLastRowNum();i++) {
Row currRow = sheet.getRow(i);
Cell currentCell = currRow.getCell(0);
if(currentCell.getCellType()!=Cell.CELL_TYPE_BLANK) {
currentMap.put(currentCell.getStringCellValue(), currentCell.getStringCellValue());
}
}
List<ProArea> list = new ArrayList<>();
Iterator it = currentMap.entrySet().iterator();
ProArea proArea = null;
while(it.hasNext()) {
Map.Entry<String, String> node = (Entry<String, String>) it.next();
proArea = new ProArea();
proArea.setAreaCode(UUID.randomUUID().toString());
proArea.setAreaName(node.getValue());
proArea.setAreaState(2);
proArea.setCreateTime(new Date(System.currentTimeMillis()));
proArea.setParentCode("-1");
list.add(proArea);
}
Integer insertProvinceRes = openSession.insert("com.lee.poi.AreaMapper.insertAreaBatch", list);
//关闭连接
openSession.commit();
openSession.close();
}
}
四、对应的mapper文件
<?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.lee.poi.AreaMapper">
<select id="selectAreaByParentCode" parameterType="java.lang.String" resultType="com.lee.poi.ProArea">
select * from pro_area where parent_code = #{parentCode}
</select>
<!-- 根据父级名称查询code -->
<select id="selectAreaCodeByName" parameterType="java.lang.String" resultType="java.lang.String">
select area_code from pro_area where area_name = #{areaName}
</select>
<!-- 根据父级名称 和 父级的父级名称查询code -->
<select id="selectAreaCodeByPNameAndAName" parameterType="java.util.Map" resultType="java.lang.String">
select p1.area_code
from pro_area p1
inner join pro_area p2 on p1.parent_code = p2.area_code
where p1.area_name=#{parentName} and p2.area_name=#{ancestorName}
</select>
<select id="selectProAreaTree" parameterType="java.util.Map" resultType="com.lee.poi.ProAreaTree">
select
area_code as value,
parent_code as parentCode,
area_name as text
from pro_area where parent_code = #{parentCode}
order by convert(text using gbk) asc
</select>
<insert id="insertAreaBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="areaId">
insert into pro_area
(area_code,parent_code,area_name,area_state,create_time)
values
<foreach collection="list" item="proArea" index="index" separator=",">
(
#{proArea.areaCode},
#{proArea.parentCode},
#{proArea.areaName},
#{proArea.areaState},
#{proArea.createTime}
)
</foreach>
</insert>
</mapper>
五、导入后的表内容
六、生成树结构
public class MyBatisTest2 {
public SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
return new SqlSessionFactoryBuilder().build(inputStream);
}
/**
* 4、地址树状结构
*/
@Test
public void findProAreaTree()throws Exception {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
Map<String,Object> params = new HashMap<>();
params.put("parentCode", "-1");
List<ProAreaTree> nodes = openSession.selectList("com.lee.poi.AreaMapper.selectProAreaTree", params);
findTree(nodes,openSession);
System.out.println(JSON.toJSONString(nodes));
//关闭连接
openSession.commit();
openSession.close();
}
private void findTree(List<ProAreaTree> parentNodes,SqlSession openSession) {
if(parentNodes!=null && parentNodes.size()>0) {
List<ProAreaTree> nodes = new ArrayList<>();
Map<String,Object> params = null;
for(int i=0;i<parentNodes.size();i++) {
params = new HashMap<>();
params.put("parentCode", parentNodes.get(i).getValue());
nodes = openSession.selectList("com.lee.poi.AreaMapper.selectProAreaTree", params);
parentNodes.get(i).setChildren(nodes);
findTree(nodes,openSession);
}
}
}
}
七、生成的树结构