Mybatis Plus框架基本语法

原创已于 2024-03-02 23:57:31 修改 · 1.1k 阅读

18 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #spring boot

于 2024-03-02 23:40:44 首次发布

Java 专栏收录该内容

15 篇文章

订阅专栏

MybatisPlus 中文官网

依赖配置

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.3.5.RELEASE</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.example</groupId>
    <artifactId>demo</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>demo</name>
    <description>Demo project for Spring Boot</description>
    <properties>
        <java.version>8</java.version>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>

        <!--MybatisPlus官方提供的starter，其中集成了Mybatis以及plus-->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.4.2</version>
        </dependency>
        
        <!-- 代码生成器 -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-generator</artifactId>
            <version>3.4.1</version>
        </dependency>
        <!-- Apache Velocity -->
        <dependency>
            <groupId>org.apache.velocity</groupId>
            <artifactId>velocity-engine-core</artifactId>
            <version>2.3</version>
        </dependency>
        
		<!-- @ApiModel等注解对应依赖 -->
        <dependency>
            <groupId>io.swagger</groupId>
            <artifactId>swagger-annotations</artifactId>
            <version>1.5.13</version>
        </dependency>
    </dependencies>
    
    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <excludes>
                        <exclude>
                            <groupId>org.projectlombok</groupId>
                            <artifactId>lombok</artifactId>
                        </exclude>
                    </excludes>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

数据库连接

#设置开发环境 @Profile指定
#spring.profiles.active=dev

#驱动类名称
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
#数据库连接的url
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=Asia/Shanghai&characterEncoding=utf-8
#连接数据库的用户名
spring.datasource.username=root
#连接数据库的密码
spring.datasource.password=123456

#配置日志:Sql如何执行的
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

#mybatis plus逻辑删除
mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0

注解

package com.example.entity;

import com.baomidou.mybatisplus.annotation.*;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Date;


@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName("user")
public class User {
    @TableId(value = "id",type = IdType.AUTO)
    private Integer id;
    @TableField(value = "name")
    private String name;
    private Short age;
    private Short gender;
    private String phone;
    @Version
    private Integer version;

	//对于字段is前缀的需要使用注解绑定
    @TableField(value = "is_deleted")
    @TableLogic
    private Integer isDeleted;

    //首次时间是插入，之后是更新
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;

    @TableField(fill = FieldFill.INSERT)
    private Date createTime;
}

CRUD接口

在这里插入图片描述
Lambda条件构造器使用原因：
QueryWrapper、UpdateWrapper条件构造器都是用字符串的形式指定。这种方式无法在编译期确定列名的合法性。

Wrapper 注意

条件调用 如果不写.select(XX,XX)，默认就是select*

自动填充

package com.example.handler;

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import lombok.extern.slf4j.Slf4j;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;

import java.util.Date;

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("start insert fill ....");
        this.setFieldValByName("createTime", new Date(),metaObject);
        this.setFieldValByName("updateTime",new Date(),metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("start update fill ....");
        this.setFieldValByName("updateTime",new Date(),metaObject);
    }
}

乐观锁和分页插件

package com.example.config;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.OptimisticLockerInnerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.aop.interceptor.PerformanceMonitorInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

// Spring Boot 方式
@Configuration
public class MybatisPlusConfig {

    /**
     * 新版 注册乐观锁插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor mybatisPlusInterceptor = new MybatisPlusInterceptor();
        mybatisPlusInterceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        mybatisPlusInterceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));//如果配置多个插件,切记分页最后添加
        return mybatisPlusInterceptor;
    }

  /*  //分页插件
    @Bean
    public PaginationInnerInterceptor pageInterceptor(){
        return new PaginationInnerInterceptor();
    }*/


}

Mapper层

说明:

 /*通用 CRUD 封装BaseMapper (opens new window)接口，为 Mybatis-Plus
   启动时自动解析实体表关系映射转换为 Mybatis 内部对象注入容器 
   */
public interface UserMapper extends BaseMapper<User> {

}

package com.example;

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.example.entity.User;
import com.example.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@SpringBootTest
class DemoApplicationTests {

    //UserMapper集成BaseMapper所有方法，也可以自定义
    @Autowired
    private UserMapper userMapper;

    @Test
    void contextLoads() {
        //wrapper：条件构造器
        //查询所有用户
        List<User> userList = userMapper.selectList(null);
        userList.stream().forEach(user -> System.out.println(user));
    }

    //批量查询 In
    @Test
    public void selectByIds(){
        List<User> users = userMapper.selectBatchIds(Arrays.asList(1, 2, 3));
        users.stream().map(user -> user.getName()).forEach(user -> System.out.println(user));
    }

