jsp省市区三级联动

1 篇文章 0 订阅
1 篇文章 0 订阅

1.添加使用.jar
c3p0-0.9.5.5.jar
commons-dbutils-1.7.jar
fastjson-1.2.62.jar
jstl-1.2.jar
mchange-commons-java-0.2.19.jar
mysql-connector-java-8.0.17.jar
2.创建数据库:

CREATE TABLE `provice`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '省份id',
  `code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '省份代号',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '省份名',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `city`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '城市ID',
  `code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `provice_code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

CREATE TABLE `area`  (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '区或县ID',
  `code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区或县代号',
  `city_code` varchar(6) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '城市代号',
  `name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '区或县名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 14 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

3.实现实体类和逻辑层

package com.daya.daomain;

import java.io.Serializable;

public class Area implements Serializable {

	private Integer id;
	private String code;
	private String cityCode;
	private String name;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getCityCode() {
		return cityCode;
	}
	public void setCityCode(String cityCode) {
		this.cityCode = cityCode;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	
	
	
}




package com.daya.daomain;

import java.io.Serializable;

public class City implements Serializable {
	
	private Integer id;
	private String code;
	private String name;
	
	private String proviceCode;

	public Integer getId() {
		return id;
	}

	public void setId(Integer id) {
		this.id = id;
	}

	public String getCode() {
		return code;
	}

	public void setCode(String code) {
		this.code = code;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public String getProviceCode() {
		return proviceCode;
	}

	public void setProviceCode(String proviceCode) {
		this.proviceCode = proviceCode;
	}
	
	
	
	
}


package com.daya.daomain;

import java.io.Serializable;

public class Provice implements Serializable {

	private Integer id;
	private String code;
	private String name;
	public Integer getId() {
		return id;
	}
	public void setId(Integer id) {
		this.id = id;
	}
	public String getCode() {
		return code;
	}
	public void setCode(String code) {
		this.code = code;
	}
	public String getName() {
		return name;
	}
	public void setName(String name) {
		this.name = name;
	}
	
	
	
}

package com.daya.dao;

import java.sql.SQLException;
import java.util.List;

import com.daya.daomain.Area;
import com.daya.daomain.City;
import com.daya.daomain.Provice;

public interface AreaDao {

	/***
	 * 查询所有的省份
	 * @return
	 */
	public List<Provice> selectAllProvice()throws SQLException;
	
	
	/***
	 * 根据省份查询地级市
	 * @param proviceCode
	 * @return
	 */
	public List<City> selectCityBy(String proviceCode)throws SQLException;
	
	
	/***
	 * 根据地级市查询区域
	 * @param ciyCode
	 * @return
	 */
	public List<Area> selectAreaBy(String ciyCode)throws SQLException;
	
	
}



package com.daya.dao.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.daya.dao.AreaDao;
import com.daya.daomain.Area;
import com.daya.daomain.City;
import com.daya.daomain.Provice;
import com.daya.util.C3p0Utils;

public class AreaDaoImpl implements AreaDao {

	@Override
	public List<Provice> selectAllProvice() throws SQLException {
		// 
		QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select id,name,code from provice ";
		Connection mConnection = C3p0Utils.getConnection();
		List<Provice> provices = qr.query(mConnection, sql,new BeanListHandler<Provice>(Provice.class));
		return provices;
	}

	@Override
	public List<City> selectCityBy(String proviceCode) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select id,name,code from city where provice_code = ?";
		Object[] params = {proviceCode};
		Connection mConnection = C3p0Utils.getConnection();
		List<City> city = qr.query(mConnection, sql,new BeanListHandler<City>(City.class),params);
		return city;
	}

	@Override
	public List<Area> selectAreaBy(String ciyCode) throws SQLException {
		// TODO Auto-generated method stub
		QueryRunner qr = new QueryRunner(C3p0Utils.getDataSource());
		String sql = "select id,name,code from area where city_code = ? order by id";
		Object[] params = {ciyCode};
		Connection mConnection = C3p0Utils.getConnection();
		List<Area> area = qr.query(mConnection, sql,new BeanListHandler<Area>(Area.class),params);
		return area;
	}

}



package com.daya.service;

import java.sql.SQLException;
import java.util.List;

import com.daya.daomain.Area;
import com.daya.daomain.City;
import com.daya.daomain.Provice;

public interface AreaService {

	/***
	 * 查询所有的省份
	 * @return
	 * @throws SQLException
	 */
	public List<Provice> searchAllProvice()throws SQLException;
	
	/***
	 * 查询地级市
	 * @param proviceCode
	 * @return
	 * @throws SQLException
	 */
	public List<City> searchCityBy(String proviceCode) throws SQLException;
	
	/***
	 * 根据市查询区
	 * @param cityCode
	 * @return
	 * @throws SQLException
	 */
	public List<Area> searchAreaBy(String cityCode) throws SQLException;
}


package com.daya.service.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.daya.dao.AreaDao;
import com.daya.dao.impl.AreaDaoImpl;
import com.daya.daomain.Area;
import com.daya.daomain.City;
import com.daya.daomain.Provice;
import com.daya.service.AreaService;
import com.daya.util.C3p0Utils;

public class AreaServiceImpl implements AreaService{

	private AreaDao mAreaDao = new AreaDaoImpl();
	
	@Override
	public List<Provice> searchAllProvice() throws SQLException {
		// TODO Auto-generated method stub
		List<Provice> products =  null;
		products = mAreaDao.selectAllProvice();
		return products;
	} 

	@Override
	public List<City> searchCityBy(String proviceCode) throws SQLException {
		// TODO Auto-generated method stub
		List<City> cities =  null;
		cities = mAreaDao.selectCityBy(proviceCode);
		return cities;
	
	}

	@Override
	public List<Area> searchAreaBy(String cityCode) throws SQLException {
		// TODO Auto-generated method stub
		List<Area> area =  null;
		area = mAreaDao.selectAreaBy(cityCode);
		return area;
	}

}

工具类

package com.daya.util;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.sql.DataSource;

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3p0Utils {

	/***
	 * 创建连接对象
	 */
	private static ComboPooledDataSource dataSource = new ComboPooledDataSource();
	
	private static ThreadLocal<Connection> threadLocal = new ThreadLocal<Connection>();
	
	public static DataSource getDataSource()
	{
		return dataSource;
	}
	
	public static Connection getConnection() throws SQLException
	{
		Connection connection = threadLocal.get();
		if(connection == null)
		{
			//如果是第一次调用,则本地线程池中没有现成的连接对象
			//则从连接池中获取一个新的连接对象
			connection = dataSource.getConnection();
			threadLocal.set(connection);
		}
		return connection;
	}
	
	public static void close(ResultSet rs,Statement statement,Connection connection)
	{
		if(rs!= null)
		{
			try {
				
				rs.close();
			}catch(SQLException e)
			{
				e.printStackTrace();
			}
		}
		
		if(statement != null)
		{
			try {
				statement.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		if(connection != null)
		{
			try {
				connection.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
	}
	
	
}

数据库配置
c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
<c3p0-config>
	<!-- 默认配置,如果没有指定,则使用这个配置 -->
	<default-config>
		<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///goods?serverTimezone=UTC</property>
		<property name="user">root</property>
		<property name="password">root</property>
		<property name="checkoutTimeout">1200</property>
		<property name="idelConnectionTestPeriod">30</property>
		<property name="initialPoolSize">10</property>
		<property name="maxIdelTime">30</property>
		<property name="maxPoolSize">20</property>
		<property name="maxStatements">0</property>
		<property name="acquireRetryDelay">500</property>
		<user-overrides user="test-user">
			<property name="maxPoolSize">10</property>
			<property name="minPoolSize">1</property>
			<property name="maxStatements">0</property>
		</user-overrides>
	</default-config>
	
	<!-- 命名配置 -->
	<name0config name="goods">
		<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
		<property name="jdbcUrl">jdbc:mysql:///goods?serverTimezone=UTC</property>
		<property name="user">root</property>
		<property name="password">root</property>
		<property name="acquireIncrement">5</property>
		<property name="acquireRetryDelay">500</property>
		<property name="initialPoolSize">10</property>
		<property name="minPoolSize">5</property>
		<property name="maxPoolSize">20</property>
		<property name="maxStatements">0</property>
		<property name="maxStatementsPerConnection">5</property>
	</name0config>
</c3p0-config>

访问接口

package com.daya.web;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSONArray;
import com.daya.daomain.Area;
import com.daya.service.AreaService;
import com.daya.service.impl.AreaServiceImpl;

public class AreaAjaxServlet extends HttpServlet {

	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		AreaService mAreaService = new AreaServiceImpl();
		String mCityCode = req.getParameter("cityCode");
		try {
			List<Area> mAreaList =  mAreaService.searchAreaBy(mCityCode);
			String mCityString = JSONArray.toJSONString(mAreaList);
			resp.setContentType("text/html;charset=UTF-8");
			PrintWriter mPrintWiter = resp.getWriter();
			mPrintWiter.append(mCityString);
			mPrintWiter.flush();
			mPrintWiter.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet( req, resp);
	}

	
	
	
	
}

package com.daya.web;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSONArray;
import com.daya.daomain.City;
import com.daya.service.AreaService;
import com.daya.service.impl.AreaServiceImpl;

public class CityAjaxServlet extends HttpServlet {

	
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		AreaService mAreaService = new AreaServiceImpl();
		String mPriviceCode = req.getParameter("proviceCode");
		try {
			List<City> mCityList =  mAreaService.searchCityBy(mPriviceCode);
			String mJSONString = JSONArray.toJSONString(mCityList);
			resp.setContentType("text/html;charset=UTF-8");
			PrintWriter mPrintWriter = resp.getWriter();
			mPrintWriter.append(mJSONString);
			mPrintWriter.flush();
			mPrintWriter.close();
	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(req, resp);
	}

}

package com.daya.web;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.alibaba.fastjson.JSONArray;
import com.daya.daomain.Provice;
import com.daya.service.AreaService;
import com.daya.service.impl.AreaServiceImpl;

public class ProviceAjaxServlet extends HttpServlet {

	private AreaService mAreaService = new AreaServiceImpl();
	
	@Override
	protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		try {
			List<Provice> mProvices =  mAreaService.searchAllProvice();
			resp.setContentType("text/html;charset=UTF-8");
			PrintWriter mPrintWriter = resp.getWriter();
			String mJsonString =  JSONArray.toJSONString(mProvices);
			mPrintWriter.print(mJsonString);
			mPrintWriter.flush();
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Override
	protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		// TODO Auto-generated method stub
		doGet(req,resp);
	}

	
	
	
}

web.xml 中配置

 <servlet>
  	<servlet-name>ProviceAjaxServlet</servlet-name>
  	<servlet-class>com.daya.web.ProviceAjaxServlet</servlet-class>
  	<load-on-startup>1</load-on-startup>
  </servlet>
  <servlet-mapping>
  	<servlet-name>ProviceAjaxServlet</servlet-name>
  	<url-pattern>/provice</url-pattern>
  </servlet-mapping>
  
  <servlet>
  	<servlet-name>CityAjaxServlet</servlet-name>
  	<servlet-class>com.daya.web.CityAjaxServlet</servlet-class>
  </servlet>
  <servlet-mapping>
  	<servlet-name>CityAjaxServlet</servlet-name>
  	<url-pattern>/city</url-pattern>
  </servlet-mapping>

	<servlet>
		<servlet-name>AreaAjaxServlet</servlet-name>
		<servlet-class>com.daya.web.AreaAjaxServlet</servlet-class>
	</servlet>
	<servlet-mapping>
		<servlet-name>AreaAjaxServlet</servlet-name>
		<url-pattern>/area</url-pattern>
	</servlet-mapping>
  

jsp页面中的jquery 实现

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<c:set var="contextPath" value="${pageContext.servletContext.contextPath }"></c:set>    
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>省市区三级联动</title>
<script type="text/javascript" src="${contextPath }/js/jquery-3.4.1.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
	//加载省份数据
		 $.post(
		    		"${contextPath}/provice",
		    		function(data){
		    			$("#provice").empty()
		    			var options = "<option value='none'>--请选择省--</option>";
		    			for(var i=0;i<data.length;i++)
						{
							var proviceItem = data[i];						
							options += "<option value='" + proviceItem.code +"'>" + proviceItem.name + "</option>";					
						}
		    			 $("#provice").append(options);
		    		},
		    		"json"	    				
		    );
	
	//监听省份的选项改变事件
		$("#provice").change(function(){
			//将城市下拉框中的实际选项给清除
			$("#city option:gt(0)").remove();
			 $.post(
			    		"${contextPath}/city",
			    		"proviceCode="+ $(this).val(),
			    		function(data){
			    			$("#city").empty()
			    			var options = "<option value='none'>--请选择市--</option>";
			    			for(var i=0;i<data.length;i++)
							{
								var cityItem = data[i];						
								options += "<option value='" + cityItem.code +"'>" + cityItem.name + "</option>";					
							}
			    			 $("#city").append(options);
			    		},
			    		"json"	    				
			    );
		});
	
		$("#city").change(function(){
			
			$("#area option:gt(0)").remove();
			$.post(
				"${contextPath}/area",
				"cityCode="+ $(this).val(),
				function(data){
					$("#area").empty()
	    			var options = "<option value='none'>--请选择县或区--</option>";
	    			for(var i=0;i<data.length;i++)
					{
						var areaItem = data[i];						
						options += "<option value='" + areaItem.code +"'>" + areaItem.name + "</option>";					
					}
	    			 $("#area").append(options);
				},
				"json"
			);
			
		});
	
	});

		
</script>

</head>
<body>

<h3>省市区三级联动</h3>
<select style="width: 100px;"  id="provice" name="provice">
	<option value="none">--请选择省--</option>
</select>
<select style="width: 100px;" id="city" name="city">
	<option value="none">--请选择市--</option>
</select>
<select style="width: 120px;" id="area" name="area">
	<option value="none">--请选择县或区--</option>
</select>

<br/>

<br/>
<br/>

</body>
</html>

注意需要 js文件请在webConent文件夹下创建js文件夹将js文件放在里面

  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值