SpringBoot整合MyBatis-Plus实现增删改查

欲无缘

已于 2023-05-18 09:33:00 修改

阅读量1k

点赞数 3

分类专栏： ORM 文章标签： mybatis spring boot java

于 2023-05-17 18:49:22 首次发布

本文链接：https://blog.csdn.net/qq_29917503/article/details/130732577

版权

ORM 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

简介

MyBatis-Plus (opens new window)的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
在这里插入图片描述

特性

无侵入：只做增强不做改变，引入它不会对现有工程产生影响，如丝般顺滑
损耗小：启动即会自动注入基本 CURD，性能基本无损耗，直接面向对象操作
强大的 CRUD 操作：内置通用 Mapper、通用 Service，仅仅通过少量配置即可实现单表大部分 CRUD 操作，更有强大的条件构造器，满足各类使用需求
支持 Lambda 形式调用：通过 Lambda 表达式，方便的编写各类查询条件，无需再担心字段写错
支持主键自动生成：支持多达 4 种主键策略（内含分布式唯一 ID 生成器 - Sequence），可自由配置，完美解决主键问题
支持 ActiveRecord 模式：支持 ActiveRecord 形式调用，实体类只需继承 Model 类即可进行强大的 CRUD 操作
支持自定义全局通用操作：支持全局通用方法注入（ Write once, use anywhere ）
内置代码生成器：采用代码或者 Maven 插件可快速生成 Mapper 、 Model 、 Service 、 Controller 层代码，支持模板引擎，更有超多自定义配置等您来使用
内置分页插件：基于 MyBatis 物理分页，开发者无需关心具体操作，配置好插件之后，写分页等同于普通 List 查询
分页插件支持多种数据库：支持 MySQL、MariaDB、Oracle、DB2、H2、HSQL、SQLite、Postgre、SQLServer 等多种数据库
内置性能分析插件：可输出 SQL 语句以及其执行时间，建议开发测试时启用该功能，能快速揪出慢查询
内置全局拦截插件：提供全表 delete 、 update 操作智能分析阻断，也可自定义拦截规则，预防误操作

SpringBoot整合MyBatis-Plus

引入maven依赖

 <dependency>
      <groupId>com.baomidou</groupId>
      <artifactId>mybatis-plus-boot-starter</artifactId>
      <version>3.4.0</version>
    </dependency>

yml配置

server:
  port: 8090

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/user?serverTimezone=UTC
    username: root
    password: etoak
    
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #开启sql日志
    call-setters-on-nulls: true
    map-underscore-to-camel-case: true

主启动类

@MapperScan("com.enbei.mapper")
@SpringBootApplication
public class App {
    public static void main( String[] args ) {
        SpringApplication.run(App.class,args);
    }
}

分页插件

@Configuration
public class MybatisPlusConfig {

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        paginationInnerInterceptor.setOverflow(true);
        interceptor.addInnerInterceptor(paginationInnerInterceptor);
        return interceptor;
    }

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseDeprecatedExecutor(false);
    }
}

MyBatis-Plus单表CRUD

创建实体

@Data
@TableName("user")
@NoArgsConstructor
@AllArgsConstructor
public class User {
    /**
     * ID
     */
    @TableId
    private Long id;
    /**
     * 用户名
     */
    private String userName;
    /**
     * 年龄
     */
    private Integer age;
    /**
     * 邮箱
     */
    private String email;

}

关于Mybatis-plus注解

MyBatis-Plus 通用Mapper

mapper接口

@Mapper
public interface UserMapper extends BaseMapper<User> {
}

service

@Service
public class UserService {

    @Resource
    private UserMapper userMapper;

    //添加数据
    public void insert() {
        User user = new User();
        user.setUserName("elena");
        user.setAge(21);
        user.setEmail("123@q63.com");
        userMapper.insert(user);
    }
    //根据id查询
    public void selectById() {
        userMapper.selectById(1);
    }
    //根据id更新
    public void updateById(){
        // 先查询
        User user = userMapper.selectById(1);
        user.setUserName("Jennie");
        // 再修改
        int result = userMapper.updateById(user);
        System.out.println(result);
    }

    //根据map更新
    public void selectByMap(){
        Map<String,Object> map = new HashMap<>();
        map.put("age",25);
        List<User> users = userMapper.selectByMap(map);
    }

