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

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

作者：湯圓

個人部落格：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條件

後記

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

相關文章