Appearance

数据库查询 ​

Niucloud 常规数据库查询采用 MyBatis-Plus，MyBatis-Plus 是一个 MyBatis 的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。针对联表查询使用 MyBatis-Plus-Join，MyBatis-Plus-Join 是一个基于 MyBatis-Plus 的多表查询扩展库。

MyBatis-Plus-Join 的优势 ​

MyBatis-Plus-Join 是一个专为 Mybatis-Plus 设计的多表操作插件。它通过扩展 Mybatis-Plus 的构造器，实现了对多表查询的简化处理，使得开发者能够更加高效地进行数据库操作。该插件的使用非常简单，即使是初学者也能在十分钟内掌握其全部功能。

MyBatis-Plus-Join 插件的核心优势在于其对 Mybatis-Plus 的非侵入性扩展。它仅依赖于 lomlock，并且不会对原有项目产生任何影响。此外，该插件支持大部分 Mybatis-Plus 的常用版本，确保了广泛的兼容性。

在技术实现上，MyBatis-Plus-Join 采用了链式调用的方式，使得代码更加简洁和直观。通过使用 QueryWraper，它几乎融合了字符串和 lambda 表达式的优点，提供了强大的查询构建能力。

特别是在大型项目中，当数据库表结构复杂，关联查询频繁时，MyBatis-Plus-Join 能够显著提升开发效率，减少代码冗余，提高系统的可维护性。

条件构造器 ​

MyBatis-Plus-Join 是一个专为 Mybatis-Plus 设计的多表操作插件，条件构造器的使用基于 Mybatis-plus 的语法，具体可以查看Mybatis-plus 条件构造器，下面是针对 niucloud 系统中常用方法做说明。

条件构造函数对应数据库语句实例对应sql语句实例
allEq设置所有字段相等的条件queryWrapper.allEq(Map.of("id", 1, "name", "老王", "age", null));SELECT * FROM user WHERE id = 1 AND name = '老王' AND age IS NULL
eq=queryWrapper.eq("name", "老王");SELECT * FROM user WHERE name = '老王'
ne<>queryWrapper.ne("name", "老王");SELECT * FROM user WHERE name <> '老王'
gt>queryWrapper.gt("age", 18);SELECT * FROM user WHERE age > 18
ge>=queryWrapper.ge("age", 18);SELECT * FROM user WHERE age >= 18
lt<queryWrapper.lt("age", 18);SELECT * FROM user WHERE age < 18
le<=queryWrapper.le("age", 18);SELECT * FROM user WHERE age <= 18
betweenBETWEENqueryWrapper.between("age", 18, 30);SELECT * FROM user WHERE age BETWEEN 18 AND 30
notBetweenNOT BETWEENqueryWrapper.notBetween("age", 18, 30);SELECT * FROM user WHERE age NOT BETWEEN 18 AND 30
likelike '%search%'queryWrapper.like("name", "王");SELECT * FROM user WHERE name LIKE '%王%'
notLikenot like '%search%'queryWrapper.notLike("name", "王");SELECT * FROM user WHERE name NOT LIKE '%王%'
likeLeftlike '%search'queryWrapper.likeLeft("name", "王");SELECT * FROM user WHERE name LIKE '%王'
likeRightlike 'search%'queryWrapper.likeRight("name", "王");SELECT * FROM user WHERE name LIKE '王%'
ininqueryWrapper.in("age", Arrays.asList(1, 2, 3));SELECT * FROM user WHERE age IN (1, 2, 3)
notInnot inqueryWrapper.notIn("age", Arrays.asList(1, 2, 3));SELECT * FROM user WHERE age NOT IN (1, 2, 3)
groupBygroup byqueryWrapper.groupBy("id", "name");SELECT * FROM user GROUP BY id, name
orderByAscorder by cloumn ascqueryWrapper.orderByAsc("id", "name");SELECT * FROM user ORDER BY id ASC, name ASC
orderByDescorder by cloumn descqueryWrapper.orderByDesc("id", "name");SELECT * FROM user ORDER BY id DESC, name DESC

一般多个查询条件是 and，但是也有部分特殊查询使用 or，针对这种查询很容易出错，所以 niucloud 建议使用 or 嵌套，下面是 mybatis-plus 的实例

java
queryWrapper.or(i -> i.and(j -> j.eq("name", "李白").eq("status", "alive"))
                         .or(j -> j.eq("name", "杜甫").eq("status", "alive")));

对应的sql语句是

sql
SELECT * FROM user WHERE (name = '李白' AND status = 'alive') OR (name = '杜甫' AND status = 'alive')

niucloud 系统中使用实例，例如商城插件中查询在特定时间段内是否存在重叠进行的活动，代码如下，里面就用到了多层嵌套

