不灭的焱

革命尚未成功,同志仍须努力下载JDK17

作者:Albert.Wen  添加时间:2022-12-12 00:34:46  修改时间:2024-04-21 10:20:06  分类:Java框架/系统  编辑

官方教程 、Gitee教程 、Maven地址

使用:

mapper继承 MPJBaseMapper

@Mapper
public interface UserMapper extends MPJBaseMapper<UserDO> {

}

(可选)service继承 MPJBaseService

public interface UserService extends MPJBaseService<UserDO> {

}

(可选)serviceImpl继承 MPJBaseServiceImpl

@Service
public class UserServiceImpl extends MPJBaseServiceImpl<UserMapper, UserDO> implements UserService {

}

 


 

# 服务包

@Service
public class TaskServiceImpl extends MPJBaseServiceImpl<TaskMapper, Task> implements ITaskService {
    /**
     * 获取分页记录列表
     */
    @Override
    public IPage<TaskVo> getPageList(int userId, TaskAdminForm taskAdminForm, Page<TaskVo> page) {
        if (userId <= 0) {
            return EmptyHelper.emptyPage();
        }

        //MPJLambdaWrapper<Task> wrapper = JoinWrappers.lambda(Task.class)
        // 或
        //MPJLambdaWrapper<Task> wrapper = JoinWrappers.<Task>lambda()
        // 或
        //MPJLambdaWrapper<Task> wrapper = MPJWrappers.<Task>lambdaJoin()
        // 或
        MPJLambdaWrapper<Task> wrapper = MPJWrappers.lambdaJoin(Task.class)
                .selectAll(Task.class)
                .selectAs(BasePlatform::getPlatformName, TaskVo::getPlatformName)
                .selectAs(SysUser::getRealname, TaskVo::getUserName)
                .leftJoin(BasePlatform.class, BasePlatform::getId, Task::getPlatformId)
                .leftJoin(SysUser.class, SysUser::getId, Task::getAddBy);

        return this.selectJoinListPage(page, TaskVo.class, wrapper);
    }
}

 

# 控制器

/**
 * 监控任务 控制器
 */
@Controller
public class TaskController extends BaseController {
    @Resource
    private ITaskService iTaskService;

    /**
     * 任务管理
     */
    @RequestMapping(UrlConfig.task_admin)
    public String index(Model model, TaskAdminForm taskAdminForm) {
        model.addAttribute("taskAdminForm", taskAdminForm);

        // 分页列表
        IPage<TaskVo> taskPage = this.iTaskService.getPageList(
                this.getLoginUserId(model),
                taskAdminForm,
                new Page<>(taskAdminForm.getPageNum(), taskAdminForm.getPageSize())
        );
        PageResult<TaskVo> pageResult = PageResult.of(taskPage);
        model.addAttribute("pageList", pageResult.getPageList());
        model.addAttribute("pageStr", pageResult.getPageStr());

 


 

1.LambdaWrapperTest.java

文件:https://gitee.com/best_handsome/mybatis-plus-join/blob/master/mybatis-plus-join-test/test-join/src/test/java/com/github/yulichang/test/join/LambdaWrapperTest.java

package com.github.yulichang.test.join;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.github.yulichang.test.join.dto.AddressDTO;
import com.github.yulichang.test.join.dto.UserDTO;
import com.github.yulichang.test.join.entity.AddressDO;
import com.github.yulichang.test.join.entity.AreaDO;
import com.github.yulichang.test.join.entity.UserDO;
import com.github.yulichang.test.join.entity.UserDto;
import com.github.yulichang.test.join.mapper.AddressMapper;
import com.github.yulichang.test.join.mapper.UserDTOMapper;
import com.github.yulichang.test.join.mapper.UserMapper;
import com.github.yulichang.test.util.ThreadLocalUtils;
import com.github.yulichang.toolkit.JoinWrappers;
import com.github.yulichang.toolkit.MPJWrappers;
import com.github.yulichang.wrapper.MPJLambdaWrapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.jdbc.BadSqlGrammarException;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;
import java.util.Objects;

/**
 * 连表测试类
 * <p>
 * 支持mybatis-plus 查询枚举字段
 * 支持mybatis-plus typeHandle功能
 * <p>
 * 移除了mybatis-plus 逻辑删除支持,逻辑删除需要在连表查询时自己添加对应的条件
 */
@SuppressWarnings("unused")
@SpringBootTest
class LambdaWrapperTest {
    @Autowired
    private UserMapper userMapper;

    @Autowired
    private UserDTOMapper userDTOMapper;

    @Autowired
    private AddressMapper addressMapper;


    @Test
    void testJoin() {
        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       t1.id  AS joina_id,\n" +
                "       t1.user_id,\n" +
                "       t1.area_id,\n" +
                "       t1.tel,\n" +
                "       t1.address,\n" +
                "       t1.del AS joina_del,\n" +
                "       t2.id  AS joinb_id,\n" +
                "       t2.province,\n" +
                "       t2.city,\n" +
                "       t2.area,\n" +
                "       t2.postcode,\n" +
                "       t2.del AS joinb_del\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN address t1 ON (t1.user_id = t.id)\n" +
                "         LEFT JOIN area t2 ON (t2.id = t1.area_id)\n" +
                "WHERE t.del = false\n" +
                "  AND t1.del = false\n" +
                "  AND t2.del = false\n" +
                "  AND (t.id <= ?)\n" +
                "ORDER BY t.id DESC");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .selectAll(UserDO.class)
                .selectCollection(AddressDO.class, UserDTO::getAddressList, addr -> addr
                        .association(AreaDO.class, AddressDTO::getArea))
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId)
                .le(UserDO::getId, 10000)
                .orderByDesc(UserDO::getId);
        List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, wrapper);