    //Map查询：条件查询
    @Test
    public void selectByMap(){
        Map<String, Object> map = new HashMap<>();
        map.put("name","圆圆");

        List<User> users = userMapper.selectByMap(map);
        users.stream().map(user -> user.getName()).forEach(user -> System.out.println(user));

    }

    //Wrapper查询：类比Map条件查询
    @Test
    public void selectByWrapper(){
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.ge("age","1")
        .eq("phone","123456");
        userMapper.selectList(queryWrapper).stream().map(user -> user.getName()).forEach(e -> System.out.println(e));


    }

    //分页查询
    @Test
    public void selectPage(){
        //参数1：当前页面    参数2：页面大小
        Page<User> page = new Page<>(3,3);  //limit 6,3
        userMapper.selectPage(page,null);

        page.getRecords().stream().forEach(user -> System.out.println(user));
    }

    @Test
    public void insert(){
        User user = new User();
        user.setName("元宝");
        user.setAge((short)1);
        user.setGender((short)1);
        user.setPhone("123456");
        int i = userMapper.insert(user);
        System.out.println("插入结果："+i);
    }

    //乐观锁测试 version初始值为1
    @Test
    public void testOptimisticLocker(){
        User user1 = userMapper.selectById(11);
        user1.setPhone("2444");

        User user2 = userMapper.selectById(11);
        user2.setPhone("2555");
        userMapper.updateById(user2);
        //执行失败，version=2
        userMapper.updateById(user1);
    }

    @Test
    public void deleteById(){
        userMapper.deleteById(12);

    }

    //只能更新一条记录
    //UPDATE user SET age=? ,updateTime=? WHERE is_deleted=0 AND (id < ?)
    @Test
    public void update1(){
        User user = new User();
        user.setId(10);
        user.setAge((short)20);
        //在调用updateById方法前，需要在T entity（对应的实体类）中的主键属性上加上@TableId注解。
        userMapper.updateById(user);
    }

    //可以更新一批对象，更为细致地设置具体字段
    // UPDATE user SET age=? WHERE is_deleted=0 AND (id < ?)
    @Test
    public void update2(){
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.lt("id",11);
        updateWrapper.set("age",20);
        userMapper.update(null,updateWrapper);
    }

    /*
    Lambda条件构造器
    使用原因：之前使用条件构造器都是用字符串的形式指定。这种方式无法在编译期确定列名的合法性。
     */

    //查询
    @Test
    public void select(){
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(User::getAge,20);
        List<User> users = userMapper.selectList(queryWrapper);
        users.stream().forEach(e -> System.out.println(e));
    }


}

Service CRUD 接口

/*
- 通用 Service CRUD 封装IService (opens new window)接口，进一步封装 CRUD 采用 get 查询单行 remove 删除 list 查询集合 page 分页 前缀命名方式区分 Mapper 层避免混淆，
- 建议如果存在自定义通用 Service 方法的可能，请创建自己的 IBaseService 继承 Mybatis-Plus 提供的基类
对象 Wrapper 为 条件构造器
*/

public interface UserService extends IService<User> {
    //自定义方法
    List<String> test();

}

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

    /*
    技巧：
    操作一个对应的表直接使用getBaseMapper()
    操作其他表@Autowired注入
     */
    @Override
    public List<String> test() {
        UserMapper userMapper = getBaseMapper();
        List<User> userList = userMapper.selectList(new QueryWrapper<User>()  //在userMapper.selectList()方法中不能使用LambdaQueryWrapper
                .lambda()
                .eq(User::getAge, 20));
        List<String> list = userList.stream().map(e -> e.getName()).collect(Collectors.toList());
        return list;
    }
}