java
        MPJQueryWrapper<ShopActiveGoods> wrapper = new MPJQueryWrapper<>();
        wrapper.setAlias("ag"). innerJoin("?_shop_active a ON ag.active_id = a.active_id".replace("?_", GlobalConfig.tablePrefix));
        wrapper.eq("a.site_id", RequestUtils.siteId());
        wrapper.in("a.active_status", Arrays.asList(ActiveStatusEnum.NOT_ACTIVE.getStatus(), ActiveStatusEnum.ACTIVE.getStatus()));
        wrapper.in("ag.goods_id", goodsIds.toArray());
        wrapper.eq("ag.active_class", "discount");
        Long startTime = DateUtils.StringToTimestamp(shopDiscountParam.getStartTime());
        Long endTime = DateUtils.StringToTimestamp(shopDiscountParam.getEndTime());
        wrapper.and(i -> i.and(j -> j.between("a.start_time", startTime, endTime))
                .or(j -> j.between("a.end_time",  startTime, endTime))
                .or(j-> j.le("a.start_time", startTime).
                        ge("a.end_time", endTime))
                .or(j-> j.le("start_time", startTime).
                        ge("a.end_time", endTime))
        );

联表查询 ​

Mybatis-Plus 大大缩减了 Mybatis 关于数据库查询配置 xml 的复杂度，但是针对联表查询等复杂的查询方式就显得力不从心，所以 Niucloud 系统引入了 Mybatis-Plus-Join 用作联表查询，大大减少了编写 sql 语句的工作量，只要掌握 sql 语句的书写就可以快速整合到系统中，目前常用业务系统复杂关联表在4到5张表，使用 Mybatis-Plus-Join 表现出轻松上手并且高效开发的优势。

项目中实际使用的联表查询示例 ​

以下是 SiteShopServiceImpl 中实际使用的联表查询示例，展示了如何查询店铺扩展信息：

java
@Override
public SiteShopInfoVo info(Integer id) {
    MPJQueryWrapper<Site> queryWrapper = new MPJQueryWrapper<>();
    queryWrapper.select("ns.site_id, ns.site_name, ns.group_id, ns.keywords, ns.app_type, ns.logo, ns.`desc`, ns.status, ns.latitude, ns.longitude, ns.province_id, ns.city_id, ns.district_id, ns.address, ns.full_address, ns.phone, ns.business_hours, ns.create_time, ns.expire_time, ns.front_end_name, ns.front_end_logo, ns.front_end_icon, ns.icon, ns.member_no, ns.app, ns.addons, ns.initalled_addon, ns.site_domain, ns.isinit, nsg.group_name, nss.id, nssc.category_name, nss.is_recommend, nss.is_self, nss.contact_mobile, nss.category_id, nss.commission_rate, nss.shop_deposit, nss.money, nss.money_get, nss.money_cash_outing, nss.bank_type, nss.bank_account_name, nss.bank_account_no, nss.bank_name, nss.bank_address, nss.follow_number, nss.alipay_name, nss.alipay_account_no, nss.alipay_payment_code, nss.wechat_name, nss.wechat_account_no, nss.wechat_payment_code, nss.business_license")
            .setAlias("ns")
            .leftJoin("?_site_shop nss ON ns.site_id = nss.site_id".replace("?_", GlobalConfig.tablePrefix))
            .leftJoin("?_site_group nsg ON ns.group_id = nsg.group_id".replace("?_", GlobalConfig.tablePrefix))
            .leftJoin("?_site_shop_category nssc ON nss.category_id = nssc.category_id".replace("?_", GlobalConfig.tablePrefix));

    queryWrapper.eq("ns.site_id", id);
    SiteShopInfoVo model = siteMapper.selectJoinOne(SiteShopInfoVo.class, queryWrapper);
    Assert.notNull(model, "店铺扩展不存在");
    SiteShopInfoVo.SiteShop shop = new SiteShopInfoVo.SiteShop();
    BeanUtils.copyProperties(model, shop);
    model.setShop(shop);
    // 会员基础信息
    SiteShopInfoVo.MemberInfo memberInfo = new SiteShopInfoVo.MemberInfo();
    memberInfo.setIsFollow(0);
    if (ObjectUtil.isNotEmpty(RequestUtils.memberId())) {
        SiteShopMember member = siteShopMemberMapper.selectOne(new QueryWrapper<SiteShopMember>().eq("member_id", RequestUtils.memberId()).eq("site_id", id).last("limit 1"));
        if (ObjectUtil.isNotEmpty(member)) {
            memberInfo.setIsFollow(member.getIsFollow());
        }
    }
    model.setMemberInfo(memberInfo);
    return model;
}

分页联表查询示例 ​

