Data Access MyBatis Plus (2) - Wrapper conditional constructor

“Offer comes, ask friends to take it! I am participating in the 2022 Spring Recruit Punch card campaign. Click here for more details.”

I. Introduction to Wrapper

MyBatis Plus uses Wrapper to construct query conditions and implement conditional read or conditional write operations.

The Wrapper abstract subclass AbstractWrapper defines various conditional parameters

Concatenated SQL statements use database fields instead of properties of Java entity classes

See the conditional constructor in the official documentation for the meaning of all query criteria in AbstractWrapper

Second, condition query

preparation

Alter insert method to insert data into t_TESLA table

@Test
public void insert(a){
    for (int i = 4; i < 40; i++) {
        Tesla tesla = new Tesla();
        tesla.setVehicleName("Model " + i);
        tesla.setVehicleType("Compact Car");
        if (i % 2= =0) {
            tesla.setFactory("Fremont Gigafactory");
        } else {
            tesla.setFactory("Shanghai Gigafactory");
        }
        tesla.setVehiclePrice(23000d + i * 10000); teslaMapper.insert(tesla); }}Copy the code

Execute this method to insert data into the database

Implement conditional query

SelectList and selectCount methods in BaseMapper

/** * query all records ** according to entity condition@paramThe queryWrapper entity object encapsulates the action class (which can be null) */
List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);

/** * Query the total number of records according to the Wrapper condition **@paramThe queryWrapper entity object encapsulates the action class (which can be null) */
Long selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
Copy the code

Requirement: Query Tesla for all Compact Car models produced at The Lamont plant

@Test
public void selectListWithWrapper(a){
    // construct the query condition
    QueryWrapper<Tesla> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("factory"."Fremont Gigafactory")
                // Add the or keyword
                .or()
                .eq("vehicle_type"."Compact Car");

    List<Tesla> teslaList = teslaMapper.selectList(queryWrapper);
    System.out.println("The total number of queries is:" + teslaList.size());

}
Copy the code

Execute the method

View the source code for the OR method

Implement the selectCount test method

@Test
public void selectCountWithWrapper(a){
    // construct the query condition
    QueryWrapper<Tesla> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("factory"."Shanghai Gigafactory")
            .eq("vehicle_type"."Compact Car");

    Long count = teslaMapper.selectCount(queryWrapper);
    System.out.println("The total number of matches found is:" + count);
}
Copy the code

Execute the method

Achieve paging condition query

Demand: paging query all Tesla produced in Shanghai factory and priced between 230000 and 3000000

@Test
public void selectPageWithWrapper(a){
    // construct the query condition
    QueryWrapper<Tesla> teslaWrapper = new QueryWrapper<>();
    teslaWrapper.between("price".230000.3000000)
                .eq("factory"."Shanghai Gigafactory");

    // Construct paging conditions
    Page<Tesla> teslaPageCondition = new Page<>(2.4);

    Page<Tesla> teslaPage = teslaMapper.selectPage(teslaPageCondition, teslaWrapper);
    System.out.println("Paging condition query data:" + teslaPage.getRecords());
    System.out.println("Total number of records queried by paging conditions:" + teslaPage.getTotal());
}
Copy the code

Execute the method

3. Update of conditions

The conditional update method in BaseMapper

/** * Update the record ** according to the whereEntity condition@paramEntity Entity object (set conditional value, which can be null) *@paramThe updateWrapper entity object encapsulates the action class (which can be null, where the entity is used to generate the WHERE statement) */
int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper);
Copy the code

Demand: Adjust the price of all Tesla products whose price is above 113000.00 and which are produced in Shanghai factory to 113000.00

@Test
public void updateWithWrapper(a){
    // Structure update condition
    UpdateWrapper<Tesla> updateWrapper = new UpdateWrapper<>();
    updateWrapper.gt("price".113000.00)
                 .eq("factory"."Shanghai Gigafactory")
                 .set(true."price".113000.00);

    int updateCount = teslaMapper.update(null, updateWrapper);
    System.out.println("The number of rows updated is:" + updateCount);

}
Copy the code

Execute the method

The update method passes in an entity class

