大纲
一、导入省市县数据表(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();}@Overridepublic 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>() {@Overridepublic 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();//循环ResultSetwhile(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;@BeforeEachvoid setUp() throws Exception {region=new Region();}@Testvoid 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();@Overridepublic Region getModel() {return region;}@Overridepublic 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:当前省份的selectvar 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> <select id="city" style="border-radius: 4px;font-size:16px;font-family:宋体;background-color:#99FF99"><option value="">-----请选择-----</option></select> <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 #REQUIREDtype CDATA #REQUIRED><!ATTLIST forwardname CDATA #REQUIREDpath CDATA #REQUIREDredirect (true|false) "false">
]><!-- config标签:可以包含0~N个action标签 --><config><action type="com.zking.jquery.action.RegionAction" path="/regionAction"></action>
</config>
最终效果展示(可自行美化)