Mybatis Plus常见用法

一、环境准备

1.1、目录结构

1.2、pom依赖

	<!-- 引入mysql依赖 -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<!-- 引入druid连接池依赖 -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.10</version>
		</dependency>
		<!-- 引入lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>1.16.20</version>
		</dependency>
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.3.2</version>
		</dependency>

1.3、配置信息

# 配置数据库连接
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/testdb?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC
spring.datasource.username=root
spring.datasource.password=root

# 指定数据源类型
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

# 日志配置
logging.level.root=warn
logging.level.com.example.mp.mapper=trace
logging.pattern.console='%p%m%n'

# 指定sql映射文件位置
mybatis-plus.mapper-locations=classpath:mybatis/mapper/*.xml

1.4、表结构

CREATE TABLE `t_user_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `username` varchar(255) DEFAULT NULL,
  `role_id` bigint(20) DEFAULT NULL,
  `birth` date DEFAULT NULL COMMENT '出生日期',
  `create_time` datetime DEFAULT NULL COMMENT '创建事件',
  `create_by` varchar(100) DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(100) DEFAULT NULL COMMENT '更新人',
  `version` int(10) DEFAULT NULL COMMENT '版本号',
  PRIMARY KEY (`id`),
  KEY `FKeagxymlcrp1q3eeyb60k1vxvp` (`role_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1282820287699673094 DEFAULT CHARSET=utf8;

1.5、其它类/方法

1.5.1、UserInfo

package com.example.mp.entity;
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.util.Date;

@Data
@TableName("t_user_info")
public class UserInfo {
//    @TableId(type = IdType.ASSIGN_UUID) // 主键需是String类型
//    @TableId(type = IdType.ID_WORKER_STR) //自增模式（String类型）
//    @TableId(type = IdType.NONE) //默认雪花算法
    @TableId(type = IdType.AUTO) //自增模式
    private Long id;
    private String username;
    private String password;
    private int age;
    private String phone;
    @TableField(fill = FieldFill.INSERT)    //自动填充
    private Date createTime;
    @TableField(fill = FieldFill.UPDATE)    //自动填充
    private Date updateTime;
    @Version    //乐观锁
    private Integer version; //版本号
}

1.5.2、UserInfoMapper

package com.example.mp.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.mp.entity.UserInfo;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserInfoMapper extends BaseMapper<UserInfo> {

    List<UserInfo> selectUserInfoList();
}

1.5.3、MybatisPlusConfig

package com.example.mp.config;

import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {
    /**
      * @Author pandafox
      * @Desctription 分页插件
      * @Date 2020/7/14 22:03
      */
    @Bean
    public PaginationInterceptor paginationInterceptor(){
        return new PaginationInterceptor();
    }

    /**
      * @Author pandafox
      * @Desctription 乐观锁插件
      * @Date 2020/7/14 22:03
      */
    @Bean
    public OptimisticLockerInterceptor optimisticLockerInterceptor(){
        return new OptimisticLockerInterceptor();
    }
}

1.5.4、MyMetaObjectHandler

package com.example.mp.component;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.util.Date;

/**
 * @Desctription: 元数据处理
 * @Date: Created in 2020/7/14 7:56
 */
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) {
        boolean hasSetter = metaObject.hasSetter("createTime");
        if(hasSetter){
            setInsertFieldValByName("createTime", new Date(), metaObject);
        }
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        Object val = getFieldValByName("updateTime", metaObject);
        if(val == null){
            setUpdateFieldValByName("updateTime", new Date(), metaObject);
        }
    }
}

1.5.5、MpApplication

package com.example.mp;

import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
@MapperScan("com.example.mp.mapper")
public class MpApplication {

	public static void main(String[] args) {
		SpringApplication.run(MpApplication.class, args);
	}
}

1.5.6、UserInfoMapper.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.example.mp.mapper.UserInfoMapper">

    <select id="selectUserInfoList" resultType="com.example.mp.entity.UserInfo">
        select * from t_user_info order by id
    </select>

</mapper>

二、常用方法

2.1、列表查询

	@Autowired
	private UserInfoMapper userInfoMapper;

	@Test
	public void testSelectList(){
		List<UserInfo> list =  userInfoMapper.selectList(null);
		list.forEach(System.out::println);
	}

2.2、新增

	@Test
	public void testInsert(){
		UserInfo info = new UserInfo();
		info.setUsername("哈哈");
		info.setAge(16);
		info.setPhone("10020099808");
		info.setPassword("ld833wwwldd");
		int rows = userInfoMapper.insert(info);
		System.out.println(rows);
		System.out.println(info.getId());
	}

2.3、根据ID查询

	@Test
	public void testSelectById(){
		System.out.println(userInfoMapper.selectById(1282460525430575106L));
	}

