mybatis-generator生成的Example无法实现复杂关联查询时，可以使用@select查询示例

本文链接：https://blog.csdn.net/qq_22701869/article/details/107030628

使用场景

controller：@RequestParam默认是必填参数

serviceImpl：对需要like查询的加上左右百分号： "%" + remark + "%"

新建自己的mapper接口，关联了多个sql查询：

返回对象：就是我们需要返回给前端的完整信息

使用场景

mybatis-generator生成的Example无法实现复杂关联查询时，可以使用@select查询示例

当要实现多个表关联查询的时候，example的无法实现，这个时候我们可以使用@select查询，一下提供一个参考

controller：@RequestParam默认是必填参数

    @GetMapping(value = "/getDeviceByPage")
    public BaseResult<List<DeviceVo>> getDeviceByPage(@RequestParam Integer pageNum,
                                                      @RequestParam Integer pageSize,
                                                      String organizationName,
                                                      Integer deviceLinkStatus,
                                                      Integer deviceStatus,
                                                      String snOrName,
                                                      String remark,
                                                      String startTime,
                                                      String endTime) {
        PageResult<DeviceVo> pageResult = deviceService.getDeviceByPage(organizationName, deviceLinkStatus, deviceStatus, snOrName, remark, startTime, endTime, pageNum, pageSize);
        return new BaseResult(200, "查询成功", pageResult.getData(), pageResult.getTotalCount());
    }

serviceImpl：对需要like查询的加上左右百分号： "%" + remark + "%"

@Override
    public PageResult<DeviceVo> getDeviceByPage(String organizationName, Integer deviceLinkStatus, Integer deviceStatus, String snOrName, String remark, String startTime, String endTime, Integer pageNum, Integer pageSize) {
        //
        SysUser currentUser = sysUserService.getCurrentUser();
        //
        Integer currentUserCustomerId =  currentUser.getCustomerId();
        if (StringUtils.isNotEmpty(snOrName)) {
            snOrName = "%" + snOrName + "%";
        }
        if (StringUtils.isNotEmpty(remark)) {
            remark = "%" + remark + "%";
        }
        if (StringUtils.isNotEmpty(organizationName)) {
            organizationName = "%" + organizationName + "%";
        }
        if (pageSize != null && pageNum != null) {
            pageNum = (pageNum - 1) * pageSize;
        }
        //分页list
        List<DeviceVo> voList = myDeviceMapper.getDeviceByPage(organizationName, deviceLinkStatus, deviceStatus, snOrName, remark, startTime, endTime, pageNum, pageSize, currentUserCustomerId);
        //分页总数
        int totalCount = myDeviceMapper.getCountDeviceByPage(organizationName, deviceLinkStatus, deviceStatus, snOrName, remark, startTime, endTime, currentUserCustomerId);

        return new PageResult<>(totalCount, voList);
    }

新建自己的mapper接口，关联了多个sql查询：

下划线变量必须要改成驼峰，如device_status AS deviceStatus，才能被自定义vo对象接收到

@Mapper
@Component
public interface MyPersonMapper {

    /**
     * 查询分页总数
     *
     */
    @Select({"<script> " +
            "SELECT\n" +
            "\tcount(1)\n" +
            "FROM\n" +
            "\tdevice d\n" +
            "LEFT JOIN customer_organization cor ON d.organization_id = cor.id\n" +
            "LEFT JOIN customer c ON d.customer_id = c.id" +
            " <where> " +
            "   <if test=\"organizationName!=null and organizationName != '' \"> " +
            "     AND cor.name like #{organizationName,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"deviceLinkStatus!=null \"> " +
            "     AND d.device_link_status = #{deviceLinkStatus,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"deviceStatus!=null \"> " +
            "     AND d.device_status like #{deviceStatus,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"snOrName!=null and snOrName != '' \"> " +
            "     AND (d.sn like #{snOrName,jdbcType=VARCHAR} or d.device_name like #{snOrName,jdbcType=VARCHAR})" +
            "   </if> " +
            "   <if test=\"remark!=null and remark != '' \"> " +
            "     AND d.remark like #{remark,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"startTime!=null and startTime != '' \"> " +
            "     AND d.update_time &gt; #{startTime,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"endTime!=null and endTime != '' \"> " +
            "     AND d.update_time &lt; #{endTime,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"currentUserCustomerId!=null\"> " +
            "     AND d.customer_id = #{currentUserCustomerId,jdbcType=INTEGER} " +
            "   </if> " +
            " </where> " +
            "</script>"})
    int getCountDeviceByPage(String organizationName, Integer deviceLinkStatus,
                             Integer deviceStatus, String snOrName,
                             String remark, String startTime,
                             String endTime, Integer currentUserCustomerId);


