创建province表，代表省份表，有如下字段 province_id int类型、主键、自增 -- 省份id province_name varchar类型(30长度) -- 省份名称 province_position varchar类型(30长度) -- 省份位置 在省份表中加入如下数据 1 辽宁省 东北 2 福建省 东南 3 云南省 西南 创建city表，代表城市表，有如下字段 city_id int类型、主键、自增 -- 城市id city_name varchar类型(30长度) -- 城市名称 city_people int类型 -- 城市人口(单位：万) city_province_id int类型 -- 城市对应省份id 在城市表中加入如下数据 1 大连市 600 1 2 沈阳市 800 1 3 鞍山市 400 1 4 厦门市 888 2 5 福州市 300 2 6 昆明市 555 3 7 大理市 233 3 新建entity包，创建指定实体类，按照需求完成实体类之间的映射关系，加入对应成员变量 新建mapper包，创建ProvinceMapper接口，接口中如下抽象方法 selectProvinceAndCity //方法用于查询所有的省份和对应城市信息 selectProvinceAndCityByPosition //方法用于查询指定位置的省份和对应的城市信息 在mapper包中建立CityMapper接口，接口中如下抽象方法 selectCityAndProvince //方法用于查询所有城市和对应省份信息 selectCityAndProvinceByName //方法用于根据城市名称模糊查询城市和对应省份信息 selectCityAndProvinceByPeople //方法用于查询指定范围内的人口对应城市和对应省份信息 上述查询可做成动态SQL 新建测试类，测试以上五个查询功能！

好的，您的问题是如何创建Province表和City表，并加入数据，并创建对应的实体类和Mapper接口，并实现五个查询功能的动态SQL。以下是对应的代码实现： Province实体类： ``` public class Province { private Integer provinceId; private String provinceName; private String provincePosition; private List<City> cities; // getter和setter方法省略 } ``` City实体类： ``` public class City { private Integer cityId; private String cityName; private Integer cityPeople; private Integer cityProvinceId; private Province province; // getter和setter方法省略 } ``` ProvinceMapper接口： ``` public interface ProvinceMapper { List<Province> selectProvinceAndCity(); List<Province> selectProvinceAndCityByPosition(String position); } ``` CityMapper接口： ``` public interface CityMapper { List<City> selectCityAndProvince(); List<City> selectCityAndProvinceByName(String cityName); List<City> selectCityAndProvinceByPeople(@Param("minPeople") Integer minPeople, @Param("maxPeople") Integer maxPeople); } ``` 对应的Mapper.xml文件中的动态SQL查询语句如下： selectProvinceAndCity： ``` <select id="selectProvinceAndCity" resultMap="ProvinceResultMap"> SELECT p.province_id, p.province_name, p.province_position, c.city_id, c.city_name, c.city_people, c.city_province_id FROM province p LEFT JOIN city c ON p.province_id = c.city_province_id ORDER BY p.province_id ASC </select> <resultMap id="ProvinceResultMap" type="Province"> <id column="province_id" property="provinceId"/> <result column="province_name" property="provinceName"/> <result column="province_position" property="provincePosition"/> <collection property="cities" ofType="City"> <id column="city_id" property="cityId"/> <result column="city_name" property="cityName"/> <result column="city_people" property="cityPeople"/> <result column="city_province_id" property="cityProvinceId"/> </collection> </resultMap> ``` selectProvinceAndCityByPosition： ``` <select id="selectProvinceAndCityByPosition" parameterType="String" resultMap="ProvinceResultMap"> SELECT p.province_id, p.province_name, p.province_position, c.city_id, c.city_name, c.city_people, c.city_province_id FROM province p LEFT JOIN city c ON p.province_id = c.city_province_id WHERE p.province_position = #{position} ORDER BY p.province_id ASC </select> ``` selectCityAndProvince： ``` <select id="selectCityAndProvince" resultMap="CityResultMap"> SELECT c.city_id, c.city_name, c.city_people, c.city_province_id, p.province_id, p.province_name, p.province_position FROM city c LEFT JOIN province p ON c.city_province_id = p.province_id ORDER BY c.city_id ASC </select> <resultMap id="CityResultMap" type="City"> <id column="city_id" property="cityId"/> <result column="city_name" property="cityName"/> <result column="city_people" property="cityPeople"/> <result column="city_province_id" property="cityProvinceId"/> <association property="province" javaType="Province"> <id column="province_id" property="provinceId"/> <result column="province_name" property="provinceName"/> <result column="province_position" property="provincePosition"/> </association> </resultMap> ``` selectCityAndProvinceByName： ``` <select id="selectCityAndProvinceByName" parameterType="String" resultMap="CityResultMap"> SELECT c.city_id, c.city_name, c.city_people, c.city_province_id, p.province_id, p.province_name, p.province_position FROM city c LEFT JOIN province p ON c.city_province_id = p.province_id WHERE c.city_name LIKE CONCAT('%', #{cityName}, '%') ORDER BY c.city_id ASC </select> ``` selectCityAndProvinceByPeople： ``` <select id="selectCityAndProvinceByPeople" parameterType="map" resultMap="CityResultMap"> SELECT c.city_id, c.city_name, c.city_people, c.city_province_id, p.province_id, p.province_name, p.province_position FROM city c LEFT JOIN province p ON c.city_province_id = p.province_id WHERE c.city_people BETWEEN #{minPeople} AND #{maxPeople} ORDER BY c.city_id ASC </select> ``` 测试类中的代码如下： ``` public class Test { public static void main(String[] args) { SqlSession sqlSession = MybatisUtils.getSqlSession(); ProvinceMapper provinceMapper = sqlSession.getMapper(ProvinceMapper.class); CityMapper cityMapper = sqlSession.getMapper(CityMapper.class); // 查询所有省份和对应城市信息 List<Province> provinces = provinceMapper.selectProvinceAndCity(); for (Province province : provinces) { System.out.println(province.getProvinceName()); for (City city : province.getCities()) { System.out.println(city.getCityName() + " " + city.getCityPeople()); } System.out.println("----------------------"); } // 查询指定位置的省份和对应的城市信息 List<Province> provinces1 = provinceMapper.selectProvinceAndCityByPosition("东南"); for (Province province : provinces1) { System.out.println(province.getProvinceName()); for (City city : province.getCities()) { System.out.println(city.getCityName() + " " + city.getCityPeople()); } System.out.println("----------------------"); } // 查询所有城市和对应省份信息 List<City> cities = cityMapper.selectCityAndProvince(); for (City city : cities) { System.out.println(city.getCityName() + " " + city.getProvince().getProvinceName()); } // 根据城市名称模糊查询城市和对应省份信息 List<City> cities1 = cityMapper.selectCityAndProvinceByName("厦门"); for (City city : cities1) { System.out.println(city.getCityName() + " " + city.getProvince().getProvinceName()); } // 查询指定范围内的人口对应城市和对应省份信息 Map<String, Integer> map = new HashMap<>(); map.put("minPeople", 500); map.put("maxPeople", 800); List<City> cities2 = cityMapper.selectCityAndProvinceByPeople(map); for (City city : cities2) { System.out.println(city.getCityName() + " " + city.getProvince().getProvinceName() + " " + city.getCityPeople()); } sqlSession.close(); } } ```