地区选择数据库形式的基础数据转成JSON串

朱雀 2022-04-06 10:48 220阅读 0赞

在做H5开发过程中，地区选择的基础数据产品经理不知从哪里哪来了一些SQL，而这个数据其实很难会变，存储在后台的DB或者缓存中，好像没有必要，于是得想办法把它转成json串保存到文件中，请求时直接请求这个JSON串文件即可。

基础SQL：

DROP TABLE IF EXISTS Province;
    DROP TABLE IF EXISTS City;
    DROP TABLE IF EXISTS District;
    
    CREATE TABLE Province (Id int,
    	Name varchar(50) ,
    	orderid int 
    )row_format=dynamic engine=innodb default charset utf8;
    insert into Province values('1','北京','0'); 
    insert into Province values('2','天津','0');
    
    CREATE TABLE City(
    	Id int ,
    	ProvinceId int,
    	Name varchar(50),
    	AreaCode varchar(50)
    )row_format=dynamic engine=innodb default charset utf8;
    
    insert into City values('1','1','北京市','010'); 
    insert into City values('2','2','天津市','022'); 
    insert into City values('3','3','石家庄市','0311'); 
    insert into City values('4','3','唐山市','0315'); 
    
    CREATE TABLE District(
    	Id int ,
    	CityId int ,
    	Name varchar(50),
    	PostCode varchar(50)
    )row_format=dynamic engine=innodb default charset utf8;
    
    insert into District values('1','1','东城区','100010'); 
    insert into District values('2','1','西城区','100032'); 
    insert into District values('3','1','崇文区','100061'); 
    insert into District values('4','1','宣武区','100054'); 
    insert into District values('5','1','朝阳区','100020'); 
    insert into District values('6','1','丰台区','100071'); 
    insert into District values('7','1','石景山区','100043');

一看，乖乖，这不就是一对多的关系吗？于是想到用MyBatis处理即可。参考myBatis系列之四：关联数据的查询和【Mybatis高级映射】一对一映射、一对多映射、多对多映射很快就写出来了。

Configuration.xml

<?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>  
        <typeAliases>  
            <typeAlias alias="Province" type="com.bijian.study.dto.Province" />
            <typeAlias alias="City" type="com.bijian.study.dto.City" />
            <typeAlias alias="District" type="com.bijian.study.dto.District" />
        </typeAliases>  
          
        <environments default="development">  
          <environment id="development">  
            <transactionManager type="JDBC"/>  
            <dataSource type="POOLED">  
                <property name="driver" value="com.mysql.jdbc.Driver" />  
                <property name="url" value="jdbc:mysql://10.107.96.172:3306/test" />  
                <property name="username" value="test" />  
                <property name="password" value="test" />  
            </dataSource>  
          </environment>  
        </environments>
          
        <mappers>  
            <mapper resource="com/bijian/study/dto/Province.xml" />
            <mapper resource="com/bijian/study/dto/City.xml" />
            <mapper resource="com/bijian/study/dto/District.xml" />
        </mappers>  
    </configuration>

log4j2.xml

<?xml version="1.0" encoding="UTF-8"?>  
    <configuration status="OFF">  
      <appenders>  
        <Console name="Console" target="SYSTEM_OUT">  
          <PatternLayout pattern="%d{HH:mm:ss.SSS} [%t] %-5level %logger{36} - %msg%n"/>  
        </Console>  
      </appenders>  
      <loggers>  
        <root level="info">  
          <appender-ref ref="Console"/>  
        </root>  
      </loggers>  
    </configuration>

ProvinceMapper.java

package com.bijian.study.dao;
    
    import java.util.List;
    
    import com.bijian.study.dto.Province;
    
    public interface ProvinceMapper {
        
        Province getProvinceById(int id);
        List<Province> getProvinceList();
        
        List<Province> getAllProvinceList();
    }

Province.java

package com.bijian.study.dto;
    
    import java.util.List;
    
    public class Province {
    
        private int id;
        private String name;
        private int orderid;
        private List<City> subs;
        
        public int getId() {
            return id;
        }
        public void setId(int id) {
            this.id = id;
        }
        public String getName() {
            return name;
        }
        public void setName(String name) {
            this.name = name;
        }
        public int getOrderid() {
            return orderid;
        }
        public void setOrderid(int orderid) {
            this.orderid = orderid;
        }
        public List<City> getSubs() {
          return subs;
        }
        public void setSubs(List<City> subs) {
          this.subs = subs;
        }
    }

