mybatisPlus动态sql语句 ${ew.sqlSegment}详解

最新推荐文章于 2024-05-16 14:13:04 发布

王林-wlin

最新推荐文章于 2024-05-16 14:13:04 发布

阅读量6.5k

点赞数 12

文章标签： sql mybatis mybatis-plus

本文为CSDN博主「王林」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。

本文链接：https://blog.csdn.net/qq_45912025/article/details/136901073

版权

mybatisPlus动态sql语句 ${ew.sqlSegment}

这里主要是介绍通过MyBatis Plus使用${ew.sqlSegment}进行条件分页查询示例等，方便以后查阅！！！

一、简介

${ew.customSqlSegment}是MyBatis Plus提供的动态SQL语句拼接功能。

1、在使用MyBatis Plus进行数据库操作时，可以通过Wrapper对象来构建查询条件。Wrapper对象可以通过链式调用的方式动态添加查询条件，包括等于、大于、小于等各种条件。而${ew.customSqlSegment}就是Wrapper对象中自定义的SQL片段，可以灵活地根据业务需求进行动态拼接。

2、例如，在上述代码中，${ew.customSqlSegment}可以用于拼接额外的查询条件或者排序规则。具体的拼接逻辑可以通过在方法的参数中传入Wrapper对象来实现。

3、${ew.customSqlSegment}和${ew.sqlSegment}是什么？

${ew.customSqlSegment}在使用时，相当与 where + queryWrapper内的条件
${ew.sqlSegment}相当于queryWrapper内的条件

二、分页条件查询

dao层

mapper-java文件

package com.wl.cloud.monitor.dao;

import com.wl.cloud.monitor.domain.StudentTest;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.wl.cloud.monitor.support.vo.StudentTestVO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * test信息 Mapper接口
 *
 * @author wanglin
 * @since 2024-03-21
 */
@Mapper
public interface StudentTestMapper extends BaseMapper<StudentTest> {
    /**
    * 查询test信息
    *
    * @return
    */
    List<StudentTestVO> selectStudentTestList();

    /**
     * 分页
     *
     * @param page
     * @param ew
     * @return
     */
    IPage<StudentTestVO> getPage(IPage<StudentTestVO> page, @Param(Constants.WRAPPER) Wrapper<StudentTest> ew);

    /**
     * 列表
     *
     * @param ew
     * @return
     */
    List<StudentTestVO> getList(@Param(Constants.WRAPPER) Wrapper<StudentTest> ew);

    /**
    * 查询详情
    *
    * @param id
    * @return
    */
    StudentTestVO getById(@Param("id") String id);
}

dao-xml

mapper-xml文件

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.wl.cloud.monitor.dao.StudentTestMapper">

        <!-- 通用查询映射结果 -->
        <resultMap id="BaseResultMap" type="com.wl.cloud.monitor.domain.StudentTest">
                    <id column="id" property="id"/>
                    <result column="create_by" property="createBy"/>
                    <result column="create_time" property="createTime"/>
                    <result column="update_by" property="updateBy"/>
                    <result column="update_time" property="updateTime"/>
                    <result column="deleted" property="deleted"/>
                    <result column="age" property="age"/>
                    <result column="description" property="description"/>
                    <result column="name" property="name"/>
                    <result column="sex" property="sex"/>
        </resultMap>
        <!-- 通用查询结果列 -->
        <sql id="Base_Column_List">
                id, create_by, create_time, update_by, update_time, deleted, age, description, name, sex
        </sql>

    <select id="selectStudentTestList" resultType="com.wl.cloud.monitor.support.vo.StudentTestVO">
        select
        <include refid="com.wl.cloud.monitor.dao.StudentTestMapper.Base_Column_List"></include>
        from gl_student_test
        order by create_time desc limit 10;
    </select>

    <select id="getList" resultType="com.wl.cloud.monitor.support.vo.StudentTestVO">
        select
        <include refid="com.wl.cloud.monitor.dao.ServerMapper.Base_Column_List"></include>
        from id, create_by, create_time, update_by, update_time, deleted, age, description, name, sex
        <where>
            deleted = 0 and
            <if test="ew.sqlSegment !=null and ew.sqlSegment !=''">
                ${ew.sqlSegment}
            </if>
        </where>
    </select>

    <select id="getPage" resultType="com.wl.cloud.monitor.support.vo.StudentTestVO">
        select
        <include refid="com.wl.cloud.monitor.dao.ServerMapper.Base_Column_List"></include>
        from id, create_by, create_time, update_by, update_time, deleted, age, description, name, sex
        <where>
            deleted = 0 and
            <if test="ew.sqlSegment !=null and ew.sqlSegment !=''">
                ${ew.sqlSegment}
            </if>
        </where>
    </select>

    <select id="getById" parameterType="string" resultType="com.wl.cloud.monitor.support.vo.StudentTestVO">
        select
        <include refid="com.wl.cloud.monitor.dao.StudentTestMapper.Base_Column_List"></include>
        from gl_student_test
        <where>
            deleted = 0 and
            id = #{id}
        </where>
    </select>
