半sql多表联查，自己开发通用Mapper

……端午放假闲的发慌来撸代码，自己也是醉了。

1.添加接口，代码如下

MoreTableTableProvider是第二部具体要实现功能的类，method就定义成动态sql就好了。因为传的参数多，需要加上@Param注解，根据功能先如下定义

public interface MoreTableTable<T> {

    /**
     * 半sql多表联查
     * @param record 传入的类的条件
     * @param mainAlias 主表的别名
     * @param otherTables 其它表,需要自带别名，eg： T_SYS_RESOURCE r
     * @param condition 条件，除了record中传入的条件，还可以添加两张表的关联条件，或非主表的条件查询
     * @return
     */
    @SelectProvider(type = MoreTableTableProvider.class, method = "dynamicSQL")
    List<T> selectInfoBy(@Param("entity") T record, @Param("mainAlias") String mainAlias, @Param("otherTables")
            String otherTables, @Param("condition") String condition);

}

2.添加实现类：MoreTableTableProvider，继承MapperTemplate,里面加一个实现方法，方法名称和接口保持一致，参数必填MappedStatement。还要填个构造器，搬过来改类名就行了。

然后代码没什么好说的，注意 # 和 $的使用，因为是多表联查，还有别名，所有我用了 $ 符号。entity是个类，里面的参数获取 用entity.id这样的格式，否则会报找不到参数的。

public class MoreTableTableProvider extends MapperTemplate {
    public MoreTableTableProvider(Class<?> mapperClass, MapperHelper mapperHelper) {
        super(mapperClass, mapperHelper);
    }

    public String selectInfoBy(MappedStatement ms){
        Class<?> entityClass = getEntityClass(ms);
        setResultType(ms, entityClass);
        StringBuilder sql = new StringBuilder();
        sql.append(selectAllColumns(entityClass));// select a.id,a.name...
        sql.append(SqlHelper.fromTable(entityClass, tableName(entityClass)));// select a.id,a.name,b.name...from t_sys_account
        sql.append(" ".concat("${mainAlias}").concat(" ")); // select a.id,a.name,b.name...from t_sys_account a
        sql.append(fromOtherTables()); // select a.id,a.name,b.name...from t_sys_account a,t_sys_office b
        sql.append("<where>");
        sql.append(whereAllIfColumns(entityClass, isNotEmpty())); // select a.id,a.name,b.name...from t_sys_account a,t_sys_office b where a.id = ...
        sql.append(whereOtherColumns());
        sql.append("</where>");
        return sql.toString();

    }

    public static String whereOtherColumns() {
        return new StringBuilder().append(" AND ").append("${condition}").toString();
    }
    /**
     * where所有列的条件，会判断是否!=null
     *
     * @param entityClass
     * @return
     */
    public static String whereAllIfColumns(Class<?> entityClass, boolean empty) {
        StringBuilder sql = new StringBuilder();
        //获取全部列
        Set<EntityColumn> columnList = EntityHelper.getColumns(entityClass);
        //当某个列有主键策略时，不需要考虑他的属性是否为空，因为如果为空，一定会根据主键策略给他生成一个值
        for (EntityColumn column : columnList) {
            sql.append(getIfNotNull(column, " AND ${mainAlias}." + column.getColumnEqualsHolder(), empty));
        }
        return sql.toString();
    }

    public static String getIfNotNull(EntityColumn column, String contents, boolean empty) {
        return getIfNotNull("entity", column, contents, empty);
    }

    public static String getIfNotNull(String entityName, EntityColumn column, String contents, boolean empty) {
        StringBuilder sql = new StringBuilder();
        sql.append("<if test=\"");
        if (StringUtil.isNotEmpty(entityName)) {
            sql.append(entityName).append(".");
        }
        sql.append(column.getProperty()).append(" != null");
        if (empty && column.getJavaType().equals(String.class)) {
            sql.append(" and ");
            if (StringUtil.isNotEmpty(entityName)) {
                sql.append(entityName).append(".");
            }
            sql.append(column.getProperty()).append(" != '' ");
        }
        sql.append("\">");
        sql.append(contents);
        sql.append("</if>");
        return sql.toString();
    }

    /**
     * 给查询的参数加上别名：select id,name...  ->  select a.id,a.name...
     */
    public static String selectOtherFields(){
        StringBuilder sql =new StringBuilder();
        sql.append("<if test=\"queryOtherField != null and queryOtherField != ''\">");
        sql.append(",${queryOtherField}");
        sql.append("</if>");
        return  sql.toString();
    }

    /**
     * 给查询的参数加上别名：select id,name...  ->  select a.id,a.name...
     */
    public static String selectAllColumns(Class<?> entityClass){
        return new StringBuilder().append("SELECT ").append(getAllColumns(entityClass)).append(" ").toString();
    }

    public static String getAllColumns(Class<?> entityClass) {
        Set<EntityColumn> columnList = EntityHelper.getColumns(entityClass);
        StringBuilder sql = new StringBuilder();
        for (EntityColumn entityColumn : columnList) {
            sql.append("${mainAlias}.");
            sql.append(entityColumn.getColumn()).append(",");
        }
        return sql.substring(0, sql.length() - 1);
    }

    public static String fromOtherTables() {
        return new StringBuilder().append(" ,${otherTables} ").toString();
    }
}

3.写好之后把第一步的接口放到Mapper下，被继承，然后就可以使用方法啦~

下面是项目中测试的代码

// 注入的mapper 
public void text()throws Exception {
        Client client = new Client();
        Map<String,String> map = new HashedMap();
        map.put("T_SYS_RESOURCES","r");
        List<Client> list = mapper.selectInfoBy(client,"c","T_SYS_RESOURCE r","c.id = r.client_id");
    }