【Java】Ajax实现级联城市
效果图
一、准备工作
(1)导入jQuery
a 下载到本地 地址 https://jquery.com/download/
b 使用网上的(当然没网不能使用)
<script src="https://cdn.jsdelivr.net/npm/jquery@3.5.1/dist/jquery.min.js"></script>
(2)引入 com.alibaba.fastjson.JSON(json转换) 和 mysql-connector-java(数据库连接),这里小编提供 maven 依赖(若需要 jar 包的自行下载)
<!-- https://mvnrepository.com/artifact/com.alibaba/fastjson --> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.75</version> </dependency> <!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.22</version> </dependency>
(3)数据库工具类,可以使用自己写的,也可以使用apache提供的dbutil类,当然也可使用小编提供的(下面是小编最近使用的DbUtil)
package pers.dbutils; import lombok.Data; import java.io.IOException; import java.io.InputStream; import java.sql.*; import java.util.*; import java.util.stream.Collectors; /** * TODO jdbc常用操作工具类 * * @author netyts@163.com * @date 2020/11/6 15:24 */ @Data public class DbUtils { private String driver = "com.mysql.cj.jdbc.Driver"; private String url = "jdbc:mysql://localhost:3306/db?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8"; private String user = "root"; private String password = "root"; private Connection conn; private int currentPage = 1; //当前页 private int pageCount = 0; //总页数 private int pageSize = 10; //每一页记录的数据量 private int recordCount = 0; //总数据量 /** * 无参构造连接数据库 */ public DbUtils() { try { Class.forName(driver); this.conn = DriverManager.getConnection(url, user, password); } catch (SQLException throwables) { throwables.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 使用数据库名 * @param dbName 数据库名 */ public DbUtils(String dbName){ try { Class.forName(driver); url = "jdbc:mysql://localhost:3306/"+dbName+"?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8"; this.conn = DriverManager.getConnection(url, user, password); } catch (Exception e) { e.printStackTrace(); } } /** * 有参连接数据库 * @param driver 加载驱动 * @param host IP地址 * @param port 端口号 * @param dbName 数据库名称 * @param user 用户名 * @param password 密码 */ public DbUtils(String driver, String host, String port, String dbName, String user, String password) { this.driver = driver; this.url = "jdbc:mysql://" + host + ":" + port + "/" + dbName + "?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8"; this.user = user; this.password = password; try { Class.forName(driver); this.conn = DriverManager.getConnection(this.url, this.user, this.password); } catch (SQLException throwables) { throwables.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } //利用db.properties配置文件连接数据库 public DbUtils(boolean flag) { InputStream is = DbUtils.class.getClassLoader().getResourceAsStream("db.properties"); Properties prop = new Properties(); try { prop.load(is); driver = prop.getProperty("db.driver", "com.mysql.cj.jdbc.Driver"); url = prop.getProperty("db.url", "jdbc:mysql://localhost:3306/db?serverTimezone=PRC&useUnicode=true&characterEncoding=utf8"); user = prop.getProperty("db.user", "root"); password = prop.getProperty("db.password", ""); pageSize = Integer.parseInt(prop.getProperty("db.pageSize", "10")); Class.forName(driver); conn = DriverManager.getConnection(url, user, password); } catch (IOException e) { e.printStackTrace(); } catch (SQLException throwables) { throwables.printStackTrace(); } catch (ClassNotFoundException e) { e.printStackTrace(); } } /** * 数据库数据语句操作(增、删、改) * * @param sql sql语句 * @param params 0个或多个 * @return 对数据表产生影响的行数 */ public int execute(String sql, Object... params) { int row = 0; try { PreparedStatement ps = conn.prepareStatement(sql); int index = 1; for (Object p : params) { ps.setObject(index++, p); } row = ps.executeUpdate(); ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } return row; } /** * 数据插入 * * @param tableName 表名 * @param values Map(key,values)键值对 * @return 对数据表产生影响的行数 */ public int insert(String tableName, Map<String, Object> values) { int row = 0; Set<String> set = values.keySet(); String fn = set.toString().replace(" ", ""); fn = fn.substring(1, fn.length() - 1); String fv = set.stream().map(m -> "?").collect(Collectors.toList()).toString().replace(" ", ""); fv = fv.substring(1, fv.length() - 1); String sql = String.format("insert into %s(%s) values(%s)", tableName, fn, fv); try { PreparedStatement ps = conn.prepareStatement(sql); int index = 1; for (String k : set) { ps.setObject(index++, values.get(k)); } row = ps.executeUpdate(); ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } return row; } /** * 数据插入 * * @param tableName 表名 * @param fieldName 字段名(1个或多个) * @param fieldValues 数据信息(和字段名顺序保持一致) * @return 对数据表产生影响的行数 */ public int insert(String tableName, String fieldName, Object[] fieldValues) { int row = 0; String fv = Arrays.stream(fieldValues).map(m -> "?").collect(Collectors.toList()).toString().replace(" ", ""); fv = fv.substring(1, fv.length() - 1); String sql = String.format("insert into %s(%s) values(%s)", tableName, fieldName, fv); try { PreparedStatement ps = conn.prepareStatement(sql); for (int i = 0; i < fieldValues.length; i++) { ps.setObject(i + 1, fieldValues[i]); } row = ps.executeUpdate(); ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } return row; } /** * 根据主键删除数据 * @param tableName 表名 * @param params 主键值(1个或多个) * @return 对数据表产生影响的行数 */ public int deleteByPK(String tableName, Object... params){ int row = 0; String symbol = "?,".repeat(params.length); String sql = String.format("delete from %s where %s in(%s)", tableName,getPK(tableName),symbol.substring(0, symbol.length()-1)); try { PreparedStatement ps = conn.prepareStatement(sql); int index = 1; for(Object o : params){ ps.setObject(index++,o); } row = ps.executeUpdate(); ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } return row; } /** * 根据主键修改数据 * @param tableName 表名 * @param fieldName 字段名(1个或多个) * @param values 修改后的值 * @param params 主键值(1个或多个) * @return 对数据表产生影响的行数 */ public int updateByPK(String tableName, String fieldName, Object values, Object... params){ int row = 0; String symbol = "?,".repeat(params.length); String sql = String.format("update %s set %s=? where %s in(%s)",tableName,fieldName,getPK(tableName),symbol.substring(0, symbol.length()-1)); try { PreparedStatement ps = conn.prepareStatement(sql); ps.setObject(1, values); int index = 2; for(Object o : params){ ps.setObject(index++, o); } row = ps.executeUpdate(); ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } return row; } /** * 对某一列(某几个)的值全部增加或减少相同的值 * * @param tableName 表名 * @param fieldName 字段名(1个或多个) * @param values 要增加或减少的值 * @param condition 条件 * @return 对数据表产生影响的行数 */ public int updateInc(String tableName, String fieldName, Object values, String condition) { int row = 0; String sql = String.format("update %s set %2$s=%s+%d %s", tableName, fieldName, values, condition); try { PreparedStatement ps = conn.prepareStatement(sql); row = ps.executeUpdate(); ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } return row; } /** * 获取主键的字段名 * @param tableName 表名 * @return 表的主键字段名 */ public String getPK(String tableName) { String PKName = null; try { DatabaseMetaData dmd = conn.getMetaData(); ResultSet rs = dmd.getPrimaryKeys(null, "%", tableName); rs.next(); PKName = rs.getString("column_name"); rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } // //方法二 // String sql = String.format("show index from %s", tableName); // try { // PreparedStatement ps = conn.prepareStatement(sql); // ResultSet rs = ps.executeQuery(); // rs.next(); // PKName = rs.getString("column_name"); // rs.close(); // ps.close(); // } catch (SQLException throwables) { // throwables.printStackTrace(); // } return PKName; } /** * 查询操作 * @param sql sql语句 * @param params 0个或多个 * @return list集合 */ public List<Map<String, Object>> select(String sql, Object... params) { List<Map<String, Object>> list = new LinkedList<>(); try { PreparedStatement ps = conn.prepareStatement(sql); int index = 1; for (Object o : params) { ps.setObject(index++, o); } ResultSet rs = ps.executeQuery(); ResultSetMetaData rsm = rs.getMetaData(); Map<String, Object> m; while (rs.next()) { m = new LinkedHashMap<>(); for (int i = 1; i <= rsm.getColumnCount(); i++) { m.put(rsm.getColumnLabel(i), rs.getObject(rsm.getColumnLabel(i))); } list.add(m); } rs.close(); ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } return list; } /** * 查询操作 * @param tableName 表名 * @param fieldName 字段名(1个或多个) * @param condition 条件 * @return list集合 */ public List<Map<String, Object>> select(String tableName, String fieldName, String condition) { String sql = String.format("select %s from %s %s", fieldName, tableName, condition); return select(sql); } // private int currPage = 1; //当前页 // private int pageCount = 0; //总页数 // private int pageSize = 10; //每一页的数据量 // private int recordCount = 0; //总数据量 /** * 获取总数据数量 * @param tableName 表名 * @param condition 条件 * @return 总数据量 */ public int getRecordCount(String tableName, String condition) { String sql = String.format("select count(*) from %s %s", tableName, condition); try { PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); rs.next(); recordCount = rs.getInt(1); rs.close(); ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } return recordCount; } /** * 获取总页数 * @param tableName 表名 * @param pageSize 每一页记录的数据量 * @param condition 条件 * @return 总页数 */ public int getPageCount(String tableName, int pageSize, String condition) { recordCount = getRecordCount(tableName, condition); if (recordCount % pageSize == 0) { pageCount = recordCount / pageSize; } else { pageCount = recordCount / pageSize + 1; } return pageCount; } /** * 获取总页码(每页默认显示10条数据) * @param tableName 表名 * @param condition 条件 * @return */ public int getPageCount(String tableName, String condition) { return getPageCount(tableName, pageSize, condition); } /** * 查看某一页的数据 * @param tableName 表名 * @param fieldName 字段名(1个或多个) * @param currentPage 选择要查看的页数(当前页) * @param pageSize 每页记录的数据量 * @param condition 条件 * @return list集合 */ public List<Map<String, Object>> page(String tableName, String fieldName, int currentPage, int pageSize, String condition) { pageCount = getPageCount(tableName, pageSize, condition); List<Map<String, Object>> list = new LinkedList<>(); if (currentPage <= pageCount) { int cp = pageSize * (currentPage - 1); String sql = String.format("select %s from %s limit %d,%d %s", fieldName, tableName, cp, pageSize, condition); // System.out.println(sql); try { PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); ResultSetMetaData rsm = rs.getMetaData(); Map<String, Object> m; while (rs.next()) { m = new LinkedHashMap<>(); for (int i = 1; i <= rsm.getColumnCount(); i++) { m.put(rsm.getColumnLabel(i), rs.getObject(rsm.getColumnLabel(i))); } list.add(m); } rs.close(); ps.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } else { Map<String, Object> m = new HashMap<>(); m.put("error", "页码输入有误!"); list.add(m); } return list; } /** * 查看某一页的数据(每页的默认10条数据,默认无条件) * @param tableName 表名 * @param fieldName 字段名(1个或多个) * @param currentPage 选择要查看的页数(当前页) * @return list集合 */ public List<Map<String, Object>> page(String tableName, String fieldName, int currentPage){ return page(tableName, fieldName, currentPage, pageSize, ""); } /** * 导出表数据到txt文件,需要判断secure_file_priv(show variables like "secure_file_priv";)的状态, * 若为null,则是对mysqld的导入、导出做限制,修改my.ini文件添加或修改secure_file_priv=""; * @param tableName 表名 * @param fieldName 字段名(1个或多个) * @param file 文件路径 */ public void exportData(String tableName, String fieldName, String file) { String sql = String.format("select %s from %s into outfile '%s'", fieldName,tableName,file); try { PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); ps.close(); rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } /** * 导入表数据,数据库必须有此表的表结构 * @param file 文件路径 * @param tableName 表名 */ public void importData(String file, String tableName) { String sql = String.format("load data infile '%s' into table %s",file,tableName); try { PreparedStatement ps = conn.prepareStatement(sql); ResultSet rs = ps.executeQuery(); ps.close(); rs.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } public void close() { try { conn.close(); } catch (SQLException throwables) { throwables.printStackTrace(); } } }
若使用小编提供的,要看清自己的数据库数据,不然使用时会报错,连接的配置一定要配好,默认是db数据库
(4)级联城市数据库,当然你也可以自己随意写一点数据做测试,下面是数据库sql语句,自行下载
链接:https://pan.baidu.com/s/1s9s5lIv58KWbiYTt7Cc42g
提取码:cctc
二、上代码(web项目)
前端代码,小编使用的是jsp,使用时要看清
index.jsp
<%@ page contentType="text/html;charset=UTF-8" language="java" isELIgnored="true" %> <html> <head> <title>级联查询 ajax</title> <script src="/select/js/jquery.js"></script> </head> <body> <table border="1" cellspacing="0" cellpadding="0"> <tr> <td> <span>省:</span><select id="province"> <option value="请选择···">请选择···</option> </select> </td> <td> <span>市:</span><select id="city"> <option value="请选择···">请选择···</option> </select> </td> <td> <span>县(区):</span><select id="area"> <option value="请选择···">请选择···</option> </select> </td> </tr> <tr> <td colspan="3"><textarea cols="30" rows="10" placeholder="输入详细地址" style="resize: none"></textarea></td> </tr> </table> <script> $(function () { //相当于window.load() 最后加载 //加载省会 $.ajax({ type: "post", url: "/province", dataType: "json",//从 /province 以json接收数据 success: function (e) { $.each(e, function (i, v) {//循环接到的值 $('#province').append(`<option value=${v.provinceID}>${v.province}</option>`); }); //加载城市 $('#province').change(function () {//当省会发生修改时,调用 let provinceID = $('#province>option:selected').val();//获取所选省会的id if (provinceID == "710000" || provinceID == "810000" || provinceID == "820000") {//香港、澳门、台湾 $('#city').empty().append('<option value=""></option>'); $('#area').empty().append('<option value=""></option>'); } else { $('#city').empty().append('<option value="请选择···">请选择···</option>');//清空之前选择省会后加载的城市,也就是重置 $('#area').empty().append('<option value="请选择···">请选择···</option>');//重置 $.ajax({ type: "post", url: "/city", data: `provinceID=${provinceID}`,//将省会id传到 /city 里 dataType: "json",//从 /city 以json接收数据 success: function (e) { $.each(e, function (i, v) { $('#city').append(`<option value=${v.cityID}>${v.city}</option>`); }) //加载区、县 $('#city').change(function () { $('#area').empty().append('<option value="请选择···">请选择···</option>');//重置 let cityID = $('#city>option:selected').val();//获取所选的城市id $.ajax({ type: "post", url: "/area", data: `cityID=${cityID}`, dataType: "json", success: function (e) { $.each(e, function (i, v) { $('#area').append(`<option value=${v.areaID}>${v.area}</option>`); }) } }) }) } }) } }) } }) }); </script> </body> </html>
后端代码
Province_db.java
package pers.select; import com.alibaba.fastjson.JSON; import pers.dbutils.DbUtils; 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.io.PrintWriter; import java.util.List; import java.util.Map; /** * TODO 从数据库获取省会 * * @author netyts@163.com * @date 2020/12/7 9:42 */ @WebServlet("/province") public class Province_db extends HttpServlet { @Override protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); resp.setCharacterEncoding("utf-8"); resp.setContentType("text/html;charset=utf-8"); PrintWriter out = resp.getWriter(); //此处是我自己封装的工具类(你可以使用自己的也可以使用本人提供的) DbUtils du = new DbUtils("select_demo"); //调用查询语句,返回List<Map<String,Object>> 集合 List<Map<String, Object>> list = du.select("select id,provinceID,province from province order by id"); //转换为json格式 out.println(JSON.toJSON(list)); out.flush(); out.close(); } }
City_db.java
package pers.select; import com.alibaba.fastjson.JSON; import pers.dbutils.DbUtils; 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.io.PrintWriter; import java.util.List; import java.util.Map; /** * TODO 根据省会id从数据库获取城市 * * @author netyts@163.com * @date 2020/12/7 10:35 */ @WebServlet("/city") public class City_db extends HttpServlet { @Override protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); resp.setCharacterEncoding("utf-8"); resp.setContentType("text/html;charset=utf-8"); PrintWriter out = resp.getWriter(); //拿到省会id String provinceID = req.getParameter("provinceID"); //调用工具类 DbUtils du = new DbUtils("select_demo"); //调用查询语句 跟据省会id查询,返回List<Map<String,Object>> 集合 List<Map<String, Object>> list = du.select("select id,cityID,city from city where father = ? order by id",provinceID); //转换为json格式 out.println(JSON.toJSON(list)); out.flush(); out.close(); } }
Area_db.java
package pers.select; import com.alibaba.fastjson.JSON; import pers.dbutils.DbUtils; 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.io.PrintWriter; import java.util.List; import java.util.Map; /** * TODO 根据城市id从数据库获取区、县 * * @author netyts@163.com * @date 2020/12/7 10:35 */ @WebServlet("/area") public class Area_db extends HttpServlet { @Override protected void service(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException { req.setCharacterEncoding("utf-8"); resp.setCharacterEncoding("utf-8"); resp.setContentType("text/html;charset=utf-8"); PrintWriter out = resp.getWriter(); //拿到城市id String cityID = req.getParameter("cityID"); //调用工具类 DbUtils du = new DbUtils("select_demo"); //调用查询语句 跟据城市id查询,返回List<Map<String,Object>> 集合 List<Map<String, Object>> list = du.select("select id,areaID,area from area where father = ? order by id",cityID); //转换为json格式 out.println(JSON.toJSON(list)); out.flush(); out.close(); } }