Are you still using QueryWrapper?

preface

Small knowledge, big challenge! This article is participating in the creation activity of “Essential Tips for Programmers”. QueryWrapper (LambdaQueryWrapper) and UpdateWrapper (LambdaUpdateWrapper), whose parent class AbstractWrapper is used in MP to generate WHERE SQL Yes, but there are so many wrappers that you can only use QueryWrapper. All kinds of advanced SAO operation must learn, combined with the case code easily control a variety of usage, by the way sort out some common conditional constructors and the use of matters needing attention, less gossip, directly into the topic.

preparation

Prepare two tables and their corresponding entity-class objects

tb_provinceProvince table

CREATE TABLE `tb_province` (
  `pid` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Province Number',
  `province` char(4) DEFAULT NULL COMMENT 'Name of province',
  `abbr` varchar(3) DEFAULT NULL COMMENT 'Short for province',
  `area` int(11) DEFAULT NULL COMMENT 'Area of province (km²)',
  `population` decimal(10.2) DEFAULT NULL COMMENT 'Population of province (ten thousand)',
  `attraction` varchar(50) DEFAULT NULL COMMENT 'Famous scenic spots in the province',
  `postcode` varchar(10) DEFAULT NULL COMMENT 'Postcode of the capital of the province'.PRIMARY KEY (`pid`),
  KEY `postcode` (`postcode`),
  CONSTRAINT `tb_province_ibfk_1` FOREIGN KEY (`postcode`) REFERENCES `tb_capital` (`postcode`)
) ENGINE=InnoDB AUTO_INCREMENT=35 DEFAULT CHARSET=utf8;
Copy the code

Entity class corresponding to the province table

@TableName(value = "tb_province")
@Data
public class Province implements Serializable {

    private static final long serialVersionUID = 1L;
    
    /**
    * 省份编号
    */
    @TableId(value = "pid", type = IdType.AUTO)
    private Integer pid;

    /** ** Name of province */
    private String province;

    /** ** province */
    private String abbr;

    /** ** The area of the province */
    private Integer area;

    / Population of ** ** province */
    private BigDecimal population;

    /** ** ** ** ** ** ** ** *
    private String attraction;

    /** * Associate provincial capital information with provincial capital postcode */
    @TableField(exist = false)
    private Capital capital;
}
Copy the code

tb_capitalThe provincial table is associated by the zip code of the provincial table

CREATE TABLE `tb_capital` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key id',
  `postcode` varchar(10) NOT NULL COMMENT 'Postcode of the provincial capital',
  `city` varchar(4) DEFAULT NULL COMMENT 'Name of provincial capital',
  `nickname` varchar(10) DEFAULT NULL COMMENT 'Alias of the provincial capital',
  `climate` varchar(20) DEFAULT NULL COMMENT 'Climatic conditions in the provincial capital',
  `carcode` varchar(5) DEFAULT NULL COMMENT 'License plate number of the provincial capital'.PRIMARY KEY (`id`,`postcode`) USING BTREE,
  KEY `postcode` (`postcode`),
  CONSTRAINT `tb_capital_ibfk_1` FOREIGN KEY (`postcode`) REFERENCES `tb_province` (`postcode`)
) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8;
Copy the code

Entity class corresponding to the provincial capital table

@TableName(value = "tb_capital")
@Data
public class Capital implements Serializable {
    
    private static final long serialVersionUID = 1L;
    
    /** * primary key id */
    private Integer id;

    /** * The postcode of the provincial capital */
    private String postcode;

    /** * The name of the provincial capital */
    private String city;

    /** ** The provincial capital's alias */
    private String nickname;

    /** ** ** * weather conditions */
    private String climate;

    /** ** ** */
    private String carcode;
}
Copy the code

Code to rehearse

OK, start coding when you’re ready.

Ordinary QueryWrapper

To start with a simple query for an entry in the province table, you might use QueryWrapper:

// Query basic information about Jiangxi province
QueryWrapper<Province> wrapper = new QueryWrapper<>();
wrapper.eq("province"."Jiangxi");
Province province = provinceMapper.selectOne(wrapper);
System.out.println(province);
Copy the code

After execution, the following statement can be seen in the console:

==>  Preparing: SELECT pid,province,abbr,area,population,attraction FROM tb_province WHERE (province =?).==>Parameters: Jiangxi Province (String)< ==    Columns: pid, province, abbr, area, population, attraction
< ==        Row: 10Jiangxi Province Jiangxi Province166900.4666.10, Lushan Mountain, Poyang Lake and Tengwang Pavilion< ==      Total: 1
Copy the code

