JavaEE项目bug修复记——一场由特殊空字符（160号ASCII码）引发的血案

我负责维护的一个JavaEE项目中有这样一个功能：可以用poi批量导入Excel表中的学号并在该系统中创建对应的用户。上周三中午，接到用户反馈，将Excel导入系统后用其中的用户名出现了无法登陆提示用户名或密码错误的提示。

第一次问题分析：

在测试环境中导入该Excel表格后，发现数据库用户表中确实出现了该用户名，但仔细观察，其前后都有空格。看来问题的故障定位在于从Excel表中按数值型读出数据失败抛出异常后，数据被当做String类型直接写入了数据库。这样，当用户上传的Excel表中用户名字段前后包含空格时，直接被写入了数据库中。而当用户通过页面输入用户名密码登录时，用户名中的空格却被忽略掉了，导致后台按前端传入的用户名查询时数据库返回无此记录从而登录失败。

第一次修复尝试：

既然直到了问题的症结，那修复起来也就清晰了。具体思路就是利用MySQL提供的一些函数完成表中指定字段的空格去除，搜索得知有trim和replace两种方法。首先在导入了存在问题Excel表的测试环境中：

• 备份数据库：由于要对数据库进行改动，所以在之前一定要进行备份。

  mysqldump -u 用户名 -p 数据库名 > 路径/文件名.sql

• 尝试去除user表中Login字段的前后空格：

  UPDATE user SET Login=trim(Login);
  UPDATE user SET Login=replace(Login, ' ', '');

  以上两种尝试都以失败告终，最终结果显示：0 rows affected。分析多半出现在正常用户名前后的不是真正的空格，而是一些显示不出的ASCII码，为了方便探查，这里就用JDBC来查询吧。

第二次原因分析

在测试环境中，编写一段JDBC程序对SQL中某行有问题的数据读出，并逐字符转换为ASCII码对应的数字进行检查。在导入了MySQL的JDBC驱动jar包后，相关代码如下：

import java.sql.*;

public class CBugFind {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        String dbUrl = "jdbc:mysql://localhost:端口/数据库名?useSSL=true";
        String dbUserName = "root";
        String dbPassword = "这里是密码";
        Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);

        Statement stat = con.createStatement();

        String sql = "SELECT * FROM user WHERE Id=78";  //78号用户的用户名恰有异常
        ResultSet rs = stat.executeQuery(sql);

        String strLogin = null;
        String strNumber = null;

        while (rs.next()) {
            strLogin = rs.getString("Login");
            for (int i = 0; i < strLogin.length(); i++) {
                System.out.println((int)strLogin.charAt(i));
            }        
        }

        rs.close();
        stat.close(); 
        con.close();

    }
}

运行后，部分结果如下：

160
50
48
49
55
48
48
48
50
48
49
48
49
50
160
32

看到了么，就是这个十进制为160的数在捣鬼，它又是何方神圣呢？其实是故人，就是HTML里常用的空格而已。在明白这一点后，我们就可以尝试将其去掉了。然而很不幸，经过多次尝试，也无法使用trim或replace成功将这个160号空格干掉。索性那就还是用Java吧：

public class CBugFixDemo1 {
    public static void main(String[] args) {
        String strUser = " 2017010801015 ";
        strUser = " 2017000201012  ";
        showAllChar(strUser);


        System.out.println("-------------------------------");
        // This is round fix1
        strUser = strUser.replaceAll("\\u00A0","");
        showAllChar(strUser);

        System.out.println("-------------------------------");
        // This is round fix2
        strUser = strUser.trim();
        showAllChar(strUser);

        System.out.println("-------------------------------");
        System.out.println(strUser);

    }

    private static void showAllChar(String strNum) {
        for (int i = 0; i < strNum.length(); i++) {
            System.out.println((int)strNum.charAt(i));
        }
    }
}

经过测试，使用这段代码能成功地去除字符串前后的空格。所以下一步就是将这段代码移植到JDBC的操作中。用SQL查了一下数据量，在近2000的水平，一次读出应该是没有问题的。

import java.sql.*;

public class CJDBCFix {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.jdbc.Driver");

