Ajax实现省市区三级联动

create database school character set utf8;
use school;

CREATE table  provice (
pid INT PRIMARY KEY  auto_increment,
pname varchar(20)
);
INSERT into provice VALUES (null,"河南省");
INSERT into provice VALUES (null,"山东省");
INSERT into provice VALUES (null,"河北省");
CREATE table  city (
cid INT PRIMARY KEY  auto_increment,
cname varchar(20),
pid int
);
-- 河南省
INSERT into city VALUES (null,"郑州市",1);
INSERT into city VALUES (null,"开封市",1);
INSERT into city VALUES (null,"洛阳市",1);
-- 山东
INSERT into city VALUES (null,"济南市",2);
INSERT into city VALUES (null,"青岛市",2);
INSERT into city VALUES (null,"淄博市",2);
-- 河北
INSERT into city VALUES (null,"石家庄市",3);
INSERT into city VALUES (null,"唐山市",3);
INSERT into city VALUES (null,"秦皇岛市",3);

CREATE table  street (
sid INT PRIMARY KEY  auto_increment,
sname varchar(20),
cid int
);
-- 郑州市
INSERT into street VALUES (null,"中原区",1);
INSERT into street VALUES (null,"二七区",1);
INSERT into street VALUES (null,"管城回族区",1);
-- 开封市
INSERT into street VALUES (null,"龙亭区",2);
INSERT into street VALUES (null,"顺河回族区",2);
INSERT into street VALUES (null,"鼓楼区",2);
-- 洛阳市
INSERT into street VALUES (null,"汝阳",3);
INSERT into street VALUES (null,"宜阳",3);
INSERT into street VALUES (null,"洛宁",3);
-- 济南市
INSERT into street VALUES (null,"商河县",4);
INSERT into street VALUES (null,"济阳县",4);
INSERT into street VALUES (null,"平阴县",4);
-- 青岛市
INSERT into street VALUES (null,"七区五市",5);
INSERT into street VALUES (null,"市南区",5);
INSERT into street VALUES (null,"市北区",5);
-- 淄博市 
INSERT into street VALUES (null,"博山",6);
INSERT into street VALUES (null,"周村",6);
INSERT into street VALUES (null,"临淄",6);
-- 石家庄市
INSERT into street VALUES (null,"正定县",7);
INSERT into street VALUES (null,"行唐县",7);
INSERT into street VALUES (null,"灵寿县",7);
-- 唐山市
INSERT into street VALUES (null,"乐亭县",8);
INSERT into street VALUES (null,"迁西县",8);
INSERT into street VALUES (null,"玉田县",8);
-- 秦皇岛市
INSERT into street VALUES (null,"青龙满族自治县",9);
INSERT into street VALUES (null,"昌黎县",9);
INSERT into street VALUES (null,"卢龙县",9);

package cn.hp.dao;
import cn.hp.model.Provice;
import java.util.List;
public interface ProviceInfoDao {
    public List<Provice> findAll();
}