        assert list.get(0).getAddressList() != null && list.get(0).getAddressList().get(0).getId() != null;
        list.forEach(System.out::println);
    }

    @Test
    void testJoinField() {
        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       t1.id AS joina_id\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN address t1 ON (t1.user_id = t.id)\n" +
                "WHERE t.del = false\n" +
                "  AND t1.del = false\n" +
                "  AND (t.id <= ?)\n" +
                "ORDER BY t.id DESC");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .selectAll(UserDO.class)

                .selectCollection(AddressDO.class, UserDTO::getAddressIds, e -> e
                        .id(AddressDO::getId))

                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .le(UserDO::getId, 10000)
                .orderByDesc(UserDO::getId);
        List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, wrapper);

        assert list.get(0).getAddressIds() != null;
        list.forEach(System.out::println);
    }


    @Test
    void testJoin1() {
        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       t1.id  AS joina_id,\n" +
                "       t1.user_id,\n" +
                "       t1.area_id,\n" +
                "       t1.tel,\n" +
                "       t1.address,\n" +
                "       t1.del AS joina_del,\n" +
                "       t2.id  AS joinb_id,\n" +
                "       t2.province,\n" +
                "       t2.city,\n" +
                "       t2.area,\n" +
                "       t2.postcode,\n" +
                "       t2.del AS joinb_del\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN address t1 ON (t1.user_id = t.id)\n" +
                "         LEFT JOIN area t2 ON (t2.id = t1.area_id)\n" +
                "WHERE t.del = false\n" +
                "  AND t1.del = false\n" +
                "  AND t2.del = false\n" +
                "ORDER BY t.id DESC");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .selectAll(UserDO.class)
                .selectCollection(AddressDO.class, UserDTO::getAddressList, addr -> addr
                        .association(AreaDO.class, AddressDTO::getArea))
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId)
                .orderByDesc(UserDO::getId);
        List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, wrapper);

        assert list.get(0).getAddressList().get(0).getId() != null;
        list.forEach(System.out::println);
    }

    /**
     * 基本数据类型测试
     */
    @Test
    void testWrapper() {
        ThreadLocalUtils.set("SELECT t.id\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN address t1 ON (t1.user_id = t.id)\n" +
                "         LEFT JOIN area t2 ON (t2.id = t1.area_id)\n" +
                "WHERE t.del = false\n" +
                "  AND t1.del = false\n" +
                "  AND t2.del = false");
        //基本数据类型 和 String
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .select(UserDO::getId)
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId);
        List<Integer> list = userMapper.selectJoinList(Integer.class, wrapper);

        assert list.get(0) != null;
        System.out.println(list);


        ThreadLocalUtils.set("SELECT t.create_time\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN address t1 ON (t1.user_id = t.id)\n" +
                "         LEFT JOIN area t2 ON (t2.id = t1.area_id)\n" +
                "WHERE t.del = false\n" +
                "  AND t1.del = false\n" +
                "  AND t2.del = false");
        //java.sql包下的类
        MPJLambdaWrapper<UserDO> wrapper1 = new MPJLambdaWrapper<UserDO>()
                .select(UserDO::getCreateTime)
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId);
        List<Timestamp> list1 = userMapper.selectJoinList(Timestamp.class, wrapper1);

        assert list1.get(0) != null;
        System.out.println(list);
    }


    @Test
    @SuppressWarnings("unchecked")
    void testMSCache() {
        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by\n" +
                "FROM `user` t\n" +
                "WHERE t.id = ?\n" +
                "  AND t.del = false\n" +
                "  AND (t.id <= ?)\n" +
                "ORDER BY t.id ASC, t.`name` ASC");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>(new UserDO() {{
            setId(1);
        }})
                .selectAll(UserDO.class)
