省 市 县 三级联动

大纲

---

 一、导入省市县数据表(t_region)

 二、引入jar包

三、导入所需util类（整体框架）

 

四、编写代码

 1、配置数据库相关信息(数据库名、用户名、密码)    config.propreties

#oracle9i
#driver=oracle.jdbc.driver.OracleDriver
#url=jdbc:oracle:thin:@localhost:1521:orcl
#user=scott
#pwd=123

#sql2005
#driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
#url=jdbc:sqlserver://localhost:1433;DatabaseName=test1
#user=sa
#pwd=123

#mysql
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8
user=root
pwd=1234

 2、创建数据库中对应数据表（t_region）的省市县实体类，即 Region.java

package com.zking.jquery.entity;
/**
 * 地区实体类
 * @author gss
 *
 */
public class Region {
	private long id;//编号
	private long parent_id;//父级编号
	private long region_id;
	private long region_parent_id;
	private String region_name;//名称
	private Integer region_type;//类型 1省 2市 3县
	public long getId() {
		return id;
	}
	public void setId(long id) {
		this.id = id;
	}
	public long getParent_id() {
		return parent_id;
	}
	public void setParent_id(long parent_id) {
		this.parent_id = parent_id;
	}
	public long getRegion_id() {
		return region_id;
	}
	public void setRegion_id(long region_id) {
		this.region_id = region_id;
	}
	public long getRegion_parent_id() {
		return region_parent_id;
	}
	public void setRegion_parent_id(long region_parent_id) {
		this.region_parent_id = region_parent_id;
	}
	public String getRegion_name() {
		return region_name;
	}
	public void setRegion_name(String region_name) {
		this.region_name = region_name;
	}
	public Integer getRegion_type() {
		return region_type;
	}
	public void setRegion_type(Integer region_type) {
		this.region_type = region_type;
	}
	
	public Region() {
		super();
	}
	
	@Override
	public String toString() {
		return "Region [id=" + id + ", parent_id=" + parent_id + ", region_id=" + region_id + ", region_parent_id="
				+ region_parent_id + ", region_name=" + region_name + ", region_type=" + region_type + "]";
	}	
}

3、 工具类  BaseDao.java

package com.zking.jquery.dao;

import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.zking.jquery.util.DBHelper;
import com.zking.jquery.util.PageBean;

public class BaseDao<T> {
	
	public static interface CallBack<E>{
		public List<E> forEach(ResultSet rs) throws SQLException;
	}
	
	/**
	 * 增删改通用方法
	 * @param sql
	 * @param params
	 */
	public void executeUpdate(String sql,Object[] params) {
		Connection conn=null;
		PreparedStatement stmt=null;
		try {
			conn=DBHelper.getConnection();
			stmt=conn.prepareStatement(sql);
			ParameterMetaData metadata = stmt.getParameterMetaData();
			for (int i = 0; i < metadata.getParameterCount(); i++) {
				stmt.setObject(i+1, params[i]);
			}
			int i = stmt.executeUpdate();
			if(i<1)
				throw new RuntimeException("执行失败，影响行数为0");
		} catch (Exception e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		}finally {
			DBHelper.close(conn, stmt, null);
		}
	}
	
    /**
	 * 查询方法
	 * @param sql
	 * @param params
	 */
	public List<T> executeQuery(String sql,PageBean pageBean,CallBack<T> callBack){
		Connection conn=null;
		PreparedStatement stmt=null;
		ResultSet rs=null;
		try {
			conn=DBHelper.getConnection();
			//判断是否分页
			if(null!=pageBean&&pageBean.isPagination()) {
				//第一次查询返回总记录数
				String countSql=this.getCountSql(sql);
				stmt=conn.prepareStatement(countSql);
				rs=stmt.executeQuery();
				if(rs.next()) {
					Object obj=rs.getObject(1);
					pageBean.setTotal(Integer.parseInt(obj.toString()));
				}
				//第二次查询返回指定页码数并满足条件的分页结果集
				sql=this.getPagerSql(sql, pageBean);
			}
			stmt=conn.prepareStatement(sql);
			rs=stmt.executeQuery();
			//遍历结果集
			return callBack.forEach(rs);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			DBHelper.close(conn, stmt, rs);
		}
		return null;
	}
	/**
	 * 将普通SQL语句转换成查询总记录数的SQL语句
	 * @param sql
	 * @return
	 */
	private String getCountSql(String sql) {
		return "select count(1) from ("+sql+") t1";
	}
	/**
	 * 将普通SQL语句转换成查询分页记录集的SQL语句
	 * @param sql
	 * @param pageBean
	 * @return
	 */
	private String getPagerSql(String sql,PageBean pageBean) {
		return sql+" Limit "+pageBean.getStartIndex()+","+pageBean.getRows();
	}
}

4、数据操作类 RegionDao.java 继承BaseDao.java

package com.zking.jquery.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import com.zking.jquery.entity.Region;
import com.zking.jquery.util.CommonUtils;
import com.zking.jquery.util.StringUtils;