Province.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="com.bijian.study.dao.ProvinceMapper">
    	
    	<resultMap type="Province" id="provinceList1">
    		<id column="Id" property="id" />
    		<result column="Name" property="name" />
    		<result column="orderid" property="orderid" />
    	</resultMap>
    
    	<select id="getProvinceById" parameterType="int" resultType="Province">
    		select * from Province where Id = #{id}
    	</select>
    
    	<select id="getProvinceList" resultMap="provinceList1">
    		select * from Province
    	</select>
    	
    	<resultMap type="Province" id="provinceList">
    		<result column="p_id" property="id" />
    		<result column="p_name" property="name" />
    		
    		<collection property="subs" ofType="City">
    			<id column="c_id" property="id" />
    			<result column="c_provinceId" property="provinceId"/>
    			<result column="c_name" property="name" />
    			<result column="c_areaCode" property="areaCode" />
    			
    			<collection property="subs" ofType="District">
    				<id column="d_id" property="id" />
    				<result column="d_cityId" property="cityId" />
    				<result column="d_name" property="name" />
    				<result column="d_postCode" property="postCode" />	
    			</collection>
    		</collection>
    	</resultMap>
    
    	<select id="getAllProvinceList" resultMap="provinceList">
    		select p.Id p_id, p.Name p_name, c.Id c_id, c.ProvinceId c_provinceId, c.Name c_name, c.AreaCode c_areaCode, d.Id d_id, d.CityId d_cityId, d.Name d_name, d.PostCode d_postCode
    		from Province p inner join City c on p.Id=c.ProvinceId
    		inner join District d on c.Id=d.CityId
    	</select>
    </mapper>

Main.java

package com.bijian.study;
    
    import java.io.IOException;
    import java.io.Reader;
    import java.util.List;
    
    import org.apache.ibatis.io.Resources;
    import org.apache.ibatis.session.SqlSession;
    import org.apache.ibatis.session.SqlSessionFactory;
    import org.apache.ibatis.session.SqlSessionFactoryBuilder;
    import org.slf4j.Logger;
    import org.slf4j.LoggerFactory;
    
    import com.bijian.study.dao.ProvinceMapper;
    import com.bijian.study.dto.Province;
    import com.fasterxml.jackson.databind.ObjectMapper;
    
    public class Main {
        
        private static final Logger log = LoggerFactory.getLogger(Main.class);  
        private static SqlSessionFactory sqlSessionFactory;  
      
        private static Reader reader;
        
        public static void main(String[] args) {
            try {
                reader = Resources.getResourceAsReader("Configuration.xml");  
                sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
                
                SqlSession session = sqlSessionFactory.openSession();
                
                ProvinceMapper mapper = session.getMapper(ProvinceMapper.class);  
                List<Province> provinceList = mapper.getAllProvinceList();
                log.info("{}", provinceList.size());
                
                ObjectMapper objMapper = new ObjectMapper();
                String provinceListStr = objMapper.writeValueAsString(provinceList);
                
                log.info("{}", provinceListStr);
            } catch (IOException e) {  
                log.error("Error thrown while reading the configuration: {}", e);  
            } finally {  
                if (reader != null) {  
                    try {  
                        reader.close();  
                    } catch (IOException e) {  
                        log.error("Error thrown while closing the reader: {}", e);  
                    }  
                }  
            }  
        }
    }

运行Main.java，结果如下：

![dfbd30e0-1cb8-322d-abd2-12eb7d4ba353.png][]  
　　当然，由于在实际应用中，根本无需id、cityId等，postCode、areaCode也统一用code，所以修改一下对应的sqlMapper及dto，详见附件工程包《JsonTransfer.rar》，运行Main2.java结果如下所示：

![b9d7e49f-3a8d-399f-977a-ba5835690b16.png][]

[dfbd30e0-1cb8-322d-abd2-12eb7d4ba353.png]: /images/20220406/24456c4be9ec41fc8d67a4d65f711685.png
[b9d7e49f-3a8d-399f-977a-ba5835690b16.png]: /images/20220406/0c6aab8e96a54f57804058d540db993e.png