项目中广泛使用的分页联表查询示例：

java
@Override
public PageResult<SiteShopListVo> page(PageParam pageParam, SiteShopSearchParam searchParam) {
    Integer page  = pageParam.getPage();
    Integer limit = pageParam.getLimit();

    MPJQueryWrapper<Site> queryWrapper = new MPJQueryWrapper<>();
    queryWrapper.select("ns.site_id, ns.site_name, ns.group_id, ns.keywords, ns.app_type, ns.logo, ns.`desc`, ns.status, ns.latitude, ns.longitude, ns.province_id, ns.city_id, ns.district_id, ns.address, ns.full_address, ns.phone, ns.business_hours, ns.create_time, ns.expire_time, ns.front_end_name, ns.front_end_logo, ns.front_end_icon, ns.icon, ns.member_no, ns.app, ns.addons, ns.initalled_addon, ns.site_domain, ns.isinit, nsg.group_name, nss.id, nssc.category_name, nss.is_recommend, nss.is_self, nss.contact_mobile, nss.category_id, nss.commission_rate, nss.shop_deposit, nss.money, nss.money_get, nss.money_cash_outing, nss.bank_type, nss.bank_account_name, nss.bank_account_no, nss.bank_name, nss.bank_address, nss.follow_number, nss.alipay_name, nss.alipay_account_no, nss.alipay_payment_code, nss.wechat_name, nss.wechat_account_no, nss.wechat_payment_code, nss.business_license")
            .setAlias("ns")
            .leftJoin("?_site_shop nss ON ns.site_id = nss.site_id".replace("?_", GlobalConfig.tablePrefix))
            .leftJoin("?_site_group nsg ON ns.group_id = nsg.group_id".replace("?_", GlobalConfig.tablePrefix))
            .leftJoin("?_site_shop_category nssc ON nss.category_id = nssc.category_id".replace("?_", GlobalConfig.tablePrefix));

    if (ObjectUtil.isNotEmpty(searchParam.getCreateTime())) {
        QueryMapperUtils.buildByTime(queryWrapper, "ns.create_time", searchParam.getCreateTime());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getGroupId())) {
        queryWrapper.eq("nss.group_id", searchParam.getGroupId());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getCategoryId())) {
        queryWrapper.eq("nss.category_id", searchParam.getCategoryId());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getStatus())) {
        queryWrapper.eq("ns.status", searchParam.getStatus());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getIsSelf())) {
        queryWrapper.eq("nss.is_self", searchParam.getIsSelf());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getKeyword())) {
        queryWrapper.and(i -> i.or(j -> j.like("ns.site_name", searchParam.getKeyword()))
                .or(j-> j.like("ns.keywords", searchParam.getKeyword()))
        );
    }
    queryWrapper.orderByDesc("ns.create_time");
    IPage<SiteShopListVo> iPage = siteMapper.selectJoinPage(new Page<>(page, limit), SiteShopListVo.class, queryWrapper);
    for (SiteShopListVo item : iPage.getRecords()) {
        SiteShopListVo.SiteShop shop = new SiteShopListVo.SiteShop();
        BeanUtils.copyProperties(item, shop);
        item.setShop(shop);
    }
    return PageResult.build(iPage);
}

带条件的列表联表查询 ​