@Test
public void updateWithWrapper(a){
    // Structure update condition
    UpdateWrapper<Tesla> updateWrapper = new UpdateWrapper<>();
    updateWrapper.gt("price".100000.00)
                 .eq("factory"."Shanghai Gigafactory")
                 .set(true."price".100000.00);

    Tesla tesla = new Tesla();
    tesla.setFactory("Shanghai Gigafactory");
    tesla.setVehiclePrice(100000.00);
    tesla.setVehicleType("Roadster");
    tesla.setVehicleName("Tesla Roadster");
    int updateCount = teslaMapper.update(tesla, updateWrapper);
    System.out.println("The number of rows updated is:" + updateCount);
}
Copy the code

Update passes in an entity classMyBatis Plus uses the value of the attribute of the entity class as the value of the updated field, according to the SQL statement executed. The value of the update field set by the set method of UpdateWrapper is not overwritten by the price of the entity class.

If a few fields are updated, you can use the set method in QueryWrapper to set the values of the updated fields. If many fields need to be updated, you are advised to create a new object and set the values of the fields to the corresponding properties of the object.

4. Conditional deletion

The conditional deletion method in BaseMapper

/** * Delete record ** according to entity condition@paramThe queryWrapper entity object encapsulates the action class (which can be null, where the entity is used to generate the WHERE statement) */
int delete(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);
Copy the code

Demand: Delete Tesla produced in Shanghai factory whose price is greater than 90,000

@Test
public void deleteWithWrapper(a){
    QueryWrapper<Tesla> queryWrapper = new QueryWrapper();

    queryWrapper.eq("factory"."Shanghai Gigafactory")
            .gt("price"."90000.00");

    int deleteCount = teslaMapper.delete(queryWrapper);
    System.out.println("Number of deletions is:" + deleteCount);
}
Copy the code

Execute the method

Other common methods for Wrapper

The sorting

The sorting condition can be a single condition or a set

@Test
public void selectPageByWrapperAndSortByPrice(a){
    // Set of sorting conditions
    List<String> orderConditionList = new ArrayList<>();
    orderConditionList.add("price");
    orderConditionList.add("name");

    // Arrange all Teslas produced by The Flamont plant in descending order of price
    QueryWrapper<Tesla> queryWrapper = new QueryWrapper<>();
    queryWrapper.eq("factory"."Fremont Gigafactory")
                .orderByDesc(orderConditionList);

    Page<Tesla> teslaPageCondition = new Page<>(2.5);

    Page<Tesla> teslaPage = teslaMapper.selectPage(teslaPageCondition, queryWrapper);

    System.out.println("Paging query and descending data:" + teslaPage.getRecords());

}
Copy the code

Execute the method

Stitching SQL

The last method concatenates the SQL at the end of the SQL

@Test
public void selectListByLastInWrapper(a){
    QueryWrapper<Tesla> queryWrapper = new QueryWrapper<>();
    queryWrapper.last("ORDER BY price");

    List<Tesla> teslaList = teslaMapper.selectList(queryWrapper);

    System.out.println(SQL > select * from 'last'; + teslaList);

}
Copy the code

Execute the method

Summary of Wrapper

Conditions can be constructed using QueryWrapper for query and delete operations and UpdateWrapper for updates.

Criteria queries are included in general Mapper and MyBatis MBG generated methods, namely QBC queries, by generating entity class XxxExaple to build the query Criteria, MyBatis (8) – MyBatis general Mapper (Part B)

The difference between the two:

Conditional queries for MBG or general purpose Mapper differ in the use of MBG construction criteriaThe properties in XxxExample are the properties of the entity class.
MyBatis Plus constructs the query criteria usingDatabase fields.

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Data Access MyBatis Plus (2) – Wrapper conditional constructor

I. Introduction to Wrapper

Second, condition query

preparation

Implement conditional query

Achieve paging condition query

3. Update of conditions

4. Conditional deletion

Other common methods for Wrapper

The sorting

Stitching SQL

Summary of Wrapper

Data Access MyBatis Plus (2) – Wrapper conditional constructor

I. Introduction to Wrapper

Second, condition query

preparation

Implement conditional query

Achieve paging condition query

3. Update of conditions

4. Conditional deletion

Other common methods for Wrapper

The sorting

Stitching SQL

Summary of Wrapper

Related Posts

From custom annotations to handwritten startup classes

Json Web Token for egg.js practice

Java Basics: Obtain operating system and browser information