Controller层

 @Autowired //将service接口注入
    private EmployeeService employeeService;

    /**
     * 员工登录
     * @param request 将员工id存到session中
     * @param param 前端传来JSON形式数据，使用@RequestBody接收
     * @return
     */
    @PostMapping("/login")
    public void login(HttpServletRequest request, @RequestBody Employee param){
        /**
         *  SELECT
         *  name,phone
         *  FROM employee
         *  WHERE name = 'xxx' AND password = 'xxx' AND id > 1000 AND (update_time between 'xxx' AND 'xxxx') AND
         *  ORDER BY create_time DESC limit 1;
         * */
        LambdaQueryWrapper<Employee> queryWrapper = new QueryWrapper<Employee>()  
                .lambda()
                .select(Employee::getName , Employee::getPhone)  //条件默认用AND连接
                .eq(Employee::getName ,param.getName())
                .eq(Employee::getPassword , param.getPassword())
                .ne(Employee::getStatus , 2)
                .ge(Employee::getId , 1000)
                .between(Employee::getUpdateTime , "2023-09-10" , "2023-10-10")
                .orderByDesc(Employee::getCreateTime)
                .last("limit 1");    //如果有多个数据，只获取一个。类似数据库中的unique。
        Employee employee1 = employeeService.getOne(queryWrapper);  //getOne()：获取一条结果

        employeeService.getOne(new QueryWrapper<Employee>()
                .lambda()
                .select(Employee::getName , Employee::getPhone)
                .eq(Employee::getName ,param.getName())
                .eq(Employee::getPassword , param.getPassword())
                .last("limit 1"));

        //查询phone是182开头、或者 (name以王开头 并且 用户状态是1)
        //SELECT XXX FROM xx WHERE (phone like 182) OR (name LIKE 王 AND status = 1)
        //王 2
        employeeService.list(new QueryWrapper<Employee>()
                .lambda()
                .likeLeft(Employee::getPhone , "182") //182XXX。likeLeft和likeRigt查询效率高。
                .or(e -> e.likeLeft(Employee::getName , "王").eq(Employee::getStatus , 1)) //这里e指代new QueryWrapper<Employee>对象
        );


        /*
        List<Employee> employeeList = new ArrayList<>();
        employeeList.stream().filter(e -> e.getName().equals("王子怡")); //这里e 代表Employee对象。如果有List类型，就代表集合元素
        */


    }

代码生成器

package com.example.demo;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.config.DataSourceConfig;
import com.baomidou.mybatisplus.generator.config.GlobalConfig;
import com.baomidou.mybatisplus.generator.config.PackageConfig;
import com.baomidou.mybatisplus.generator.config.StrategyConfig;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import lombok.extern.slf4j.Slf4j;
import org.junit.jupiter.api.Test;

/**
 * @author
 * @since 2018/12/13
 */
@Slf4j
public class CodeGenerator {

    @Test
    public void run() {

        // 1、创建代码生成器
        AutoGenerator mpg = new AutoGenerator();

        // 2、全局配置
        GlobalConfig gc = new GlobalConfig();
        String projectPath = System.getProperty("user.dir");
        gc.setOutputDir(projectPath + "/src/main/java");
        gc.setAuthor("Vivi"); //需要手动修改！
        gc.setOpen(false); //生成后是否打开资源管理器
        gc.setFileOverride(false); //重新生成时文件是否覆盖
        gc.setServiceName("%sService");    //去掉Service接口的首字母I
        gc.setIdType(IdType.ASSIGN_ID); //主键策略
        gc.setDateType(DateType.ONLY_DATE);//定义生成的实体类中日期类型
        gc.setSwagger2(true);//开启Swagger2模式

        mpg.setGlobalConfig(gc);

        // 3、数据源配置
        DataSourceConfig dsc = new DataSourceConfig();
        dsc.setUrl("jdbc:mysql://localhost:3306/mybatis?serverTimezone=GMT%2B8"); //需要手动修改！
        dsc.setDriverName("com.mysql.cj.jdbc.Driver");
        dsc.setUsername("root"); //需要手动修改！
        dsc.setPassword("123456");  //需要手动修改！
        dsc.setDbType(DbType.MYSQL);
        mpg.setDataSource(dsc);

        // 4、包配置
        PackageConfig pc = new PackageConfig();
        pc.setModuleName("project1"); //模块名 需要手动修改！
        pc.setParent("com.example.demo");  //需要手动修改！
        pc.setController("controller");
        pc.setEntity("entity");
        pc.setService("service");
        pc.setMapper("mapper");
        mpg.setPackageInfo(pc);

        // 5、策略配置
        StrategyConfig strategy = new StrategyConfig();
        strategy.setInclude("user"); //表名 需要手动修改！
        strategy.setNaming(NamingStrategy.underline_to_camel);//数据库表映射到实体的命名策略
        //strategy.setTablePrefix(pc.getModuleName() + "_"); //生成实体时去掉表前缀

        strategy.setColumnNaming(NamingStrategy.underline_to_camel);//数据库表字段映射到实体的命名策略
        strategy.setEntityLombokModel(true); // lombok 模型 @Accessors(chain = true) setter链式操作

        strategy.setRestControllerStyle(true); //restful api风格控制器
        strategy.setControllerMappingHyphenStyle(true); //url中驼峰转连字符

        mpg.setStrategy(strategy);


        // 6、执行
        mpg.execute();
    }
}

代码模板