//                .setEntity(new UserDO() {{
//                    setId(1);
//                }})
                .le(UserDO::getId, 100)
                .orderByAsc(UserDO::getId, UserDO::getName);

        List<UserDO> list = userMapper.selectList(wrapper);
        list.forEach(System.out::println);
    }

    @Test
    void testTableAliasR() {
        ThreadLocalUtils.set("SELECT tt.id,\n" +
                "       tt.user_id,\n" +
                "       tt.create_by,\n" +
                "       tt.update_by,\n" +
                "       ua.`name` AS userName,\n" +
                "       ub.`name` AS createName,\n" +
                "       uc.`name` AS updateName\n" +
                "FROM user_dto tt\n" +
                "         LEFT JOIN `user` ua ON (ua.id = tt.user_id)\n" +
                "         LEFT JOIN `user` ub ON (ub.id = tt.create_by)\n" +
                "         LEFT JOIN `user` uc ON (uc.id = tt.update_by)\n" +
                "WHERE ua.del = false\n" +
                "  AND ub.del = false\n" +
                "  AND uc.del = false\n" +
                "  AND (ua.id <= ? AND ub.id >= ?)");
        MPJLambdaWrapper<UserDto> wrapper = new MPJLambdaWrapper<UserDto>("tt")
                .selectAll(UserDto.class)
                .leftJoin(UserDO.class, "ua", UserDO::getId, UserDto::getUserId, ext -> ext
                        .selectAs(UserDO::getName, UserDto::getUserName)
                        .le(UserDO::getId, 100))
                .leftJoin(UserDO.class, "ub", UserDO::getId, UserDto::getCreateBy, ext -> ext
                        .selectAs(UserDO::getName, UserDto::getCreateName)
                        .ge(UserDO::getId, 0))
                .leftJoin(UserDO.class, "uc", UserDO::getId, UserDto::getUpdateBy, ext -> ext
                        .selectAs(UserDO::getName, UserDto::getUpdateName));
        List<UserDto> userDtos = userDTOMapper.selectJoinList(UserDto.class, wrapper);
        assert StringUtils.isNotBlank(userDtos.get(0).getUserName());
        assert StringUtils.isNotBlank(userDtos.get(0).getCreateName());
        assert StringUtils.isNotBlank(userDtos.get(0).getUpdateName());


        ThreadLocalUtils.set("SELECT tt.id,\n" +
                "       tt.pid,\n" +
                "       tt.`name`,\n" +
                "       tt.`json`,\n" +
                "       tt.sex,\n" +
                "       tt.head_img,\n" +
                "       tt.create_time,\n" +
                "       tt.address_id,\n" +
                "       tt.address_id2,\n" +
                "       tt.del,\n" +
                "       tt.create_by,\n" +
                "       tt.update_by,\n" +
                "       ua.id,\n" +
                "       ub.head_img\n" +
                "FROM `user` tt\n" +
                "         LEFT JOIN `user` ua ON (ua.id = tt.pid)\n" +
                "         LEFT JOIN `user` ub ON (ub.id = tt.create_by)\n" +
                "         LEFT JOIN `user` uc ON (uc.id = tt.update_by)\n" +
                "WHERE tt.del = false\n" +
                "  AND ua.del = false\n" +
                "  AND ub.del = false\n" +
                "  AND uc.del = false\n" +
                "  AND (ua.head_img = tt.`name` AND tt.id = ua.id)");
        MPJLambdaWrapper<UserDO> w = new MPJLambdaWrapper<UserDO>("tt")
                .selectAll(UserDO.class)
                .leftJoin(UserDO.class, "ua", UserDO::getId, UserDO::getPid, ext -> ext
                        .select(UserDO::getId)
                        .eq(UserDO::getImg, UserDO::getName))
                .leftJoin(UserDO.class, "ub", UserDO::getId, UserDO::getCreateBy, ext -> ext
                        .select(UserDO::getImg))
                .leftJoin(UserDO.class, "uc", UserDO::getId, UserDO::getUpdateBy)
                .eq(UserDO::getId, UserDO::getId);
        userMapper.selectJoinList(UserDO.class, w);
        System.out.println(1);
    }

    /**
     * 自连接测试
     */
    @Test
    void testInner() {
        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       t1.id          AS joina_id,\n" +
                "       t1.pid         AS joina_pid,\n" +
                "       t1.`name`      AS joina_name,\n" +
                "       t1.`json`      AS joina_json,\n" +
                "       t1.sex         AS joina_sex,\n" +
                "       t1.head_img    AS joina_head_img,\n" +
                "       t1.create_time AS joina_create_time,\n" +
                "       t1.address_id  AS joina_address_id,\n" +
                "       t1.address_id2 AS joina_address_id2,\n" +
                "       t1.del         AS joina_del,\n" +
                "       t1.create_by   AS joina_create_by,\n" +
                "       t1.update_by   AS joina_update_by\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN `user` t1 ON (t1.pid = t.id)\n" +
                "WHERE t.del = false\n" +
                "  AND (t.id > ?)");
        //自连接
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .disableSubLogicDel()//关闭副表逻辑删除
                .selectAll(UserDO.class)
                .selectCollection(UserDO.class, UserDO::getChildren)
                .leftJoin(UserDO.class, UserDO::getPid, UserDO::getId)
                .gt(UserDO::getId, 0);
        List<UserDO> list = userMapper.selectJoinList(UserDO.class, wrapper);
        System.out.println(list);

        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       t1.`name` AS createName,\n" +
                "       t2.`name` AS updateName\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN `user` t1 ON (t1.id = t.create_by)\n" +
                "         LEFT JOIN `user` t2 ON (t2.id = t.update_by)\n" +
                "WHERE (t2.id = t.update_by AND t.id = t1.id)");
        //关联一张表多次
        MPJLambdaWrapper<UserDO> w = new MPJLambdaWrapper<UserDO>()
                .disableLogicDel()
                .disableSubLogicDel()
                .selectAll(UserDO.class)
                .leftJoin(UserDO.class, UserDO::getId, UserDO::getCreateBy, ext -> ext
                        .selectAs(UserDO::getName, UserDO::getCreateName))
                .leftJoin(UserDO.class, (on, ext) -> {
                    on.eq(UserDO::getId, UserDO::getUpdateBy);
                    ext.selectAs(UserDO::getName, UserDO::getUpdateName)
                            .eq(UserDO::getId, UserDO::getUpdateBy);
                })
                .eq(UserDO::getId, UserDO::getId);
        List<UserDO> dos = userMapper.selectJoinList(UserDO.class, w);
        assert dos.get(0).getCreateName() != null && dos.get(0).getUpdateName() != null;


        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       t1.`name`      AS alias,\n" +
                "       t1.id          AS joina_id,\n" +
                "       t1.pid         AS joina_pid,\n" +
                "       t1.`name`      AS joina_name,\n" +
                "       t1.`json`      AS joina_json,\n" +
                "       t1.sex         AS joina_sex,\n" +
                "       t1.head_img    AS joina_head_img,\n" +
                "       t1.create_time AS joina_create_time,\n" +
                "       t1.address_id  AS joina_address_id,\n" +
                "       t1.address_id2 AS joina_address_id2,\n" +
                "       t1.del         AS joina_del,\n" +
                "       t1.create_by   AS joina_create_by,\n" +
                "       t1.update_by   AS joina_update_by,\n" +
                "       t2.id          AS joinb_id,\n" +
                "       t2.pid         AS joinb_pid,\n" +
                "       t2.`name`      AS joinb_name,\n" +
                "       t2.`json`      AS joinb_json,\n" +
                "       t2.sex         AS joinb_sex,\n" +
                "       t2.head_img    AS joinb_head_img,\n" +
                "       t2.create_time AS joinb_create_time,\n" +
                "       t2.address_id  AS joinb_address_id,\n" +
                "       t2.address_id2 AS joinb_address_id2,\n" +
                "       t2.del         AS joinb_del,\n" +
                "       t2.create_by   AS joinb_create_by,\n" +
                "       t2.update_by   AS joinb_update_by\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN `user` t1 ON (t1.pid = t.id)\n" +
                "         LEFT JOIN `user` t2 ON (t2.pid = t1.id)\n" +
                "WHERE t.del = false\n" +
                "  AND (t1.id <= ? AND t.id <= ?)");
        MPJLambdaWrapper<UserDO> wrapper1 = new MPJLambdaWrapper<UserDO>()
                .disableSubLogicDel()
                .selectAll(UserDO.class)
                .selectCollection("t1", UserDO.class, UserDO::getChildren, c -> c
                        .collection("t2", UserDO.class, UserDO::getChildren))
                .leftJoin(UserDO.class, UserDO::getPid, UserDO::getId, ext -> ext
                        .selectAs(UserDO::getName, UserDO::getAlias)
                        .leftJoin(UserDO.class, UserDO::getPid, UserDO::getId)
                        .le(UserDO::getId, 5))
                .le(UserDO::getId, 4);
        List<UserDO> list1 = userMapper.selectJoinList(UserDO.class, wrapper1);
        System.out.println(list1);
    }

    /**
     * 逻辑删除测试
     */
    @Test
    void testLogicDel() {
        List<UserDTO> l1 = userMapper.selectJoinList(UserDTO.class, new MPJLambdaWrapper<>());
        assert l1.size() == 14;

        List<UserDTO> l2 = userMapper.selectJoinList(UserDTO.class, new MPJLambdaWrapper<UserDO>()
                .selectAll(UserDO.class)
                .select(AddressDO::getAddress)
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId));
        assert l2.size() == 10;

        List<UserDTO> l3 = userMapper.selectJoinList(UserDTO.class, new MPJLambdaWrapper<UserDO>()
                .disableSubLogicDel()
                .selectAll(UserDO.class)
                .selectCollection(AddressDO.class, UserDTO::getAddressList)
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId));
        assert l3.size() == 14 && l3.get(0).getAddressList().size() == 9;

        List<UserDTO> l4 = userMapper.selectJoinList(UserDTO.class, new MPJLambdaWrapper<UserDO>()
                .disableSubLogicDel()
                .selectAll(UserDO.class)
                .selectCollection(AddressDO.class, UserDTO::getAddressList)
                .leftJoin(AddressDO.class, on -> on
                        .eq(AddressDO::getUserId, UserDO::getId)
                        .eq(AddressDO::getDel, false)));
        assert l4.size() == 14 && l4.get(0).getAddressList().size() == 5;
    }


    /**
     * 别名测试
     */
    @Test
    void testAlias() {
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
//                .disableSubLogicDel()//关闭副表逻辑删除
//                .disableLogicDel()//关闭主表逻辑删除
                .selectAll(UserDO.class)
                .selectCollection(UserDO.class, UserDO::getChildren)
                .leftJoin(UserDO.class, UserDO::getPid, UserDO::getId);
        List<UserDO> list = userMapper.selectJoinList(UserDO.class, wrapper);
        assert list.get(0).getName() != null && list.get(0).getChildren().get(0).getName() != null;
        assert list.get(0).getImg() != null && list.get(0).getChildren().get(0).getImg() != null;
        System.out.println(list);
    }

    @Test
    void testTableAlias() {
        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       aa.id,\n" +
                "       aa.user_id,\n" +
                "       aa.area_id,\n" +
                "       aa.tel,\n" +
                "       aa.address,\n" +
                "       aa.del\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN address aa ON (aa.user_id = t.id)\n" +
                "WHERE t.del = false\n" +
                "  AND aa.del = false");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
