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文件放在里面