The QueryWrapper query condition wrapper class uses the eq (equal) method to equal the first argument (column name in the database table) to the second argument (conditional value), and then calls the selectOne() method inherited from BaseMapper by the Mapper interface. Pass in the Wrapper to add the query criteria and you get the above SQL statement.

The final print result is:

Province [Hash = 629321967, pid=10, type = jiangxi, abbr= Jiangxi, area=166900, population=4666.10, attraction= capital=null]Copy the code

Capital = null!!!!! Because database table fields do not correspond, annotations are used to exclude non-table fields.

How to insert the provincial capital information into the entity object when querying the province information, so as to get a more detailed province details?

Two ways:

Delete the annotated capital attribute from the Province class, add the foreign key to the capital entity through the field, do a secondary query and then assign.
A province corresponds to a provincial capital, which is a one-to-one relationship. Therefore, we can write resultMap mapping in the corresponding XML file, and map the results returned by the mapping query of foreign key values to the capital attribute.

The second approach is used here:

ProvinceMapper. XML:

<resultMap id="BaseResultMap" type="com.xx.xxx.entity.Province">
  <id column="pid" jdbcType="INTEGER" property="pid" />
  <result column="province" jdbcType="CHAR" property="province" />
  <result column="abbr" jdbcType="VARCHAR" property="abbr" />
  <result column="area" jdbcType="INTEGER" property="area" />
  <result column="population" jdbcType="DECIMAL" property="population" />
  <result column="attraction" jdbcType="VARCHAR" property="attraction" />
  <association property="capital" javaType="com.xx.xxx.entity.Capital" column="postcode"
               select="com.xx.xxx.mapper.CapitalMapper.selectAllByPostcode">
  </association>
</resultMap>
Copy the code

Capitalmapper. XML foreign key value mapping SQL:

<select id="selectAllByPostcode" parameterType="map" resultMap="BaseResultMap">
  select 
  <include refid="Base_Column_List" />
  from tb_capital
  where postcode = #{postcode,jdbcType=VARCHAR}
</select>
Copy the code

If resultMap=”BaseResultMap”, the provincial information you query contains the provincial information.

Province province = provinceMapper.selectByProvinceName(Zhejiang Province);
System.out.println(province); // type [Hash = 120157876, pid=7, type = zhejiang, abbr= Zhejiang, area=105500, population=5850.00, attraction= Xihu, Wuzhen, Qiandahu, Capital = capital [Hash = 1919497442, id=14, postcode=310000, city= hangzhou, nickname= linan, climate= sub climate, carcode= zhejiang A]]

/* SQL statement after execution ==> Preparing: select pid, province, abbr, area, population, attraction, postcode from tb_province where province=? Parameters: zhejiang province (String) <== Columns: pid, type, abbr, area, population, attraction, postcode <== > Row: 7, Xihu, Wuzhen, Qiandao Lake, Zhejiang 105500, 5850.00, China 310000 ====> Preparing: select id, postcode, city, nickname, climate, carcode from tb_capital where postcode = ? ====> Parameters: 310000(String) <==== Columns: id, postcode, city, nickname, climate, carcode <==== Row: 14, 310000, Hangzhou, Lin 'an, Subtropical monsoon climate, Zhejiang A <==== Total: 1 <== Total: 1 */
Copy the code

The Wrapper supports chained programming and if the query condition is zhejiang province, zhejiang for short and the postal code is 310000, you might write:

QueryWrapper<Province> eq = new QueryWrapper<Province>()
        .eq("province".Zhejiang Province)
        .eq("abbr"."Zhe")
        .eq("postcode"."310000");
Province province = provinceMapper.selectOne(eq);
System.out.println(province);
Copy the code

In fact, instead of writing three eq() in succession, we can write an allEq() directly:

QueryWrapper<Province> eq = new QueryWrapper<Province>()
        .allEq({"province": Zhejiang Province."abbr": "Zhe"."postcode": "310000"}, true);
Copy the code

The first parameter receives a Map, where key is the database field name and value is the field value.

The second parameter receives a Boolean value, which may not be passed. The default value is true. If the value is true, isNull is called when the map value isNull.

Chain Lambda operation

In addition, writing a fixed column name damages the robustness of the code and is quite rigid.

So the use of functional interface, the implementation of chain query is very necessary!

Query the provinces whose names contain “Jiang”, whose population exceeds 20 million, and whose area ranges from 10 million to 25 million square kilometers in descending order by population.