    //根据ID批量查询
    public void selectBatchIds() {
        List<User> employees = userMapper.selectBatchIds(Arrays.asList(1, 2, 3));
        employees.forEach(System.out::println);
    }

    //根据ID删除
    public void deleteById() {
        userMapper.deleteById(1);
    }
    //根据ID批量删除
    public void deleteBatchIds() {
       userMapper.deleteBatchIds(Arrays.asList(4, 5, 6));

    }
}

MyBatis-Plus 通用Service

通用service

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

如果有接口的话

public interface IUserService extends IService<User> {}

service

@Service
public class TestService {

    @Resource
    private UserService userService;



    public void save(){
        User user = new User();
        user.setUserName("jack");
        user.setAge(22);
        user.setEmail("123@q63.com");
        userService.save(user);
    }

    public void saveBatch(){
        User user1 = new User();
        user1.setUserName("jack");
        user1.setAge(22);
        user1.setEmail("123@q63.com");

        User user2 = new User();
        user2.setUserName("elena");
        user2.setAge(23);
        user2.setEmail("123@q63.com");

        List<User> list = new ArrayList();
        Collections.addAll(list,user1,user2);
        userService.saveBatch(list);
    }

    public void saveOrUpdate(){
        User user = new User();
        user.setId(1658731091639959553L);
        user.setUserName("jack");
        user.setAge(100);
        user.setEmail("123@q63.com");
        userService.saveOrUpdate(user);
       //userService.saveOrUpdateBatch(Collection<T> entityList); 与saveBatch用法一致
    }

    public void removeById(){
        userService.removeById(1658731091639959553L);
    }

    public void removeByIds(){
        userService.removeByIds(Arrays.asList(1, 2, 3));
    }


    public void removeByMap(){
        Map<String,Object> map = new HashMap<>();
        map.put("age",25);
        userService.removeByMap(map);
    }


    public void updateById(){
        User user = new User();
        user.setId(1658731091639959553L);
        user.setUserName("jack");
        user.setAge(101);
        user.setEmail("123@q63.com");
        userService.updateById(user);
    }

    public void updateBatchById(){
        User user1 = new User();
        user1.setUserName("jack");
        user1.setAge(200);
        user1.setEmail("123@q63.com");

        User user2 = new User();
        user2.setUserName("elena");
        user2.setAge(200);
        user2.setEmail("123@q63.com");

        List<User> list = new ArrayList();
        Collections.addAll(list,user1,user2);
        userService.updateBatchById(list);
    }


    public User getById(){
       return userService.getById(1);
    }

    public List<User> list(){
      return   userService.list();
    }

    public List<Map<String, Object>> listMaps(){
        return  userService.listMaps();
    }

    public List<User> page(){
         int page = 1;
         int pageSize = 2;
         Page<User> userPage = new Page<>(page, pageSize);
         Page<User> result = userService.page(userPage);
         result.getTotal();//总记录数
         result.getCurrent();//当前页
         result.getSize();//每页记录数
         result.getPages();//总页数
        return userPage.getRecords();
    }

    public int count(){
        return userService.count();
    }

}

条件构造器

通用mapper

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

    @Resource
    private UserMapper userMapper;

      //查询单条数据
    public User selectOne(String name) {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        //queryWrapper.eq("user_name","elena");
        queryWrapper.eq(!StringUtils.isEmpty(name),"user_name",name);
        return userMapper.selectOne(queryWrapper);
       // userMapper.selectOne(Wrappers.lambdaQuery(User.class).eq(User::getUserName,name));
    }
    //删除
    public void delete(String name) {
        QueryWrapper<User> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("user_name",name);
        userMapper.delete(queryWrapper);
     //   userMapper.delete(Wrappers.lambdaQuery(User.class).eq(User::getUserName,name));
    }

    //将符合queryWrapper条件的数据更新为实体类中的数据
    public void update(String name) {
        User user = userMapper.selectById(1);
        UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("user_name",name);
    //    LambdaUpdateWrapper<User> lambdaUpdateWrapper = Wrappers.lambdaUpdate(User.class).eq(User::getUserName, name);

        if (user != null){
            user.setAge(10086);
            userMapper.update(user,updateWrapper);
          //  userMapper.update(user,lambdaUpdateWrapper);
        }
    }

   //分页
    public List<User> pageList(String name) {
        int page = 1;
        int pageSize = 2;
        Page<User> userPage = new Page<>(page, pageSize);
        LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.lambdaQuery(User.class)
                .like(User::getUserName, name)
                .eq(User::getAge,23)
                .orderByDesc(User::getId);//根据id排序
        Page<User> result = userMapper.selectPage(userPage, lambdaQueryWrapper);
        result.getTotal();//总记录数
        result.getCurrent();//当前页
        result.getSize();//每页记录数
        result.getPages();//总页数
        return userPage.getRecords();
    }

}

