声明:我创建的是一个普通的web工程,没有使用maven等,使用的是最原始的javascript代码,只在集合对象转换成Json类型数据的时候导入了jar包;
先给大家看下工程的大体结构:
简单介绍一下然后就在后面直接把代码给大家,我使用的是原始的ajax技术,也就是创建异步请求对象和调用反馈函数的那种技术,连接的是mysql数据库,其实在index.html中写了这些标签:
其中那三个select就是显示省市县用的,后面那个input标签是我添加的,用来在点击之后把标签里面的内容显示出来的,后面那个span标签就是真正显示省市县的地方;
接下面接收上面controller中的三个Servlet对象,它们是接收请求处理请求的地方,具体与数据库的交互在dao里面的Dao对象中,这样其实把dao里面的也说了;
然后说model里面的java类,那个和数据库中省市县表是对着的;
既然说到了数据库中的表,那就说一下吧,表中有三个字段,分别是id、pid、name,把所有的省市县数据都放在一个表中了,市的pid对应省的id,然后县的pid对应市的id,这就是表的大体结构,我会在后面把SQL文件给大家;
DBUtil是我使用jdbc连接数据中的时候创建的一个工具类,里面就是基本的jdbc连接数据库代码;
dbinfo.properties是mysql数据库连接的配置文件;
web.xml就是总配置文件;
lib中的就是相关的jar包;
写这些代码的思路:当页面加载完成的时候,去数据库中取出省相关的对象数据(包含id和name),然后放到List集合中,之后把使用外部引入的jar包把集合变成json对象,然后使用response把json对象转换成json格式的字符串传给前面的ajax,ajax对字符串使用eval()函数编译一下,把json格式的字符串变成json对象,然后获取其中的值放入HTML中;当省的数据发生变动的时候把让市的数据也发生改变,也是去取对象传json字符串等操作;当市的数据发生变动的时候让县的数据也变动;
编程过程中的难点:获得省和市的的都是对象,在前端获取里面值的时候需要注意,而获取县的数据直接就是字符串,然后放入list集合中,在前端获取里面值的时候也要注意和上面的两种不一样;使用外部引入的jar包把对象转换成json的时候注意,如果是普通的对象比如User之类的可以使用JsonObject,而如果是集合的话需要使用JsonArray,不过两种使用的方法都是fromObject();往前端传的值有的是中文,所有需要写response.setContentType(“text/html;charset=utf-8”);,里面的text/html不能省,省了就会导致乱码,我已经尝试了;前面我所说的显示省市区的那个按钮相关的js代码有点复杂,主要是获取select框中的文本内容有点难搞,毕竟通过javascript不好搞的
代码如下:
index.html:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>使用ajax完成省市县三级联动</title>
<script type="text/javascript">
// 1.省
window.onload = function () {
province();
function province() {
var xmlHttp = new XMLHttpRequest();
xmlHttp.onreadystatechange = function () {
var data = xmlHttp.responseText;
data = eval("("+data+")");
provinceCollBack(data);
};
xmlHttp.open("get","/myWeb/province.do",true);
xmlHttp.send();
}
function provinceCollBack(data) {
var html = "";
for (var i = 0;i<data.length;i++){
var dept = data[i];
html+="<option value="+dept.id+">"+dept.name+"</option>";
}
document.getElementById("province").innerHTML+=html;
}
}
// 2.市
function city() {
var xmlHttp = new XMLHttpRequest();
xmlHttp.onreadystatechange = function () {
var data = xmlHttp.responseText;
data = eval("("+data+")");
cityCollBack(data);
};
var id=document.getElementById("province").value;
xmlHttp.open("get","/myWeb/city.do?id="+id+"&date="+new Date(),true);
xmlHttp.send();
}
function cityCollBack(data) {
document.getElementById("city").innerHTML = "<option value='0'>请选择城市</option>";
document.getElementById("county").innerHTML = "<option value='0'>请选择区/县</option>";
var html = "";
for (var i = 0;i<data.length;i++){
var city = data[i];
html+="<option value="+city.id+">"+city.name+"</option>";
}
document.getElementById("city").innerHTML+=html;
}
// 3.县
function county() {
var xmlHttp = new XMLHttpRequest();
xmlHttp.onreadystatechange = function () {
var data = xmlHttp.responseText;
data = eval("("+data+")");
countyCollBack(data);
};
var id=document.getElementById("city").value;
xmlHttp.open("get","/myWeb/county.do?id="+id+"&date="+new Date(),true);
xmlHttp.send();
}
function countyCollBack(data) {
document.getElementById("county").innerHTML = "<option value='0'>请选择区/县</option>";
var html = "";
for (var i = 0;i<data.length;i++){
var name = data[i];
html+="<option>"+name+"</option>";
}
document.getElementById("county").innerHTML+=html;
}
// 点击按钮,显示选择的地址
function btn() {
var show = document.getElementById("show");
var province = document.getElementById("province").options[document.getElementById("province").selectedIndex].text;
var city = document.getElementById("city").options[document.getElementById("city").selectedIndex].text;
var county = document.getElementById("county").options[document.getElementById("county").selectedIndex].text;
if (document.getElementById("province").value=="0" || document.getElementById("city").value =="0" || document.getElementById("county").value == "0"){
show.innerText = "注意:请选择完整的地址,然后在点击按钮显示";
show.style.color="red";
} else{
show.innerText = "您选择的地址是:"+province+","+city+","+county;
show.style.color="green";
}
}
</script>
</head>
<body>
区域信息:
<select id="province" onchange="city()">
<option value="0">请选择省份/地区</option>
</select>
<select id="city" onchange="county()">
<option value="0">请选择城市</option>
</select>
<select id="county">
<option value="0">请选择区/县</option>
</select>
<input type="button" onclick="btn()" value="点击显示选择的地址"/>
<br/>
<span id="show"></span>
</body>
</html>
web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-app xmlns="http://xmlns.jcp.org/xml/ns/javaee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.jcp.org/xml/ns/javaee http://xmlns.jcp.org/xml/ns/javaee/web-app_4_0.xsd"
version="4.0">
<servlet>
<servlet-name>ProvinceServlet</servlet-name>
<servlet-class>com.bjpowernode.controller.ProvinceServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>CityServlet</servlet-name>
<servlet-class>com.bjpowernode.controller.CityServlet</servlet-class>
</servlet>
<servlet>
<servlet-name>CountyServlet</servlet-name>
<servlet-class>com.bjpowernode.controller.CountyServlet</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProvinceServlet</servlet-name>
<url-pattern>/province.do</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>CityServlet</servlet-name>
<url-pattern>/city.do</url-pattern>
</servlet-mapping>
<servlet-mapping>
<servlet-name>CountyServlet</servlet-name>
<url-pattern>/county.do</url-pattern>
</servlet-mapping>
</web-app>
CityServlet:
package com.bjpowernode.controller;
import com.bjpowernode.dao.Dao;
import com.bjpowernode.model.PrivinceCityCounty;
import net.sf.json.JSONArray;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
public class CityServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String pid = null;
List<PrivinceCityCounty> pccList = null;
JSONArray jsonArray = null;
Dao dao = new Dao();
// 1.获取省的id,也就是该省的城市中的pid
pid = request.getParameter("id");
// 2.通过dao获取城市对象
pccList = dao.getCity(pid);
// 3.把集合转变成json格式的数据
jsonArray = JSONArray.fromObject(pccList);
// 4.把json字符串推送到前端
response.setContentType("text/html;charset=utf-8");
response.getWriter().print(jsonArray.toString());
}
}
CountyServlet:
package com.bjpowernode.controller;
import com.bjpowernode.dao.Dao;
import com.bjpowernode.model.PrivinceCityCounty;
import net.sf.json.JSONArray;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
public class CountyServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String pid = null;
List pccList = null;
JSONArray jsonArray = null;
Dao dao = new Dao();
// 1.获取省的id,也就是该省的城市中的pid
pid = request.getParameter("id");
// 2.通过dao获取城市对象
pccList = dao.getCounty(pid);
// 3.把集合转变成json格式的数据
jsonArray = JSONArray.fromObject(pccList);
// 4.把json字符串推送到前端
response.setContentType("text/html;charset=utf-8");
response.getWriter().print(jsonArray.toString());
}
}
ProvinceServlet:
package com.bjpowernode.controller;
import com.bjpowernode.dao.Dao;
import com.bjpowernode.model.PrivinceCityCounty;
import net.sf.json.JSONArray;
import net.sf.json.JsonConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
public class ProvinceServlet extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
List<PrivinceCityCounty> pccList = null;
JSONArray jsonArray = null;
Dao dao = new Dao();
// 1.通过dao获取省对象
pccList = dao.getProvince();
// 2.把集合转变成json格式的数据
jsonArray = JSONArray.fromObject(pccList);
// 3.把json字符串推送到前端
response.setContentType("text/html;charset=utf-8");
response.getWriter().print(jsonArray.toString());
}
}
Dao:
package com.bjpowernode.dao;
import com.bjpowernode.controller.ProvinceServlet;
import com.bjpowernode.model.PrivinceCityCounty;
import com.bjpowernode.util.DBUtil;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class Dao {
/**
* 获取省对象集合
* @return 省对象集合
*/
public List<PrivinceCityCounty> getProvince() {
List<PrivinceCityCounty> pccList = new ArrayList<>();
Connection conn = null;
PreparedStatement ps = null;
PrivinceCityCounty pcc = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
// 虽然解决了排序问题,但是重庆由于电子拼音里面是zhong qing,所以排在了后面,如果我自己写的时候,可以把拼音和id结合起来解决这个问题
String sql = "select * from privince_city_county where id between 0 and 100 order by convert(name using GBK)";
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
pcc = new PrivinceCityCounty(id, null, name);
pccList.add(pcc);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn, ps, rs);
}
return pccList;
}
/**
* 获取城市对象集合
* @param pid 省id
* @return 城市对象集合
*/
public List<PrivinceCityCounty> getCity(String pid) {
List<PrivinceCityCounty> pccList = new ArrayList<>();
Connection conn = null;
PreparedStatement ps = null;
PrivinceCityCounty pcc = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql = "select * from privince_city_county where id between 1000 and 10000 and pid = ? order by convert(name using GBK)";
ps = conn.prepareStatement(sql);
ps.setInt(1, Integer.valueOf(pid));
rs = ps.executeQuery();
while (rs.next()){
int id = rs.getInt("id");
String name = rs.getString("name");
pcc = new PrivinceCityCounty(id, null, name);
pccList.add(pcc);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn, ps, rs);
}
return pccList;
}
/**
* 获取县区名称集合
* @param pid 市id
* @return 县区名称集合
*/
public List getCounty(String pid) {
List pccList = new ArrayList<>();
Connection conn = null;
PreparedStatement ps = null;
PrivinceCityCounty pcc = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
String sql = "select name from privince_city_county where id between 100000 and 1000000 and pid = ? order by convert(name using GBK)";
ps = conn.prepareStatement(sql);
ps.setInt(1, Integer.valueOf(pid));
rs = ps.executeQuery();
while (rs.next()){
String name = rs.getString("name");
pccList.add(name);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(conn, ps, rs);
}
return pccList;
}
}
PrivinceCityCounty:
package com.bjpowernode.model;
public class PrivinceCityCounty {
private Integer id;
private Integer pid;
private String name;
public PrivinceCityCounty() {
}
public PrivinceCityCounty(Integer id, Integer pid, String name) {
this.id = id;
this.pid = pid;
this.name = name;
}
public Integer getId() {
return id;
}
public PrivinceCityCounty setId(Integer id) {
this.id = id;
return this;
}
public Integer getPid() {
return pid;
}
public PrivinceCityCounty setPid(Integer pid) {
this.pid = pid;
return this;
}
public String getName() {
return name;
}
public PrivinceCityCounty setName(String name) {
this.name = name;
return this;
}
}
DBUtil:
package com.bjpowernode.util;
import java.sql.*;
import java.util.ResourceBundle;
public class DBUtil {
private static ResourceBundle rb = ResourceBundle.getBundle("resources/dbinfo");
// 构造方法私有化,禁止对象实例化
private DBUtil(){}
static {
// 注册驱动,只需要注册一次就可以了
String driver = rb.getString("driver");
try {
Class.forName(driver);
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
/**
* 获取连接
* @return 获取和数据库的连接通道
* @throws SQLException
*/
public static Connection getConnection() throws SQLException {
String url = rb.getString("url");
String user = rb.getString("user");
String password = rb.getString("password");
Connection conn = DriverManager.getConnection(url, user,password );
return conn;
}
/**
* 设置事务的手动提交
* @param conn
* @throws SQLException
*/
public static void startTransaction(Connection conn) throws SQLException {
conn.setAutoCommit(false);
}
/**
* 提交事务
* @param conn
* @throws SQLException
*/
public static void commitTransaction(Connection conn) throws SQLException {
conn.commit();
}
/**
* 如果事务出现异常,回滚事务
* @param conn
*/
public static void rollbackTransaction(Connection conn){
if (conn!=null){
try {
conn.rollback();// 如果出现异常,就回滚事务
} catch (SQLException e1) {
e1.printStackTrace();
}
}
}
/**
* 释放对象
* @param conn
* @param ps
* @param rs
*/
public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps!=null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
dbinfo.properties:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/bjpowernode
user=root
password=******#这个是自己的数据库密码,就不告诉你了