mybatisplus-混合查询

多条件多筛选多排序查询

mapper

   <select id="selectCouponByIno" resultType="com.gzsz.shop.api.shop.bean.ShopPool">
        select a.*, b.cost_amount from (
        select * from t_cs_shop_coupon
        <where>
            status = 1 and stock > 0 and pool_code != 3
            <if test="couponIno != null">
                and coupon_ino = #{couponIno}
            </if>
        </where>
        <choose>
            <when test="sort == 1 and sortField == 1">
                order by create_time desc
            </when>
            <when test="sort == 1 and sortField == 0">
                order by sale_price desc
            </when>
            <otherwise>
                order by create_time asc
            </otherwise>
        </choose>
        limit #{startIndex}, #{size}

        ) a
        left join t_cs_act_acctype b on b.atno = a.coupon_atno
        limit #{startIndex}, #{size}
    </select>

  //test
    List<ShopPool> selectCouponByIno(CouponReq req);

service

@RunWith(SpringRunner.class)
@SpringBootTest(classes = GzszShopApiApplication.class)
public class ShopPoolMapperTest {

    @Resource
    private ShopPoolDao shopPoolDao;

    @Test
    public void shopPoolTest() {
        CouponReq couponReq = new CouponReq();
        couponReq.setCouponIno("S350303H5GPQ21572543");
        System.out.println(couponReq);
        CouponReq couponReq2 = new CouponReq(1,2);
        couponReq2.setCouponIno("S350303H5GPQ21572543");
        couponReq2.setSort(1);
        couponReq2.setSortField(0);
        System.out.println(couponReq2.getStartIndex());
        List<ShopPool> shopPools = null;
        try {
            shopPools = shopPoolDao.selectCouponByIno(couponReq2);
        } catch (NumberFormatException e) {
            e.printStackTrace();
        }

        System.out.println(shopPools);

    }
}

DTO

BaseReq

package com.gzsz.shop.api.shop.dto.req;

import lombok.Data;
import lombok.ToString;

import java.io.Serializable;

/**
 * @description: test
 */
@Data
@ToString
public class BaseReq implements Serializable {
    private Integer page;
    private Integer size;
    private Integer startIndex;

    //静态代码块
    static {
//        this.startIndex = (this.page - 1) * this.size;
        System.out.println("静态代码块");
    }
    //非静态代码块
    {
        System.out.println("非静态代码块");
    }

    public BaseReq() {
        System.out.println("无参构造函数");
    }

    public BaseReq(Integer page, Integer size) {
        System.out.println("有参构造函数");
        this.size = size;
        this.startIndex = (page - 1) * this.size;
    }

}

CouponReq

package com.gzsz.shop.api.shop.dto.req;

import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

/**
 * @description: 测试
 */
@Data
@ToString
@NoArgsConstructor
public class CouponReq extends BaseReq {
    private String couponIno;
    private Integer sort;
    private Integer sortField;

    public CouponReq(Integer page, Integer size) {
        super(page, size);
    }
}

截图

f3b268acacbe391226fa46868c4fa60.png