package cn.hp.impl;
import cn.hp.dao.ProviceInfoDao;
import cn.hp.model.Provice;
import cn.hp.util.DBHelper;
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 ProviceInfoDaoImpl implements ProviceInfoDao {
    @Override
    public List<Provice> findAll() {
        Connection conn = DBHelper.getConn();
        List<Provice> list = new ArrayList<Provice>();
        String sql = "select * from provice";
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                Provice p = new Provice();
                p.setPid(rs.getInt(1));
                p.setPname(rs.getString(2));
                list.add(p);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

package cn.hp.model;
public class Provice {
    private int pid;
    private String pname;
    public Provice() {
    }
    public Provice(int pid, String pname) {
        this.pid = pid;
        this.pname = pname;
    }
    @Override
    public String toString() {
        return "Provice{" +
                "pid=" + pid +
                ", pname='" + pname + '\'' +
                '}';
    }
    public int getPid() {
        return pid;
    }
    public void setPid(int pid) {
        this.pid = pid;
    }
    public String getPname() {
        return pname;
    }
    public void setPname(String pname) {
        this.pname = pname;
    }
}

package cn.hp.servlet;
import cn.hp.dao.ProviceInfoDao;
import cn.hp.impl.ProviceInfoDaoImpl;
import cn.hp.model.Provice;
import com.alibaba.fastjson.JSONObject;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/findprovice")
public class FindProviceServlet extends HttpServlet {
    public FindProviceServlet() {
        super();
    }
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
//        super.doGet(req, resp);
        req.setCharacterEncoding("utf-8");
        resp.setContentType("text/html;charset=utf-8");
        ProviceInfoDao pid = new ProviceInfoDaoImpl();
        List<Provice> plist=pid.findAll();
        //把这个省份的集合转换成json格式的数据发送到前端页面
        resp.getWriter().write(JSONObject.toJSONString(plist));
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        super.doPost(req, resp);
    }

}

package cn.hp.dao;
import cn.hp.model.City;
import java.util.List;
public interface CityInfoDao {
    public List<City> findAllCity(int pid);
}

package cn.hp.impl;
import cn.hp.dao.CityInfoDao;
import cn.hp.model.City;
import cn.hp.model.Provice;
import cn.hp.util.DBHelper;
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 CityInfoDaoImpl implements CityInfoDao {
    @Override
    public List<City> findAllCity(int pid) {
        Connection conn = DBHelper.getConn();
        List<City> list = new ArrayList<City>();
        String sql = "select * from city where pid=?";
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,pid);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                City c=new City();
                c.setCid(rs.getInt(1));
                c.setCname(rs.getString(2));
                c.setPid(rs.getInt(3));
                list.add(c);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

package cn.hp.model;
public class City {
    private int cid;
    private String cname;
    private int pid;
    public City() {
    }
    public City(int cid, String cname, int pid) {
        this.cid = cid;
        this.cname = cname;
        this.pid = pid;
    }
    @Override
    public String toString() {
        return "City{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                ", pid=" + pid +
                '}';
    }
    public int getCid() {
        return cid;
    }
    public void setCid(int cid) {
        this.cid = cid;
    }
    public String getCname() {
        return cname;
    }
    public void setCname(String cname) {
        this.cname = cname;
    }
    public int getPid() {
        return pid;
    }
    public void setPid(int pid) {
        this.pid = pid;
    }
}

package cn.hp.servlet;
import cn.hp.dao.CityInfoDao;
import cn.hp.impl.CityInfoDaoImpl;
import cn.hp.model.City;
import com.alibaba.fastjson.JSONObject;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/findcitypid")
public class FindCityPidServlet extends HttpServlet{
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("text/html;charset=utf-8");
        String id = req.getParameter("id");
        CityInfoDao cid = new CityInfoDaoImpl();
        List<City> list = cid.findAllCity(Integer.parseInt(id));
        //把城市的集合转换成json格式的字符串发送到前端页面
        resp.getWriter().write(JSONObject.toJSONString(list));
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        super.doPost(req, resp);
    }
}

package cn.hp.dao;
import cn.hp.model.Street;
import java.util.List;
public interface StreetInfoDao {
    public List<Street> findAllStreet(int cid);
}

package cn.hp.impl;
import cn.hp.dao.StreetInfoDao;
import cn.hp.model.Provice;
import cn.hp.model.Street;
import cn.hp.util.DBHelper;
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 StreetInfoDaoImpl implements StreetInfoDao {
    @Override
    public List<Street> findAllStreet(int cid) {
        Connection conn = DBHelper.getConn();
        List<Street> list = new ArrayList<Street>();
        String sql = "select * from Street where cid=?";
        try {
            PreparedStatement ps=conn.prepareStatement(sql);
            ps.setInt(1,cid);
            ResultSet rs = ps.executeQuery();
            while (rs.next()){
                Street s = new Street();
                s.setDid(rs.getInt(1));
                s.setDname(rs.getString(2));
                s.setCid(rs.getInt(3));
                list.add(s);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
}

package cn.hp.model;
public class Street {
    private int did;
    private String dname;
    private int cid;
    public Street() {
    }
    public Street(int did, String dname, int cid) {
        this.did = did;
        this.dname = dname;
        this.cid = cid;
    }
    @Override
    public String toString() {
        return "Street{" +
                "did=" + did +
                ", dname='" + dname + '\'' +
                ", cid=" + cid +
                '}';
    }
    public int getDid() {
        return did;
    }
    public void setDid(int did) {
        this.did = did;
    }
    public String getDname() {
        return dname;
    }
    public void setDname(String dname) {
        this.dname = dname;
    }
    public int getCid() {
        return cid;
    }
    public void setCid(int cid) {
        this.cid = cid;
    }
}

package cn.hp.servlet;
import cn.hp.dao.CityInfoDao;
import cn.hp.dao.ProviceInfoDao;
import cn.hp.dao.StreetInfoDao;
import cn.hp.impl.CityInfoDaoImpl;
import cn.hp.impl.ProviceInfoDaoImpl;
import cn.hp.impl.StreetInfoDaoImpl;
import cn.hp.model.City;
import cn.hp.model.Provice;
import cn.hp.model.Street;
import com.alibaba.fastjson.JSONObject;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.util.List;
@WebServlet("/findstreetdid")
public class FindStreetServlet extends HttpServlet {
    public FindStreetServlet() {
        super();
    }
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        req.setCharacterEncoding("utf-8");
        resp.setContentType("text/html;charset=utf-8");
        String id = req.getParameter("id");
        StreetInfoDao did = new StreetInfoDaoImpl();
        List<Street> list=did.findAllStreet(Integer.parseInt(id));
        //把这个省份的集合转换成json格式的数据发送到前端页面
        resp.getWriter().write(JSONObject.toJSONString(list));
    }
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        super.doPost(req, resp);
    }
}

<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<script src="js/jquery-1.8.3.js"></script>
<html>
<head>
    <title>Title</title>

</head>
<body>
<script>
    $(function () {
        $.ajax({
            type:"get",
            url:"findprovice",
            dataType:"json",
            success:function (data) {//data的值就是从后端发送过来的json格式的字符串
                //拿到当前省份的元素对象
                var obj = $("#provice");
                for (var i =0;i<data.length;i++){
                    var ob="<option value='"+data[i].pid+"'>"+data[i].pname+"</option>";
                    obj.append(ob);
                }
            }
        })
    })
</script>

    <select name="provice" id="provice">
        <option value="0">请选择</option>
    </select>省
    <select name="city" id="city">
        <option value="0">请选择</option>
    </select>市
    <select name="street" id="street">
        <option value="0">请选择</option>
    </select>区
<script>
    $("#provice").change(function () {
        $("#city option").remove();
        $.ajax({
            type: "get",
            url:"findcitypid?id="+$("#provice").val(),
            dataType: "json",
            success:function (data) {
                var obj = $("#city");
                for (var i =0;i<data.length;i++){
                    var ob="<option value='"+data[i].cid+"'>"+data[i].cname+"</option>";
                    obj.append(ob);
                }
            }
        })
    })
</script>
<script>
    $("#provice").change(function () {
        $("#street option").remove();
        $.ajax({
            type: "get",
            url:"findstreetdid?id="+$("#provice").val(),
            dataType: "json",
            success:function (data) {
                var obj = $("#street");
                for (var i =0;i<data.length;i++){
                    var ob="<option value='"+data[i].did+"'>"+data[i].dname+"</option>";
                    obj.append(ob);
                }
            }
        })
    })
</script>
</body>
</html>

package cn.hp.util;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DBHelper {
	private static String Driver = "com.mysql.jdbc.Driver";
	private static String Url = "jdbc:mysql://localhost:3306/school?characterEncoding=utf8";
	private static String user = "root";
	private static String pwd = "root";
	public static Connection conn;
	// 创建数据库连接
	public static Connection getConn() {
		try {
			Class.forName(Driver);
			conn = DriverManager.getConnection(Url, user, pwd);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}
	// 关闭数据库连接
	public static void getClose() {
		try {
			if (conn != null) {
				conn.close();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	// 测试数据库连接
	public static void main(String[] args) {
		System.out.println(getConn());
		if (getConn()!=null) {
			System.out.println("链接成功");
		}
	}
}