Mybaits使用SQL拦截器实现字符串修剪

概述

• 一般情况下，保存到数据库中的字符串类型的数据，我们一般都不希望它前后带着空格，类似于" 哈哈哈 "。
• 在业务中，如果每一个保存到数据库中的SQL都去对字符串参数进行trim的操作，这是很繁琐的，且容易漏掉。

解决方案

• 使用Mybatis的拦截器，拦截每一个SQL，针对SQL中的字符串参数进行trim操作就OK了。

代码

@Component
@Intercepts({@Signature(type = ParameterHandler.class, method = "setParameters", args = {PreparedStatement.class})})
public class TrimSQLParamsInterceptor implements Interceptor {
    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        ParameterHandler handler = (ParameterHandler) invocation.getTarget();
        MetaObject metaObjectParamsHandler = SystemMetaObject.forObject(invocation.getTarget());
        SqlCommandType commandType = (SqlCommandType) metaObjectParamsHandler.getValue("sqlCommandType");
        if (commandType == SqlCommandType.INSERT || commandType == SqlCommandType.UPDATE) {
            BoundSql boundSql = (BoundSql) metaObjectParamsHandler.getValue("boundSql");
            TypeHandlerRegistry typeHandlerRegistry = (TypeHandlerRegistry) metaObjectParamsHandler.getValue("typeHandlerRegistry");
            List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            if (parameterMappings != null) {
                for (ParameterMapping parameterMapping : parameterMappings) {
                    if (parameterMapping.getMode() != ParameterMode.OUT) {
                        Object value;
                        String propertyName = parameterMapping.getProperty();
                        if (boundSql.hasAdditionalParameter(propertyName)) {
                            value = boundSql.getAdditionalParameter(propertyName);
                            if (value instanceof String) {
                                metaObjectParamsHandler.setValue("boundSql.additionalParameters." + propertyName, ((String) value).trim());
                            }
                        } else if (handler.getParameterObject() == null) {
                            value = null;
                        } else if (typeHandlerRegistry.hasTypeHandler(handler.getParameterObject().getClass())) {
                            //如果字段的处理器时自定义处理器
                            value = handler.getParameterObject();
                            throw new RuntimeException("SQL拦截器出现了未知类型，请联系信息部解决该问题。");
                        } else {
                            //最后会执行此处的代码，也就是说从getAdditionalParameter中找不到对应的参数时
                            value = metaObjectParamsHandler.getValue("parameterObject." + propertyName);
                            if (value instanceof String) {
                                metaObjectParamsHandler.setValue("parameterObject." + propertyName, ((String) value).trim());
                            }
                        }
                    }
                }
            }
        }
        return invocation.proceed();
    }
}

• 上面的代码逻辑来自ParameterHandler类中的setParameters函数:

public void setParameters(PreparedStatement ps) {
        ErrorContext.instance().activity("setting parameters").object(this.mappedStatement.getParameterMap().getId());
        List<ParameterMapping> parameterMappings = this.boundSql.getParameterMappings();
        if (parameterMappings != null) {
            for (int i = 0; i < parameterMappings.size(); i++) {
                ParameterMapping parameterMapping = parameterMappings.get(i);
                if (parameterMapping.getMode() != ParameterMode.OUT) {
                    Object value;
                    String propertyName = parameterMapping.getProperty();
                    if (this.boundSql.hasAdditionalParameter(propertyName)) { // issue #448 ask first for additional params
                        value = this.boundSql.getAdditionalParameter(propertyName);
                    } else if (this.parameterObject == null) {
                        value = null;
                    } else if (this.typeHandlerRegistry.hasTypeHandler(this.parameterObject.getClass())) {
                        value = parameterObject;
                    } else {
                        MetaObject metaObject = this.configuration.newMetaObject(this.parameterObject);
                        value = metaObject.getValue(propertyName);
                    }
                    TypeHandler typeHandler = parameterMapping.getTypeHandler();
                    JdbcType jdbcType = parameterMapping.getJdbcType();
                    if (value == null && jdbcType == null) {
                        jdbcType = this.configuration.getJdbcTypeForNull();
                    }
                    try {
                        typeHandler.setParameter(ps, i + 1, value, jdbcType);
                    } catch (TypeException | SQLException e) {
                        throw new TypeException("Could not set parameters for mapping: " + parameterMapping + ". Cause: " + e, e);
                    }
                }
            }
        }
    }