多表聯合查詢 - 基於註解SQL

湯圓學Java發表於2021-06-02

作者:湯圓

個人部落格:javalover.cc

前言

背景:Spring Boot + MybatisPlus

用MybatisPlus就是為了不寫SQL,用起來方便;

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

本節介紹的還是通用的寫法,基於註解SQL實現的多表聯合查詢

簡介

大概流程就是

  1. 先把要聯合查詢的引數封裝到一個類裡進行返回 - 結果類
  2. 再在mapper中注入SQL查詢語句 - @Select
  3. 最後在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 ?,?

總結

基於註解的多表聯合查詢,分三步:

  1. 定義實體結果類:封裝需要多表聯合查詢的資料
  2. 在mapper中注入SQL語句
  3. 在service中呼叫mapper,拼接where條件

後記

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

相關文章