SpringBoot中的SQL异常美化捕获

本人目前所用捕获较为粗糙，欢迎大佬们修改。
首先用全局拦截器拦截

@Slf4j
@RestControllerAdvice
public class GlobalException {
  
    /**
     * 处理数据完整性违规异常
     * @param e DataIntegrityViolationException
     * @return 统一的JSON错误结果
     */
    @ExceptionHandler(DataIntegrityViolationException.class)
    public Result<Void> handleDataIntegrityViolationException(DataIntegrityViolationException e) {
        log.error("数据完整性异常: {}", e.getMessage(), e);
        String friendlyMessage = SqlExceptionBeautifier.beautify(e);
        return Result.failure(friendlyMessage);
    }

    /**
     * 处理SQL语法异常
     * @param e BadSqlGrammarException
     * @return 统一的JSON错误结果
     */
    @ExceptionHandler(BadSqlGrammarException.class)
    public Result<Void> handleBadSqlGrammarException(BadSqlGrammarException e) {
        log.error("SQL语法异常: {}", e.getMessage(), e);
        String friendlyMessage = SqlExceptionBeautifier.beautify(e);
        return Result.failure(friendlyMessage);
    }

    /**
     * 处理通用数据库访问异常
     * @param e DataAccessException
     * @return 统一的JSON错误结果
     */
    @ExceptionHandler(DataAccessException.class)
    public Result<Void> handleDataAccessException(DataAccessException e) {
        log.error("数据访问异常: {}", e.getMessage(), e);
        String friendlyMessage = SqlExceptionBeautifier.beautify(e);
        return Result.failure( friendlyMessage);
    }

    /**
     * 处理原生SQL异常
     * @param e SQLException
     * @return 统一的JSON错误结果
     */
    @ExceptionHandler(SQLException.class)
    public Result<Void> handleSQLException(SQLException e) {
        log.error("SQL异常: {}", e.getMessage(), e);
        String friendlyMessage = SqlExceptionBeautifier.beautify(e);
        return Result.failure(friendlyMessage);
    }
}

然后定义专门的异常美化器

/**
 * SqlExceptionBeautifier
 *
 * <p>本工具类用于将各种层级的 SQL 异常（从最具体的业务约束、语法错误，到最通用的 JDBC 异常）按“从小到大”顺序捕获并
 * 转换为可国际化展示的用户友好提示。</p>
 *
 * <h3>捕获顺序（从小到大）</h3>
 * <ol>
 *   <li>DataIntegrityViolationException — 数据完整性约束（主键冲突、非空、长度等）</li>
 *   <li>BadSqlGrammarException   — Spring 封装的 SQL 语法/表列不存在异常</li>
 *   <li>InvalidDataAccessResourceUsageException — 资源使用（表/视图未找到等）</li>
 *   <li>SQLDataException          — JDBC 数据类型转换错误（日期/数字格式）</li>
 *   <li>SQLSyntaxErrorException   — JDBC 语法错误</li>
 *   <li>DataAccessException       — Spring 更通用的 DataAccessException</li>
 *   <li>SQLException              — JDBC 更通用的 SQLException</li>
 *   <li>Throwable.getCause()      — 递归查找底层 SQLException/DataAccessException</li>
 * </ol>
 *
 * <p>每一级都会尽可能提取 MySQL 或标准 SQLState 信息，并调用 i18n.MessageUtils
 * 获得国际化消息键。</p>
 */
@Slf4j
public class SqlExceptionBeautifier {

    private static final Pattern DATE_TIME_PATTERN =
            Pattern.compile(
                    "Cannot convert string '([^']*)' to java\\.time\\.(LocalDateTime|LocalDate|LocalTime) value");
    private static final Pattern NUMERIC_PATTERN =
            Pattern.compile("Cannot convert string '([^']*)' to numeric");
    private static final Pattern MYSQL_TABLE_NOT_FOUND_PATTERN =
            Pattern.compile("Table '([^']*)' doesn't exist");
    private static final Pattern MYSQL_UNKNOWN_COLUMN_PATTERN =
            Pattern.compile("Unknown column '([^']*)' in");
    private static final Pattern MYSQL_UNKNOWN_TABLE_PATTERN =
            Pattern.compile("Unknown table '([^']*)' in");

