1、mp的lambda分页查询：
public IPage<TestVO> page(TestQueryDTO testQueryDTO, Query query) {
List<Long> testIds = null;
if (StringUtil.isNotBlank(testQueryDTO.getManager())) {
// 模糊查询
List<Test> testList = testService.list(Wrappers.<User>lambdaQuery()
.select(Test::getId)
.eq(Test::getTestId, testQueryDTO.getTestId)
.likeRight(Test::getName, testQueryDTO.getName())
);
if (CollectionUtil.isNotEmpty(testList)) {
testIds = testList.stream().map(Test::getId).collect(Collectors.toList());
} else {
return Condition.getPage(query);
}
log.info("右模糊查询【{}】",testList);
}
if (ObjectUtil.isNotEmpty(testQueryDTO.getId())) {
// 根据id查询
List<Test> testList = operateTestClient.selectUserByDeptId(testQueryDTO.getId());
if (CollectionUtil.isNotEmpty(testList)) {
testIds = testList .stream().map(Test::getId).collect(Collectors.toList());
} else {
return Condition.getPage(query);
}
}
IPage<Test> pages = this.page(Condition.getPage(query), Wrappers.<Test>lambdaQuery()
.likeRight(StringUtil.isNotBlank(testQueryDTO.getName()), Test::getName, testQueryDTO.getName())
.likeRight(StringUtil.isNotBlank(testQueryDTO.getPhone()),Test::getPhone,testQueryDTO.getPhone())
.ge(ObjectUtil.isNotEmpty(testQueryDTO.getStartTime()),Test::getCreateTime,testQueryDTO.getStartTime())
.le(ObjectUtil.isNotEmpty(testQueryDTO.getEndTime()),Test::getCreateTime,testQueryDTO.getEndTime())
.in(StringUtil.isNotBlank(testQueryDTO.getId()),Test::getId,testIds )
.eq(ObjectUtil.isNotEmpty(testQueryDTO.getId()),Test::getId,testQueryDTO.getId())
);
return TestWrapper.build().pageVO(pages);
}Condition.getPage(query) 可以用
Page<T> page = new Page(query.getCurrent(), query.getSize()); 代替
1.1、TestWrapper：
public class TestWrapper extends BaseEntityWrapper<Test, TestVO> {
private ITestService testService;
public TestWrapper (ITestService testService) {
this.testService= testService;
}
public static TestWrapper build(ITestService testService) {
return new TestWrapper (channelService);
}
@Override
public TestVOentityVO(Test test) {
TestVO testVO= Objects.requireNonNull(BeanUtil.copy(test, TestVO.class));
Test1 test1 = testService.getById(test.getId());
if (ObjectUtil.isNotEmpty(test1)) {
testVO.setId1(test1 .getId1());
testVO.setName(test1 .getName());
}
return sealWebVO;
}
}
1.2、BaseEntityWrapper：
public abstract class BaseEntityWrapper<E, V> {
public BaseEntityWrapper() {
}
public abstract V entityVO(E entity);
public List<V> listVO(List<E> list) {
return (List)list.stream().map(this::entityVO).collect(Collectors.toList());
}
public IPage<V> pageVO(IPage<E> pages) {
List<V> records = this.listVO(pages.getRecords());
IPage<V> pageVo = new Page(pages.getCurrent(), pages.getSize(), pages.getTotal());
pageVo.setRecords(records);
return pageVo;
}
}
2、and、or查询：
List<Test> list = this.list(Wrappers.<Test>lambdaQuery()
.eq(Test::getStatus,TestStatusEnum.NORMAL.getCode())
.and(
ObjectUtil.isEmpty(reqDTO.getType()) && reqDTO.getBelongType() == TestStatusEnum.NORMAL.getCode()
,wrapper -> wrapper
.eq(Seal::getUserId,reqDTO.getUserId())
.eq(Seal::getBelongType,reqDTO.getBelongType())
)
.and(
ObjectUtil.isNotEmpty(reqDTO.getType())
,wrapper -> wrapper
.eq(Test::getUserId,reqDTO.getUserId())
.eq(Test::getType,reqDTO.getType())
)
.and(
ObjectUtil.isNotEmpty(reqDTO.getType()) && reqDTO.getBelongType() == TestStatusEnum.NORMAL.getCode()
,wrapper -> wrapper
.eq(Test::getType,reqDTO.getType())
.or()
.eq(Test::getUserId,reqDTO.getUserId())
)
);3、保存：
this.save(test);4、修改：
this.updateById(test);5、删除：
this.remove(Wrappers.<CaseType>lambdaUpdate()
.set(CaseType::getStatus,caseTypeUpdateStatusDTO.getStatus())
.eq(CaseType::getId,caseTypeUpdateStatusDTO.getId())
);6、原生sql查询：
<select id="selectTestPage" resultMap="ukeyOrderResultMap">
select
test1,test2
from Test
<where>
is_deleted = 0
<if test="test.test1!= 0">
and test1= #{test.test1}
</if>
<if test="test.test1!= null ">
and test1= #{test.test1}
</if>
<if test="test.test1!= null">
and test1= #{test.test1}
</if>
<if test="test1.test1!= null and test.test1.trim != ''">
and whatsoever LIKE CONCAT('',#{test.test1},'%')
</if>
<if test="test.test1!= null ">
and id_no LIKE CONCAT('',#{test.test1},'%')
</if>
<if test="test.test1!= null ">
and test1<![CDATA[>=]]> #{test.timeStart}
</if>
<if test="test.test1!= null ">
and test1<![CDATA[<=]]> #{test1.timeEnd}
</if>
</where>
</select>
<if test="list != null and list.size > 0" >
WHERE id IN
<foreach collection="list" item="id" index="index" open="(" close=")" separator=",">
#{id}
</foreach>
</if>
逗号分隔：
<if test="testName != null and testName != ''">
<if test="testName.indexOf(',') != -1 ">
and test_name in
<foreach item="testItem" index="testIndex" collection="testName.split(',')" open="(" separator="," close=")">
#{testItem}
</foreach>
</if>
<if test="testName.indexOf(',') == -1 ">
and test_name = #{testName}
</if>
</if>
大于等于：
where a.create_time >= #{startTime}
小于等于
and a.create_time <= #{endTime}2023-05-26 start:
java 枚举类：
import lombok.AllArgsConstructor;
import lombok.Getter;
@Getter
@AllArgsConstructor
public enum TestEnum {
/**
* 1 测试1
*/
TEST_FIRST(1, "测试1"),
/**
* 2 测试2
*/
TEST_SECOND(2,"测试2"),
;
private final Integer code;
private final String msg;
public static TestEnum getInstance(Integer code) {
for (TestEnum value : TestEnum.values()) {
if (value.code.equals(code)) {
return value;
}
}
return null;
}
}
end
2025-03-07 start:
分组查询：
<resultMap id="FaultGroupResultMap" type="FaultGroupResult">
<result property="faultGroupCode" column="faultGroupCode"/>
<result property="faultGroupName" column="faultGroupName"/>
<collection property="dataList" ofType="FaultAnalyzeThresholdConfig">
<result property="id" column="id"/>
<result property="faultGroupName" column="faultGroupName"/>
<result property="faultGroupCode" column="faultGroupCode"/>
<result property="faultThresholdName" column="faultThresholdName"/>
<result property="threshold" column="threshold"/>
<result property="unit" column="unit"/>
<result property="remark" column="remark"/>
<result property="createTime" column="createTime"/>
<result property="createBy" column="createBy"/>
<result property="updateTime" column="updateTime"/>
<result property="updateBy" column="updateBy"/>
</collection>
</resultMap>
<select id="groupByFaultGroup" resultMap="FaultGroupResultMap">
SELECT
fault_group_code AS faultGroupCode,
fault_group_name AS faultGroupName,
JSON_ARRAYAGG(
JSON_OBJECT(
'id', id,
'faultGroupName', fault_group_name,
'faultGroupCode', fault_group_code,
'faultThresholdName', fault_threshold_name,
'threshold', threshold,
'unit', unit,
'remark', remark,
'createTime', create_time,
'createBy', create_by,
'updateTime', update_time,
'updateBy', update_by
)
) AS dataList
FROM
t_fault_analyze_threshold_config
GROUP BY
fault_group_code, fault_group_name;
</select>返回的实体对象：
import lombok.Data;
import java.util.List;
@Data
public class FaultGroupResult {
private String faultGroupCode;
private String faultGroupName;
private List<FaultAnalyzeThresholdConfig> dataList;
}数据实体对象：
import lombok.Data;
import java.util.Date;
@Data
public class FaultAnalyzeThresholdConfig {
private Long id;
private String faultGroupName;
private String faultGroupCode;
private String faultThresholdName;
private Long threshold;
private String unit;
private String remark;
private Date createTime;
private String createBy;
private Date updateTime;
private String updateBy;
}end
2025-03-19 start
批量修改多条数据：
<update id="updateThresholdById" parameterType="xyz.haijin.UpdateVo">
UPDATE t_fault_analyze_threshold_config
SET
threshold = CASE id
<foreach collection="list" item="item" separator=" ">
WHEN #{item.id} THEN #{item.threshold}
</foreach>
END
WHERE id IN
<foreach collection="list" item="item" open="(" separator="," close=")">
#{item.id}
</foreach>
</update>请求参数：
@Data
public class UpdateVo {
private List<UpdateObjectVo> list;
@Data
public static class UpdateObjectVo {
/**
* 主键ID
*/
private Long id;
/**
* 阈值
*/
private Long threshold;
}
}end