Jeecg-多表联合查询

需求

Jeecg提供的生成工具满足了最普遍的代码功能，也将代码最简化，但在业务处理中常出现多表联合查询的需求，比如两表条件联合查询，而mybatis-plus框架使用的QueryWrapper显然无法满足，考虑使用其他方式实现。

思路

方式一
controller层与分页参数形式一样进行他表参数添加，使用map形式传参，不影响默认的底层业务；
方式二
对Entity添加字段属性，问题是在生成查询语句时，会自动映射Entity字段为数据库属性导致出错，需要重写原先的查询方法，此时添加注解@TableField(exist = false)即可；

实现

方式一
controller层改装

	@AutoLog(value = "建筑信息-分页列表查询")
    @ApiOperation(value = "建筑信息-分页列表查询", notes = "建筑信息-分页列表查询")
    @GetMapping(value = "/list")
    public Result<?> queryPageList1(
            ProBuildInfo proBuildInfo,
            @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
            @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
            @RequestParam(name = "unitId", defaultValue = "") String unitId,
            HttpServletRequest req) {
        Page<ProBuildInfo> page = new Page<ProBuildInfo>(pageNo, pageSize);
        if (StringUtils.isEmpty(unitId)) {
            QueryWrapper<ProBuildInfo> queryWrapper = QueryGenerator.initQueryWrapper(proBuildInfo, req.getParameterMap());
			IPage<ProBuildInfo> pageList = proBuildInfoService.page(page, queryWrapper);
            return Result.ok(pageList);
        } else {
            Map map = com.alibaba.fastjson.JSONObject.parseObject(com.alibaba.fastjson.JSONObject.toJSONString(proBuildInfo), Map.class);
            map.put("unitId", unitId);
            IPage<ProBuildInfo> pageList = proBuildInfoService.pageByUnitAndType(page, map);
            return Result.ok(pageList);
        }
    }

mapper添加方法

public interface ProBuildInfoMapper extends BaseMapper<ProBuildInfo> {
    IPage<ProBuildInfo> pageByUnitAndType(IPage<ProBuildInfo> page, Map map);
}

xml文件方法映射

	<select id="pageByUnitAndType" parameterType="hashmap" resultType="org.jeecg.modules.xiaoan.entity.ProBuildInfo">
        SELECT pro_build_info.*,pro_build_r_unit.unit_id,pro_build_r_unit.build_r_unit_type
        FROM  pro_build_r_unit
        inner join pro_build_info
        on pro_build_r_unit.unit_id = #{unitId} and pro_build_info.id=pro_build_r_unit.build_id
        <if test="buildRUnitType!=null and buildRUnitType!=''">
            and pro_build_r_unit.build_r_unit_type = #{buildRUnitType}
        </if>
        WHERE 1=1
        <include refid="assembling"/>
    </select>

	<!--检索条件拼装-->
    <sql id="assembling">
        <if test="id!=null and id!=''">
            and pro_build_info.id = #{id}
        </if>
        <if test="code!=null and code!=''">
            and pro_build_info.code = #{code}
        </if>
        <if test="name!=null and name!=''">
            and pro_build_info.name like CONCAT('%',#{name},'%')
        </if>
    </sql>

方式二
controller层改装

	@AutoLog(value = "建筑信息-分页列表查询")
    @ApiOperation(value = "建筑信息-分页列表查询", notes = "建筑信息-分页列表查询")
    @GetMapping(value = "/list")
    public Result<?> queryPageList(ProBuildInfo proBuildInfo,
                                   @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                   @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                                   HttpServletRequest req) {
        Page<ProBuildInfo> page = new Page<ProBuildInfo>(pageNo, pageSize);
        if (StringUtils.isEmpty(proBuildInfo.getUnitId())) {
            IPage<ProBuildInfo> pageList = proBuildInfoService.selectPage(page, proBuildInfo);
            return Result.ok(pageList);
        } else {
            IPage<ProBuildInfo> pageList = proBuildInfoService.pageByUnitAndType(page, proBuildInfo);
            return Result.ok(pageList);
        }
    }

mapper添加方法

public interface ProBuildInfoMapper extends BaseMapper<ProBuildInfo> {
    IPage<ProBuildInfo> selectPage(IPage<ProBuildInfo> page, @Param("proBuildInfo") ProBuildInfo proBuildInfo);
    IPage<ProBuildInfo> pageByUnitAndType(IPage<ProBuildInfo> page, @Param("proBuildInfo") ProBuildInfo proBuildInfo);
}

xml文件方法映射

	<select id="selectPage" parameterType="java.lang.String" resultType="org.jeecg.modules.xiaoan.entity.ProBuildInfo">
        SELECT pro_build_info.*
        FROM  pro_build_info
        WHERE 1=1
        <include refid="assembling"/>
    </select>
	<select id="pageByUnitAndType" parameterType="java.lang.String" resultType="org.jeecg.modules.xiaoan.entity.ProBuildInfo">
        SELECT pro_build_info.*,pro_build_r_unit.unit_id,pro_build_r_unit.build_r_unit_type
        FROM  pro_build_r_unit
        inner join pro_build_info
        on pro_build_r_unit.unit_id = #{proBuildInfo.unitId} and pro_build_info.id=pro_build_r_unit.build_id
        <if test="proBuildInfo.buildRUnitType!=null and proBuildInfo.buildRUnitType!=''">
            and pro_build_r_unit.build_r_unit_type = #{proBuildInfo.buildRUnitType}
        </if>
        WHERE 1=1
        <include refid="assembling"/>
    </select>

	<!--检索条件拼装-->
    <sql id="assembling">
        <if test="proBuildInfo.id!=null and proBuildInfo.id!=''">
            and pro_build_info.id = #{proBuildInfo.id}
        </if>
        <if test="proBuildInfo.code!=null and proBuildInfo.code!=''">
            and pro_build_info.code = #{proBuildInfo.code}
        </if>
        <if test="proBuildInfo.name!=null and proBuildInfo.name!=''">
            and pro_build_info.name like CONCAT('%',#{proBuildInfo.name},'%')
        </if>
    </sql>

补充
两种方式都至少需要提供一个方法从service到mapper的映射，检索条件拼装可以使用该工具类。建议使用方式一保证代码规范，Entity只和数据库映射。