public class RegionDao extends BaseDao<Region>{
	//查询
	public List<Region> queryRegionLst(Region region){
		String sql="select id,region_id,parent_id,region_parent_id,"
				+ "region_name,region_type from t_region where 1=1";
	    //判断不为空
		if(StringUtils.isNotBlank(region.getParent_id()+""))
			sql+=" and parent_id="+region.getParent_id();
		
		return super.executeQuery(sql, null, new CallBack<Region>() {
			@Override
			public List<Region> forEach(ResultSet rs) throws SQLException {
				try {
					return CommonUtils.toList(rs, Region.class);
				} catch (Exception e) {
					e.printStackTrace();
				}
				return null;
			}
		});
	}
}

5、 工具类 CommonUtils.java

package com.zking.jquery.util;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CommonUtils {
	/**
	 * 根据ResultSet数据集，利用反射机制动态赋值并返回List<T>
	 * @param rs ResultSet数据集
	 * @param clazz 实体类对象
	 * @return 返回List实体集合
	 * @throws Exception
	 */
	public static <T> List<T> toList(ResultSet rs,Class<T> clazz) throws Exception{
		//定义实体集合
		List<T> lst=new ArrayList<T>();
		//获取ResultSet的metadata列信息
		ResultSetMetaData metaData = rs.getMetaData();
		//获取对象属性集合
		Field[] fields=clazz.getDeclaredFields();
		//循环ResultSet
		while(rs.next()) {
			//反射机制实例化
			T obj = clazz.newInstance();
			for (int i = 0; i < metaData.getColumnCount(); i++) {
				//获取列名
				String columnName=metaData.getColumnLabel(i+1).toUpperCase();
				for (Field field : fields) {
					//判断属性名与列名是否相同
					if(field.getName().toUpperCase().equals(columnName)) {
						//获取属性对应的set方法名，方法名首字母大写
						String methodName="set"+field.getName().substring(0, 1).toUpperCase()+field.getName().substring(1);
						//获取属性对应的set方法
						Method method = obj.getClass().getDeclaredMethod(methodName, field.getType());
						//设置访问权限
						method.setAccessible(true);
						//执行set方法，将数据存储到对象中的相应属性中
						method.invoke(obj, rs.getObject(columnName));
						break;
					}
				}
			}
			lst.add(obj);
		}
		return lst;
	}
	
	/**
	 * 多表查询时返回结果集,利用反射机制赋值 
	 * @param rs
	 * @return 返回List<Map<String,Object>>
	 * @throws Exception
	 */
	public static List<Map<String,Object>> toList(ResultSet rs) throws Exception{
		//定义实体集合
		List<Map<String,Object>> lst=new ArrayList<Map<String,Object>>();
		//获取ResultSet的metadata列信息	
		ResultSetMetaData metaData = rs.getMetaData();
		Map<String,Object> set=null;
		while(rs.next()) {
			set=new HashMap<String,Object>();
			for (int i = 0; i < metaData.getColumnCount(); i++) {
				String columnName=metaData.getColumnLabel(i+1);
				set.put(columnName, rs.getObject(columnName));
			}
			lst.add(set);
		}
		return lst;
	}
}

---

6、测试Dao类是否有误        RegionDaoTest.java

package com.zking.jquery.dao;

import static org.junit.jupiter.api.Assertions.*;
import java.util.List;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import com.zking.jquery.entity.Region;

class RegionDaoTest {
	//实例化一个dao类对象
	private RegionDao regionDao = new RegionDao();
    
	private Region region;

	@BeforeEach
	void setUp() throws Exception {
		region=new Region();
	}

	@Test
	void testQueryRegionLst() {
        //给父级编号赋值
		region.setParent_id(7459);
        //调用Dao类查询方法
		List<Region> lst=regionDao.queryRegionLst(region);
		for (Region reg: lst) {
			System.out.print(reg);
		}
	}
}

测试结果没有报错，可以继续下一步

---

 7、RegionAction.java

package com.zking.jquery.action;

import java.io.IOException;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.zking.jquery.dao.RegionDao;
import com.zking.jquery.entity.Region;
import com.zking.mvc.framework.Action;
import com.zking.mvc.framework.DriverModel;

public class RegionAction extends Action implements DriverModel<Region> {
	
	private RegionDao regionDao=new RegionDao();
	
	private Region region=new Region();
	
	private ObjectMapper mapper=new ObjectMapper();
	
	@Override
	public Region getModel() {
		return region;
	}

	@Override
	public String execute(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException {
		List<Region> lst = regionDao.queryRegionLst(region);
		mapper.writeValue(res.getOutputStream(), lst);
		return null;
	}
}

---

1、创建 address.jsp 页面。先在 js 中引入 jquery.min.js 。