2.4、根据IDS批量查询

	@Test
	public void testSelectBatchIds(){
		List<Long> ids = Arrays.asList(1282460525430575106L,1282464381938036737L);
		List<UserInfo> list =  userInfoMapper.selectBatchIds(ids);
		list.forEach(System.out::println);
	}

2.5、根据map构造条件查询

	@Test
	public void testSelectByMap(){
		Map<String, Object> columnMap = new HashMap<>();
		columnMap.put("age", 20);
		List<UserInfo> list =  userInfoMapper.selectByMap(columnMap);
		list.forEach(System.out::println);
	}

2.6、根据wrapper构造条件查询

	@Test
	public void testSelectByWrapper01(){
		QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
		//1.年龄小于20岁，姓名中包含张字
//		wrapper.lt("age", 20).like("username","张");

		//2.年大于20小于50，姓名中包含李字，电话不为空
//		wrapper.between("age", 20,50).like("username","李").isNotNull("phone");

		//3.年龄大于20，按照年龄降序
		wrapper.ge("age", 20).orderByDesc("age");
		List<UserInfo> list =  userInfoMapper.selectList(wrapper);
		list.forEach(System.out::println);
	}

2.7、查询指定列

	@Test
	public void testSelectByWrapper02(){
		QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
		wrapper.select("username","age").ge("age", 20).orderByDesc("age");
		List<UserInfo> list =  userInfoMapper.selectList(wrapper);
		list.forEach(System.out::println);
	}

2.8、根据condition查询

	@Test
	public void testSelectByCondition(){
		QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
		String username = "15566668888";
		String phone = "张";
		wrapper.like(StringUtils.isNotBlank(phone) ,"phone", username)
				.like(StringUtils.isNotBlank(username), "username", phone);

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

2.9、根据Maps查询

	@Test
	public void testSelectByWrapperMaps01(){
		QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
		wrapper.select("username","age").ge("age", 20).orderByDesc("age");
		List<Map<String, Object>> list =  userInfoMapper.selectMaps(wrapper);
		list.forEach(System.out::println);
	}

2.10、根据Maps查询（统计）

	@Test
	public void testSelectByWrapperMaps02(){
		QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
		wrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age").ge("age", 20).orderByDesc("age");
		List<Map<String, Object>> list =  userInfoMapper.selectMaps(wrapper);
		list.forEach(System.out::println);
	}

2.11、查询记录数

	@Test
	public void testSelectByWrapperCount(){
		QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
		wrapper.ge("age", 20);
		Integer count =  userInfoMapper.selectCount(wrapper);
		System.out.println(count);
	}

2.12、查询一条数据

	@Test
	public void testSelectByWrapperOne(){
		QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
		wrapper.eq("age", 50);
		UserInfo info =  userInfoMapper.selectOne(wrapper);
		System.out.println(info);
	}

2.13、分页查询（物理分页）

	@Test
	public void testSelectByPage(){
		QueryWrapper<UserInfo> wrapper = new QueryWrapper<>();
		wrapper.ge("age", 18);

		long current = 1L;
		long size = 5L;
		Page<UserInfo> page = new Page<>(current, size);

		IPage<UserInfo> iPage =  userInfoMapper.selectPage(page, wrapper);
		System.out.println("总页数：" + iPage.getPages());
		System.out.println("总记录数：" + iPage.getTotal());
		System.out.println(iPage.getRecords());

	}

2.14、根据ID更新

	@Test
	public void testUpdateById(){
		UserInfo info = new UserInfo();
		info.setVersion(1);	//乐观锁版本号
		info.setId(1282820287699673093L);
		info.setPhone("15566663333");
		System.out.println(userInfoMapper.updateById(info));
	}

2.15、根据Wrapper条件更新

	@Test
	public void testUpdateByWrapper(){
		UpdateWrapper<UserInfo> wrapper = new UpdateWrapper<>();
		wrapper.eq("username","张三");
		UserInfo info = new UserInfo();
		info.setPhone("15566666666");
		System.out.println(userInfoMapper.update(info, wrapper));
	}

2.16、根据ID删除

	@Test
	public void testDeleteById(){
		System.out.println(userInfoMapper.deleteById(1282460525430575106L));
	}

2.17、根据ID批量删除

	@Test
	public void testDeleteByIds(){
		System.out.println(userInfoMapper.deleteBatchIds(Arrays.asList(1L, 3L, 4L)));
	}

2.18、根据Map条件删除

	@Test
	public void testDeleteByMap(){
		Map<String, Object> map = new HashMap<>();
		map.put("username", "lucy");
		System.out.println(userInfoMapper.deleteByMap(map));
	}

2.19、列表查询（自定义xml语句）

	@Test
	public void testSelectUserInfoList(){
		System.out.println(userInfoMapper.selectUserInfoList());
	}