社区微信群开通啦，扫一扫抢先加入社区官方微信群
社区微信群
社区微信群开通啦，扫一扫抢先加入社区官方微信群
社区微信群
DropdownDAO.java —–Dao层
package com.imooc.dao;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import DataBaseHelp.DBHelp;
public class DropdownDAO {
//获得省
@SuppressWarnings("rawtypes")
public static ArrayList<Map> getprovince() throws SQLException {
Map<String, String> map = new HashMap<String, String>();
ArrayList<Map> rsList = new ArrayList<Map>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from province;" ;
try {
conn = DBHelp.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
map = new HashMap<String, String>();
map.put("code", rs.getString("code_p"));
map.put("name",rs.getString("name"));
rsList.add(map);
}
return rsList;
} catch (Exception e) {
System.out.println("----获得省的方法出现异常:" + e);
e.printStackTrace();
return null;
} finally {
}
}
//根据省编号获得市
@SuppressWarnings("rawtypes")
public static ArrayList<Map> getcity(String code_p) throws SQLException {
Map<String, String> map = new HashMap<String, String>();
ArrayList<Map> rsList = new ArrayList<Map>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from city where code_p="+code_p+";" ;
try {
conn = DBHelp.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
map = new HashMap<String, String>();
map.put("code", rs.getString("code_c"));
map.put("name",rs.getString("name"));
rsList.add(map);
}
return rsList;
} catch (Exception e) {
System.out.println("----获得市的方法出现异常:" + e);
e.printStackTrace();
return null;
} finally {
}
}
//根据市编号获得区
@SuppressWarnings("rawtypes")
public static ArrayList<Map> getarea(String code_c) throws SQLException {
Map<String, String> map = new HashMap<String, String>();
ArrayList<Map> rsList = new ArrayList<Map>();
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
String sql = "select * from area where code_c="+code_c+";" ;
try {
conn = DBHelp.getConnection();
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
while (rs.next()) {
map = new HashMap<String, String>();
map.put("code", rs.getString("code_a"));
map.put("name",rs.getString("name"));
rsList.add(map);
}
return rsList;
} catch (Exception e) {
System.out.println("----获得区的方法出现异常:" + e);
e.printStackTrace();
return null;
} finally {
}
}
}
DBHelp.java—-数据库连接层
package DataBaseHelp;
import java.sql.Connection;
import java.sql.DriverManager;
public class DBHelp {
private static final String driver = "com.mysql.jdbc.Driver"; //数据库驱动
//连接数据库的URL地址
private static final String url="jdbc:mysql://localhost:3306/region?useUnicode=true&characterEncoding=UTF-8";
private static final String username="root";//数据库的用户名
private static final String password="LYY1996*";//数据库的密码
private static Connection conn=null;
//静态代码块负责加载驱动
static
{
try
{
Class.forName(driver);
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
//单例模式返回数据库连接对象
public static Connection getConnection() throws Exception
{
if(conn==null)
{
conn = DriverManager.getConnection(url, username, password);
return conn;
}
return conn;
}
public static void main(String[] args) {
try
{
Connection conn = DBHelp.getConnection();
if(conn!=null)
{
System.out.println("数据库连接正常！");
}
else
{
System.out.println("数据库连接异常！");
}
}
catch(Exception ex)
{
ex.printStackTrace();
}
}
}
Dropdown.java——-Servlet层
package DropdownServlet;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.google.gson.Gson;
import com.imooc.dao.DropdownDAO;
public class Dropdown extends HttpServlet{
/**
*
*/
private static final long serialVersionUID = 1L;
public void doGet(HttpServletRequest request, HttpServletResponse response)throws IOException,ServletException{
doPost(request,response);
}
public void doPost(HttpServletRequest request, HttpServletResponse response) throws IOException,ServletException{
String code = request.getParameter("code");
String grade = request.getParameter("grade");
System.out.println("grade="+grade);
System.out.println("code="+code);
String json = "";
ArrayList<?> rsList = new ArrayList<Object>();
Gson gson = new Gson();
try {
if(grade.equals("province"))
rsList=DropdownDAO.getprovince();
else if(grade.equals("city"))
rsList=DropdownDAO.getcity(code);
else if(grade.equals("area"))
rsList=DropdownDAO.getarea(code);
json = gson.toJson(rsList);
System.out.println("json="+json);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
response.setCharacterEncoding("UTF-8");//防止出现中文乱码现象
response.getWriter().print(json);
}
}
为Servlet配置web.xml
<?xml version="1.0" encoding="UTF-8"?>
<web-app version="2.5"
xmlns="http://java.sun.com/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/javaee
http://java.sun.com/xml/ns/javaee/web-app_2_5.xsd">
<servlet>
<servlet-name>Dropdown</servlet-name>
<servlet-class>DropdownServlet.Dropdown</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>Dropdown</servlet-name>
<url-pattern>/DropdownServlet/Dropdown</url-pattern>
</servlet-mapping>
<welcome-file-list>
<welcome-file>index.jsp</welcome-file>
</welcome-file-list>
</web-app>
index.jsp —–JSP页面、视图层
<%@ page language="java" import="java.util.*" pageEncoding="UTF-8"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<base href="<%=basePath%>">
<title>省市区三级联动下拉框 |Three - Three Linkage Drop-Down Box </title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyword3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.css">
-->
<script type="text/javascript" src="<%=path %>/js/jquery/jquery-1.7.min.js"></script>
<script type="text/javascript" src="<%=path %>/js/json/json-minified.js"></script>
</head>
<body>
<select name="province" id="province" onchange="onSelectChange(this,'city');" style="width:100px" ></select>
<select name="city" id="city" onchange="onSelectChange(this,'area');" style="width:100px" ></select>
<select name="area" id="area" style="width:100px"></select>
</body>
<script type="text/javascript">
function onSelectChange(obj,toSelId){
//alert(obj.value);
setSelect(obj.value,toSelId);
}
function setSelect(fromSelVal,toSelId){
//alert(fromSelVal);
//alert(toSelId);
document.getElementById(toSelId).innerHTML="";
jQuery.ajax({
url: "<%=path%>/DropdownServlet/Dropdown",
cache: false,
data:{"code":fromSelVal,"grade":toSelId},
success: function(data){
createSelectObj(data,toSelId);
}
});
}
function createSelectObj(data,toSelId){
var arr = jsonParse(data);
if(arr != null && arr.length>0){
var obj = document.getElementById(toSelId);
obj.innerHTML="";
var nullOp = document.createElement("option");
nullOp.setAttribute("value","");
obj.appendChild(nullOp);
for(var o in arr){
var op = document.createElement("option");
op.setAttribute("value",arr[o].code);
//op.text=arr[o].name;//这一句在ie下不起作用，用下面这一句或者innerHTML
op.appendChild(document.createTextNode(arr[o].name));
obj.appendChild(op);
}
}
}
setSelect('110000','province');
</script>
</html>/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50720
Source Host : localhost:3306
Source Database : region
Target Server Type : MYSQL
Target Server Version : 50720
File Encoding : 65001
Date: 2017-11-30 23:03:33
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for area
-- ----------------------------
DROP TABLE IF EXISTS `area`;
CREATE TABLE `area` (
`id` varchar(15) NOT NULL,
`code_a` varchar(15) NOT NULL,
`name` varchar(20) NOT NULL,
`code_c` varchar(15) DEFAULT NULL,
PRIMARY KEY (`code_a`),
KEY `FK_Reference_9` (`code_c`),
CONSTRAINT `FK_Reference_9` FOREIGN KEY (`code_c`) REFERENCES `city` (`code_c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='该实体为行政区域划分等级';
-- ----------------------------
-- Records of area
-- ----------------------------
INSERT INTO `area` VALUES ('1', '110101', '东城区', '110100');
INSERT INTO `area` VALUES ('2', '110102', '西城区', '110100');
INSERT INTO `area` VALUES ('3', '110105', '朝阳区', '110100');
INSERT INTO `area` VALUES ('4', '110106', '丰台区', '110100');
INSERT INTO `area` VALUES ('5', '110107', '石景山区', '110100');
INSERT INTO `area` VALUES ('6', '110108', '海淀区', '110100');
INSERT INTO `area` VALUES ('7', '110109', '门头沟区', '110100');
INSERT INTO `area` VALUES ('8', '110111', '房山区', '110100');
INSERT INTO `area` VALUES ('9', '110112', '通州区', '110100');
INSERT INTO `area` VALUES ('10', '110113', '顺义区', '110100');
INSERT INTO `area` VALUES ('11', '110114', '昌平区', '110100');
INSERT INTO `area` VALUES ('12', '110115', '大兴区', '110100');
INSERT INTO `area` VALUES ('13', '110116', '怀柔区', '110100');
INSERT INTO `area` VALUES ('14', '110117', '平谷区', '110100');
INSERT INTO `area` VALUES ('15', '110118', '密云区', '110100');
INSERT INTO `area` VALUES ('16', '110119', '延庆区', '110100');
INSERT INTO `area` VALUES ('17', '120101', '和平区', '120100');
INSERT INTO `area` VALUES ('18', '120102', '河东区', '120100');
INSERT INTO `area` VALUES ('19', '120103', '河西区', '120100');
INSERT INTO `area` VALUES ('20', '120104', '南开区', '120100');
INSERT INTO `area` VALUES ('21', '120105', '河北区', '120100');
INSERT INTO `area` VALUES ('22', '120106', '红桥区', '120100');
INSERT INTO `area` VALUES ('23', '120110', '东丽区', '120100');
INSERT INTO `area` VALUES ('24', '120111', '西青区', '120100');
INSERT INTO `area` VALUES ('25', '120112', '津南区', '120100');
INSERT INTO `area` VALUES ('26', '120113', '北辰区', '120100');
INSERT INTO `area` VALUES ('27', '120114', '武清区', '120100');
INSERT INTO `area` VALUES ('28', '120115', '宝坻区', '120100');
INSERT INTO `area` VALUES ('29', '120116', '滨海新区', '120100');
INSERT INTO `area` VALUES ('30', '120117', '宁河区', '120100');
INSERT INTO `area` VALUES ('31', '120118', '静海区', '120100');
INSERT INTO `area` VALUES ('32', '120119', '蓟州区', '120100');
INSERT INTO `area` VALUES ('33', '130101', '市辖区', '130100');
INSERT INTO `area` VALUES ('34', '130102', '长安区', '130100');
INSERT INTO `area` VALUES ('35', '130104', '桥西区', '130100');
INSERT INTO `area` VALUES ('36', '130105', '新华区', '130100');
INSERT INTO `area` VALUES ('37', '130107', '井陉矿区', '130100');
INSERT INTO `area` VALUES ('38', '130108', '裕华区', '130100');
INSERT INTO `area` VALUES ('39', '130109', '藁城区', '130100');
INSERT INTO `area` VALUES ('40', '130110', '鹿泉区', '130100');
INSERT INTO `area` VALUES ('41', '130111', '栾城区', '130100');
INSERT INTO `area` VALUES ('42', '130121', '井陉县', '130100');
INSERT INTO `area` VALUES ('43', '130123', '正定县', '130100');
INSERT INTO `area` VALUES ('44', '130125', '行唐县', '130100');
INSERT INTO `area` VALUES ('45', '130126', '灵寿县', '130100');
INSERT INTO `area` VALUES ('46', '130127', '高邑县', '130100');
INSERT INTO `area` VALUES ('47', '130128', '深泽县', '130100');
INSERT INTO `area` VALUES ('48', '130129', '赞皇县', '130100');
INSERT INTO `area` VALUES ('49', '130130', '无极县', '130100');
INSERT INTO `area` VALUES ('50', '130131', '平山县', '130100');
INSERT INTO `area` VALUES ('51', '130132', '元氏县', '130100');
INSERT INTO `area` VALUES ('52', '130133', '赵县', '130100');
INSERT INTO `area` VALUES ('53', '130183', '晋州市', '130100');
INSERT INTO `area` VALUES ('54', '130184', '新乐市', '130100');
INSERT INTO `area` VALUES ('55', '130201', '市辖区', '130200');
INSERT INTO `area` VALUES ('56', '130202', '路南区', '130200');
INSERT INTO `area` VALUES ('57', '130203', '路北区', '130200');
INSERT INTO `area` VALUES ('58', '130204', '古冶区', '130200');
INSERT INTO `area` VALUES ('59', '130205', '开平区', '130200');
INSERT INTO `area` VALUES ('60', '130207', '丰南区', '130200');
INSERT INTO `area` VALUES ('61', '130208', '丰润区', '130200');
INSERT INTO `area` VALUES ('62', '130209', '曹妃甸区', '130200');
INSERT INTO `area` VALUES ('63', '130223', '滦县', '130200');
INSERT INTO `area` VALUES ('64', '130224', '滦南县', '130200');
INSERT INTO `area` VALUES ('65', '130225', '乐亭县', '130200');
INSERT INTO `area` VALUES ('66', '130227', '迁西县', '130200');
INSERT INTO `area` VALUES ('67', '130229', '玉田县', '130200');
INSERT INTO `area` VALUES ('68', '130281', '遵化市', '130200');
INSERT INTO `area` VALUES ('69', '130283', '迁安市', '130200');
INSERT INTO `area` VALUES ('70', '130301', '市辖区', '130300');
INSERT INTO `area` VALUES ('71', '130302', '海港区', '130300');
INSERT INTO `area` VALUES ('72', '130303', '山海关区', '130300');
INSERT INTO `area` VALUES ('73', '130304', '北戴河区', '130300');
INSERT INTO `area` VALUES ('74', '130306', '抚宁区', '130300');
INSERT INTO `area` VALUES ('75', '130321', '青龙满族自治县', '130300');
INSERT INTO `area` VALUES ('76', '130322', '昌黎县', '130300');
INSERT INTO `area` VALUES ('77', '130324', '卢龙县', '130300');
INSERT INTO `area` VALUES ('78', '130401', '市辖区', '130400');
INSERT INTO `area` VALUES ('79', '130402', '邯山区', '130400');
INSERT INTO `area` VALUES ('80', '130403', '丛台区', '130400');
INSERT INTO `area` VALUES ('81', '130404', '复兴区', '130400');
INSERT INTO `area` VALUES ('82', '130406', '峰峰矿区', '130400');
INSERT INTO `area` VALUES ('83', '130421', '邯郸县', '130400');
INSERT INTO `area` VALUES ('84', '130423', '临漳县', '130400');
INSERT INTO `area` VALUES ('85', '130424', '成安县', '130400');
INSERT INTO `area` VALUES ('86', '130425', '大名县', '130400');
INSERT INTO `area` VALUES ('87', '130426', '涉县', '130400');
INSERT INTO `area` VALUES ('88', '130427', '磁县', '130400');
INSERT INTO `area` VALUES ('89', '130428', '肥乡县', '130400');
INSERT INTO `area` VALUES ('90', '130429', '永年县', '130400');
INSERT INTO `area` VALUES ('91', '130430', '邱县', '130400');
INSERT INTO `area` VALUES ('92', '130431', '鸡泽县', '130400');
INSERT INTO `area` VALUES ('93', '130432', '广平县', '130400');
INSERT INTO `area` VALUES ('94', '130433', '馆陶县', '130400');
INSERT INTO `area` VALUES ('95', '130434', '魏县', '130400');
INSERT INTO `area` VALUES ('96', '130435', '曲周县', '130400');
INSERT INTO `area` VALUES ('97', '130481', '武安市', '130400');
INSERT INTO `area` VALUES ('98', '130501', '市辖区', '130500');
INSERT INTO `area` VALUES ('99', '130502', '桥东区', '130500');
INSERT INTO `area` VALUES ('100', '130503', '桥西区', '130500');
INSERT INTO `area` VALUES ('101', '130521', '邢台县', '130500');
INSERT INTO `area` VALUES ('102', '130522', '临城县', '130500');
INSERT INTO `area` VALUES ('103', '130523', '内丘县', '130500');
INSERT INTO `area` VALUES ('104', '130524', '柏乡县', '130500');
INSERT INTO `area` VALUES ('105', '130525', '隆尧县', '130500');
INSERT INTO `area` VALUES ('106', '130526', '任县', '130500');
INSERT INTO `area` VALUES ('107', '130527', '南和县', '130500');
INSERT INTO `area` VALUES ('108', '130528', '宁晋县', '130500');
INSERT INTO `area` VALUES ('109', '130529', '巨鹿县', '130500');
INSERT INTO `area` VALUES ('110', '130530', '新河县', '130500');
INSERT INTO `area` VALUES ('111', '130531', '广宗县', '130500');
INSERT INTO `area` VALUES ('112', '130532', '平乡县', '130500');
INSERT INTO `area` VALUES ('113', '130533', '威县', '130500');
INSERT INTO `area` VALUES ('114', '130534', '清河县', '130500');
INSERT INTO `area` VALUES ('115', '130535', '临西县', '130500');
INSERT INTO `area` VALUES ('116', '130581', '南宫市', '130500');
INSERT INTO `area` VALUES ('117', '130582', '沙河市', '130500');
INSERT INTO `area` VALUES ('118', '130601', '市辖区', '130600');
INSERT INTO `area` VALUES ('119', '130602', '竞秀区', '130600');
INSERT INTO `area` VALUES ('120', '130606', '莲池区', '130600');
INSERT INTO `area` VALUES ('121', '130607', '满城区', '130600');
INSERT INTO `area` VALUES ('122', '130608', '清苑区', '130600');
INSERT INTO `area` VALUES ('123', '130609', '徐水区', '130600');
INSERT INTO `area` VALUES ('124', '130623', '涞水县', '130600');
INSERT INTO `area` VALUES ('125', '130624', '阜平县', '130600');
INSERT INTO `area` VALUES ('126', '130626', '定兴县', '130600');
INSERT INTO `area` VALUES ('127', '130627', '唐县', '130600');
INSERT INTO `area` VALUES ('128', '130628', '高阳县', '130600');
INSERT INTO `area` VALUES ('129', '130629', '容城县', '130600');
INSERT INTO `area` VALUES ('130', '130630', '涞源县', '130600');
INSERT INTO `area` VALUES ('131', '130631', '望都县', '130600');
INSERT INTO `area` VALUES ('132', '130632', '安新县', '130600');
INSERT INTO `area` VALUES ('133', '130633', '易县', '130600');
INSERT INTO `area` VALUES ('134', '130634', '曲阳县', '130600');
INSERT INTO `area` VALUES ('135', '130635', '蠡县', '130600');
INSERT INTO `area` VALUES ('136', '130636', '顺平县', '130600');
INSERT INTO `area` VALUES ('137', '130637', '博野县', '130600');
INSERT INTO `area` VALUES ('138', '130638', '雄县', '130600');
INSERT INTO `area` VALUES ('139', '130681', '涿州市', '130600');
INSERT INTO `area` VALUES ('140', '130683', '安国市', '130600');
INSERT INTO `area` VALUES ('141', '130684', '高碑店市', '130600');
INSERT INTO `area` VALUES ('142', '130701', '市辖区', '130700');
INSERT INTO `area` VALUES ('143', '130702', '桥东区', '130700');
INSERT INTO `area` VALUES ('144', '130703', '桥西区', '130700');
INSERT INTO `area` VALUES ('145', '130705', '宣化区', '130700');
INSERT INTO `area` VALUES ('146', '130706', '下花园区', '130700');
INSERT INTO `area` VALUES ('147', '130708', '万全区', '130700');
INSERT INTO `area` VALUES ('148', '130709', '崇礼区', '130700');
INSERT INTO `area` VALUES ('149', '130722', '张北县', '130700');
INSERT INTO `area` VALUES ('150', '130723', '康保县', '130700');
INSERT INTO `area` VALUES ('151', '130724', '沽源县', '130700');
INSERT INTO `area` VALUES ('152', '130725', '尚义县', '130700');
INSERT INTO `area` VALUES ('153', '130726', '蔚县', '130700');
INSERT INTO `area` VALUES ('154', '130727', '阳原县', '130700');
INSERT INTO `area` VALUES ('155', '130728', '怀安县', '130700');
INSERT INTO `area` VALUES ('156', '130730', '怀来县', '130700');
INSERT INTO `area` VALUES ('157',
版权声明：本文来源CSDN，感谢博主原创文章，遵循 CC 4.0 by-sa 版权协议，转载请附上原文出处链接和本声明。
原文链接：https://blog.csdn.net/lyy296293760/article/details/78685163
站方申明：本站部分内容来自社区用户分享，若涉及侵权，请联系站方删除。
-
发表于 2021-06-14 21:53:47
- 阅读 ( 998 )
- 分类：数据库
如果觉得我的文章对您有用，请随意打赏。你的支持将鼓励我继续创作！