java
@Override
public List<SiteShopListVo> list(SiteShopSearchParam searchParam) {
    MPJQueryWrapper<Site> queryWrapper = new MPJQueryWrapper<>();
    queryWrapper.select("ns.site_id, ns.site_name, ns.group_id, ns.keywords, ns.app_type, ns.logo, ns.`desc`, ns.status, ns.latitude, ns.longitude, ns.province_id, ns.city_id, ns.district_id, ns.address, ns.full_address, ns.phone, ns.business_hours, ns.create_time, ns.expire_time, ns.front_end_name, ns.front_end_logo, ns.front_end_icon, ns.icon, ns.member_no, ns.app, ns.addons, ns.initalled_addon, ns.site_domain, ns.isinit, nsg.group_name, nss.id, nssc.category_name, nss.is_recommend, nss.is_self, nss.contact_mobile, nss.category_id, nss.commission_rate, nss.shop_deposit, nss.money, nss.money_get, nss.money_cash_outing, nss.bank_type, nss.bank_account_name, nss.bank_account_no, nss.bank_name, nss.bank_address, nss.follow_number, nss.alipay_name, nss.alipay_account_no, nss.alipay_payment_code, nss.wechat_name, nss.wechat_account_no, nss.wechat_payment_code, nss.business_license")
            .setAlias("ns")
            .leftJoin("?_site_shop nss ON ns.site_id = nss.site_id".replace("?_", GlobalConfig.tablePrefix))
            .leftJoin("?_site_group nsg ON ns.group_id = nsg.group_id".replace("?_", GlobalConfig.tablePrefix))
            .leftJoin("?_site_shop_category nssc ON nss.category_id = nssc.category_id".replace("?_", GlobalConfig.tablePrefix));

    if (ObjectUtil.isNotEmpty(searchParam.getCreateTime())) {
        QueryMapperUtils.buildByTime(queryWrapper, "ns.create_time", searchParam.getCreateTime());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getGroupId())) {
        queryWrapper.eq("ns.group_id", searchParam.getGroupId());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getCategoryId())) {
        queryWrapper.eq("nss.category_id", searchParam.getCategoryId());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getStatus())) {
        queryWrapper.eq("ns.status", searchParam.getStatus());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getIsSelf())) {
        queryWrapper.eq("ns.is_self", searchParam.getIsSelf());
    }
    if (ObjectUtil.isNotEmpty(searchParam.getKeyword())) {
        queryWrapper.and(i -> i.or(j -> j.like("ns.site_name", searchParam.getKeyword()))
                .or(j-> j.like("ns.keywords", searchParam.getKeyword()))
        );
    }
    queryWrapper.orderByDesc("ns.create_time");
    List<SiteShopListVo> list = siteMapper.selectJoinList(SiteShopListVo.class, queryWrapper);
    for (SiteShopListVo item : list) {
        SiteShopListVo.SiteShop shop = new SiteShopListVo.SiteShop();
        BeanUtils.copyProperties(item, shop);
        item.setShop(shop);
    }
    return list;
}

特殊查询操作 ​

进行数据库查询存在一些特殊的字段查询，如果单独书写费时费力，Niucloud 进行了单独的封装

时间段查询 (buildByTime) ​

针对时间段查询，比如查询会员列表，需要查询2024-11-1到2024-11-15注册的会员，数据表中存储注册时间是时间戳，传入时间是字符串数组，包括开始时间与结束时间。

首先传入时间字段是时间格式的字符串数组，不需要定义开始时间，结束时间的格式，比如会员列表传入参数：

java
    /** 创建时间筛选 */
    private String[] createTime;

查询条件构造器只需调用固定函数即可：

java
if (ObjectUtil.isNotEmpty(searchParam.getCreateTime())) {
    QueryMapperUtils.buildByTime(queryWrapper, "m.create_time", searchParam.getCreateTime());
}

在项目中的实际使用示例：

java
// 会员账户流水记录查询
if (ObjectUtil.isNotEmpty(param.getCreateTime())) {
    QueryMapperUtils.buildByTime(queryWrapper, "create_time", param.getCreateTime());
}

数值范围查询 (buildByBetween) ​

针对数值范围查询，比如查询价格在100-200之间的商品，系统提供了buildByBetween方法。

java
// 商品价格范围查询示例
if (ObjectUtil.isNotEmpty(searchParam.getStartPrice()) || ObjectUtil.isNotEmpty(searchParam.getEndPrice())) {
    Double[] prices = {searchParam.getStartPrice(), searchParam.getEndPrice()};
    QueryMapperUtils.buildByBetween(queryWrapper, "gs.price", prices);
}

// 商品销量范围查询示例
QueryMapperUtils.buildByBetween(queryWrapper, "g.sale_num", new Integer[]{searchParam.getStartSaleNum(), searchParam.getEndSaleNum()});

在项目中的实际使用示例（MallGoodsServiceImpl）：

java
if (ObjectUtil.isNotEmpty(searchParam.getGoodsSource())){
    if(searchParam.getGoodsSource().equals("self_goods") || searchParam.getGoodsSource().equals("0")){
        queryWrapper.eq("g.supply_goods_id", 0);
    }else{
        queryWrapper.ne("g.supply_goods_id", 0);
    }
}
// 价格范围查询
QueryMapperUtils.buildByBetween(queryWrapper, "gs.price", new Double[]{searchParam.getStartPrice(), searchParam.getEndPrice()});

MyBatis-Plus-Join 查询支持 ​

QueryMapperUtils同样支持MPJQueryWrapper，用于多表查询的条件构造：

java
MPJQueryWrapper<Site> queryWrapper = new MPJQueryWrapper<>();
queryWrapper.select("ns.site_id, ns.site_name, ...")
        .setAlias("ns")
        .leftJoin("?_site_shop nss ON ns.site_id = nss.site_id".replace("?_", GlobalConfig.tablePrefix));

if (ObjectUtil.isNotEmpty(searchParam.getCreateTime())) {
    QueryMapperUtils.buildByTime(queryWrapper, "ns.create_time", searchParam.getCreateTime());
}

基于 MIT 协议发布

Copyright © 2019-present NiuCloud Team