Mybatis-plus链表查询

DROP TABLE IF EXISTS user;

CREATE TABLE user
(
	id 				BIGINT(20) NOT NULL 			COMMENT '主键ID',
	company_id 		BIGINT(20) NOT NULL 			COMMENT '公司ID',
	name 			VARCHAR(30) NULL DEFAULT NULL 	COMMENT '姓名',
	age 			INT(11) NULL DEFAULT NULL 		COMMENT '年龄',
	email 			VARCHAR(50) NULL DEFAULT NULL 	COMMENT '邮箱',
	PRIMARY KEY (id)
);

DROP TABLE IF EXISTS company;

CREATE TABLE company
(
	id 				BIGINT(20) NOT NULL 			COMMENT '主键ID',
	name 			VARCHAR(30) NULL DEFAULT NULL 	COMMENT '公司名称',
	PRIMARY KEY (id)
);

DELETE FROM company;

INSERT INTO company (id, name) VALUES
(1, 'HuaWei'),
(2, 'BYD');


DELETE FROM user;

INSERT INTO user (id, company_id,name, age, email) VALUES
(1, 1, 'Jone', 18, 'test1@baomidou.com'),
(2, 1,'Jack', 20, 'test2@baomidou.com'),
(3, 1, 'Tom', 28, 'test3@baomidou.com'),
(4, 2, 'Sandy', 21, 'test4@baomidou.com'),
(5, 2, 'Billie', 24, 'test5@baomidou.com');

# Logger Config
logging:
  level:
    com.baomidou.mybatisplus.samples: debug

mybatis-plus:
  mapper-locations: classpath:/mapper/*Mapper.xml
  global-config:
    db-config:
      property-format: "\"%s\""
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test_zs?useSSL=false&useUnicode=true&characterEncoding=utf-8&serverTimezone=GMT%2B8
    username: root
    password: root

package com.zs.testmybatisplus.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.baomidou.mybatisplus.core.incrementer.IKeyGenerator;
import com.baomidou.mybatisplus.extension.incrementer.H2KeyGenerator;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;


@Configuration
public class MybatisPlusConfig {
	/**
     *	 分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor());
        return interceptor;
    }



    /**
     *	 注入主键生成器
     */
    @Bean
    public IKeyGenerator keyGenerator(){
        return new H2KeyGenerator();
    }
}

在这里插入代码片

1实体类

package com.zs.testmybatisplus.entity;
import com.baomidou.mybatisplus.annotation.TableName;

import lombok.Data;

@Data
@TableName("company")
public class Company {
	private Long id;
    private String name;
}

package com.zs.testmybatisplus.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

import lombok.Getter;
import lombok.Setter;
import lombok.ToString;

@Setter
@Getter
@ToString
@TableName("user")
public class User {
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    @TableField(value = "company_id", property = "company.id")
    private Company company;
    private String name;
    private Integer age;
    private String email;

}

2service&&mapper

package com.zs.testmybatisplus.service;


import java.util.List;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.IService;
import com.zs.testmybatisplus.entity.User;

public interface IUserService extends IService<User> {

    List<User> selectUserPage(IPage<User> page, QueryWrapper<User> wrapper);
}

package com.zs.testmybatisplus.service.impl;

import java.util.List;

import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zs.testmybatisplus.entity.User;
import com.zs.testmybatisplus.mapper.UserMapper;
import com.zs.testmybatisplus.service.IUserService;


@Service
@Component
public class UserService extends ServiceImpl<UserMapper, User> implements IUserService {

    @Override
    public List<User> selectUserPage(IPage<User> page, QueryWrapper<User> wrapper) {
        return baseMapper.selectUserPage(page, wrapper);
    }
}

package com.zs.testmybatisplus.mapper;



import java.util.List;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.zs.testmybatisplus.entity.User;

@Mapper
public interface UserMapper extends BaseMapper<User> {

    List<User> selectUserPage(IPage<User> page, @Param("ew") QueryWrapper<User> wrapper);
}

