一个mybatis查询排序问题

Question

前端传递排序的字段（对应的是实体的属性，与数据库实际字段不一样的）及排序方式，后端如何动态处理映射成对应数据库字段查询，mybatis逆向工程生成的查询

Answer 1

弄了套解决方案：

1、applicationContext配置bean

<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactory" />
</bean>

2、SqlSessionTemplateUtils.java

import org.apache.ibatis.mapping.ResultMap;
import org.apache.ibatis.mapping.ResultMapping;
import org.mybatis.spring.SqlSessionTemplate;

public class SqlSessionTemplateUtils {

    /**
     * 获取指定mapper定义好的映射关系
     * 
     * @param sqlSessionTemplate
     * @param clazzName
     * @return
     */
    public static ResultMap getBaseResultMap(SqlSessionTemplate sqlSessionTemplate, String clazzName) {
        assert sqlSessionTemplate != null;
        return sqlSessionTemplate.getConfiguration().getResultMap(clazzName + ".BaseResultMap");
    }


    /**
     * 根据实体类的属性名获取对应的数据表的字段列名
     *
     * @param property 属性名
     * @param clazz    实体类
     * @return 字段名
     */
    public static String property2Field(SqlSessionTemplate sqlSessionTemplate, String property, Class<?> clazz) {
        ResultMap resultMap = getBaseResultMap(sqlSessionTemplate, clazz.getName());
        if (resultMap != null) {
            for (ResultMapping resultMapping : resultMap.getPropertyResultMappings()) {
                if (resultMapping.getProperty().equals(property)) {
                    property = resultMapping.getColumn();
                    return property;
                }
            }
        }
        //没有找到映射信息，将使用驼峰命名法转换下划线属性名
        return camelToUnderline(property, false);
    }

    private static final String UNDERLINE = "_";
    /**
     * 驼峰转下划线
     *
     * @param param     字符串
     * @param upperCase 是否全大写
     * @return 结果
     */
    public static String camelToUnderline(String param, boolean upperCase) {
        if (param == null || "".equals(param.trim())) {
            return "";
        }
        int len = param.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = param.charAt(i);
            if (Character.isUpperCase(c)) {
                sb.append(UNDERLINE);
            }
            if (upperCase) {
                //统一都转大写
                sb.append(Character.toUpperCase(c));
            } else {
                //统一都转小写
                sb.append(Character.toLowerCase(c));
            }
        }
        return sb.toString();
    }


}

3、在serviceImpl：

@Resource(name = "sqlSessionTemplate")
private SqlSessionTemplate sqlSessionTemplate;


@Override
public PageResult<Commodity> getCommodityStoreListByCondition(PageResult<Commodity> page) {
    CommodityExample example = new CommodityExample();
    if (StringUtils.isNotEmpty(page.getOrderBy())) {
        String[] orderBy = page.getOrderBy().split(" ");  // DESC
        String field = SqlSessionTemplateUtils.property2Field(sqlSessionTemplate, orderBy[0].trim(), CommodityMapper.class);
        if ("zomore_commodity_in".equals(field) || "zomore_commodity_out".equals(field) || "zomore_commodity_outall".equals(field)) {
            field = "cast(" + field + " as DECIMAL(10, 2))";
        }
        if (dd.length > 1) {
            field = field + " " + orderBy[1];
        }
        example.setOrderByClause(field);
    }
    ...
}

跑通了，请教一下会不会有什么毛病？？？