多表聯合查詢 - 基于注解SQL

作者：湯圓

個人部落格：javalover.cc

前言

背景：Spring Boot + MybatisPlus

用MybatisPlus就是為了不寫SQL，用起來友善；

但是如果需要多表聯合查詢，還是需要手寫SQL（不過GitHub上也是有一些開源的庫，可以不寫SQL）

本節介紹的還是通用的寫法，基于注解SQL實作的多表聯合查詢

簡介

大概流程就是

先把要聯合查詢的參數封裝到一個類裡進行傳回 - 結果類
再在mapper中注入SQL查詢語句 - @Select
最後在service中拼接查詢條件 - QueryWrapper構造器（這裡沒用Lambda構造器，因為它不支援編寫自定義的字段名)

正文

我們就按照上面的流程來示範：

先貼一下這裡我們要執行的SQL查詢語句：這裡隻貼了我們手寫的部分，還有一部分是程式在後面自動追加的（比如條件、分頁），這裡先不寫

select device.*, car.car_number from gps_device as device left join gps_car as car on device.car_id = car.car_id

1. 定義實體結果類

@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class DeviceResult extends Device implements Serializable {

    /**
     * 車牌号:隻有這個屬性是聯合Car查詢的，其他屬性都是Device自帶的
     */
    private String carNumber;

    /**
     * 裝置id
     */
    private Long deviceId;

    /**
     * 車輛id
     */
    private Long carId;

    /**
     * 裝置類型：0-無線，1-有線
     */
    private Integer deviceType;

    /**
     * 裝置編号
     */
    private String deviceNumber;

    /**
     * SIM卡号
     */
    private String simNumber;

}

可以看到，這裡我們将聯合查詢的carNumber封裝了進去，這樣傳回時，就可以将裝置資訊和車牌号一并傳回（多表聯合查詢的目的就是這個，聯合多個表的資料進行傳回）

2. mapper中注入SQL語句

這裡有多種方式：

基于注解
基于xml

這裡我們用的是基于注解（因為Spring Boot中xml的使用還是比較少的）

DeviceMapper.java

public interface DeviceMapper extends BaseMapper<Device> {
    /**
     * 聯合查詢 left join car
     */
    @Select("select device.*, car.car_number from gps_device as device left join gps_car as car on device.car_id = car.car_id ${ew.customSqlSegment}")
    Page<DeviceResult> joinCarPage(Page<?> page, @Param(Constants.WRAPPER) Wrapper<Device> wrapper);
}

代碼說明：

@Select 注解：注入SQL語句
@Param(Constants.WRAPPER) Wrapper<Device> wrapper ：這個注解有點類似@RequestParam，用來代替SQL語句中的ew變量（如果把形參wrapper改為ew，就不需要加@Param注解)；
這裡的構造器wrapper中的自定義SQL會自動追加到@Select語句的後面，最後的service中會有拼接結果SQL
BaseMapper：Mybatis-Plus的基類Mapper，封裝了各種常用的資料庫操作（增删改查分頁等）,有了它，一些基本的操作（增删改查等）我們就不用自己去寫SQL
Page：Mybatis-Plus中的分頁對象，将聯合查詢的資料進行分頁；這裡的分頁相關的SQL會自動追加到wrapper包裝器的後面（同上面的wrapper）

最後拼接的SQL為：

select device.*, car.car_number from gps_device as device left join gps_car as car on device.car_id = car.car_id LIMIT ?,?

其中

limit ?, ?

就是Page自動追加的SQL，wrapper追加的SQL在下面的service中定義

3. Service中調用mapper

此時在Service中就不能再使用Lambda表達式了，因為這裡需要自定義字段名

核心代碼如下：

QueryWrapper<Device> queryWrapper = new QueryWrapper<>();
queryWrapper.eq(ObjectUtil.isNotEmpty(deviceParam.getDeviceType()), "device.device_type", deviceParam.getDeviceType());
queryWrapper.like(ObjectUtil.isNotEmpty(deviceParam.getCarNumber()), "car.car_number", deviceParam.getCarNumber());

最後拼接的SQL為：（這裡假設deviceType和carNumber都有傳進來）

select device.*, car.car_number from gps_device as device left join gps_car as car on device.car_id = car.car_id where (device.device_number = ? and car.car_number = ?) LIMIT ?,?

總結

基于注解的多表聯合查詢，分三步：

定義實體結果類：封裝需要多表聯合查詢的資料
在mapper中注入SQL語句
在service中調用mapper，拼接where條件

後記

其實這種寫法還是比較繁瑣的，但好在用的不多；如果用的多，建議去找一些開源的庫來整合