有些业务场景需要查询两张表的数据，这时候就需要用到联表查询。下面将介绍两种方式进行联表查询。
场景：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低代码应用平台体验
