在做H5开发过程中，地区选择的基础数据产品经理不知从哪里哪来了一些SQL，而这个数据其实很难会变，存储在后台的DB或者缓存中，好像没有必要，于是得想办法把它转成json串保存到文件中，请求时直接请求这个JSON串文件即可。
基础SQL：
class="sql" name="code">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><!-- ORM映射文件 -->
<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" />
<!-- Province属性映射到City类 -->
<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" />
<!-- District属性映射到District类 -->
<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，结果如下：
当然，由于在实际应用中，根本无需id、cityId等，postCode、areaCode也统一用code，所以修改一下对应的sqlMapper及dto，详见附件工程包《JsonTransfer.rar》，运行Main2.java结果如下所示：
相关文章