//                .disableLogicDel()//关闭主表逻辑删除
                .selectAll(UserDO.class)
                .selectAll(AddressDO.class, "aa")
//                .selectCollection(UserDO.class, UserDO::getChildren)
                .leftJoin(AddressDO.class, "aa", AddressDO::getUserId, UserDO::getId);
        List<UserDO> list = userMapper.selectJoinList(UserDO.class, wrapper);

        System.out.println(list);
    }

    @Test
    void testLabel() {
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .disableSubLogicDel()
                .selectAll(UserDO.class)
                .selectCollection("t1", AddressDO.class, UserDO::getAddressList)
                .selectCollection("t2", AddressDO.class, UserDO::getAddressList2)
                .leftJoin(AddressDO.class, AddressDO::getId, UserDO::getAddressId)
                .leftJoin(AddressDO.class, AddressDO::getId, UserDO::getAddressId2);
        List<UserDO> list = userMapper.selectJoinList(UserDO.class, wrapper);

        assert list.get(0).getAddressList().get(0).getAddress() != null;
        assert list.get(0).getAddressList2().get(0).getAddress() != null;
        System.out.println(list);
    }


    /**
     * 简单的分页关联查询 lambda
     */
    @Test
    void test1() {
        Page<UserDTO> page = new Page<>(1, 10);
        page.setSearchCount(false);
        IPage<UserDTO> iPage = userMapper.selectJoinPage(page, UserDTO.class,
                JoinWrappers.lambda(UserDO.class)
                        .selectAll(UserDO.class)
                        .select(AddressDO::getAddress)
                        .select(AreaDO::getProvince)
                        .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                        .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId));
        iPage.getRecords().forEach(System.out::println);
    }

    /**
     * 简单的分页关联查询 lambda
     * ON语句多条件
     */
    @Test
    void test3() {
        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       t1.address\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN address t1 ON (t.id = t1.user_id AND t.id = t1.user_id)\n" +
                "WHERE t.del = false\n" +
                "  AND t1.del = false\n" +
                "  AND (t.id = ? AND (t.head_img = ? OR t1.user_id = ?) AND t.id = ?)\n" +
                "LIMIT ?");
        IPage<UserDTO> page = userMapper.selectJoinPage(new Page<>(1, 10), UserDTO.class,
                JoinWrappers.<UserDO>lambda()
                        .selectAll(UserDO.class)
                        .select(AddressDO::getAddress)
                        .leftJoin(AddressDO.class, on -> on
                                .eq(UserDO::getId, AddressDO::getUserId)
                                .eq(UserDO::getId, AddressDO::getUserId))
                        .eq(UserDO::getId, 1)
                        .and(i -> i.eq(UserDO::getImg, "er")
                                .or()
                                .eq(AddressDO::getUserId, 1))
                        .eq(UserDO::getId, 1));
        page.getRecords().forEach(System.out::println);
    }

    /**
     * 简单的函数使用
     */
    @Test
    void test4() {
        UserDTO one = userMapper.selectJoinOne(UserDTO.class, JoinWrappers.<UserDO>lambda()
                .selectSum(UserDO::getId)
                .selectMax(UserDO::getId, UserDTO::getHeadImg)
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId));
        System.out.println(one);
    }


    /**
     * 忽略个别查询字段
     */
    @Test
    void test6() {
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .selectAll(UserDO.class)
                .select(AddressDO.class, p -> true)
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .eq(UserDO::getId, 1);
        IPage<UserDTO> page = userMapper.selectJoinPage(new Page<>(1, 10), UserDTO.class, wrapper);
        assert page.getRecords().get(0).getAddress() != null;
        page.getRecords().forEach(System.out::println);
    }

    /**
     * 忽略个别查询字段
     */
    @Test
    void test8() {
        ThreadLocalUtils.set("SELECT t.`name` FROM `user` t WHERE t.del=false AND (t.`name` = ?)");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .select(UserDO::getName)
                .eq(UserDO::getName, "ref");
        userMapper.selectList(wrapper);
    }


    /**
     * 关联查询返回map
     */
    @Test
    void test7() {
        List<Map<String, Object>> list = userMapper.selectJoinMaps(JoinWrappers.<UserDO>lambda()
                .selectAll(UserDO.class)
                .select(AddressDO::getAddress)
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId));
        assert list.get(0).get("ADDRESS") != null;
        list.forEach(System.out::println);
    }

    /**
     * 原生查询
     */
    @Test
    void testMP() {
        List<UserDO> dos = userMapper.selectList(new LambdaQueryWrapper<UserDO>()
                .gt(UserDO::getId, 3)
                .lt(UserDO::getId, 8));
        assert dos.size() == 4;
    }

    /**
     * 函数测试
     */
    @Test
    void testFunc() {
        ThreadLocalUtils.set("SELECT if(t1.user_id < 5,t1.user_id,t1.user_id + 100) AS id FROM `user` t LEFT JOIN address t1 ON (t1.user_id = t.id) WHERE t.del=false AND t1.del=false");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .selectFunc("if(%s < 5,%s,%s + 100)", arg -> arg.accept(AddressDO::getUserId, AddressDO::getUserId, AddressDO::getUserId), UserDO::getId)
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId);

        try {
            List<UserDO> dos = userMapper.selectJoinList(UserDO.class, wrapper);
        } catch (BadSqlGrammarException ignored) {
        }
    }

    /**
     * 泛型测试
     */
    @Test
    void testGeneric() {
        MPJLambdaWrapper<AddressDO> wrapper = new MPJLambdaWrapper<AddressDO>()
                .selectAll(AddressDO.class)
                .le(AddressDO::getId, 10000)
                .orderByDesc(AddressDO::getId);
        List<AddressDTO> list = addressMapper.selectJoinList(AddressDTO.class, wrapper);
        assert Objects.equals("[AddressDTO(id=22, userId=22, areaId=10022, tel=10000000022, address=朝阳22, del=false, areaList=null, area=null), AddressDTO(id=21, userId=21, areaId=10021, tel=10000000021, address=朝阳21, del=false, areaList=null, area=null), AddressDTO(id=20, userId=20, areaId=10020, tel=10000000020, address=朝阳20, del=false, areaList=null, area=null), AddressDTO(id=19, userId=19, areaId=10019, tel=10000000019, address=朝阳19, del=false, areaList=null, area=null), AddressDTO(id=18, userId=18, areaId=10018, tel=10000000018, address=朝阳18, del=false, areaList=null, area=null), AddressDTO(id=17, userId=17, areaId=10017, tel=10000000017, address=朝阳17, del=false, areaList=null, area=null), AddressDTO(id=16, userId=16, areaId=10016, tel=10000000016, address=朝阳16, del=false, areaList=null, area=null), AddressDTO(id=15, userId=15, areaId=10015, tel=10000000015, address=朝阳15, del=false, areaList=null, area=null), AddressDTO(id=14, userId=14, areaId=10014, tel=10000000014, address=朝阳14, del=false, areaList=null, area=null), AddressDTO(id=13, userId=13, areaId=10013, tel=10000000013, address=朝阳13, del=false, areaList=null, area=null), AddressDTO(id=12, userId=12, areaId=10012, tel=10000000012, address=朝阳12, del=false, areaList=null, area=null), AddressDTO(id=11, userId=11, areaId=10011, tel=10000000011, address=朝阳11, del=false, areaList=null, area=null), AddressDTO(id=10, userId=10, areaId=10010, tel=10000000010, address=朝阳10, del=false, areaList=null, area=null), AddressDTO(id=5, userId=1, areaId=10005, tel=10000000005, address=朝阳05, del=false, areaList=null, area=null), AddressDTO(id=4, userId=1, areaId=10004, tel=10000000004, address=朝阳04, del=false, areaList=null, area=null), AddressDTO(id=3, userId=1, areaId=10003, tel=10000000003, address=朝阳03, del=false, areaList=null, area=null), AddressDTO(id=2, userId=1, areaId=10002, tel=10000000002, address=朝阳02, del=false, areaList=null, area=null), AddressDTO(id=1, userId=1, areaId=10001, tel=10000000001, address=朝阳01, del=false, areaList=null, area=null)]"
                , list.toString());
    }

    /**
     * count
     */
    @Test
    void testCount() {
        ThreadLocalUtils.set(
                "SELECT COUNT( 1 ) FROM `user` t LEFT JOIN address t1 ON (t1.user_id = t.id) LEFT JOIN area t2 ON (t2.id = t1.area_id) WHERE t.del=false AND t1.del=false AND t2.del=false",
                "SELECT COUNT( * ) FROM `user` t LEFT JOIN address t1 ON (t1.user_id = t.id) LEFT JOIN area t2 ON (t2.id = t1.area_id) WHERE t.del=false AND t1.del=false AND t2.del=false",
                "SELECT COUNT( * ) AS total FROM `user` t LEFT JOIN address t1 ON (t1.user_id = t.id) LEFT JOIN area t2 ON (t2.id = t1.area_id) WHERE t.del=false AND t1.del=false AND t2.del=false");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId);
        Object integer = userMapper.selectCount(wrapper);

        ThreadLocalUtils.set("SELECT COUNT( * ) FROM `user` t LEFT JOIN address t1 ON (t1.user_id = t.id) LEFT JOIN area t2 ON (t2.id = t1.area_id) WHERE t.del=false AND t1.del=false AND t2.del=false");
        MPJLambdaWrapper<UserDO> wrapper1 = new MPJLambdaWrapper<UserDO>()
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId);
        Long aLong1 = userMapper.selectJoinCount(wrapper1);
    }


    /**
     * 动态别名
     */
    @Test
    void testTable() {
        ThreadLocalUtils.set("SELECT t.id FROM `user`bbbbbbb t LEFT JOIN addressaaaaaaaaaa t1 ON (t1.user_id = t.id) LEFT JOIN area t2 ON (t2.id = t1.area_id) WHERE t.del=false AND t1.del=false AND t2.del=false AND (t.id <= ?) ORDER BY t.id DESC");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .select(UserDO::getId)
                .leftJoin(AddressDO.class, on -> on
                        .eq(AddressDO::getUserId, UserDO::getId)
                        .setTableName(name -> name + "aaaaaaaaaa"))
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId)
                .le(UserDO::getId, 10000)
                .orderByDesc(UserDO::getId)
                .setTableName(name -> name + "bbbbbbb");
        try {
            List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, wrapper);
        } catch (BadSqlGrammarException ignored) {
        }
    }


    /**
     * 逻辑删除类型
     */
    @Test
    void logicDelType() {
        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       t1.id  AS joina_id,\n" +
                "       t1.user_id,\n" +
                "       t1.area_id,\n" +
                "       t1.tel,\n" +
                "       t1.address,\n" +
                "       t1.del AS joina_del,\n" +
                "       t2.id  AS joinb_id,\n" +
                "       t2.province,\n" +
                "       t2.city,\n" +
                "       t2.area,\n" +
                "       t2.postcode,\n" +
                "       t2.del AS joinb_del\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN address t1 ON (t1.user_id = t.id AND t1.del = false)\n" +
                "         LEFT JOIN area t2 ON (t2.id = t1.area_id AND t2.del = false)\n" +
                "WHERE t.del = false\n" +
                "  AND (t.id <= ?)\n" +
                "ORDER BY t.id DESC\n");
        MPJLambdaWrapper<UserDO> wrapper = new MPJLambdaWrapper<UserDO>()
                .logicDelToOn()
                .selectAll(UserDO.class)
                .selectCollection(AddressDO.class, UserDTO::getAddressList, addr -> addr
                        .association(AreaDO.class, AddressDTO::getArea))
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId)
                .le(UserDO::getId, 10000)
                .orderByDesc(UserDO::getId);
        System.out.println(wrapper.getFrom());
        List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, wrapper);

        assert list.get(0).getAddressList() != null && list.get(0).getAddressList().get(0).getId() != null;
        list.forEach(System.out::println);
    }

    /**
     * wrappers 测试
     */
    @Test
    void joinWrapper() {
        ThreadLocalUtils.set("SELECT t.id,\n" +
                "       t.pid,\n" +
                "       t.`name`,\n" +
                "       t.`json`,\n" +
                "       t.sex,\n" +
                "       t.head_img,\n" +
                "       t.create_time,\n" +
                "       t.address_id,\n" +
                "       t.address_id2,\n" +
                "       t.del,\n" +
                "       t.create_by,\n" +
                "       t.update_by,\n" +
                "       t1.id  AS joina_id,\n" +
                "       t1.user_id,\n" +
                "       t1.area_id,\n" +
                "       t1.tel,\n" +
                "       t1.address,\n" +
                "       t1.del AS joina_del,\n" +
                "       t2.id  AS joinb_id,\n" +
                "       t2.province,\n" +
                "       t2.city,\n" +
                "       t2.area,\n" +
                "       t2.postcode,\n" +
                "       t2.del AS joinb_del\n" +
                "FROM `user` t\n" +
                "         LEFT JOIN address t1 ON (t1.user_id = t.id AND t1.del = false)\n" +
                "         LEFT JOIN area t2 ON (t2.id = t1.area_id AND t2.del = false)\n" +
                "WHERE t.del = false\n" +
                "  AND (t.id <= ?)\n" +
                "ORDER BY t.id DESC\n");
        MPJLambdaWrapper<UserDO> wrapper = MPJWrappers.lambdaJoin(UserDO.class)
                .logicDelToOn()
                .selectAll(UserDO.class)
                .selectCollection(AddressDO.class, UserDTO::getAddressList, addr -> addr
                        .association(AreaDO.class, AddressDTO::getArea))
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId)
                .le(UserDO::getId, 10000)
                .orderByDesc(UserDO::getId);

        List<UserDTO> list = wrapper.list(UserDTO.class);

        System.out.println(list);
        assert list.get(0).getAddressList() != null && list.get(0).getAddressList().get(0).getId() != null;
        list.forEach(System.out::println);
    }

    @Test
    void joinRandomMap() {
        MPJLambdaWrapper<UserDO> wrapper = JoinWrappers.lambda(UserDO.class)
                .logicDelToOn()
                .selectAll(UserDO.class)
//                .selectCollection(AddressDO.class, UserDTO::getAddressList, addr -> addr
//                        .id(AddressDO::getId))
                .selectCollection(UserDTO::getAddressList, addr -> addr
                        .id(AddressDO::getId, AddressDTO::getId)
                        .result(UserDO::getName, AddressDTO::getAddress)
                        .collection(AddressDTO::getAreaList, map -> map
                                .id(AreaDO::getId)
                                .result(AreaDO::getArea, AreaDO::getArea)))
                .leftJoin(AddressDO.class, AddressDO::getUserId, UserDO::getId)
                .leftJoin(AreaDO.class, AreaDO::getId, AddressDO::getAreaId)
                .le(UserDO::getId, 10000)
                .orderByDesc(UserDO::getId);

        List<UserDTO> list = wrapper.list(UserDTO.class);

        System.out.println(list);
        assert list.get(0).getAddressList() != null && list.get(0).getAddressList().get(0).getId() != null;
        list.forEach(System.out::println);
    }
}

 

2.QueryWrapperTest.java

文件:https://gitee.com/best_handsome/mybatis-plus-join/blob/master/mybatis-plus-join-test/test-join/src/test/java/com/github/yulichang/test/join/QueryWrapperTest.java