    public static String beautify(Exception e) {
        if (e == null) {
            return MessageUtils.getMessage("sql.error.default");
        }

        log.debug("美化SQL异常: {}", e.getMessage(), e);

        // 1. 数据完整性违规
        if (e instanceof DataIntegrityViolationException) {
            return handleDataIntegrity((DataIntegrityViolationException) e);
        }
        // 2. Spring 语法/表列不存在
        if (e instanceof BadSqlGrammarException) {
            return handleBadSqlGrammar((BadSqlGrammarException) e);
        }
        // 3. Spring 资源使用异常
        if (e instanceof InvalidDataAccessResourceUsageException) {
            return handleInvalidResource((InvalidDataAccessResourceUsageException) e);
        }
        // 4. JDBC 数据转换错误
        if (e instanceof SQLDataException) {
            return handleSqlData((SQLDataException) e);
        }
        // 5. JDBC 语法错误
        if (e instanceof SQLSyntaxErrorException) {
            return handleSqlSyntax((SQLSyntaxErrorException) e);
        }
        // 6. 更通用的 Spring 数据访问异常
        if (e instanceof DataAccessException) {
            return handleDataAccess((DataAccessException) e);
        }
        // 7. 更通用的 JDBC 异常
        if (e instanceof SQLException) {
            return handleSqlException((SQLException) e);
        }
        // 8. 兜底
        return MessageUtils.getMessage("sql.error.default");
    }

    private static String handleDataIntegrity(DataIntegrityViolationException e) {
        String msg = e.getMessage();
        if (msg.contains("Duplicate entry")) {
            return MessageUtils.getMessage("sql.error.duplicate");
        }
        if (msg.contains("cannot be null")) {
            return MessageUtils.getMessage("sql.error.not.null");
        }
        if (msg.contains("Data too long")) {
            return MessageUtils.getMessage("sql.error.data.too.long");
        }
        if (msg.contains("column") && msg.contains("result set")) {
            return MessageUtils.getMessage("sql.error.result.set");
        }
        Throwable cause = e.getCause();
        if (cause instanceof SQLException) {
            return handleSqlException((SQLException) cause);
        }
        return MessageUtils.getMessage("sql.error.data.integrity");
    }

    private static String handleBadSqlGrammar(BadSqlGrammarException e) {
        SQLException sqlEx = e.getSQLException();
        if (sqlEx != null) {
            String msg = sqlEx.getMessage();
            Matcher m;
            m = MYSQL_TABLE_NOT_FOUND_PATTERN.matcher(msg);
            if (m.find()) {
                return MessageUtils.getMessage("sql.error.table.not.found", m.group(1));
            }
            m = MYSQL_UNKNOWN_COLUMN_PATTERN.matcher(msg);
            if (m.find()) {
                return MessageUtils.getMessage("sql.error.column.not.found", m.group(1));
            }
            m = MYSQL_UNKNOWN_TABLE_PATTERN.matcher(msg);
            if (m.find()) {
                return MessageUtils.getMessage("sql.error.table.not.found", m.group(1));
            }
            if (sqlEx instanceof SQLSyntaxErrorException) {
                return handleSqlSyntax((SQLSyntaxErrorException) sqlEx);
            }
        }
        String badSql = e.getSql();
        if (badSql != null && !badSql.trim().isEmpty()) {
            log.debug("Bad SQL: {}", badSql);
        }
        return MessageUtils.getMessage("sql.error.bad.grammar");
    }

    private static String handleInvalidResource(InvalidDataAccessResourceUsageException e) {
        Throwable cause = e.getCause();
        if (cause instanceof SQLException) {
            return handleSqlException((SQLException) cause);
        }
        String msg = e.getMessage();
        Matcher m = MYSQL_TABLE_NOT_FOUND_PATTERN.matcher(msg);
        if (m.find()) {
            return MessageUtils.getMessage("sql.error.table.not.found", m.group(1));
        }
        return MessageUtils.getMessage("sql.error.resource.usage");
    }

