🙋‍♂️ 需求背景

有个需求上的功能点,叫做城市反选。正好有我们这边有一个 Excel,上面是一个传入的模板,大概长这个样子:
Excel.jpeg
什么是城市反选

  1. 当范围是全国的时候
  2. 比如选择一个省,包含关系不属于,则落在数据库中的字段为其他的省份;
  3. 比如选择一个市区,包含关系不属于,则落在数据库中的字段为其他的省份+该省下面的其他市区;
  4. 比如选择一个省 + 一个市区(另外的一个省下面的),包含关系不属于,则落在数据库中的字段为其他的省+该省下面的其他市区。

那我们测试的时候,落入数据库中的字段很多的话,肉眼比对的时候会很痛苦。
基于以上几点,正好是 Excel 的模板,联想到以前的对于 Excel 读写熟悉一点,就开始想着写一个小工具来实现比对。

💻 代码实现

package City_FanXuan;

import java.io.File;
import java.io.FileInputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashSet;
import java.util.Scanner;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import tool_date.sql_Connection;

/*
 * @author Xuan
 * */
public class cities_fanxuan {

	static XSSFWorkbook wb = null;
	static File src = null;
	static FileInputStream fis = null;

	public static String cities_fanxuan(String[] city) {
		String city_id = null;
		String city_name = null;
		String city_type = null;
		String up_city = null;
		int j = 0;
		int i;
		int index;
		HashSet<Integer> arr_sheng = new HashSet<Integer>();
		HashSet<Integer> arr_result = new HashSet<Integer>();
		ArrayList<Integer> list_sheng = new ArrayList<Integer>();
		ArrayList<Integer> list_result = new ArrayList<Integer>();
		// 加载城市ID模板,读取excel中的城市信息
		XSSFSheet sh1 = wb.getSheetAt(0);
		// 入参的字符串将会从流量活动的详情页上面复制
		// 对于入参进行判断,先在B列进行匹配判断,如果匹配到了城市名称,则获取当前一行的四个字段的信息
		for (i = 0; i < city.length; i++) {
			for (j = 1; j < sh1.getPhysicalNumberOfRows(); j++) {
				if (city[i].equals(sh1.getRow(j).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
						.getStringCellValue().trim())) {
					city_id = sh1.getRow(j).getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue()
							.trim();
					city_name = sh1.getRow(j).getCell(1, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
							.getStringCellValue().trim();
					city_type = sh1.getRow(j).getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
							.getStringCellValue().trim();
					up_city = sh1.getRow(j).getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue()
							.trim();
					arr_result.add(Integer.valueOf(city_id));
					break;
				}
			}
			System.out.println("第" + (i + 1) + "个参数选城市ID:" + city_id);
			System.out.println("第" + (i + 1) + "个参数选城市名称:" + city_name);
			if (city_type.equals("0")) {
				// 1. 先判断是否为省/区——判断条件:当前行所在的c列是否为0;如果是省/区,则会记录以省ID开头的城市ID并扣掉,拿出其他省份;
				for (int q = 1; q < sh1.getPhysicalNumberOfRows(); q++) {
					// 抠除当前省份的行数
					if (q != j) {
						// 循环遍历其他行,通过0/1来判断是否为省,如果是,则拿该行的第一列(城市id)放入数组中
						if (Integer.valueOf(sh1.getRow(q).getCell(2, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
								.getStringCellValue().trim()) == 0) {
							arr_sheng.add(
									Integer.valueOf(sh1.getRow(q).getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
											.getStringCellValue().trim()));
						}
					}
				}
			} else {
				System.out.println("我的上级城市是:" + up_city);
				// 2. 当前为市区:
				for (int q = 1; q < sh1.getPhysicalNumberOfRows(); q++) {
					arr_result.add(Integer.valueOf(up_city));
					// 抠除当前市区以及当前市区所在的省份,且只有省的行数下:
					if (!sh1.getRow(q).getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue()
							.trim().equals(city_id)
							&& !sh1.getRow(q).getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
									.getStringCellValue().trim().equals(up_city)
							&& Integer.valueOf(sh1.getRow(q).getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK)
									.getStringCellValue().trim().substring(2, 6)) == 0000) {
						arr_sheng.add(Integer.valueOf(sh1.getRow(q)
								.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
					}
					if (sh1.getRow(q).getCell(3, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim()
							.equals(up_city) && q != j) {
						arr_sheng.add(Integer.valueOf(sh1.getRow(q)
								.getCell(0, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK).getStringCellValue().trim()));
					}
				}
			}
		}
		for (Integer str : arr_sheng) {
			list_sheng.add(str);
		}
		for (Integer str : arr_result) {
			list_result.add(str);
		}
		for (int k = 0; k < list_sheng.size(); k++) {
			if (list_result.contains(list_sheng.get(k))) {
				list_sheng.remove(k);
			}
		}
		System.out.print("本次运行结果:");
		for (index = 0; index < list_sheng.size(); index++) {
			if (index != list_sheng.size() - 1) {
				System.out.print(list_sheng.get(index) + ",");
			}
		}
		return list_sheng.toString();
	}

	// 去数据库查询城市ID
	public static String sql(String a) {
		//避免暴露信息所以**代替
		String sql = "SELECT ** FROM `******` WHERE ****= '" + a + "'";// SQL语句
		sql_Connection db1 = new sql_Connection(sql);// 创建sql_Connection对象
		ResultSet ret = null;
		String city_code = null;
		try {
			ret = db1.pst.executeQuery();// 执行语句,得到结果
			while (ret.next()) {
				city_code = ret.getString(1);
				System.out.println("数据库中的值为:" + city_code);
			} // 显示数据
			ret.close();
			db1.close();// 关闭连接
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return city_code;
	}

	// 运行结果和数据库中的字段进行比对
	public static boolean BiDui(String a, String b) {
		String[] strArr = sql(b).split(",");
		String[] strArr2 = a.replace(" ", "").replace("[", "").replace("]", "").split(",");
		Arrays.sort(strArr);
		Arrays.sort(strArr2);
		boolean result = Arrays.equals(strArr, strArr2);
		System.out.println("本次数据库结果按升序排序:");
		for (int x = 0; x < strArr.length; x++) {
			System.out.print(strArr[x]);
		}
		System.out.println();
		System.out.println("本次运行结果按升序排序:");
		for (int y = 0; y < strArr2.length; y++) {
			System.out.print(strArr2[y]);
		}
		System.out.println();
		System.out.println("本次比对结果为:");
		return result;
	}

	public static void main(String[] args) {
		// TODO Auto-generated method stub
		src = new File("./Files_CityID/CityID.xlsx");
		// 加载文件
		try {
			fis = new FileInputStream(src);
			wb = new XSSFWorkbook(fis);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		long t1 = System.currentTimeMillis();
		Scanner input = new Scanner(System.in);
		System.out.println("请复制详情页面城市字段的值到这儿进行粘贴:");
		String s = input.next();
		System.out.println(BiDui(cities_fanxuan(s.split(",")), "1904160030000510756"));
		long t2 = System.currentTimeMillis();
		System.out.println("大吉大利!请进行下次数据比对!本次用时 " + (t2 - t1) + " 毫秒");
	}
}

还有一个jdbc:

package tool_date;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class sql_Connection {
	public static final String url = "jdbc:mysql://****";
	public static final String name = "com.mysql.jdbc.Driver";
	public static final String user = "*****";
	public static final String password = "****";

	public Connection conn = null;
	public PreparedStatement pst = null;

	public sql_Connection(String sql) {
		try {
			Class.forName(name);// 指定连接类型
			conn = DriverManager.getConnection(url, user, password);// 获取连接
			pst = conn.prepareStatement(sql);// 准备执行语句
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void close() {
		try {
			this.conn.close();
			this.pst.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}

✅ 运行结果

  1. 单个市区:
    D08C1E60A016406C87F17BB4B517CC52.jpeg
  2. 单个省:
    6DAF5D3CDC054BB9A0E2B36E1C700C3B.jpeg
  3. 多个市区:
    6B499AB150F24E46A3358F855762C401.jpeg
  4. 多个省+多个市区:
    BD2C33BD6BF14799967F1114766B5AEC.jpeg
    以上就是实现的功能
    在使用的时候,需要传入两个参数:
    一个 Id 是用来进行 SQL 查询的;
    另外一个是反选的城市名称
    这两个参数都可以在详情页直接复制,拷贝进来(连逗号都不用去,入参已经考虑到这种情况),直接运行,很方便。
    EF0FB81983254777A06FC621CE23164B.jpeg
    在这过程中,遇到了很多的痛苦点,差点把头搞晕。

难点

  1. 只选择省,则只返回其他省份(需要抠掉市区);
  2. 只选择市区,则需要抠掉当前市区对应的省,再拿到该省下其他的市区+其他省;
  3. 选择省+市区,则需要拿到…
    几种情况一累加,稍微有点晕。

📝 总结

当然,目前这个小工具写的很多地方都不完善,比如入参的校验、异常的捕捉等等,但是基本上是可以使用的!,其实有相关的城市的静态代码块,用集合相关的方法来处理,其实比我这种硬用 Excel 读写来判断要简单的多!其实这个工具感觉稍微有一点点鸡肋…但是在这个工具写的过程中,暴露出很多问题:

  1. 对于 Java 基础知识不牢靠;
  2. JDK-API 上面的方法淡忘了;
  3. 做事积极性不高!

待改善的地方:
我这边的范围是全国,如果范围不是全国,可以自己传入参数来设定的范围:
比如几个省+几个市区那么反选的结果是什么。