查找省市区县--数据库查询方法

数据库表

数据库文件地址链接: https://download.csdn.net/download/qq_45740503/79746382.

Area 实体类：

package com.entity;

public class Area {
	//城市id
	private int id;
	//城市名称
	private String areaName;
	//父级城市id
	private int parentId;
	//城市等级
	private int level;
	public int getId() {
		return id;
	}
	public void setId(int id) {
		this.id = id;
	}
	public String getAreaName() {
		return areaName;
	}
	public void setAreaName(String areaName) {
		this.areaName = areaName;
	}
	public int getParentId() {
		return parentId;
	}
	public void setParentId(int parentId) {
		this.parentId = parentId;
	}
	public int getLevel() {
		return level;
	}
	public void setLevel(int level) {
		this.level = level;
	}
	@Override
	public String toString() {
		return "Area [id=" + id + ", areaName=" + areaName + ", parentId=" + parentId + ", level=" + level + "]";
	}
}

AreaDao文件

package com.dao;

import java.util.List;
import org.apache.ibatis.annotations.Param;

import com.entity.Area;

public interface AreaDao {
	// 根据地区id查询
	public Area findAreaById(int id);

	// 根据地区名称查找城市信息
	public List<Area> findAreaByName(String areaName);

	// 查找等级为1的城市
	public List<Area> findAreaByLevel1();

	// 查找等级为2的城市
	public List<Area> findAreaByLevel2(String areaName);

	// 查找等级为3的城市
	public List<Area> findAreaByLevel3(@Param("areaName") String areaName, @Param("cityName") String cityName);

	// 根据区县姓名查找父级1级城市
	public List<Area> findAreaFatherByName(String areaName);
}

AreaDao.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.dao.AreaDao">
	<!-- 通过ID地区信息 -->
	<select id="findAreaById" resultType="com.entity.Area" parameterType="int">
		SELECT * FROM `area`
		WHERE id = #{id}
	</select>
	<!-- 根据地区名称查找城市信息 -->
	<select id="findAreaByName" resultType="com.entity.Area" parameterType="java.lang.String">
		SELECT * FROM `area`
		WHERE area_name = #{areaName}
	</select>
	<!-- 根据区县姓名查找父级1级城市 -->
	<select id="findAreaFatherByName" resultType="com.entity.Area" parameterType="java.lang.String">
		SELECT area_name
		FROM `area`
		WHERE id = ANY 
			(SELECT parent_id FROM `area` WHERE id = ANY 
				(SELECT parent_id FROM `area` WHERE area_name = #{areaName}
			)
		);
	</select>

	<!--查找等级为1的城市 -->
	<select id="findAreaByLevel1" resultType="com.entity.Area" parameterType="java.lang.String">
		SELECT * FROM `area`
		WHERE level = 1
	</select>

	<!--查找等级为2的城市 -->
	<select id="findAreaByLevel2" resultType="com.entity.Area" parameterType="java.lang.String">
		SELECT * FROM area
		WHERE 
			`level` = 2 AND 
			parent_id = (SELECT id FROM area WHERE area_name = #{areaName})
	</select>


	<!--查找等级为3的城市 -->
	<select id="findAreaByLevel3" resultType="com.entity.Area"
		parameterType="java.lang.String">
		SELECT *
		FROM area
		WHERE `level` = 3
			AND parent_id = (
				SELECT id FROM area 
					WHERE `area_name` = #{cityName}
					AND parent_id = (SELECT id FROM area WHERE area_name = #{areaName})
			)
	</select>

</mapper>

AreaService 层

package com.service;

import java.util.List;

import com.entity.Area;

public interface AreaService {
	// 根据等级1查询城市名称
	public List<Area> findAreaByLevel();

	// 根据等级2查询城市名称
	public List<Area> findAreaByLevel(String areaName);

	// 根据等级3查询城市名称
	public List<Area> findAreaByLevel(String areaName, String cityName);

}

AreaServiceImpl 层

package com.service.impl;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.dao.AreaDao;
import com.entity.Area;
import com.service.AreaService;
@Service
public class AreaServiceImpl implements AreaService {
	@Autowired
	private AreaDao areaDao;
	@Override
	public List<Area> findAreaByLevel() {
		return areaDao.findAreaByLevel1();
	}

	@Override
	public List<Area> findAreaByLevel(String areaName) {
		return areaDao.findAreaByLevel2(areaName);
	}

	@Override
	public List<Area> findAreaByLevel(String areaName, String cityName) {
		return areaDao.findAreaByLevel3(areaName, cityName);
	}

}

AreaController层：

package com.web;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.entity.Area;
import com.service.AreaService;

@Controller
public class AreaController {
	@Autowired
	private AreaService areaAervice;
	/**
	 * 查询2级城市
	 * 
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value = "/findAreaByLevel2", method = RequestMethod.POST)
	public Map<String, Object> findAreaByLevel2(String areaName) {
		List<Area> area = areaAervice.findAreaByLevel(areaName);
		System.out.println(area);
		Map<String, Object> rtnMap = new HashMap<String, Object>();
		rtnMap.put("area", area);
		return rtnMap;
	}
	
	/**
	 * 查询3级城市
	 * 
	 * @return
	 */
	@ResponseBody
	@RequestMapping(value = "/findAreaByLevel3", method = RequestMethod.POST)
	public Map<String, Object> findAreaByLevel3(String areaName,String cityName) {
		List<Area> district = areaAervice.findAreaByLevel(areaName,cityName);
		System.out.println(district);
		Map<String, Object> rtnMap = new HashMap<String, Object>();
		rtnMap.put("district", district);
		return rtnMap;
	}

}