<?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.zs.testmybatisplus.mapper.UserMapper">

    <sql id="Base_Column_List">
       t.id, t.name, t.age, t.email,
       t.company_id as "company.id",
       c.name as "company.name"
    </sql>

    <select id="selectUserPage" resultType="com.zs.testmybatisplus.entity.User">
        SELECT
        <include refid="Base_Column_List"/>
        FROM user t
        LEFT JOIN company c on t.company_id=c.id
        <where>
            ${ew.sqlSegment}
        </where>
    </select>

</mapper>

3测试类

package com.zs.testmybatisplus;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zs.testmybatisplus.entity.Company;
import com.zs.testmybatisplus.entity.User;
import com.zs.testmybatisplus.service.IUserService;


@RunWith(SpringRunner.class)
@SpringBootTest
public class SampleTest {
    @Autowired
    IUserService userService;

    @Test
    public void testSelectList() {
        /**
         * ==>  Preparing: SELECT id,company_id AS "company.id",name,age,email FROM user
         */
        userService.list().forEach(t -> System.out.println(t.getCompany()));
    }

    @Test
    public void testInsert() {
        List<User> userList = new ArrayList<>();
        for (int i = 0; i < 100; ++i) {
            Company cmp = new Company();
            cmp.setId(1L);
            User user = new User();
            user.setId(100L + i);
            user.setCompany(cmp);
            user.setName("Han Meimei" + i);
            user.setEmail(user.getName() + "@baomidou.com");
            user.setAge(18);
            userList.add(user);
        }
        // User(id=100, company=Company(id=1, name=null), name=Han Meimei0, age=18, email=Han Meimei0@baomidou.com)
        /**
         * ==>  Preparing: INSERT INTO user ( id, company_id, name, age, email ) VALUES ( ?, ?, ?, ?, ? )
         * ==> Parameters: 100(Long), 1(Long), Han Meimei0(String), 18(Integer), Han Meimei0@baomidou.com(String)
         */
        userService.saveBatch(userList);
        /**
         * ==>  Preparing: SELECT id,company_id AS "company.id",name,age,email FROM user
         * ==> Parameters:
         */
        //userService.list().forEach(t -> System.out.println(t));
        testSelect();
        testUpdate();
    }

    @Test
    public void testSelect() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.eq("t.company_id", 1);
        int pageSize = 5;
        IPage<User> page = new Page<User>(1, pageSize);
        /**
         * ==>  Preparing: SELECT t.id, t.name, t.age, t.email, t.company_id as "company.id", c.name as "company
         * .name" FROM user t LEFT JOIN company c on t.company_id=c.id WHERE (t.company_id = ?) LIMIT ?
         * ==> Parameters: 1(Integer), 5(Long)
         * <==    Columns: id, name, age, email, company.id, company.name
         * <==        Row: 1, 1625798644898, 18, test1@baomidou.com, 1, HuaWei
         */
        List<User> userList = userService.selectUserPage(page, wrapper);
        for (int i = 1; i <= page.getPages(); ++i) {
            page = new Page<User>(i, pageSize);
            userList = userService.selectUserPage(page, wrapper);
            System.out.printf("==========================>共%d条数据,当前显示第%d页，每页%d条，共%d页====================================>\n", page.getTotal(), page.getCurrent(), page.getSize(), page.getPages());
            userList.forEach(System.out::println);
        }
    }

    @Test
    public void testUpdate() {
        System.out.println("=======================开始更新==================================================>");
        UpdateWrapper<User> wrapper = new UpdateWrapper<>();
        wrapper.eq("company_id", 1);
        User user = new User();
        user.setName(new Date().getTime() + "");
        /**
         * ==>  Preparing: UPDATE user SET name=? WHERE (company_id = ?)
         * ==> Parameters: 1625881322081(String), 1(Integer)
         */
        userService.update(user, wrapper);
        testSelect();
    }
}