</mapper>

server层

StudentTestServiceImpl

package com.wl.cloud.monitor.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.CollectionUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.google.common.collect.Lists;
import com.wl.cloud.monitor.dao.StudentTestMapper;
import com.wl.cloud.monitor.domain.StudentTest;
import com.wl.cloud.monitor.service.StudentTestService;
import com.wl.cloud.monitor.support.dto.StudentTestDTO;
import com.wl.cloud.monitor.support.dto.query.StudentTestQueryDTO;
import com.wl.cloud.monitor.support.vo.StudentTestVO;
import com.wl.cloud.core.dto.DataStoreDTO;
import com.wl.cloud.core.utils.PageUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import java.util.List;
import java.util.Objects;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * test信息 服务实现类
 *
 * @author wanglin
 * @since 2024-03-21
 */
@Service
public class StudentTestServiceImpl implements StudentTestService {

    @Autowired
    private StudentTestMapper studentTestMapper;

    @Transactional(readOnly = true)
    @Override
    public DataStoreDTO<StudentTestVO> page(Pageable pageable, StudentTestQueryDTO queryDto) {
        QueryWrapper<StudentTest> queryWrapper = this.buildQuery(queryDto);
        Page<StudentTestVO> page = PageUtils.transferPage(pageable);
        IPage<StudentTestVO> result = this.studentTestMapper.getPage(page, queryWrapper);
        return new DataStoreDTO(result.getTotal(), result.getRecords());
    }

    @Transactional(readOnly = true)
    @Override
    public List<StudentTestVO> list(Sort sort, StudentTestQueryDTO queryDto) {
        QueryWrapper<StudentTest> queryWrapper = this.buildQuery(queryDto);
        PageUtils.transferSort(queryWrapper, sort);
        return this.studentTestMapper.getList(queryWrapper);
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void save(StudentTestDTO dto) {
        // TODO 唯一性字段校验
        dto.setId(null);
        studentTestMapper.insert(this.transferEntity(null, dto));
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void update(StudentTestDTO dto) {
        Assert.hasText(dto.getId(), "id不能为空");
        // TODO 唯一性字段校验
        StudentTest entity = studentTestMapper.selectById(dto.getId());
        Assert.notNull(entity, "找不到id为 " + dto.getId() + " 的记录");
        studentTestMapper.updateById(this.transferEntity(entity, dto));
    }

    @Transactional(rollbackFor = Exception.class)
    @Override
    public void delete(Set<String> ids) {
        if (CollectionUtils.isNotEmpty(ids)) {
            studentTestMapper.deleteBatchIds(ids);
        }
    }

    @Transactional(readOnly = true)
    @Override
    public StudentTestVO get(String id) {
        Assert.hasText(id, "id不能为空");
        StudentTest entity = studentTestMapper.selectById(id);
        Assert.notNull(entity, "找不到id为 " + id + " 的记录");
        return this.transferVo(entity);
    }

    private QueryWrapper<StudentTest> buildQuery(StudentTestQueryDTO queryDto) {
        QueryWrapper<StudentTest> queryWrapper = new QueryWrapper<>();
        if (Objects.nonNull(queryDto)) {
            queryWrapper.lambda().eq(StringUtils.isNotBlank(queryDto.getId()), StudentTest::getId, queryDto.getId());
        }
        return queryWrapper;
    }

    private StudentTest transferEntity(StudentTest entity, StudentTestDTO dto) {
        if (Objects.isNull(entity)) {
            entity = new StudentTest();
        }
        BeanUtils.copyProperties(dto, entity);
        return entity;
    }

    private List<StudentTestVO> transferVo(List<StudentTest> entities) {
        if (CollectionUtils.isEmpty(entities)) {
            return Lists.newArrayList();
        }

        List<StudentTestVO> voList = entities.stream().map(entity -> {
            StudentTestVO vo = new StudentTestVO();
            BeanUtils.copyProperties(entity, vo);
            return vo;
        }).collect(Collectors.toList());
        return voList;
    }

    private StudentTestVO transferVo(StudentTest entity) {
        if (Objects.isNull(entity)) {
            return null;
        }
        StudentTestVO vo = new StudentTestVO();
        BeanUtils.copyProperties(entity, vo);
        return vo;
    }
}