// In QueryWrapper we get the LambdaQueryWrapper
LambdaQueryWrapper<Province> eq = new LambdaQueryWrapper<Province>()
        .like(Province::getProvince, "River")
        .gt(Province::getPopulation, 2000)
        .or()
        .between(Province::getArea, 100000.250000)
        .orderByDesc(Province::getPopulation);
List<Province> provinces = provinceMapper.selectList(eq);
Optional.ofNullable(provinces).ifPresent(p -> p.forEach(System.out::println));
Copy the code

Note: do not callor()The default value is YesandThe connection.

SQL statement after execution:

==>  Preparing: SELECT pid,province,abbr,area,population,attraction FROM tb_province WHERE (province LIKE ? AND population > ? OR area BETWEEN ? AND?).ORDER BY population DESC 
==> Parameters: %jiang%(String), 2000(Integer), 100000(Integer), 250000(Integer)
< ==    Columns: pid, province, abbr, area, population, attraction
< ==        Row: 12Guangdong Province guangdong Province179725.11521.00, Mt. Danxia oct baiyun Mountain< ==        Row: 19Shandong Province, Shandong Province157900.10070.21Mount Tai Yimeng Mountain Penglai Pavilion< ==        Row: 11Henan Province, Henan Province167000.9640.00Shaolin Temple longmen Grottoes Yin Ruins< ==        Row: 6Su, Jiangsu Province107200.8070.00, Zhongshan Mausoleum, Flower and Fruit Mountain, Santai Mountain< ==        Row: 1Hebei Province Hebei Province188800.7591.97Baiyangdian Mountain Resort Beidaihe< ==        Row: 17Hunan Province hunan Province211800.6918.38Zhangjiajie Yueyang Tower Hengshan< ==        Row: 8Anhui Province, Anhui Province,140100.6365.90Huangshan Mountain Jiuhua Mountain Heavenly Village< ==        Row: 18Hubei Province185900.5927.00, Yellow Crane Tower, Shennongjia, three Gorges of the Yangtze River< ==        Row: 7Zhejiang Province, Zhejiang Province,105500.5850.00West Lake Wuzhen Thousand Island Lake< ==        Row: 13Guangxi, Guangxi237600.4960.00Guilin landscape Silver Beach Qingxiu Mountain< ==        Row: 10Jiangxi Province Jiangxi Province166900.4666.10, Lushan Mountain, Poyang Lake and Tengwang Pavilion< ==        Row: 3Liaoning Province, Liaoning Province,148600.4351.70Dongling, Shenyang Xinghai Square, Dalian< ==        Row: 9Fujian Province, Fujian Province,124000.3973.00Gulangyu Wuyi Mountain Weizhou Island< ==        Row: 20Shaanxi Province shaanxi Province205600.3876.21, terra-cotta Warriors, Huashan Mountain, the Mausoleum of the Yellow Emperor< ==        Row: 5Hei Province, Heilongjiang Province,473000.3751.30, North Pole Village, Zhalong Wetland, Wudalianchi< ==        Row: 2Jin, Shanxi Province,156700.3729.22, Wutai Mountain, Pingyao Ancient City, Yungang Grottoes< ==        Row: 14Guizhou, Guizhou or Guizhou,176167.3622.95, Huangguoshu Waterfall, Fanjing Mountain, Wanfeng Forest< ==        Row: 4Ji, Jilin Province,187400.2690.73Changbai Mountain, Jingyuetan Lake, Goguryeo Royal Tomb< ==      Total: 18
Copy the code

Group by climate and select municipalities with nicknames of three or more Chinese characters.

The first entryboolean conditionRepresents the conditionWhether or notAdd generatedSQLThe default value istrue. Such as:query.like(StringUtils.isNotBlank(name), Entity::getName, name) .eq(age! =null && age >= 0, Entity::getAge, age)

List<Capital> capitals = new LambdaQueryChainWrapper<Capital>(capitalMapper)
        .isNull(Capital::getCity)
        .groupBy(Capital::getClimate)
        .having(true."length(nickname) >= 9") Having (true, "length(nickname) < {0}", 9) {0} placeholder
        .list();

Optional.ofNullable(capitals).ifPresent(list -> {
    list.forEach(System.out::println);
});
Copy the code

LambdaQueryChainWrapper chained query lambda-type, initialized using CapitalMapper interface (inherited from BaseMapper), The end of the chain calls this.getBasemapper ().selectList(this.getwrapper ()) with the list() method; Returns a List result set.

At the same time, the last can also pass

One () returns a T entity-class object
OneOpt () returns Optional

Optional wrapped entity classes
Count () returns Integer The total number of statistical result sets
Page () returns the Page page object.