        String dbUrl = "jdbc:mysql://localhost:3306/aptat?useSSL=true";
        String dbUserName = "root";
        String dbPassword = "密码";
        Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);

        Statement stat = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

        String sql = "SELECT * FROM user";
        ResultSet rs = stat.executeQuery(sql);

        String strLogin = null;
        String strNumber = null;
        while (rs.next()) {     
            strLogin = rs.getString("Login");
            strNumber = rs.getString("Number");
            strLogin = strLogin.replaceAll("\\u00A0","");
            strNumber = strNumber.replaceAll("\\u00A0","");
            rs.updateString("Login", strLogin);
            rs.updateString("Number", strNumber);
            rs.updateRow();
            System.out.println(strLogin + "已处理 & " + strNumber);
        }

        rs.close();
        stat.close(); 
        con.close(); 
    }
}

在测试环境中运行这段代码后，再检查数据库，发现原本有问题的用户名立即恢复正常了。于是打了个jar包上传到生产环境上，在备份了数据库后一运行，结果傻眼了。看抛出的异常，意思是违反了唯一性约束。第一反应就是想到在可能有同学因为发现账号无法登录，所以在我修复bug时已经手工用自己的学号注册了一个新账号，这样在我修复时尝试将修正后的账号写入数据库时，就会因为与其手动注册的账号重复而抛出异常。在直到了这点后，在while里加一个异常处理语句块，并在发证异常后继续循环的下一层执行即可。运行后，数据库中已存在问题的用户名被修复了。

临时解决方案

由于修复bug、进行严格单元测试的过程需要一定的时间，但在这段时间内我们又不能关闭相应功能，毕竟最近刚开学，老师们都赶着开课呢！虽然我们一再向老师们强调使用Excel批量导入学生时，一定要按使用指南上的说明操作，但依然不可能完全杜绝因为各种原因赵成的疏忽和遗漏。为此，想出这么一个办法，创建一个特殊的JSP文件，其中就封装了上面那段代码，当用户指定访问其后，即自动执行，完成对数据库中用户名字段的归一化。当导入了带空格用户名后，使用其URL进行访问，则将有系统自动完成归一化。

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8" import="java.sql.*"%>
<!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>aptat-批量导入中空格问题的归一化修复</title>
</head>
<body>
<%
    try {
        Class.forName("com.mysql.jdbc.Driver");

        String dbUrl = "jdbc:mysql://localhost:端口/用户名?useSSL=true";
        String dbUserName = "root";
        String dbPassword = "密码";
        Connection con = DriverManager.getConnection(dbUrl, dbUserName, dbPassword);

        Statement stat = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

        String sql = "SELECT * FROM user";
        ResultSet rs = stat.executeQuery(sql);

        String strLogin = null;
        String strNumber = null;

        while (rs.next()) {

            try {
                strLogin = rs.getString("Login");
                 strNumber = rs.getString("Number");
                 strLogin = strLogin.replaceAll("\\u00A0","").trim();
                 strNumber = strNumber.replaceAll("\\u00A0","").trim();
                 rs.updateString("Login", strLogin);
                 rs.updateString("Number", strNumber);
                 rs.updateRow();
                 //out.println(strLogin + "已处理 & " + strNumber + "<br />");
            } catch (Exception e) {
                System.out.println(strLogin + " error");
            }

        }
        out.println("已成功完成用户名的归一化" + "<br />");
        rs.close();
        stat.close();
        con.close();
    } catch (Exception e) {
        out.println("在执行格式归一化过程中出现问题，请联系系统管理员");
    }
%>
</body>
</html>

最终解决方案

解决方案：

• 一概使用String格式读出表格中的数据

• 忽略字符串前后的空格（包含160号空格和32号空格）

• 对字符串从头到位按char检查，只允许出现数字或字母

• 如果去除头尾部空格的字符串包含其他非法字符，立即抛出异常，拒绝继续执行。

此外，将前后包含空格的字符串写入单元测试用例中，包含160号空格和32号空格。