@Override
    public PageResult<EmployeeListVO> pageEmployee(HrEmployeeRequest hrEmployeeRequest) {
        LambdaQueryWrapper<HrEmployee> wrapper = createWrapper(hrEmployeeRequest);
        //创建时间倒序
        wrapper.orderByDesc(HrEmployee::getCreateTime);
        wrapper.select(
                HrEmployee::getEmployeeId , HrEmployee::getEmployeeName , HrEmployee::getPhone
        );
        Page<HrEmployee> sysRolePage = this.page(PageFactory.defaultPage(), wrapper);

        //提取List并转成对应的VO
        List<HrEmployee> employeePageList = sysRolePage.getRecords();
        List<EmployeeListVO> employeeVOList = BeanUtil.copyToList(employeePageList , EmployeeListVO.class);

        //提取这一批数据的员工id集合
        List<Long> employeeIdList = employeePageList.stream().map(e -> e.getEmployeeId()).collect(Collectors.toList());

        //从hr_employee_org关系表 提取这一批员工的主要组织机构 + 主要部门 + 主要任职岗位
        List<EmployeeOrg> relationList = employeeOrgService.list(new QueryWrapper<EmployeeOrg>()
                .lambda()
                .eq(EmployeeOrg::getMainFlag , YesOrNotEnum.Y.getCode())
                .in(EmployeeOrg::getEmployeeId , employeeIdList)
        );
        Map<Long , EmployeeOrg> relationMap = new HashMap<>();
        Map<Long , String> orgMap = new HashMap<>();
        Map<Long , String> departmentMap = new HashMap<>();
        Map<Long , String> positionMap = new HashMap<>();
        if (!relationList.isEmpty()) {
            //转map，备用
            relationMap = relationList.stream().collect(
                    Collectors.toMap(e -> e.getEmployeeId() , Function.identity() , (e1 , e2) -> e2)
            );

            //提取组织机构、部门、任职岗位信息
            List<Long> orgIdList = relationList.stream().map(e -> e.getOrgId()).collect(Collectors.toList());
            List<Long> departmentIdList = relationList.stream().map(e -> e.getDepartmentId()).collect(Collectors.toList());
            List<Long> positionIdList = relationList.stream().map(e -> e.getPositionId()).collect(Collectors.toList());
            //组织机构信息
            List<HrOrganization> orgList = hrOrganizationService.list(new QueryWrapper<HrOrganization>()
                    .lambda()
                    .select(HrOrganization::getOrgId , HrOrganization::getOrgName)
                    .in(HrOrganization::getOrgId , orgIdList)
            );
            orgMap = orgList.stream().collect(
                    Collectors.toMap(e -> e.getOrgId() , e -> e.getOrgName() , (e1,e2) -> e2)
            );
            //部门信息
            List<HrOrganization> departmentList = hrOrganizationService.list(new QueryWrapper<HrOrganization>()
                    .lambda()
                    .select(HrOrganization::getOrgId , HrOrganization::getOrgName)
                    .in(HrOrganization::getOrgId , departmentIdList)
            );
            departmentMap = departmentList.stream().collect(
                    Collectors.toMap(e -> e.getOrgId() , e -> e.getOrgName() , (e1,e2) -> e2)
            );
            //职位信息
            List<HrPosition> positionList = hrPositionService.list(new QueryWrapper<HrPosition>()
                    .lambda()
                    .select(HrPosition::getPositionId , HrPosition::getPositionName)
                    .in(HrPosition::getPositionId , positionIdList)
            );
            positionMap = positionList.stream().collect(
                    Collectors.toMap(e -> e.getPositionId() , e -> e.getPositionName() , (e1,e2) -> e2)
            );

        }

        //TODO 提取员工的登录账号
        List<EmployeeUser> accountRelationList = employeeUserService.list(new QueryWrapper<EmployeeUser>()
                .lambda()
                .in(EmployeeUser::getEmployeeId , employeeIdList)
        );
        Map<Long , EmployeeUser> accountRelationMap = new HashMap<>();
        if (!accountRelationList.isEmpty()) {
            accountRelationMap = accountRelationList.stream().collect(
                    Collectors.toMap(e -> e.getEmployeeId() , Function.identity() , (e1 , e2) -> e2)
            );
        }

        //遍历一遍，设置关联字段的属性值
        for (EmployeeListVO vo : employeeVOList) {
            //从关系map提取当前员工的数据
            EmployeeOrg relation = relationMap.get(vo.getEmployeeId());
            if (relation == null) {
                continue;
            }

            vo.setOrgName(orgMap.get(relation.getOrgId()));
            vo.setDepartmentName(departmentMap.get(relation.getDepartmentId()));
            vo.setPositionName(positionMap.get(relation.getPositionId()));
        }

        //复制分页信息，绑定List
        Page<EmployeeListVO> employVOPage = new Page<>();
        BeanUtil.copyProperties(sysRolePage , employVOPage);
        employVOPage.setRecords(employeeVOList);

        return PageResultFactory.createPageResult(employVOPage);
    }