Author: Tangyuan

Personal blog: Javalover.cc

preface

Background: Spring Boot + MybatisPlus

Use MybatisPlus is not to write SQL, easy to use;

However, if you want to join multiple tables, you still need to write SQL by hand (although there are some open source libraries on GitHub that do not need to write SQL).

This section introduces a common way of writing, based on annotated SQL implementation of multi-table union query

Introduction to the

So basically the process is

  1. Encapsulate the parameters of the federated query into a return – result class
  2. Insert SQL query statement into mapper – @select
  3. Finally, concatenate the query condition – QueryWrapper constructor in the service (Lambda constructor is not used here because it does not support writing custom field names)

The body of the

Let’s follow the above process to demonstrate:

Here is the SQL query we are going to execute: only the handwritten part is posted here, and some parts are automatically appended by the program later (such as conditions, paging), so we will not write it here

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

1. Define entity result classes

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

    /** * License plate: Only this attribute is queried by Car. All other attributes are */
    private String carNumber;

    /** * Device ID */
    private Long deviceId;

    /** * vehicle id */
    private Long carId;

    /** * Device type: 0- wireless, 1- wired */
    private Integer deviceType;

    /** * Device number */
    private String deviceNumber;

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

}
Copy the code

As you can see, we have enclosed the carNumber of the union query, so that when we return, we can return the device information and the license plate number.

2. Inject SQL statements into mapper

Here are several ways:

  • Based on the annotation
  • Based on XML

Here we are using annotations based (because XML is still used sparingly in Spring Boot)

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);
}
Copy the code

Code description:

  • @SELECT annotation: Inject SQL statement

  • @ Param (the WRAPPER) WRAPPER < Device > WRAPPER: This annotation is similar to @requestParam, used instead of the EW variable in SQL statements (@param annotation is not required if the wrapper parameter is changed to ew);

    The custom SQL in the wrapper constructor here is automatically appended to the @SELECT statement, and the final service has concatenated result SQL

  • BaseMapper: Mybatis-Plus base class Mapper, package a variety of common database operations (add, delete, change, search pages, etc.), with it, some basic operations (add, delete, change, search, etc.) we do not need to write SQL

  • Page: Paging object in Mybatis-Plus, paging the data queried jointly; The page-related SQL here is automatically appended to the Wrapper wrapper (same as the Wrapper above)

The final concatenated SQL is:

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

The limit? ,? SQL appended to Page automatically, and SQL appended to wrapper is defined in the following service

3. Invoke mapper in the Service

Lambda expressions can no longer be used in a Service because custom field names are required

The core code is as follows:

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());
Copy the code

The final concatenated SQL is :(here we assume that deviceType and carNumber are passed in)

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 ? ,?Copy the code

conclusion

Annotation-based multi-table joint query, in three steps:

  1. Define entity result classes: Encapsulate data that requires joint queries for multiple tables
  2. Inject SQL statements into mapper
  3. Call mapper in the service to concatenate where conditions

Afterword.

In fact, this writing method is more cumbersome, but fortunately, not much; If use many, suggest to find some open source libraries to integrate