修改ExcelUtils.java文件：

    /**
     * 函数: checkUserName
     * 功能: 判断传入字符串中是否仅含数字或字母
     * 参数: String strUserName,传入的字符串
     * 返回: boolean类型,true表示仅含数字和字母,false表示含有除数字和字母外的其他字符
     * 作者: famous214
     * 历史: 2017.09.24 10:48 famous214 第一版
     */
    private static boolean checkUserName(String strUserName) {
        // 逐字符检查,挨个判断是否为字母或是否为数字
        for (int i = 0; i < strUserName.length(); i++) {
            char c = strUserName.charAt(i);
            boolean b1 = ((int)c >= (int)'a' && (int)c <= (int)'z') || ((int)c >= (int)'A' && (int)c <= (int)'Z');
            boolean b2 = Character.isDigit(c);
            if (!b1 && !b2) {
                return false;
            }
        }
        return true;
     }

    /**
     * 函数: trimUserName
     * 功能: 去除传入字符串前后的空格,包括32号空格和160号空格
     * 参数: String str,传入的字符串
     * 返回: String类型,去除首尾空格后的字符串
     * 作者: famous214
     * 历史: 2017.09.24 10:50 famous214 第一版
     */
    private static String trimUserName(String str) {
        // 替换掉用户名中的160号空格（通常由网页产生）和首尾的常规空格
        String s = str.replaceAll("\\u00A0","").trim();
        return s;
    }

    /**
     * 函数: readExcel
     * 功能: 读取Excel97文件,批量注册学生.注册用户名为学号,密码为12345678
     * 参数: String fileName,Excel97文件的路径
     * 返回: ArrayList<User>类型
     * 作者: 不详
     * 历史: 2011-2012 不详 第一版
     *       2017.09.05 23:12 famous214 第二版:解决了学号不能按字符串读出的问题(2017.09.22补  BUG:未对字符串进行校验,导致教师不按文档规定格式上传Excel表后出现用户名前有空格的情况)
     *       2017.09.24 10:55 famous214 第三版:增加了按字符串读出学号后的校验过程,去除首尾空格并只允许有数字和字母出现
     */
    public static ArrayList<User> readExcel(String fileName) throws Exception {

        File file = new File(fileName);
        FileInputStream in = null;
        try {
            ArrayList<User> students = new ArrayList<User>();
            // 创建对Excel工作簿文件的引用
            in = new FileInputStream(file);
            HSSFWorkbook workbook = new HSSFWorkbook(in);

            // 创建对工作表的引用。
            // 这里使用按名引用
            // HSSFSheet sheet = workbook.getSheet("Test_Table");
            HSSFSheet sheet = workbook.getSheetAt(0);

            HSSFRow row = null;
            int rowNum = 0;// 行标

            for (; rowNum < sheet.getLastRowNum() + 1; rowNum++) {
                row = sheet.getRow(rowNum);
                if (row != null) {


                    // 读出该行学生的姓名
                    Cell nameCell = row.getCell(0);
                    if (nameCell == null)
                        continue;

                    String strName = nameCell.getStringCellValue();


                    Cell numberCell = row.getCell(1);
                    if (numberCell == null)
                        continue;

                    // famous214-2017.09.24 注:按数值型无法读取就按字符串读取是有问题的,字符串需要经过校验,且要排除单元格格式为其他格式.
                        // 2017.09.05原来的代码:user.setNumber(String.valueOf((long) numberCell.getNumericCellValue()));
                        // famous214-20170905 临时补救措施,防止Excel导入失败
                    // 2017.09.24如果按常规方式导入未出现异常则pass,如果出现异常则按字符串导入Excel表数据
                    //try {
                    //  user.setNumber(String.valueOf((long) numberCell.getNumericCellValue()));
                    //} catch (Exception e) {
                    //  user.setNumber(numberCell.getStringCellValue());
                    //}

                    String strNum = null;
                    // 数值型和字符串型分别处理,其余类型概不接受,报错返回
                    if (numberCell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
                        strNum = String.valueOf((long) numberCell.getNumericCellValue());
                    }
                    else if (numberCell.getCellType() == Cell.CELL_TYPE_STRING) {
                        strNum = numberCell.getStringCellValue();
                    }
                    else {
                        continue;
                    }
                    // 若有学号为空的列,报错返回
                    if (strNum == null || strNum.isEmpty()) {
                        throw new Exception("在表格的第" + rowNum + "行学号为空");
                    }
                    String stuNum = trimUserName(strNum);
                    if (checkUserName(stuNum)) {
                        User user = new User();
                        user.setName(strName);
                        user.setNumber(stuNum);

                        students.add(user);
                    }
                    else {
                        throw new Exception("在表格的第" + rowNum + "行学号中存在非法字符");
                    }                           
                }
            }
            in.close();
            return students;
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        } finally {
            if (in != null) {
                try {
                    in.close();
                } catch (IOException e1) {
                }
            }
        }
    }

注意：Java中的isLetter方法由于char是Unicode类型，所以即便是传入中文也返回true。