使用Mapper方式进行联表查询

有些业务场景需要查询两张表的数据,这时候就需要用到联表查询。下面将介绍两种方式进行联表查询。

场景:A模型页面,查询条件中包含B模型字段

模型A

@Model.model(YesOne.MODEL_MODEL)
@Model(displayName = "YesOne", summary = "YesOne")
public class YesOne extends IdModel {

    public static final String MODEL_MODEL = "top.YesOne";

    @Field.Integer
    @Field(displayName = "YesId")
    private Long yesId;

    @Field.String
    @Field(displayName = "名字")
    private String name;

    @Field.String
    @Field(displayName = "科目名字")
    private String professionalName;

    @Field(displayName = "关联YesTwo")
    @Field.many2one
    @Field.Relation(relationFields = {"yesId"},referenceFields = {"id"})
    private YesTwo yesTwo;

}

模型B

@Model.model(YesTwo.MODEL_MODEL)
@Model(displayName = "YesTwo", summary = "YesTwo")
public class YesTwo extends IdModel {

    public static final String MODEL_MODEL = "top.YesTwo";

    @Field.Integer
    @Field(displayName = "科目id")
    private Long professionalId;

    @Field.String
    @Field(displayName = "科目名字")
    private String professionalName;

}

1. 使用in的方式查询

通过B模型的查询条件查询出符合条件的所有数据ID,再根据这个ID去A模型里面查询出所需的数据。

    @Function.Advanced(displayName = "查询列表", type = FunctionTypeEnum.QUERY, category = FunctionCategoryEnum.QUERY_PAGE, managed = true)
    @Function(openLevel = {FunctionOpenEnum.LOCAL, FunctionOpenEnum.REMOTE, FunctionOpenEnum.API})
    public Pagination<YesOne> queryPage(Pagination<YesOne> page, IWrapper<YesOne> queryWrapper) {

        String professionalName = (String) queryWrapper.getQueryData().get("professionalName");
        if (StringUtils.isNotBlank(professionalName)) {
            List<Long> yesTwoId = new YesTwo().queryList(Pops.<YesTwo>lambdaQuery()
                            .from(YesTwo.MODEL_MODEL)
                            .eq(YesTwo::getProfessionalName, professionalName))
                    .stream().map(YesTwo::getId)
                    .collect(Collectors.toList());
            LambdaQueryWrapper<YesOne> wq = Pops.<YesOne>lambdaQuery().from(YesOne.MODEL_MODEL);
            if (CollectionUtils.isNotEmpty(yesTwoId)) {
                wq.in(YesOne::getYesId, yesTwoId);
            }
            return new YesOne().queryPage(page, wq);
        }
        return new YesOne().queryPage(page, queryWrapper);
    }

2. 使用mapper的方式查询

利用sql的方式去直接查询出结果。使用联表查询的方式查询

    @Autowired
    private YesOneQueryMapper yesOneQueryMapper;

    @Function.Advanced(displayName = "查询列表", type = FunctionTypeEnum.QUERY, category = FunctionCategoryEnum.QUERY_PAGE, managed = true)
    @Function(openLevel = {FunctionOpenEnum.LOCAL, FunctionOpenEnum.REMOTE, FunctionOpenEnum.API})
    public Pagination<YesOne> queryPage(Pagination<YesOne> page, IWrapper<YesOne> queryWrapper) {

        try (DsHintApi dsHint = DsHintApi.model(YesOne.MODEL_MODEL)) {
            String professionalName = (String) queryWrapper.getQueryData().get("professionalName");
            String yesOneTable = PamirsSession.getContext().getModelCache().get(YesOne.MODEL_MODEL).getTable();
            String yesTwoTable = PamirsSession.getContext().getModelCache().get(YesTwo.MODEL_MODEL).getTable();
            StringBuffer where = new StringBuffer().append("a.is_deleted = 0").append(CharacterConstants.SEPARATOR_BLANK)
                    .append(SqlConstants.AND).append(CharacterConstants.SEPARATOR_BLANK)
                    .append("b.is_deleted=0").append(CharacterConstants.SEPARATOR_BLANK);
            if (StringUtils.isNotBlank(professionalName)) {
                where.append(SqlConstants.AND).append(CharacterConstants.SEPARATOR_BLANK).append("b.").
                        append(PStringUtils.fieldName2Column(LambdaUtil.fetchFieldName(YesOne::getProfessionalName))).append(CharacterConstants.SEPARATOR_BLANK).
                        append(SqlConstants.EQ).append(professionalName);
            }
            StringBuffer limit = new StringBuffer().append(page.getStart() + " , " + page.getSize());
            List<YesOne> yesOnes = yesOneQueryMapper.unionTableQuery(yesOneTable, yesTwoTable, where.toString(), limit.toString());
            Long total = yesOneQueryMapper.queryTotal(yesOneTable, yesTwoTable, where.toString());
            page.setTotalElements(total);
            page.setContent(yesOnes);
        }
        return page;
    }

