实体类：
Province：
package com.bjpowernode.entity;
public class Province {
private Integer id;
private String name;
private String jiancheng;
private String shenghui;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getJiancheng() {
return jiancheng;
}
public void setJiancheng(String jiancheng) {
this.jiancheng = jiancheng;
}
public String getShenghui() {
return shenghui;
}
public void setShenghui(String shenghui) {
this.shenghui = shenghui;
}
@Override
public String toString() {
return "Province{" +
"id=" + id +
", name='" + name + ''' +
", jiancheng='" + jiancheng + ''' +
", shenghui='" + shenghui + ''' +
'}';
}
}
City：
package com.bjpowernode.entity;
public class City {
private Integer id;
private String name;
private Integer provinceId;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Integer getProvinceId() {
return provinceId;
}
public void setProvinceId(Integer provinceId) {
this.provinceId = provinceId;
}
@Override
public String toString() {
return "City{" +
"id=" + id +
", name='" + name + ''' +
", provinceId=" + provinceId +
'}';
}
}
dao层：QueryDao：
package com.bjpowernode.dao;
import com.bjpowernode.entity.City;
import com.bjpowernode.entity.Province;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
public class QueryDao {
private Connection conn;
private PreparedStatement pst;
private ResultSet rs;
private String sql;
private String url="jdbc:mysql://localhost:3306/springdb";
private String username="root";
private String password="123456";
//查询所有的省份信息
public List queryProvinceList(){
List provinces = new ArrayList<>();
try{
Province p = null;
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url,username,password);
sql="select id,name,jiancheng,shenghui from province order by id";
pst = conn.prepareStatement(sql);
rs = pst.executeQuery();
while(rs.next()){
p = new Province();
p.setId( rs.getInt("id"));
p.setName( rs.getString("name"));
p.setJiancheng( rs.getString("jiancheng"));
p.setShenghui( rs.getString("shenghui"));
provinces.add(p);
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if( rs != null){
rs.close();
}
if( pst != null){
pst.close();
}
if( conn != null){
conn.close();
}
}catch (Exception ex){
ex.printStackTrace();
}
}
return provinces;
}
//查询一个省份下面的所有城市
public List queryCityList(Integer provinceId){
List cities = new ArrayList<>();
try{
City city = null;
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection(url,username,password);
sql="select id, name from city where provinceid = ? ";
pst = conn.prepareStatement(sql);
//设置省份的参数值
pst.setInt(1, provinceId);
rs = pst.executeQuery();
while(rs.next()){
city = new City();
city.setId( rs.getInt("id"));
city.setName( rs.getString("name"));
cities.add(city);
}
}catch(Exception e){
e.printStackTrace();
}finally{
try{
if( rs != null){
rs.close();
}
if( pst != null){
pst.close();
}
if( conn != null){
conn.close();
}
}catch (Exception ex){
ex.printStackTrace();
}
}
return cities;
}
}
controller层：
QueryProviceServlet：
package com.bjpowernode.controller;
import com.bjpowernode.dao.QueryDao;
import com.bjpowernode.entity.Province;
import com.fasterxml.jackson.databind.ObjectMapper;
import javax.management.Query;
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.io.PrintWriter;
import java.util.List;
public class QueryProviceServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String json = "{}";
//调用dao，获取所有的省份信息， 是一个List集合
QueryDao dao = new QueryDao();
List provinces = dao.queryProvinceList();
//把list转为json格式的数据，输出给ajax请求
if( provinces != null ){
//调用jackson工具库，实现List--json
ObjectMapper om = new ObjectMapper();
json = om.writeValueAsString(provinces);
}
//输出json数据，响应ajax请求的，返回数据
response.setContentType("application/json;charset=utf-8");
PrintWriter pw = response.getWriter();
pw.println(json);
pw.flush();
pw.close();
}
}
QueryCityServlet：
package com.bjpowernode.controller;
import com.bjpowernode.dao.QueryDao;
import com.bjpowernode.entity.City;
import com.fasterxml.jackson.databind.ObjectMapper;
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.io.PrintWriter;
import java.util.List;
public class QueryCityServlet extends HttpServlet {
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
String json = "{}";
// 获取请求传过来的， 省份id
String strProviceId = request.getParameter("proid");
if( strProviceId != null && !"".equals(strProviceId.trim())){
QueryDao dao =new QueryDao();
List cityList = dao.queryCityList( Integer.valueOf( strProviceId));
//把list转为json
ObjectMapper om = new ObjectMapper();
json = om.writeValueAsString(cityList);
}
//输出数据
response.setContentType("application/json;charset=utf-8");
PrintWriter pw = response.getWriter();
pw.println(json);
pw.flush();
pw.close();
}
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
}
}
web.xml:
QueryProviceServlet com.bjpowernode.controller.QueryProviceServlet QueryProviceServlet /queryProvince QueryCityServlet com.bjpowernode.controller.QueryCityServlet QueryCityServlet /queryCity
index.jsp:
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
省市级联查询
省市级联查询,使用ajax
| 省份： | |
| 城市： |
数据库表：