    private static String handleSqlData(SQLDataException e) {
        String msg = e.getMessage();
        Matcher m = DATE_TIME_PATTERN.matcher(msg);
        if (m.find()) {
            String val = m.group(1), type = m.group(2);
            switch (type) {
                case "LocalDateTime":
                    return MessageUtils.getMessage("sql.error.datetime.format", val);
                case "LocalDate":
                    return MessageUtils.getMessage("sql.error.date.format", val);
                case "LocalTime":
                    return MessageUtils.getMessage("sql.error.time.format", val);
            }
        }
        m = NUMERIC_PATTERN.matcher(msg);
        if (m.find()) {
            return MessageUtils.getMessage("sql.error.numeric.format", m.group(1));
        }
        if (msg.contains("Cannot parse")) {
            return MessageUtils.getMessage("sql.error.parse.failed");
        }
        return MessageUtils.getMessage("sql.error.datatype");
    }

    private static String handleSqlSyntax(SQLSyntaxErrorException e) {
        String msg = e.getMessage();
        Matcher m;
        m = MYSQL_TABLE_NOT_FOUND_PATTERN.matcher(msg);
        if (m.find()) {
            return MessageUtils.getMessage("sql.error.table.not.found", m.group(1));
        }
        m = MYSQL_UNKNOWN_COLUMN_PATTERN.matcher(msg);
        if (m.find()) {
            return MessageUtils.getMessage("sql.error.column.not.found", m.group(1));
        }
        m = MYSQL_UNKNOWN_TABLE_PATTERN.matcher(msg);
        if (m.find()) {
            return MessageUtils.getMessage("sql.error.table.not.found", m.group(1));
        }
        return MessageUtils.getMessage("sql.error.syntax");
    }

    private static String handleDataAccess(DataAccessException e) {
        if (e instanceof DataIntegrityViolationException) {
            return handleDataIntegrity((DataIntegrityViolationException) e);
        }
        if (e instanceof BadSqlGrammarException) {
            return handleBadSqlGrammar((BadSqlGrammarException) e);
        }
        if (e instanceof InvalidDataAccessResourceUsageException) {
            return handleInvalidResource((InvalidDataAccessResourceUsageException) e);
        }
        Throwable cause = e.getCause();
        if (cause instanceof SQLException) {
            return handleSqlException((SQLException) cause);
        }
        return MessageUtils.getMessage("sql.error.data.integrity");
    }

    private static String handleSqlException(SQLException e) {
        if (e instanceof SQLDataException) {
            return handleSqlData((SQLDataException) e);
        }
        if (e instanceof SQLSyntaxErrorException) {
            return handleSqlSyntax((SQLSyntaxErrorException) e);
        }
        String state = e.getSQLState();
        String msg = e.getMessage();
        Matcher m;
        m = MYSQL_TABLE_NOT_FOUND_PATTERN.matcher(msg);
        if (m.find()) {
            return MessageUtils.getMessage("sql.error.table.not.found", m.group(1));
        }
        m = MYSQL_UNKNOWN_COLUMN_PATTERN.matcher(msg);
        if (m.find()) {
            return MessageUtils.getMessage("sql.error.column.not.found", m.group(1));
        }
        if (state != null && state.length() >= 2) {
            switch (state.substring(0, 2)) {
                case "22":
                    return MessageUtils.getMessage("sql.error.datatype");
                case "23":
                    return MessageUtils.getMessage("sql.error.integrity.constraint");
                case "42":
                    return MessageUtils.getMessage("sql.error.syntax");
                case "28":
                    return MessageUtils.getMessage("sql.error.permission");
                case "08":
                    return MessageUtils.getMessage("sql.error.connection");
            }
        }
        if (msg.contains("constraint")) {
            return MessageUtils.getMessage("sql.error.constraint");
        }
        return MessageUtils.getMessage("sql.error.default");
    }
}