接口

package pro.shushi.pamirs.top.core.service;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import pro.shushi.pamirs.top.api.model.YesOne;

import java.util.List;

@Mapper
public interface YesOneQueryMapper {

    @Select("<script>"
            + "SELECT "
            + "a.id , "
            + "a.name, "
            + "b.professional_name as professionalName "
            + "FROM ${yesOne} a "
            + "INNER JOIN ${yesTwo} b ON a.yes_id = b.id "
            + "<if test='whereConditions != null'>"
            + "where (${whereConditions}) "
            + "</if>"
            + "ORDER BY a.id ASC "
            + "<if test='limitConditions != null'>"
            + "LIMIT ${limitConditions} "
            + "</if>"
            + "</script>")
    List<YesOne> unionTableQuery(@Param("yesOne") String yesOne, @Param("yesTwo") String yesTwo, @Param("whereConditions") String whereConditions, @Param("limitConditions") String limitConditions);

    @Select("<script>"
            + "SELECT count(a.id )"
            + "FROM ${yesOne} a "
            + "INNER JOIN ${yesTwo} b ON a.yes_id = b.id "
            + "<if test='whereConditions != null'>"
            + "where (${whereConditions}) "
            + "</if>"
            + "</script>")
    Long queryTotal(@Param("yesOne") String yesOne, @Param("yesTwo") String yesTwo, @Param("whereConditions") String whereConditions);

}

Oinone社区 作者:yexiu原创文章,如若转载,请注明出处:https://doc.oinone.top/kai-fa-shi-jian/17950.html

访问Oinone官网:https://www.oinone.top获取数式Oinone低代码应用平台体验

(0)
yexiu的头像yexiu数式员工
上一篇 2024年9月27日 am11:20
下一篇 2024年9月29日 pm8:56

