java代码
package com.jd.servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.SQLException;
import java.util.ArrayList;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import com.google.gson.Gson;
import com.jd.util.DBUtil;
import com.jd.vo.Area;
public class AreaServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
response.setContentType("text/html; charset=UTF-8");
String code = request.getParameter("code");
String sql="select id,code,name,parent_code from area where parent_code='"+code+"'";
ArrayList<Area> list = new ArrayList<>();
DBUtil.select(sql,(resultset)->{
try {
while(resultset.next()) {
list.add(new Area(resultset.getString("id"),resultset.getString("code"),resultset.getString("name"),resultset.getString("parent_code")));
}
} catch (SQLException e) {
e.printStackTrace();
}
});
PrintWriter out = response.getWriter();
Gson gson = new Gson();
out.write(gson.toJson(list));
out.flush();
out.close();
}
}
前端代码
<%@ page language="java" contentType="text/html; charset=UTF-8"
pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>三级互联</title>
<script type="text/javascript" src="./js/jquery-3.3.1.min.js"></script>
<script type="text/javascript" src="./js/json2.js"></script>
</head>
<body>
<select id="province" onchange="changeProvince()">
<option>--请输入省份--</option>
</select>
<select id="city" onchange="changeCities()">
<option>--请输入市区--</option>
</select>
<select id="county" >
<option>--请输入县区--</option>
</select>
<script type="text/javascript">
$.ajax({
url:"./AreaServlet?code="+"",
success:function(areas){
var provinces=JSON.parse(areas);
var options="<option>--请输入省份--</option>";
for (var i = 0; i < provinces.length; i++) {
options=options+"<option value=\""+provinces[i].code+"\">"+provinces[i].name+"</option>";
}
$("#province").empty()
$("#province").append(options);
}
});
function changeProvince(){
var code=$("#province").val();
if(code==""){
$("#city").empty();
$("#city").append("<option>--请输入市区--</option>");
return;
}
$.ajax({
url:"./AreaServlet?code="+code,
success:function(areas){
var cities=JSON.parse(areas);
var options="<option>--请输入市区--</option>";
for (var i = 0; i < cities.length; i++) {
options=options+"<option value=\""+cities[i].code+"\">"+cities[i].name+"</option>";
}
$("#city").empty();
$("#city").append(options);
$("#county").empty();
$("#county").append("<option>--请输入县区--</option>");
}
});
}
function changeCities(){
var code=$("#city").val();
if(code==""){
$("#county").empty();
$("#county").append("<option>--请输入县区--</option>");
return;
}
$.ajax({
url:"./AreaServlet?code="+code,
success:function(areas){
var counties=JSON.parse(areas);
var options="<option>--请输入县区--</option>";
for (var i = 0; i < counties.length; i++) {
options=options+"<option value=\""+counties[i].code+"\">"+counties[i].name+"</option>";
}
$("#county").empty();
$("#county").append(options);
}
});
}
</script>
</body>
</html>
DBUtil工具类
package com.jd.util;
import java.sql.*;
public class DBUtil {
private static Connection getConnection() throws SQLException {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
String url = "jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
return DriverManager.getConnection(url,"root","root");
}
public static boolean batch(String...sqls) {
String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
Connection connection = null;
try {
connection = DriverManager.getConnection(url, "root", "root");
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
for (String sql: sqls) {
statement.addBatch(sql);
}
statement.executeBatch();
connection.commit();
statement.close();
connection.close();
return true;
} catch (SQLException throwables) {
if (connection!=null) {
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
throwables.printStackTrace();
}
return false;
}
public static boolean transaction(String...sqls) {
String url="jdbc:mysql://127.0.0.1:3306/test?useSSL=false";
Connection connection = null;
try {
connection = DriverManager.getConnection(url, "root", "root");
connection.setAutoCommit(false);
Statement statement = connection.createStatement();
for (String sql: sqls) {
statement.executeUpdate(sql);
}
connection.commit();
statement.close();
connection.close();
return true;
} catch (SQLException throwables) {
if (connection!=null) {
try {
connection.rollback();
} catch (SQLException e) {
e.printStackTrace();
}
}
throwables.printStackTrace();
}
return false;
}
public static Boolean exist(String sql){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
try {
Connection connection = getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);
return resultSet.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
return false;
}
public static Boolean exist(String sql,Object...shuzhu){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection =null;
PreparedStatement preparedStatement =null;
ResultSet resultSet =null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < shuzhu.length; i++) {
preparedStatement.setObject(i+1,shuzhu[i]);
}
resultSet = preparedStatement.executeQuery();
return resultSet.next();
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
close(resultSet,preparedStatement,connection);
}
return false;
}
public static void select(String sql,IRowMapper rowMapper){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Statement statement = null;
Connection connection = null;
ResultSet resultSet = null;
try {
connection = getConnection();
statement = connection.createStatement();
resultSet = statement.executeQuery(sql);
rowMapper.rowMapper(resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(resultSet,statement,connection);
}
}
public static void select(String sql, IRowMapper rowMapper,Object...shuzhu){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection =null;
PreparedStatement preparedStatement =null;
ResultSet resultSet =null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < shuzhu.length; i++) {
preparedStatement.setObject((i+1),shuzhu[i]);
}
resultSet = preparedStatement.executeQuery();
rowMapper.rowMapper(resultSet);
} catch (SQLException throwables) {
throwables.printStackTrace();
}finally {
close(resultSet,preparedStatement,connection);
}
}
public static boolean update(String sql){
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
Connection connection = null;
Statement statement = null;
try {
connection = getConnection();
statement = connection.createStatement();
int effect = statement.executeUpdate(sql);
return effect>0;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(statement,connection);
}
return false;
}
public static boolean update(String sql,Object...shuzhu){
Connection connection = null;
PreparedStatement preparedStatement = null;
try {
connection = getConnection();
preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < shuzhu.length; i++) {
preparedStatement.setObject(i+1,shuzhu[i]);
}
int effect = preparedStatement.executeUpdate();
return effect>0;
} catch (SQLException throwables) {
throwables.printStackTrace();
} finally {
close(preparedStatement,connection);
}
return false;
}
public static void close(Statement statement,Connection connection){
if (statement != null) {
try {
statement.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
}
public static void close(ResultSet resultSet,Statement statement,Connection connection){
if (resultSet != null) {
try {
resultSet.close();
} catch (SQLException throwables) {
throwables.printStackTrace();
}
}
close(statement,connection);
}
}
vo类
package com.jd.vo;
public class Area {
private String id;
private String code;
private String name;
private String parent_code;
public Area(String id, String code, String name, String parent_code) {
super();
this.id = id;
this.code = code;
this.name = name;
this.parent_code = parent_code;
}
public String getId() {
return id;
}
public String getCode() {
return code;
}
public String getName() {
return name;
}
public String getParent_code() {
return parent_code;
}
public void setId(String id) {
this.id = id;
}
public void setCode(String code) {
this.code = code;
}
public void setName(String name) {
this.name = name;
}
public void setParent_code(String parent_code) {
this.parent_code = parent_code;
}
@Override
public String toString() {
return "Area [id=" + id + ", code=" + code + ", name=" + name + ", parent_code=" + parent_code + "]";
}
}