思路:
准备数据库表
*将mysql的jar包拷贝到WEB-INF/lib目录下
*将DBUtil工具类拷贝到项目中
*建表,模拟数据:
drop table if exists t_city;
drop table if exists t_province;
create table t_province(
code char(3) primary key ,
name varchar(255)
);
insert into t_province(code,name) values('001','山东省');
insert into t_province(code,name) values('002','山西省');
insert into t_province(code,name) values('003','河北省');
create table t_city(
code int primary key auto_increment,
name varchar(255),
pcode char(3),
foreign key(pcode) refrences t_province(code)
);
insert into t_city(code,name,pcode) values('001','保定市','003');
insert into t_city(code,name,pcode) values('002','石家庄市','003');
insert into t_city(code,name,pcode) values('003','廊坊市','003');
insert into t_city(code,name,pcode) values('004','张家口市','003');
insert into t_city(code,name,pcode) values('005','济南市','001');
insert into t_city(code,name,pcode) values('006','淄博市','001');
insert into t_city(code,name,pcode) values('007','青岛市','001');
insert into t_city(code,name,pcode) values('008','烟台市','001');
insert into t_city(code,name,pcode) values('009','太原市','002');
insert into t_city(code,name,pcode) values('010','运城市','002');
insert into t_city(code,name,pcode) values('011','大同市','002');
insert into t_city(code,name,pcode) values('012','临汾市','002');
commit;
select *from t_city;
select *from t_province;
在页面加载完毕之后,发送ajax请求,获取所有的省份,后台拼接一个JSON格式的字符串,发送给前端,前端解析JSON拼接HTML字符串,将HTML字符串设置到下拉列表当中。
*JSON:
[
{
"code":110,
"name":"山东省"
},
{
"code":110,
"name":"山东省"
}
{
"code":110,
"name":"山东省"
}
]
*前端接收到以上的JSON字符串之后,eval之后生成json对象,显然它是一个数组,需要在前端使用循环语句取出每一个
json对象,每一个json对象都是一个省份,然后<option value="110">山东省</option>,放到下拉列表<select>标签内部.
1.先导入mysql连接所用的jar包,然后导入工具类
2.接着创建jsp,作为前端发送ajax请求来接收并解析服务器传来的json数据,
3.创建servlet用来响应给jsp页面json格式的数据, 具体代码如下:
jdbc工具类:
package com.bjpowernode.pc.web.action;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils {
private JdbcUtils() {
}
//加载DB驱动
static {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (Exception e) {
throw new RuntimeException(e + "数据库连接失败");
}
}
private static Connection con;
//获取Connection对象
public static Connection getConnection() throws SQLException {
String url = "jdbc:mysql://localhost:3306/mybase?serverTimezone=UTC";
String username = "root", password = "ls12345";
if (con == null || con.isClosed()) {
con = DriverManager.getConnection(url, username, password);
}
return con;
}
// 关闭资源
public static void close(Connection con, Statement stat, ResultSet rs) throws SQLException {
if (con != null && !con.isClosed()) {
con.close();
}
if (stat != null && !stat.isClosed()) {
stat.close();
}
if (rs != null && !rs.isClosed()) {
rs.close();
}
}
}
JSP页面:
<%@page contentType="text/html; charset=UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>省市联动</title>
</head>
<body onload="listProvince();">
<script type="text/javascript">
function listProvince(){
//发送ajax请求,查询所有的省份,返回json
var xmlHttp;
if(window.XMLHttpRequest){
xmlHttp=new XMLHttpRequest();
}else{
xmlHttp=new ActiveXObject("Microsoft.XMLHttp");
}
xmlHttp.onreadystatechange=function(){
if(xmlHttp.readyState==4){
if(xmlHttp.status == 200){
//接收服务端返回的json
var jsonString=xmlHttp.responseText;
eval("var arr="+jsonString);
//遍历数组
var html="<option value=''>--请选择省份--</option>";
for(var i=0;i<arr.length;i++){
var provinceJson=arr[i];
html+="<option value='"+provinceJson.code+"'>"+provinceJson.name+"</option>";
}
document.getElementById("provinceList").innerHTML=html;
}else{
alert(xmlHttp.status);
}
}
}
var timeStamp=new Date().getTime();
xmlHttp.open("GET","${pageContext.request.contextPath}/province/list.do",true);//这里只开启通道,不发送请求,为了解决ajax-get请求缓存问题,可以加个时间戳
//4.通知异步请求对象代替浏览器发送请求协议包
xmlHttp.send();
}
function listCitys(pcode){
//发送ajax请求,查询所有的省份,返回json
var xmlHttp;
if(window.XMLHttpRequest){
xmlHttp=new XMLHttpRequest();
}else{
xmlHttp=new ActiveXObject("Microsoft.XMLHttp");
}
xmlHttp.onreadystatechange=function(){
if(xmlHttp.readyState==4){
if(xmlHttp.status == 200){
//接收服务端返回的json
var jsonString=xmlHttp.responseText;
eval("var arr="+jsonString);
//遍历数组
var html="<select><option value=''>--请选择市区--</option>";
for(var i=0;i<arr.length;i++){
var cityJson=arr[i];
html+="<option value='"+cityJson.code+"'>"+cityJson.name+"</option>";
}
html+="</select>";
document.getElementById("citySpan").innerHTML=html;
}else{
alert(xmlHttp.status);
}
}
}
var timeStamp=new Date().getTime();
xmlHttp.open("GET","${pageContext.request.contextPath}/city/list.do?_timeStamp="+timeStamp+"&pcode="+pcode,true);//这里只开启通道,不发送请求,为了解决ajax-get请求缓存问题,可以加个时间戳
//4.通知异步请求对象代替浏览器发送请求协议包
xmlHttp.send();
}
</script>
<select id="provinceList" onchange="listCitys(this.value);">
</select>
<span id="citySpan"></span>
</body>
</html>
省份类:
package com.bjpowernode.pc.web.action;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class ProvinceListAction extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//局部变量不存在线程安全隐患,所以建议使用StringBuilder,效率较高。
StringBuilder json=new StringBuilder();
json.append("[");
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=JdbcUtils.getConnection();
String sql="select code,name from t_province order by code asc";
ps=conn.prepareStatement(sql);
rs=ps.executeQuery();
while(rs.next()) {
String code=rs.getString("code");
String name=rs.getString("name");
json.append("{\"code\":\"");
json.append(code);
json.append("\",\"name\":\"");
json.append(name);
json.append("\"},");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.close(conn, ps, rs);
} catch (SQLException e) {
}
}
String endJson=json.substring(0,json.length()-1)+"]";
response.setContentType("text/html;charset=UTF-8");
response.getWriter().println(endJson);
}
}
城市类:
package com.bjpowernode.pc.web.action;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
public class CityListAction extends HttpServlet {
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
//局部变量不存在线程安全隐患,所以建议使用StringBuilder,效率较高。
String pcode=request.getParameter("pcode");
StringBuilder json=new StringBuilder();
json.append("[");
Connection conn=null;
PreparedStatement ps=null;
ResultSet rs=null;
try {
conn=JdbcUtils.getConnection();
String sql="select code,name from t_city where pcode=? order by code asc";
ps=conn.prepareStatement(sql);
ps.setString(1, pcode);
rs=ps.executeQuery();
while(rs.next()) {
String code=rs.getString("code");
String name=rs.getString("name");
json.append("{\"code\":\"");
json.append(code);
json.append("\",\"name\":\"");
json.append(name);
json.append("\"},");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try {
JdbcUtils.close(conn, ps, rs);
} catch (SQLException e) {
}
}
String endJson=json.substring(0,json.length()-1)+"]";
response.setContentType("text/html;charset=UTF-8");
response.getWriter().println(endJson);
}
}
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_2_5.xsd" id="WebApp_ID" version="2.5">
<display-name>pc</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>
<servlet>
<description></description>
<display-name>ProvinceListAction</display-name>
<servlet-name>ProvinceListAction</servlet-name>
<servlet-class>com.bjpowernode.pc.web.action.ProvinceListAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>ProvinceListAction</servlet-name>
<url-pattern>/province/list.do</url-pattern>
</servlet-mapping>
<servlet>
<description></description>
<display-name>CityListAction</display-name>
<servlet-name>CityListAction</servlet-name>
<servlet-class>com.bjpowernode.pc.web.action.CityListAction</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>CityListAction</servlet-name>
<url-pattern>/city/list.do</url-pattern>
</servlet-mapping>
</web-app>