相关推荐

  • Oinone项目引入Nacos作为配置中心

    Oinone项目引入nacos作为配置中心 Oinone项目配置默认读取的项目中yml文件(application-xxx.yml), 实际项目中有可能要求项目的配置放到Nacos配置中心中; Oinone默认引入的nacos-client-1.4.1,低于1.4.1的版本不支持认证配置;1.4.1的客户端版本支持Nacos服务端1.x的和2.x的版本; 一、项目中增加依赖 项目主pom引入依赖(最新版平台已默认引入), Nacos版本要求1.4.1以上,低版本不支持认证配置 <dependency> <groupId>com.alibaba.cloud</groupId> <artifactId>spring-cloud-starter-alibaba-nacos-config</artifactId> <version>2021.1</version> </dependency> <dependency> <groupId>com.alibaba.nacos</groupId> <artifactId>nacos-client</artifactId> <version>1.4.1</version> </dependency> 项目的boot工程的pom引入依赖(最新版平台已默认引入) <dependency> <groupId>com.alibaba.cloud</groupId> <artifactId>spring-cloud-starter-alibaba-nacos-config</artifactId> </dependency> <dependency> <groupId>com.alibaba.nacos</groupId> <artifactId>nacos-client</artifactId> </dependency> 二、项目工程bootstrap配置 【全】bootstrap.yml文件的配置修改为: spring: profiles: dev cloud: nacos: #【必须】修改为nacos服务端的地址 server-addr: http://192.168.0.118:8848 discovery: enabled: false namespace: prefix: application file-extension: yml config: enabled: true #【必须】修改为nacos服务端配置的namespace namespace: 5a8b3710-a9a2-4f7c-932f-50f326cb1ccf group: DEFAULT_GROUP prefix: pamirs-demo-application file-extension: yml #【可选】修改为和nacos一致即可(如果服务端未开启可以不用配置) username: wangxian password: wangxian 【精简】bootstrap.yml文件的配置修改为: spring: cloud: nacos: server-addr: http://192.168.6.206:8848 config: file-extension: yml enabled: true namespace: 5a8b3710-a9a2-4f7c-932f-50f326cb1ccf shared-configs: – data-id: pamirs-demo-application-dev.yml 三、Naocs服务端配置 在Nacos服务端的对应的namespace(5a8b3710-a9a2-4f7c-932f-50f326cb1ccf)下增加配置,把原本配置在代码中的(application-xxx.yml)配置到Nacos中

    2024年2月28日
    86400
  • 项目中常用的 Tools 工具类

    模型拷贝工具类 KryoUtils.get().copy(modelData); ArgUtils.convert(DataReport.MODEL_MODEL, DataDesignerReport.MODEL_MODEL, report); pro.shushi.pamirs.framework.common.utils.ObjectUtils#clone(T) Rsql工具类 RsqlParseHelper.parseRsql2Sql(queryWrapper.getModel(), rsql); RSQLHelper.getRsqlValues(sql.getOriginRsql(), fieldSet); 序列化工具类 后端使用的JSON序列化 JsonUtils.toJSONString(nodes); 前端使用的JSON序列化 PamirsJsonUtils.toJSONString(nodes, SerializerFeature.DisableCircularReferenceDetect, SerializerFeature.WriteDateUseDateFormat, SerializerFeature.BrowserCompatible); 生成ID //根据模型生成id Long generate = (Long) Spider.getDefaultExtension(IdGenerator.class).generate(PamirsTableInfo.fetchKeyGenerator(Teacher.MODEL_MODEL)); //生成id Long l = Long.valueOf(UidGeneratorFactory.getCachedUidGenerator().getUID()); 权限相关 // 获取权限路径path AccessResourceInfoSession.getInfo().getOriginPath(); 其他 PamirsSession相关 PamirsSession.isAdmin() # 是否admin用户 PamirsSession.getUserId() # 获取登录用户ID PamirsSession.getRequestVariables() PamirsSession.getContext().getModelCache().get(PetShop.MODEL_MODEL).getTable(); # 获取模型信息 PamirsSession.getContext().getExtendCache(ActionCacheApi.class).get(Teacher.MODEL_MODEL, "importArchivesInfo") # 获取函数信息

    2025年5月8日
    56201
  • OSS(CDN)配置和文件系统的一些操作

    目前Oinone支持的OSS类型 类型 服务 OSS 阿里云OSS UPYUN 又拍云 MINIO MinIO HUAWEI_OBS 华为云OBS LOCAL 本地NGINX文件存储 TENCENT_COS 腾讯云COS OSS通用yaml配置 cdn: oss: name: # 名称 type: # 类型 bucket: uploadUrl: # 上传URL downloadUrl: # 下载URL accessKeyId: accessKeySecret: mainDir: # 主目录 validTime: 3600000 timeout: 600000 active: true referer: localFolderUrl: others: [key]: name: # 名称 type: # 类型 bucket: uploadUrl: # 上传URL downloadUrl: # 下载URL accessKeyId: accessKeySecret: mainDir: # 主目录 validTime: 3600000 timeout: 600000 active: true referer: localFolderUrl: PS:others中使用自定义key来指定OSS服务进行文件上传/下载功能。上传/下载必须匹配,否则无法正常使用。 OSS配置示例 阿里云OSS cdn: oss: name: 阿里云 type: OSS bucket: pamirs(根据实际情况修改) uploadUrl: oss-cn-hangzhou.aliyuncs.com downloadUrl: oss-cn-hangzhou.aliyuncs.com accessKeyId: 你的accessKeyId accessKeySecret: 你的accessKeySecret # 根据实际情况修改 mainDir: upload/ validTime: 3600000 timeout: 600000 active: true imageResizeParameter: referer: 华为云OBS cdn: oss: name: 华为云 type: HUAWEI_OBS bucket: pamirs(根据实际情况修改) uploadUrl: obs.cn-east-2.myhuaweicloud.com downloadUrl: obs.cn-east-2.myhuaweicloud.com accessKeyId: 你的accessKeyId accessKeySecret: 你的accessKeySecret # 根据实际情况修改 mainDir: upload/ validTime: 3600000 timeout: 600000 active: true allowedOrigin: http://192.168.95.31:8888,https://xxxx.xxxxx.com referer: 华为云OBS需要在启动工程增加以下依赖 <okhttp3.version>4.9.3</okhttp3.version> <dependency> <groupId>com.squareup.okhttp3</groupId> <artifactId>okhttp</artifactId> <version>${okhttp3.version}</version> </dependency> 注意事项华为云OBS的防盗链配置,仅允许携带特定referer的才可以,而excel导入后端处理的逻辑匿名读的时候是不带referer的,所以会被拒绝 MINIO 文件系统,mino的配置: cdn: oss: name: minio type: MINIO bucket: pamirs(根据实际情况修改) uploadUrl: http://192.168.243.6:32190(根据实际情况修改) downloadUrl: http://192.168.243.6:9000(根据实际情况修改) accessKeyId: 你的accessKeyId accessKeySecret: 你的accessKeySecret # 根据实际情况修改 mainDir: upload/ validTime: 3600000 timeout: 600000 active: true referer: localFolderUrl: MINIO无公网访问地址下OSS的配置方式: https://doc.oinone.top/yun-wei-shi-jian/7112.html 又拍云 cdn: oss: name: 又拍云 type: UPYUN…

    后端 2023年11月1日
    1.2K00
  • 首次登录修改密码和自定义密码规则等

    场景描述 在某些场景下,可能需要实现 用户首次登录强制修改密码的功能,或者存在修改平台默认密码等校验规则等需求;本文将讲解不改变平台代码的情况下,如何实现这些功能需求。 首次登录修改密码 方案概述 自定义User增加是否是第一次登录的属性,登录后执行一个扩展点。 判断是否是一次登录,如果是则返回对应的状态码,前端根据状态码重定向到修改密码的页面。修改完成则充值第一次登录的标识。 PS:首次登录的标识平台前端已默认实现 扩展PamirsUser(例如:DemoUser) /** * @author wangxian */ @Model.model(DemoUser.MODEL_MODEL) @Model(displayName = "用户", labelFields = {"nickname"}) @Model.Advanced(index = {"companyId"}) public class DemoUser extends PamirsUser { public static final String MODEL_MODEL = "demo.DemoUser"; @Field.Integer @Field.Advanced(columnDefinition = "bigint DEFAULT '0'") @Field(displayName = "公司ID", invisible = true) private Long companyId; /** * 默认true->1 */ @Field.Boolean @Field.Advanced(columnDefinition = "tinyint(1) DEFAULT '1'") @Field(displayName = "是否首次登录") private Boolean firstLogin; } 定义扩展点接口(实际项目按需要增加和删减接口的定义) import pro.shushi.pamirs.meta.annotation.Ext; import pro.shushi.pamirs.meta.annotation.ExtPoint; import pro.shushi.pamirs.user.api.model.tmodel.PamirsUserTransient; @Ext(PamirsUserTransient.class) public interface PamirsUserTransientExtPoint { @ExtPoint PamirsUserTransient loginAfter(PamirsUserTransient user); @ExtPoint PamirsUserTransient loginCustomAfter(PamirsUserTransient user); @ExtPoint PamirsUserTransient firstResetPasswordAfter(PamirsUserTransient user); @ExtPoint PamirsUserTransient firstResetPasswordBefore(PamirsUserTransient user); @ExtPoint PamirsUserTransient modifyCurrentUserPasswordAfter(PamirsUserTransient user); @ExtPoint PamirsUserTransient modifyCurrentUserPasswordBefore(PamirsUserTransient user); } 编写扩展点实现(例如:DemoUserLoginExtPoint) @Order(0) @Component @Ext(PamirsUserTransient.class) @Slf4j public class DemoUserLoginExtPoint implements PamirsUserTransientExtPoint { @Override @ExtPoint.Implement public PamirsUserTransient loginAfter(PamirsUserTransient user) { return checkFirstLogin(user); } private PamirsUserTransient checkFirstLogin(PamirsUserTransient user) { //首次登录需要修改密码 Long userId = PamirsSession.getUserId(); if (userId == null) { return user; } DemoUser companyUser = new DemoUser().queryById(userId); // 判断用户是否是第一次登录,如果是第一次登录,需要返回错误码,页面重新向登录 Boolean isFirst = companyUser.getFirstLogin(); if (isFirst) { //如果是第一次登录,返回一个标识给前端。 // 首次登录的标识平台已默认实现 user.setBroken(Boolean.TRUE); user.setErrorCode(UserExpEnumerate.USER_FIRST_LOGIN_ERROR.code()); return user; } return user; } @Override public PamirsUserTransient loginCustomAfter(PamirsUserTransient user) { return checkFirstLogin(user); } @Override…

    2024年5月25日
    3.1K00
  • Oinone登录扩展:对接SSO(适应于4.7.8之前的版本)

    适配版本 4.7.8之前的版本 概述 在企业内部,对于已有一套完整的登录系统(SSO)的情况下,通常会要求把所有的系统都对接到SSO中;本文主要讲解用Oinone开发的项目对接SSO的具体实现。 对接步骤 1、项目自定义实现UserCookieLogin,可参考默认是的示例说明:pro.shushi.pamirs.user.api.login.UserCookieLoginFree 2、具体的对接示例 package pro.shushi.pamirs.demo.core.sso; import com.alibaba.fastjson.JSON; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.core.annotation.Order; import org.springframework.data.redis.core.RedisTemplate; import org.springframework.stereotype.Component; import org.springframework.web.context.request.RequestContextHolder; import org.springframework.web.context.request.ServletRequestAttributes; import pro.shushi.pamirs.demo.core.sso.constant.HttpConstant; import pro.shushi.pamirs.demo.core.sso.constant.SSOConstant; import pro.shushi.pamirs.demo.core.sso.constant.SessionUserTypeEnum; import pro.shushi.pamirs.demo.core.sso.model.ApiCommonTransient; import pro.shushi.pamirs.demo.core.sso.utils.AuthenticateUtils; import pro.shushi.pamirs.demo.core.sso.model.PermissionInfoResp; import pro.shushi.pamirs.meta.annotation.fun.extern.Slf4j; import pro.shushi.pamirs.meta.api.dto.model.PamirsUserDTO; import pro.shushi.pamirs.meta.api.dto.protocol.PamirsRequestVariables; import pro.shushi.pamirs.meta.api.session.PamirsSession; import pro.shushi.pamirs.meta.common.exception.PamirsException; import pro.shushi.pamirs.meta.common.spring.BeanDefinitionUtils; import pro.shushi.pamirs.meta.common.util.UUIDUtil; import pro.shushi.pamirs.resource.api.enmu.UserSignUpType; import pro.shushi.pamirs.user.api.cache.UserCache; import pro.shushi.pamirs.user.api.constants.UserConstant; import pro.shushi.pamirs.user.api.enmu.UserExpEnumerate; import pro.shushi.pamirs.user.api.enmu.UserLoginTypeEnum; import pro.shushi.pamirs.user.api.login.IUserLoginChecker; import pro.shushi.pamirs.user.api.login.UserCookieLogin; import pro.shushi.pamirs.user.api.login.UserCookieLoginSimple; import pro.shushi.pamirs.user.api.model.PamirsUser; import pro.shushi.pamirs.user.api.model.tmodel.PamirsUserTransient; import pro.shushi.pamirs.user.api.service.UserService; import pro.shushi.pamirs.user.api.utils.CookieUtil; import javax.servlet.http.HttpServletRequest; import javax.servlet.http.HttpServletResponse; import java.util.concurrent.TimeUnit; /** * * @author wangxian * * 完全自定义login的过程 * 需要实现登陆部分login 以及拦截部分fetchUserIdByReq * 如果fetchUserIdByReq返回值为null的时候 将会被拦截 */ @Slf4j @Order(0) @Component public class DemoUserSSOCookieLogin extends UserCookieLogin<PamirsUser> { //刷新令牌 private static String REFRESH_TOKEN = "refreshToken"; //系统id private static String CLIENT_ID = "client-id"; //访问令牌 private static String AUTHORIZATION = "Authorization"; private IUserLoginChecker checker; @Autowired private UserService userService; @Autowired private RedisTemplate<String, String> redisTemplate; @Override public String createSessionId(HttpServletRequest request, PamirsUser idModel) { return UUIDUtil.getUUIDNumberString(); } @Override public String type() { return UserLoginTypeEnum.COOKIE.value(); } @Override public PamirsUser resolveAndVerification(PamirsUserTransient user) { if (checker == null) { checker = BeanDefinitionUtils.getBean(IUserLoginChecker.class); } return checker.check4login(user); } /** * 重写登录拦截功能 * 该函数主要作用,通过三方权限校验.…

    2023年11月24日
    93300

Leave a Reply

登录后才能评论