 将 jquery.min.js 引入到 address.jsp 中

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>省 市 县之三级联动</title>
<script type="text/javascript" src="${pageContext.request.contextPath }/js/jquery.min.js"></script>
<script type="text/javascript">
$(function(){
	//初始化查询
	searchProvince(7459);
	//select的change事件
	$('#province').change(function(){
		//searchProvince($(this).val());
        //this:当前省份的select
		var pid=$(this).val();
		if(pid=="")
			return false;
		
		$.ajax({
			//请求路径
			url:'regionAction.action',
			//请求参数
			data:{'parent_id':pid},
			//提交方式
			type:'post',
			//数据类型
			dataType:'json',
			//是否同步
			async:false,
			//请求成功之后的回调方法
			success:function(data){
				//console.log(data);
				//清空选项，只保留第一个选项
				$('#city option:not(:first)').remove();
				//循环
				$.each(data,function(idx,elem){
					var region=elem;
					//绑定元素
					$('#city').append('<option value="'+region.id+'">'+region.region_name+'</option>');
					console.log(elem);
				});
			}
		});
	});
	
	$('#city').change(function(){
		//searchProvince($(this).val());
		var pid=$(this).val();
		if(pid=="")
			return false;
		
		$.ajax({
			//请求路径
			url:'regionAction.action',
			//请求参数
			data:{'parent_id':pid},
			//提交方式
			type:'post',
			//数据类型
			dataType:'json',
			//是否同步
			async:false,
			//请求成功之后的回调方法
			success:function(data){
				//console.log(data);
				//清空选项，只保留第一个选项
				$('#county option:not(:first)').remove();
				//循环
				$.each(data,function(idx,elem){
					var region=elem;
					//绑定元素
					$('#county').append('<option value="'+region.id+'">'+region.region_name+'</option>');
					console.log(elem);
				});
			}
		});
	});
	
});

function searchProvince(pid){
	//var pid=$(this).val();
	if(pid=="")
		return false;
	
	$.ajax({
		//请求路径
		url:'regionAction.action',
		//请求参数
		data:{'parent_id':pid},
		//提交方式
		type:'post',
		//数据类型
		dataType:'json',
		//是否同步
		async:false,
		//请求成功之后的回调方法
		success:function(data){
			//console.log(data);
			//清空选项，只保留第一个选项
			$('#province option:not(:first)').remove();
			//循环
			$.each(data,function(idx,elem){
				var region=elem;
				//绑定元素
				$('#province').append('<option value="'+region.id+'">'+region.region_name+'</option>');
				console.log(elem);
			});
		}
	});
}
</script>

</head>
<body>
	<h2>省市县三级联动 lxy</h2>
	<select id="province" style="border-radius: 4px;font-size:16px;font-family:宋体;background-color:#99FFFF">
		<option value="">-----请选择-----</option>
	</select>
	&nbsp;
	<select id="city" style="border-radius: 4px;font-size:16px;font-family:宋体;background-color:#99FF99">
		<option value="">-----请选择-----</option>
	</select>
	&nbsp;
	<select id="county" style="border-radius: 4px;font-size:16px;font-family:宋体;background-color:#FFCCCC">
		<option value="">-----请选择-----</option>
	</select>

</body>
</html>

打印结果：

---

2、在 web.xml 中配置过滤器

<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://java.sun.com/xml/ns/javaee" xsi:schemaLocation="http://java.sun.com/xml/ns/javaee http://java.sun.com/xml/ns/javaee/web-app_3_0.xsd" id="WebApp_ID" version="3.0">
  <display-name>Jquery_address</display-name>
  <welcome-file-list>
    <welcome-file>index.html</welcome-file>
    <welcome-file>index.htm</welcome-file>
    <welcome-file>index.jsp</welcome-file>
    <welcome-file>default.html</welcome-file>
    <welcome-file>default.htm</welcome-file>
    <welcome-file>default.jsp</welcome-file>
  </welcome-file-list>
  
  <filter>
  	<filter-name>FilterEncoding</filter-name>
  	<filter-class>com.zking.jquery.util.EncodingFilter</filter-class>
  </filter>
  <filter-mapping>
  	<filter-name>FilterEncoding</filter-name>
  	<url-pattern>/*</url-pattern>
  </filter-mapping>
  
  <servlet>
  	<servlet-name>ActionServlet</servlet-name>
  	<servlet-class>com.zking.mvc.framework.ActionServlet</servlet-class>
  	<init-param>
  		<param-name>config</param-name>
  		<param-value>/mvc.xml</param-value>
  	</init-param>
  </servlet>
  <servlet-mapping>
  	<servlet-name>ActionServlet</servlet-name>
  	<url-pattern>*.action</url-pattern>
  </servlet-mapping>
  
</web-app>

3、将 mvc.xml 导入到 src 根目录下  配置RegionAction

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE config[
	<!ELEMENT config (action*)>
	<!ELEMENT action (forward*)>
	<!ELEMENT forward EMPTY>
	<!ATTLIST action 
		path CDATA #REQUIRED
		type CDATA #REQUIRED
	>
	<!ATTLIST forward
		name CDATA #REQUIRED
		path CDATA #REQUIRED
		redirect (true|false) "false"
	>
]>
	<!-- config标签:可以包含0~N个action标签 -->

<config>
	<action type="com.zking.jquery.action.RegionAction" path="/regionAction"></action>
</config>

---

                                            最终效果展示(可自行美化)