web.xml
<servlet>
<servlet-name>GetCity</servlet-name>
<servlet-class>com.yyb.servlet.GetCity</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>GetCity</servlet-name>
<url-pattern>/getCity</url-pattern>
</servlet-mapping>
index.jsp
<%@page import="com.yyb.po.Region"%>
<%@page import="java.util.List"%>
<%@page import="com.yyb.dao.impl.ProvinceDaoImpl"%>
<%@page import="com.yyb.dao.ProvinceDao"%>
<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%
ProvinceDao pdao=new ProvinceDaoImpl();
List<Region> regions=pdao.findAllProvince();
request.setAttribute("regions", regions);
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>省市县三级联动</title>
<script type="text/javascript" src="js/jquery-1.4.2.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$("#province").change(function(){
var provinceId=$("#province").val();
$.ajax({
type:"post",
url:"getCity",
data:"provinceId="+provinceId,
success:function(msg){
var city=JSON.parse(msg);
if(city.resultCode==0){
var provinceArry=city.rs;
$("#city").get(0).options.length=provinceArry.length+1;
$("#city").get(0).options[0]=new Option("请选择",0);
for(var i=0;i<provinceArry.length;i++){
$("#city").get(0).options[i+1]=new Option( provinceArry[i].region_name, provinceArry[i].region_id);
}
}else{
alert("未知错误!");
}
}
});
});
$("#city").change(function(){
var provinceId=$("#city").val();
$.ajax({
type:"post",
url:"getCity",
data:"provinceId="+provinceId,
success:function(msg){
var city=JSON.parse(msg);
if(city.resultCode==0){
var provinceArry=city.rs;
$("#county").get(0).options.length=provinceArry.length+1;
$("#county").get(0).options[0]=new Option("请选择",0);
for(var i=0;i<provinceArry.length;i++){
$("#county").get(0).options[i+1]=new Option( provinceArry[i].region_name, provinceArry[i].region_id);
}
}else{
alert("未知错误!");
}
}
});
});
});
</script>
</head>
<body>
请选择:
<select id="province">
<c:forEach items="${regions}" var="r">
<option value="${r.region_id}">${r.region_name}</option>
</c:forEach>
</select>
<select id="city"></select>
<select id="county"></select>
</body>
</html>
package com.yyb.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DBUtil {
private static final String DRIVER = "oracle.jdbc.OracleDriver";
private static final String URL = "jdbc:oracle:thin:@127.0.0.1:1521:orcl";
private static final String USER = "scott";
private static final String PASSWORD = "tiger";
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(DRIVER);
conn = DriverManager.getConnection(URL, USER, PASSWORD);
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}
return conn;
}
public static void close(ResultSet rs, Statement stmt, Connection conn) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (stmt != null) {
try {
stmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void main(String[] args) {
System.out.println(DBUtil.getConnection());
}
}
Region.java
package com.yyb.po;
public class Region {
private int region_id; // 行政区划id
private String region_code; // 行政区划代码
private String region_name; // 行政区划名称
private int parent_id; // 父行政区划
private int region_level; // 层级
private int region_order; // 排序,用来调整顺序
private String region_name_en; // 行政区划英文名称
private String region_shortname_en; // 行政区划简称
public int getRegion_id() {
return region_id;
}
public void setRegion_id(int region_id) {
this.region_id = region_id;
}
public String getRegion_code() {
return region_code;
}
public void setRegion_code(String region_code) {
this.region_code = region_code;
}
public String getRegion_name() {
return region_name;
}
public void setRegion_name(String region_name) {
this.region_name = region_name;
}
public int getParent_id() {
return parent_id;
}
public void setParent_id(int parent_id) {
this.parent_id = parent_id;
}
public int getRegion_level() {
return region_level;
}
public void setRegion_level(int region_level) {
this.region_level = region_level;
}
public int getRegion_order() {
return region_order;
}
public void setRegion_order(int region_order) {
this.region_order = region_order;
}
public String getRegion_name_en() {
return region_name_en;
}
public void setRegion_name_en(String region_name_en) {
this.region_name_en = region_name_en;
}
public String getRegion_shortname_en() {
return region_shortname_en;
}
public void setRegion_shortname_en(String region_shortname_en) {
this.region_shortname_en = region_shortname_en;
}
}
ProvinceDao.java
package com.yyb.dao;
import java.util.List;
import com.yyb.po.Region;
public interface ProvinceDao {
public List<Region> findAllProvince();
public List<Region> findByProvinceId(int provinceId);
}
ProvinceDaoImpl.java
package com.yyb.dao.impl;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.yyb.dao.ProvinceDao;
import com.yyb.po.Region;
import com.yyb.utils.DBUtil;
public class ProvinceDaoImpl implements ProvinceDao {
@Override
public List<Region> findAllProvince() {
List<Region> regions = new ArrayList<Region>();
String sql = "select * from region where parent_id=1";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
Region r = new Region();
r.setRegion_id(rs.getInt("region_id"));
r.setRegion_code(rs.getString("region_code"));
r.setRegion_name(rs.getString("region_name"));
r.setParent_id(rs.getInt("parent_id"));
r.setRegion_level(rs.getInt("region_level"));
r.setRegion_order(rs.getInt("region_order"));
r.setRegion_name_en(rs.getString("region_name_en"));
r.setRegion_shortname_en(rs.getString("region_shortname_en"));
regions.add(r);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, pstmt, conn);
}
return regions;
}
@Override
public List<Region> findByProvinceId(int provinceId) {
List<Region> regions = new ArrayList<Region>();
String sql = "select * from region where parent_id="+provinceId;
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = DBUtil.getConnection();
pstmt = conn.prepareStatement(sql);
rs = pstmt.executeQuery();
while (rs.next()) {
Region r = new Region();
r.setRegion_id(rs.getInt("region_id"));
r.setRegion_code(rs.getString("region_code"));
r.setRegion_name(rs.getString("region_name"));
r.setParent_id(rs.getInt("parent_id"));
r.setRegion_level(rs.getInt("region_level"));
r.setRegion_order(rs.getInt("region_order"));
r.setRegion_name_en(rs.getString("region_name_en"));
r.setRegion_shortname_en(rs.getString("region_shortname_en"));
regions.add(r);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
DBUtil.close(rs, pstmt, conn);
}
return regions;
}
}
GetCity.java
package com.yyb.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.yyb.dao.ProvinceDao;
import com.yyb.dao.impl.ProvinceDaoImpl;
import com.yyb.po.Region;
import net.sf.json.JSONObject;
public class GetCity extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void service(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
request.setCharacterEncoding("UTF-8");
response.setCharacterEncoding("UTF-8");
String provinceId = request.getParameter("provinceId");
ProvinceDao dao = new ProvinceDaoImpl();
int id = 0;
if (provinceId != null && !"".equals(provinceId)) {
id = Integer.parseInt(provinceId);
}
List<Region> regions = dao.findByProvinceId(id);
JSONObject jobj = new JSONObject();
if (regions != null && regions.size() > 0) {
jobj.element("resultCode", 0);
jobj.element("rs", regions);
} else {
jobj.element("resultCode", 1);
}
PrintWriter out = response.getWriter();
out.write(jobj.toString());
out.flush();
out.close();
}
}