通用service

 @Service
public class TestService{

    @Resource
    private UserService userService;

    //查询
    public void lambdaQuery(){
        List<User> list = userService.lambdaQuery().eq(User::getAge, 18).list();
        list.forEach(System.out::println);
    }

    //修改
    public void lambdaUpdate(){
        boolean update = userService.lambdaUpdate().eq(User::getAge, 18).set(User::getAge, 31).update();
        System.out.println(update);
    }

    //删除
    public void lambdaDel(){
        boolean remove = userService.lambdaUpdate().eq(User::getAge, 18).remove();
        System.out.println(remove);
    }

}

条件构造器的其他方法

eq

eq(R column, Object val)
eq(boolean condition, R column, Object val)

等于 =
例: eq("name", "老王")—>name = '老王'

ne

ne(R column, Object val)
ne(boolean condition, R column, Object val)

不等于 <>
例: ne("name", "老王")—>name <> '老王'

gt

gt(R column, Object val)
gt(boolean condition, R column, Object val)

大于 >
例: gt("age", 18)—>age > 18

ge

ge(R column, Object val)
ge(boolean condition, R column, Object val)

大于等于 >=
例: ge("age", 18)—>age >= 18

lt

lt(R column, Object val)
lt(boolean condition, R column, Object val)

小于 <
例: lt("age", 18)—>age < 18

le

le(R column, Object val)
le(boolean condition, R column, Object val)

小于等于 <=
例: le("age", 18)—>age <= 18

between

between(R column, Object val1, Object val2)
between(boolean condition, R column, Object val1, Object val2)

BETWEEN 值1 AND 值2
例: between("age", 18, 30)—>age between 18 and 30

notBetween

notBetween(R column, Object val1, Object val2)
notBetween(boolean condition, R column, Object val1, Object val2)

NOT BETWEEN 值1 AND 值2
例: notBetween("age", 18, 30)—>age not between 18 and 30

like

like(R column, Object val)
like(boolean condition, R column, Object val)

LIKE ‘%值%’
例: like("name", "王")—>name like '%王%'

notLike

notLike(R column, Object val)
notLike(boolean condition, R column, Object val)

NOT LIKE ‘%值%’
例: notLike("name", "王")—>name not like '%王%'

likeLeft

likeLeft(R column, Object val)
likeLeft(boolean condition, R column, Object val)

LIKE ‘%值’
例: likeLeft("name", "王")—>name like '%王'

likeRight

likeRight(R column, Object val)
likeRight(boolean condition, R column, Object val)

LIKE ‘值%’
例: likeRight("name", "王")—>name like '王%'

notLikeLeft

notLikeLeft(R column, Object val)
notLikeLeft(boolean condition, R column, Object val)

NOT LIKE ‘%值’
例: notLikeLeft("name", "王")—>name not like '%王'

notLikeRight

notLikeRight(R column, Object val)
notLikeRight(boolean condition, R column, Object val)

NOT LIKE ‘值%’
例: notLikeRight("name", "王")—>name not like '王%'

isNull

isNull(R column)
isNull(boolean condition, R column)

字段 IS NULL
例: isNull("name")—>name is null

isNotNull

isNotNull(R column)
isNotNull(boolean condition, R column)

字段 IS NOT NULL
例: isNotNull("name")—>name is not null

in

in(R column, Collection<?> value)
in(boolean condition, R column, Collection<?> value)

字段 IN (value.get(0), value.get(1), …)
例: in("age",{1,2,3})—>age in (1,2,3)

in(R column, Object... values)
in(boolean condition, R column, Object... values)

字段 IN (v0, v1, …)
例: in("age", 1, 2, 3)—>age in (1,2,3)

notIn

notIn(R column, Collection<?> value)
notIn(boolean condition, R column, Collection<?> value)