In addition to usingnew LambdaQueryWrapper<T>()To get oneLambdaQueryWrapper, can also be usedWrappersClass callquery()To get aQueryWrapperObject, calllambdaQuery()The static method gets oneLambdaQueryWrapper, etc.

Select * from provincial capital where name = “linchen”; select * from provincial capital where name = “linchen”; select * from provincial capital where name = “linchen”; The final query records are in ascending order by primary key ID and only the first two records are selected.

InSql (), the first parameter is the table column name, and the other parameter is sqlString, which is passed into THE SQL statement. After joining, the effect is: column IN (query result set).

// Generate a random Boolean value
boolean randomBool = Math.random() > 0.5 d;

LambdaQueryWrapper<Capital> lambdaQueryWrapper = Wrappers.<Capital>lambdaQuery().
        inSql(Capital::getPostcode, "select postcode \n" +
                "from tb_province \n" +
                "where length(abbr) ! = 3 and attraction not like '% catch %'")
        .func(true, wrapper -> { // condition: true, can be ignored
            if (randomBool) { 
                wrapper.ne(Capital::getNickname, "Lin Cheng"); 
            } else { 
                wrapper.exists("Select * from tb_capital where climate = 'subtropical monsoon '"); 
            } 
        })
        .orderByAsc(Capital::getId)
        .last("limit 2");

List<Capital> capitals = capitalMapper.selectList(lambdaQueryWrapper);

Optional.ofNullable(capitals).ifPresent(list -> {
    list.forEach(System.out::println);
});
Copy the code

The func() method is used to facilitate if… Else calls can be continuously chained using different methods, and the difference between the two cases will result in different SQL statements.

SQL statement printed by the console after running:

randomBool: true
==>  Preparing: SELECT id,postcode,city,nickname,climate,carcode FROM tb_capital WHERE (postcode IN (select postcode from tb_province where length(abbr) ! = 3 and attraction not like '% % sea') AND nickname <>?).ORDER BY id ASC limit 2 
==>The Parameters: Lin Cheng (String)< ==    Columns: id, postcode, city, nickname, climate, carcode
< ==        Row: 25.610000Chengdu The Land of Abundance Subtropical monsoon humid climate Sichuan A< ==        Row: 28.730000Temperate continental climate, Lanzhou, Jincheng, Gansu A< ==      Total: 2

randomBool: false
==>  Preparing: SELECT id,postcode,city,nickname,climate,carcode FROM tb_capital WHERE (postcode IN (select postcode from tb_province where length(abbr) ! = 3 and attraction not like '% % sea') AND EXISTS (select * from tb_capital where climate = 'Subtropical monsoon climate')) ORDER BY id ASC limit 2
==> Parameters: 
< ==      Total: 0
Copy the code

last()Ignore the optimization rules and splice directly toSQLBut note that the call can only be called once, if multiple calls are subject to the last call, and there isSQL injectionRisk, need to be careful use!!

CustomSqlSegment into arguments

It is common to encounter a requirement like this: Mapper.selectpage () returns a page object wrapped around a List

/ page

. If mapper.selectPage() returns a page object wrapped around a List

. So this is where you need to customize mapper layer methods and write XML files.

ProvinceVO objects:

@Data
public class ProvinceVO {

    private Integer pid;

    privateString province; aprivateString abbr; . }Copy the code

Mapper layer interface method:

List<ProvinceVO> queryPageList(Page page, @Param("provinceVO") ProvinceVO provinceVO);

Page<ProvinceVO> pageList(Page<ProvinceVO> page, @Param("provinceVO") ProvinceVO provinceVO);
Copy the code

The queryFruitList() method of the Mapper interface returns a paged List of lists, but it can also be a wrapper object around the Page. To get the List data inside, just call the getRecords() static method.

Page<ProvinceVO> page = new Page<ProvinceVO>(1.10);
ProvinceVO provinceVO = new ProvinceVO();
provinceVO.setProvince("River");
Page<ProvinceVO> provinceVOPage = provinceMapper.pageList(page, provinceVO);

System.out.println(provinceVOPage.getTotal()); // Total number of queried records
System.out.println(provinceVOPage.getCurrent()); / / the current page
System.out.println(provinceVOPage.getRecords()); // Paging list data

/ / and provinceVOPage. GetRecords () equivalent
List<ProvinceVO> provinceVOPageList = fruitMapper.queryPageList(page, provinceVO);
Optional.ofNullable(provinceVOPageList).ifPresent(list -> {
    list.forEach(System.out::println);
});
Copy the code

If you want to use Wrapper custom SQL to generate where conditions, you can use annotations to write:

@Select("select * from tb_province ${ew.customSqlSegment}")
List<ProvinceVO> getVOListByCustomSqlSegment(@Param("ew") Wrapper ew);
Copy the code

Test code:

List<ProvinceVO> provinceVOList = provinceMapper.getVOListByCustomSqlSegment(new QueryWrapper<ProvinceVO>()
        .like("province"."River"));

Optional.ofNullable(provinceVOList).ifPresent(list -> {
    list.forEach(System.out::println);
});
Copy the code

Note: Not supportedWrapperWithin theentitygeneratewhereStatement!!!!! That is, you cannot use functional interfaces instead of column names.

List<ProvinceVO> provinceVOList = provinceMapper.getVOListByCustomSqlSegment(new LambdaQueryWrapper<ProvinceVO>()
        .like(ProvinceVO::getProvince, "River")); * // / or
List<ProvinceVO> provinceVOList = provinceMapper.getVOListByCustomSqlSegment(Wrappers.<ProvinceVO>lambdaQuery()
        .like(ProvinceVO::getProvince, "River"));
Copy the code

Otherwise, the console displays the following error message:

org.apache.ibatis.builder.BuilderException:
    Error evaluating expression 'ew.customSqlSegment'.
    ... MybatisPlusException: can not find lambda cache for this entity.
Copy the code

In addition to annotations, you can also write in an XML file:

Constants.WRAPPERUsing theMPIs a string constant pool whose value isew, equivalent to the@Param("ew")

Page<ProvinceVO> getPageListByCustomSqlSegment(Page<ProvinceVO> page, @Param(Constants.WRAPPER) Wrapper wrapper);
Copy the code

Write SQL statements in XML:

<select id="getPageListByCustomSqlSegment" resultType="com.xx.xxx.vo.ProvinceVO">
  select *
  from tb_province
  ${ew.customSqlSegment}
</select>
Copy the code

Test code:

Page<ProvinceVO> page = new Page<>(1.10);

Page<ProvinceVO> provinceVOPage = provinceMapper.getPageListByCustomSqlSegment(page, new QueryWrapper<Province>()
        .like("province"."River"));

Optional.ofNullable(provinceVOPage).ifPresent(p -> {
    List<ProvinceVO> provinceVOList = p.getRecords();
    long total = p.getTotal();
    long currentPage = p.getCurrent();
    long pageSize = p.getSize();

    provinceVOList.forEach(System.out::println);

    System.out.println("Total number of items:" + total);
    System.out.println("Current page:" + currentPage);
    System.out.println("Number of items per page" + pageSize);
});
Copy the code

After execution, the console can see:

JsqlParserCountOptimize sql=select * from tb_province WHERE (province LIKE ?) ==> Preparing: SELECT COUNT(1) FROM tb_province WHERE (province LIKE ?) Parameter: % Jiang% (String) <== Columns: COUNT(1) <== Row: 4 ==> Preparing: select * from tb_province WHERE (province LIKE ?) LIMIT ? ,? = = > Parameters: % % (String), jiang 0 (Long), 10 (Long) < = = the Columns: pid, province, abbr, area, population, attraction, postcode <== Row: 5, Hei, 473000, 3751.30, Beiji Village, Zhalong Wetland, Wudalianchi, 150000 <== Row: 6, Su, 107200, 8070.00, Zhongshan Mountain, Huaguo Mountain, Santai Mountain, 210000 <== Row: 7, Zhejiang, Zhejiang, 105500, 5850.00, West Lake, Wuzhen, Thousand Island Lake, 310000 <== Row: 10, Jiangxi, Jiangxi, 166900, 4666.10, Lushan, Poyang Lake, Tengwangge, 330000 <== Total: 4 ProvinceVO{pid=6, province=' jiangsu ', abbr=' su '} ProvinceVO{pid=6, abbr=' su '} Province =' zhejiang ', abbr=' zhejiang '} ProvinceVO{pid=10, province=' jiangxi ', abbr=' jiangxi 'Copy the code

Note:${ew.customSqlSegment}Don’t put it in front of itwhereKey words,QueryWrapperWill be attached.

At the end

Writing is not easy, welcome everyone to like, comment, your attention, like is my unremitting power, thank you to see here! Peace and Love.

reference

Conditions for the constructor | MyBatis – Plus (baomidou.com)

preface

preparation

Code to rehearse

Ordinary QueryWrapper

Chain Lambda operation

CustomSqlSegment into arguments

At the end

reference

Related Posts

SpringBoot actual mail alarm based on exception log

Customize the SpringBoot starter

Question 120 – Sum of minimum paths of a triangle