SpringBoot's example code to implement Lambda Query query based on MyBatis-Plus

introduction

MyBatis-Plus is an enhanced tool for MyBatis, simplifying database operations and improving development efficiency. It provides a variety of query methods, including regular SQL queries, Lambda Query queries, pagination queries, conditional queries, etc. In this blog, we will explain in detail how to use MyBatis-Plus various query methods, covering the following content:

Basic environment configuration
Table structure design
Common query methods
Normal query
Lambda Query
Conditional constructor
Aggregation query
Pagination query
Complex query and multi-table joint search

Basic environment configuration

First, you need to make sure that the project has introduced MyBatis-Plus-related dependencies. Assuming your project is based on Spring Boot, here is an example of how to configure Maven dependencies.

Dependency configuration (Maven)

<dependencies>
    <!-- MyBatis-Plus Starter -->
    <dependency>
        <groupId></groupId>
        <artifactId>mybatis-plus-boot-starter</artifactId>
        <version>3.5.0</version>
    </dependency>

    <!-- MySQL Connector -->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.23</version>
    </dependency>

    <!-- Spring Boot Starter Web -->
    <dependency>
        <groupId></groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
</dependencies>

Configuration

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/mydb?useSSL=false&amp;serverTimezone=UTC
    username: root
    password: root
    driver-class-name: 
  mybatis-plus:
    # Configure MyBatis-Plus    mapper-locations: classpath:/mappers/*.xml
    typeAliasesPackage:

Table structure design

Suppose there are two tables:

demo_student: Student information
demo_class: class information

demo_student table structure

CREATE TABLE demo_student (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT,
  class_id INT,
  gender ENUM('M', 'F') DEFAULT 'M',
  FOREIGN KEY (class_id) REFERENCES demo_class(id)
);

The demo_student table stores student information, where class_id is a foreign key pointing to the demo_class table.

demo_class table structure

CREATE TABLE demo_class (
  id INT AUTO_INCREMENT PRIMARY KEY,
  class_name VARCHAR(50) NOT NULL
);

Common query methods

Normal query (selectOne, selectList, selectById)

selectOne: Query a single record.
selectList: Query multiple records.
selectById: Query a record by the primary key.

import ;
import ;
import ;
import ;

import ;

@Service
public class DemoStudentService {

    @Autowired
    private DemoStudentMapper demoStudentMapper;

    // Inquiry of individual students    public DemoStudent getStudentById(Long id) {
        return (id);
    }

    // Inquiry of all students    public List&lt;DemoStudent&gt; getAllStudents() {
        return (new QueryWrapper&lt;&gt;());
    }
}

Lambda Query (LambdaQueryWrapper)

MyBatis-Plus ProvidedLambdaQueryWrapper, you can avoid hard-coded field names by using Lambda expressions.

import ;

public List&lt;DemoStudent&gt; getStudentsByAge(int age) {
    LambdaQueryWrapper&lt;DemoStudent&gt; queryWrapper = new LambdaQueryWrapper&lt;&gt;();
    // Query students who are older than the specified value    (DemoStudent::getAge, age);  
    return (queryWrapper);
}

Conditional constructor (QueryWrapper)

QueryWrapper Allows querying by building conditions. It supports eq、ne、lt、gt、likeand other conditions.

public List&lt;DemoStudent&gt; getStudentsByClassId(Long classId) {
    QueryWrapper&lt;DemoStudent&gt; queryWrapper = new QueryWrapper&lt;&gt;();
    // Query according to class ID    ("class_id", classId);  
    return (queryWrapper);
}

Conditional chain query

LambdaQueryWrapperandQueryWrapperSupports chain calls, and multiple conditions can be combined into one query.

public List&lt;DemoStudent&gt; getFemaleStudentsOver18() {
    LambdaQueryWrapper&lt;DemoStudent&gt; queryWrapper = new LambdaQueryWrapper&lt;&gt;();
    (DemoStudent::getGender, "F")
                .gt(DemoStudent::getAge, 18);  // Find students who are of female gender and older than 18 years old    return (queryWrapper);
}

Aggregation query (selectCount, selectMax, selectMin, etc.)

MyBatis-Plus supports basic aggregation queries, such as statistics, maximum value, minimum value, average value, etc.

// Check the total number of studentspublic int getTotalStudents() {
    return (new QueryWrapper&lt;&gt;());
}

// Query the maximum age valuepublic Integer getMaxAge() {
    return (DemoStudent::getAge);
}

// Query the minimum age valuepublic Integer getMinAge() {
    return (DemoStudent::getAge);
}

Pagination query

Pagination query is very common in actual development. MyBatis-Plus provides built-in pagination function that can be implemented very simply.

import ;
import ;

public IPage&lt;DemoStudent&gt; getStudentsPage(int pageNum, int pageSize) {
    Page&lt;DemoStudent&gt; page = new Page&lt;&gt;(pageNum, pageSize);  // Create a paging object    LambdaQueryWrapper&lt;DemoStudent&gt; queryWrapper = new LambdaQueryWrapper&lt;&gt;();
    (DemoStudent::getAge, 18);  // Inquiry of students older than 18    return (page, queryWrapper);  // Return to the paged results}

Paging plugin configuration

The paging function needs to be Configure the paging plugin:

mybatis-plus:
  global-config:
    db-config:
      id-type: auto
  configuration:
    log-impl: 
  plugins:
    -

Complex query and multi-table joint search

existMyBatis-PlusAlthough not directly supportedJOINOperation, but we can implement complex conjunction table queries through custom SQL.
Example: Query student and class information (joint search)

import ;
import ;

public interface DemoStudentMapper {
    
    @Select("SELECT  AS student_id,  AS student_name, c.class_name " +
            "FROM demo_student s LEFT JOIN demo_class c ON s.class_id = ")
    List<StudentWithClass> selectStudentsWithClass();
}

Implement custom queries and complex queries

For some scenarios where custom SQL is required, you can directly use the @Select or @Update annotations to write SQL.

@Select("SELECT * FROM demo_student WHERE age > #{age} AND gender = #{gender}")
List<DemoStudent> selectByAgeAndGender(int age, String gender);

Summarize

MyBatis-PlusIt provides extremely rich query functions, and database query operations can be easily performed through a simple API and a flexible query constructor. Through the following points, you can better understand and apply the query function of MyBatis-Plus:

Normal query: PassselectOne, selectList, selectByIdMethods can quickly query data.
LambdaQuery: PassLambdaQueryWrapperBuild conditional queries to avoid hard-coded field names and improve code maintainability.
Conditional constructor:QueryWrapperandLambdaQueryWrapperProvides a variety of conditional construction methods and supports chain calls.
Pagination query:MyBatis-PlusBuilt-in pagination support is provided, allowing easy pagination query.
Aggregation query: Supports common aggregation operations, such asselectCount, selectMax, selectMinwait.
Complex query: Supports conjunction table queries through custom SQL to handle more complex query requirements.

The above is the detailed content of the sample code of SpringBoot implements Lambda Query query based on MyBatis-Plus. For more information about MyBatis-Plus Lambda Query query, please pay attention to my other related articles!