字段 NOT IN (value.get(0), value.get(1), …)
例: notIn("age",{1,2,3})—>age not in (1,2,3)

notIn(R column, Object... values)
notIn(boolean condition, R column, Object... values)

字段 NOT IN (v0, v1, …)
例: notIn("age", 1, 2, 3)—>age not in (1,2,3)

inSql

inSql(R column, String inValue)
inSql(boolean condition, R column, String inValue)

字段 IN ( sql语句 )
例: inSql("age", "1,2,3,4,5,6")—>age in (1,2,3,4,5,6)
例: inSql("id", "select id from table where id < 3")—>id in (select id from table where id < 3)

notInSql

notInSql(R column, String inValue)
notInSql(boolean condition, R column, String inValue)

字段 NOT IN ( sql语句 )
例: notInSql("age", "1,2,3,4,5,6")—>age not in (1,2,3,4,5,6)
例: notInSql("id", "select id from table where id < 3")—>id not in (select id from table where id < 3)

groupBy

groupBy(R... columns)
groupBy(boolean condition, R... columns)

分组：GROUP BY 字段, …
例: groupBy("id", "name")—>group by id,name

orderByAsc

orderByAsc(R... columns)
orderByAsc(boolean condition, R... columns)

排序：ORDER BY 字段, … ASC
例: orderByAsc("id", "name")—>order by id ASC,name ASC

orderByDesc

orderByDesc(R... columns)
orderByDesc(boolean condition, R... columns)

排序：ORDER BY 字段, … DESC
例: orderByDesc("id", "name")—>order by id DESC,name DESC

orderBy

orderBy(boolean condition, boolean isAsc, R... columns)

排序：ORDER BY 字段, …
例: orderBy(true, true, "id", "name")—>order by id ASC,name ASC

having

having(String sqlHaving, Object... params)
having(boolean condition, String sqlHaving, Object... params)

HAVING ( sql语句 )
例: having("sum(age) > 10")—>having sum(age) > 10
例: having("sum(age) > {0}", 11)—>having sum(age) > 11

func

func(Consumer<Children> consumer)
func(boolean condition, Consumer<Children> consumer)

func 方法(主要方便在出现if…else下调用不同方法能不断链)
例: func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})

or

or()
or(boolean condition)

拼接 OR

注意事项:

主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)
例: eq("id",1).or().eq("name","老王")—>id = 1 or name = '老王'

or(Consumer<Param> consumer)
or(boolean condition, Consumer<Param> consumer)

OR 嵌套
例: or(i -> i.eq("name", "李白").ne("status", "活着"))—>or (name = '李白' and status <> '活着')

and

and(Consumer<Param> consumer)
and(boolean condition, Consumer<Param> consumer)

AND 嵌套
例: and(i -> i.eq("name", "李白").ne("status", "活着"))—>and (name = '李白' and status <> '活着')

nested

nested(Consumer<Param> consumer)
nested(boolean condition, Consumer<Param> consumer)

正常嵌套不带 AND 或者 OR
例: nested(i -> i.eq("name", "李白").ne("status", "活着"))—>(name = '李白' and status <> '活着')

apply

apply(String applySql, Object... params)
apply(boolean condition, String applySql, Object... params)

拼接 sql

注意事项:

该方法可用于数据库函数动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!
例: apply("id = 1")—>id = 1
例: apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")—>date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")—>date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")

last

last(String lastSql)
last(boolean condition, String lastSql)

无视优化规则直接拼接到 sql 的最后

注意事项:

只能调用一次,多次调用以最后一次为准有sql注入的风险,请谨慎使用
例: last("limit 1")

exists

exists(String existsSql)
exists(boolean condition, String existsSql)

拼接 EXISTS ( sql语句 )
例: exists("select id from table where age = 1")—>exists (select id from table where age = 1)

notExists

notExists(String notExistsSql)
notExists(boolean condition, String notExistsSql)

拼接 NOT EXISTS ( sql语句 )
例: notExists("select id from table where age = 1")—>not exists (select id from table where age = 1)

欲无缘

关注

3
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
SpringBoot整合MyBatis-Plus实现增删改查

MyBatis-Plus (opens new window)的增强工具，在 MyBatis 的基础上只做增强不做改变，为简化开发、提高效率而生。
复制链接

扫一扫

专栏目录