    /**
     * 设备实时状态查询
     */
    @Select({"<script> " +
            "SELECT\n" +
            "\td.id AS id,\n" +
            "\td.sn AS sn,\n" +
            "\td.organization_id AS organizationId,\n" +
            "\td.device_name AS deviceName,\n" +
            "\td.device_status AS deviceStatus,\n" +
            "\td.device_power AS devicePower,\n" +
            "\td.device_work_status AS deviceWorkStatus,\n" +
            "\td.device_link_status AS deviceLinkStatus,\n" +
            "\td.total_work_time AS totalWorkTime,\n" +
            "\td.total_work_distance AS totalWorkDistance,\n" +
            "\td.remark AS remark,\n" +
            "\td.device_stop AS deviceStop,\n" +
            "\td.create_id AS createId,\n" +
            "\td.create_time AS createTime,\n" +
            "\td.create_name AS createName,\n" +
            "\td.lastest_flag AS lastestFlag,\n" +
            "\td.version AS version,\n" +
            "\td.type AS type,\n" +
            "\td.product_time AS productTime,\n" +
            "\td.device_power_status AS devicePowerStatus,\n" +
            "\td.speed AS speed,\n" +
            "\td.action AS action,\n" +
            "\td.action_time AS actionTime,\n" +
            "\td.power_time AS powerTime,\n" +
            "\td.charge_work AS chargeWork,\n" +
            "\td.stop_charge AS stopCharge,\n" +
            "\td.stop_charge_delay AS stopChargeDelay,\n" +
            "\td.alarm_over_time AS alarmOverTime,\n" +
            "\td.slow_speed AS slowSpeed,\n" +
            "\td.device_default_param_id AS deviceDefaultParamId,\n" +
            "\td.product_model_type AS productModelType,\n" +
            "\td.mac AS mac,\n" +
            "\td.customer_id AS customerId,\n" +
            "\td.enter_time AS enterTime,\n" +
            "\td.outer_time AS outerTime,\n" +
            "\td.update_time AS updateTime,\n" +
            "\tcor.name AS organizationName,\n" +
            "\tc.name AS customerName\n" +
            "FROM\n" +
            "\tdevice d\n" +
            "LEFT JOIN customer_organization cor ON d.organization_id = cor.id\n" +
            "LEFT JOIN customer c ON d.customer_id = c.id" +
            " <where> " +
            "   <if test=\"organizationName!=null and organizationName != '' \"> " +
            "     AND cor.name like #{organizationName,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"deviceLinkStatus!=null \"> " +
            "     AND d.device_link_status = #{deviceLinkStatus,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"deviceStatus!=null \"> " +
            "     AND d.device_status like #{deviceStatus,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"snOrName!=null and snOrName != '' \"> " +
            "     AND (d.sn like #{snOrName,jdbcType=VARCHAR} or d.device_name like #{snOrName,jdbcType=VARCHAR})" +
            "   </if> " +
            "   <if test=\"remark!=null and remark != '' \"> " +
            "     AND d.remark like #{remark,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"startTime!=null and startTime != '' \"> " +
            "     AND d.update_time &gt; #{startTime,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"endTime!=null and endTime != '' \"> " +
            "     AND d.update_time &lt; #{endTime,jdbcType=VARCHAR} " +
            "   </if> " +
            "   <if test=\"currentUserCustomerId!=null\"> " +
            "     AND d.customer_id = #{currentUserCustomerId,jdbcType=INTEGER} " +
            "   </if> " +
            " </where> " +
            "  ORDER BY  d.update_time desc LIMIT #{pageNum,jdbcType=INTEGER} ,#{pageSize,jdbcType=INTEGER}" +
            "</script>"})
    List<DeviceVo> getDeviceByPage(String organizationName, Integer deviceLinkStatus,
                                   Integer deviceStatus, String snOrName,
                                    String remark, String startTime,
                                   String endTime, Integer pageNum, Integer pageSize, Integer currentUserCustomerId);

}

返回对象：就是我们需要返回给前端的完整信息

@Data
public class DeviceVo{
    /**
     * 主键
     */
    private Integer id;

    private String sn;

    private Integer organizationId;

    private String organizationName;

    private String deviceName;

    private Integer deviceStatus;

    private Integer devicePower;

    private Integer deviceWorkStatus;

    private Integer deviceLinkStatus;

    private Double totalWorkTime;

    private Double totalWorkDistance;

}