Ajax实现省市区三级联动
目录
- 需要的jar包:
- 数据库代码:
- 省:
- 市:
- 区:
- 页面展示代码:
- DBHelper类:
- 总结
需要的jar包:
数据库代码:
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>
DBHelper类:
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("链接成功"); } } }
总结
本篇文章就到这里了,希望能给你带来帮助,也希望你能够多多关注我们的更多内容!
赞 (0)