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( "链接成功" );

         }

     }

}