Mybatis Plus概述
MyBatis-Plus(简称 MP)是一个 MyBatis 的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生(提供了快速使用mybatis的方式)。
版本
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus</artifactId>
<version>3.4.2</version>
</dependency>
快速入门
SpringBoot 整合 MyBatis-Plus,并实现根据Id查询功能。
1、数据库环境准备
2、创建SpringBoot工程,引入MyBatis-Plus场景依赖
3、编写DataSource相关配置
4、编写mapper
5、测试
数据库环境准备
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for tb_user 没有给自增
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
`id` bigint(20) NOT NULL,
`user_name` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
`t_name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
`email` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
-- Records of tb_user
-- ----------------------------
BEGIN;
INSERT INTO `tb_user` VALUES (1, '赵一伤', '123456', 'zys', 19, 'zys@itcast.cn');
INSERT INTO `tb_user` VALUES (2, '钱二败', '123456', 'qes', 18, 'qes@itcast.cn');
INSERT INTO `tb_user` VALUES (3, '孙三毁', '123456', 'ssh', 20, 'ssh@itcast.cn');
INSERT INTO `tb_user` VALUES (4, '李四摧', '123456', 'lsc', 20, 'lsc@itcast.cn');
INSERT INTO `tb_user` VALUES (5, '周五输', '123456', 'zws', 20, 'zws@itcast.cn');
INSERT INTO `tb_user` VALUES (6, '吴六破', '123456', 'wlp', 21, 'wlp@itcast.cn');
INSERT INTO `tb_user` VALUES (7, '郑七灭', '123456', 'zqm', 22, 'zqm@itcast.cn');
INSERT INTO `tb_user` VALUES (8, '王八衰', '123456', 'wbs', 22, 'wbs@itcast.cn');
INSERT INTO `tb_user` VALUES (9, '张无忌', '123456', 'zwj', 25, 'zwj@itcast.cn');
INSERT INTO `tb_user` VALUES (10, '赵敏', '123456', 'zm', 26, 'zm@itcast.cn');
INSERT INTO `tb_user` VALUES (11, '赵二伤', '123456', 'zes', 25, 'zes@itcast.cn');
INSERT INTO `tb_user` VALUES (12, '赵三伤', '123456', 'zss1', 28, 'zss1@itcast.cn');
INSERT INTO `tb_user` VALUES (13, '赵四伤', '123456', 'zss2', 29, 'zss2@itcast.cn');
INSERT INTO `tb_user` VALUES (14, '赵五伤', '123456', 'zws', 39, 'zws@itcast.cn');
INSERT INTO `tb_user` VALUES (15, '赵六伤', '123456', 'zls', 29, 'zls@itcast.cn');
INSERT INTO `tb_user` VALUES (16, '赵七伤', '123456', 'zqs', 39, 'zqs@itcast.cn');
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
创建工程,引入场景依赖
<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>2.3.10.RELEASE</version>
<relativePath/> <!-- lookup parent from repository -->
</parent>
<properties>
<java.version>1.8</java.version>
</properties>
<dependencies>
<!-- mysql 驱动-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.26</version>
</dependency>
<!-- lombok ,自动生成get,Set 方法-->
<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起步依赖-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.2</version>
</dependency>
</dependencies>
编写DataSource相关配置
# datasource
spring:
datasource:
url: jdbc:mysql://192.168.23.129:3306/MybatisPl?useSSL=false
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
说明:
如果想在mybatis-plus自行是看打印的sql,可配置:
#mybatis-plus配置控制台打印完整带参数SQL语句
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
编码
实体类:
package com.wuman.pojo;
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.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
@TableName("tb_user")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
@Accessors(chain = true)
public class User {
// @TableId(type = IdType.AUTO)//指定为主键字段 指定当前字段为主键自增字段
//应用场景:无主键自增,且主键的格式是数字类型的字段
@TableId(value = "id",type = IdType.ASSIGN_ID)//不指定,则默认采取NONE模式,等价于ID_WORKER,只能针对number类型和string【生成数字格式的字符串】
//@TableId(value = "user_id",type = IdType.ASSIGN_UUID)//生成随机字符串,包含字母,应用场景:主键类型是string类型,并且组成元素包含字母和数字
private Long id;
//mp驼峰映射默认开启
private String userName;
private String password;
//Caused by: java.sql.SQLSyntaxErrorException: Unknown column 'name' in 'field list'
@TableField("t_name")//指定对应table中的字段的名称,如果pojo中属性名称与table中列名称一致,则可以不写注解
private String name;
private Integer age;
private String email;
/**
* exist
* true:默认值,当前属性是table的列
* false:不是
*/
@TableField(exist = false)
private String address;
}
@TableName("tb_user”) : 如果数据库的表名和实体类一致时可以省略
编写mapper:
package com.itheima.sh.mapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.itheima.sh.pojo.User;
/**
* 使用mp定义Mapper,需要让Mapper接口继承 BaseMapper接口。
*/
public interface UserMapper extends BaseMapper<User> {
}
启动类增加 @MapperScan 注解
package com.itheima.sh;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("com.itheima.sh.mapper")
public class MpApplication {
public static void main(String[] args) {
SpringApplication.run(MpApplication.class, args);
}
}
测试
package com.itheima.sh;
import com.itheima.sh.mapper.UserMapper;
import com.itheima.sh.pojo.User;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
public class UserMapperTest {
@Autowired
private UserMapper userMapper;
/**
* 根据id查询
*/
@Test
public void testSelectById() {
User user = userMapper.selectById(1L);
System.out.println(user);
}
}
CRUD
新增
@Test
public void testInsert() {
User user =
User.builder()
.userName("itheima")
.name("itcast")
.age(15)
.email("itcast@itcast.cn")
.password("111111")
.build();
int insert = userMapper.insert(user);
System.out.println(insert);
}
主键生成策略-@TableId
@Documented
@Retention(RetentionPolicy.RUNTIME)
@Target({ElementType.FIELD, ElementType.ANNOTATION_TYPE})
public @interface TableId {
/**
* 字段名(该值可无)
*/
String value() default "";
/**
* 主键类型
* {@link IdType} 查看 IdType
*/
IdType type() default IdType.NONE; // 默认是 NONE
}
// IdType
@Getter
public enum IdType {
/**
* 数据库ID自增
* <p>该类型请确保数据库设置了 ID自增 否则无效</p>
*/
AUTO(0),
/**
* 该类型为未设置主键类型(注解里等于跟随全局,全局里约等于 INPUT)
*/
NONE(1),
/**
* 用户输入ID
* <p>该类型可以通过自己注册自动填充插件进行填充</p>
*/
INPUT(2),
/* 以下3种类型、只有当插入对象ID 为空,才自动填充。 */
/**
* 分配ID (主键类型为number或string),
* 默认实现类 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}(雪花算法)
*
* @since 3.3.0
*/
ASSIGN_ID(3),
/**
* 分配UUID (主键类型为 string)
* 默认实现类 {@link com.baomidou.mybatisplus.core.incrementer.DefaultIdentifierGenerator}(UUID.replace("-",""))
*/
ASSIGN_UUID(4),
// 省略部分代码.....
}
普通列注解- @TableField
作用:
1)@TableField(“user_name”) 指定映射关系
以下情况可以省略:
- 名称一样
- 数据库字段使用_分割,实体类属性名使用驼峰名称
2)忽略某个字段的查询和插入 @TableField(exist = false)
/**
* 实体类的属性名和数据库的字段名自动映射:
* 1. 名称一样
* 2. 数据库字段使用_分割,实体类属性名使用驼峰名称
*/
@TableName("tb_user")
@Data
public class User {
//设置id生成策略:AUTO 数据库自增
@TableId(type = IdType.AUTO)
private Long id;
//@TableField("user_name")
private String userName;
private String password;
@TableField("t_name")
private String name;
private Integer age;
private String email;
//不希望该值存入数据库
// @TableField(exist = false)
// private String address;
}
删除
根据id删除
int count = userMapper.deleteById(8L);
根据id集合批量删除
List ids = new ArrayList();
ids.add(6);
ids.add(7);
userMapper.deleteBatchIds(ids);
根据map构造条件,删除
Map<String, Object> map = new HashMap<>();
//delete from tb_user where user_name = ? and age = ?
map.put("user_name","itcast");
map.put("age","18");
userMapper.deleteByMap(map);
更新
@Test
public void testUpdateById() {
User user = new User();
user.setId(2L);
user.setPassword("1111111");
int count = userMapper.updateById(user);
}
查询
分页查询
配置 拦截器
package com.itheima.sh.config;
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* @Description:
* @Version: V1.0
*/
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
PaginationInnerInterceptor paginationInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);
// 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求 默认false
// paginationInterceptor.setOverflow(false);
// 设置最大单页限制数量,默认 500 条,-1 不受限制
paginationInterceptor.setMaxLimit(-1L);
// 开启 count 的 join 优化,只针对部分 left join
interceptor.addInnerInterceptor(paginationInterceptor);
return interceptor;
}
}
查询
/**
* 分页查询:
* 1. 当前页码:currentPage
* 2. 每页显示条数:size
*
* 注意:使用mp的分页要设置一个拦截器!!!
*/
@Test
public void testSelectPage() {
int current = 1;//当前页码
int size = 2;//每页显示条数
IPage<User> page = new Page(current,size);
userMapper.selectPage(page,null);
List<User> records = page.getRecords();//当前页的数据
long pages = page.getPages();//总页数 2
long total = page.getTotal();//总记录数 4
System.out.println(records);
System.out.println(pages);
System.out.println(total);
}
基础查询
eq( ) : 等于 =
ne( ) : 不等于 <>
gt( ) : 大于 >
ge( ) : 大于等于 >=
lt( ) : 小于 <
le( ) : 小于等于 <=
between ( ) : BETWEEN 值1 AND 值2
notBetween ( ) : NOT BETWEEN 值1 AND 值2
in( ) : in
notIn( ) :not in
@Test
public void testWrapper1() throws Exception{
QueryWrapper<User> wrapper = new QueryWrapper<>();
// 封装查询条件
wrapper.like("user_name", "伤")
.eq("password","123456")
.in("age",19,25,29)
.orderByDesc("age","id"); // 降序 升序:asc
List<User> users = userMapper.selectList(wrapper);
System.out.println(users);
}
逻辑查询 or
or( ) :让紧接着下一个方法用or连接
@Test
public void testWrapper2(){
//1.创建查询条件构建器
QueryWrapper<User> wrapper = new QueryWrapper<>();
//2.设置条件
wrapper.eq("user_name","lisi")
.or()
.lt("age",23)
.in("name","李四","王五");
/*
select * from tb_user where user_name = ? or age < ? and name in (?,?)
*/
List<User> users = userMapper.selectList(wrapper);
System.out.println(users);
}
模糊查询 like
like 自动添加 %条件值%
notLike
likeLeft 自动添加 %条件值
likeRight 自动条件 条件值%
/**
* 模糊查询
*/
@Test
public void testWrapper3(){
//1.创建查询条件构建器
QueryWrapper<User> wrapper = new QueryWrapper<>();
//2.设置条件
wrapper.likeLeft("user_name","zhang");
/*
SELECT id,user_name,password,name,age,email
from tb_user
where user_name like ?
%zhang
*/
List<User> users = userMapper.selectList(wrapper);
System.out.println(users);
}
排序查询
orderBy
orderByAsc 升序排序
orderByDesc 降序排序
@Test
public void testWrapper4(){
//1.创建查询条件构建器
QueryWrapper<User> wrapper = new QueryWrapper<>();
//2.设置条件
wrapper.eq("user_name","lisi")
.or()
.lt("age",23)
.in("name","李四","王五")
//.orderBy(true,true,"age")
.orderByDesc("age");
/*
select * from tb_user where user_name = ? or age < ? and name in (?,?) order by age asc
*/
List<User> users = userMapper.selectList(wrapper);
System.out.println(users);
}
select:指定需要查询的字段
@Test
public void testWrapper5(){
//1.创建查询条件构建器
QueryWrapper<User> wrapper = new QueryWrapper<>();
//2.设置条件
wrapper.eq("user_name","lisi")
.or()
.lt("age",23)
.in("name","李四","王五")
//.orderBy(true,true,"age")
.orderByDesc("age")
.select("id","user_name");
/*
select id,user_name from tb_user where user_name = ? or age < ? and name in (?,?) order by age asc
*/
List<User> users = userMapper.selectList(wrapper);
System.out.println(users);
}
分页条件查询
@Test
public void testWrapper6(){
int current = 1;//当前页码
int size = 2;//每页显示条数
//1. 构建分页对象
Page<User> page = new Page<>(current,size);
//2. 构建条件对象
QueryWrapper<User> wrapper = new QueryWrapper();
wrapper.lt("age",23);
userMapper.selectPage(page,wrapper);
List<User> records = page.getRecords();
long total = page.getTotal();
long pages = page.getPages();
System.out.println(records);
System.out.println(total);//2
System.out.println(pages);//1
}
LambdaQueryWrapper
目的:消除代码中的硬编码,解耦合
@Test
public void testWrapper4() throws Exception{
// LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
LambdaQueryWrapper<User> wrapper = Wrappers.<User>lambdaQuery();
// wrapper.like("user_name", "%伤%")
// .eq("password","123456")
// .ge("age", 28)
// .between("age",29 , 39); // 包含边界值
wrapper.like(User::getUserName, "%伤%")
.eq(User::getPassword, "123456")
.ge(User::getAge, 28)
.between(User::getAge, 29, 39)
.orderByDesc(User::getAge)
.select(User::getId, User::getUserName);
List<User> users = userMapper.selectList(wrapper);
System.out.println(users);
}
条件删除
/**
* 条件删除
* @throws Exception
*/
@Test
public void testWrapper5() throws Exception{
LambdaQueryWrapper<User> wrapper = Wrappers.<User>lambdaQuery().eq(User::getUserName, "武大郎");
int i = userMapper.delete(wrapper);
System.out.println(i);
}
条件 update
/**
* 条件更新
* @throws Exception
*/
@Test
public void testWrapper6() throws Exception{
/**
* UPDATE tb_user SET t_name=? WHERE (id = ?)
*/
// 参数1: 最新的值
User user = new User();
user.setUserName("张三丰");
// 参数2:更新时条件
LambdaQueryWrapper<User> wrapper = Wrappers.<User>lambdaQuery();
wrapper.eq(User::getId, 15);
int update = userMapper.update(user, wrapper);
System.out.println(update);
}
/**
* 条件更新
* @throws Exception
*/
@Test
public void testWrapper7() throws Exception{
/**
* UPDATE tb_user SET t_name=?, user_name=? WHERE (id = ?)
*/
// 参数1: 最新的值
// 参数2:更新时条件
LambdaUpdateWrapper<User> wrapper = Wrappers.<User>lambdaUpdate();
wrapper.eq(User::getId, 15)
.set(User::getUserName, "张三丰666")
.set(User::getName,"zsf666");
int update = userMapper.update(null, wrapper);
System.out.println(update);
}
service 封装
Mybatis-Plus 为了开发更加快捷,对业务层也进行了封装,直接提供了相关的接口和实现类。我们在进行业务层开发时,可以继承它提供的接口和实现类,使得编码更加高效
定义接口继承IService
定义实现类继承ServiceImpl<Mapper,Entity> 实现定义的接口
接口
public interface UserService extends IService<User> {}
实现类封装
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {}
编写Controller,使用PostMan测试
@DeleteMapping("/{id}")
public boolean delete(@PathVariable("id") Long id) {
return userService.removeById(id);
}
@PutMapping("/{id}")
public boolean update(@PathVariable("id") Long id,@RequestBody User user) {
return userService.update(user, Wrappers.<User>lambdaQuery().eq(User::getId, id));
}
@GetMapping("/{id}")
public User findOne(@PathVariable("id") Long id) {
return userService.getById(id);
}
Mybatis X插件
MybatisX 是一款基于 IDEA 的快速开发插件,为效率而生。
安装方法:打开 IDEA,进入 File -> Settings -> Plugins -> Browse Repositories,输入 mybatisx
搜索并安装。
功能:
- Java 与 XML 调回跳转
- Mapper 方法自动生成 XML
逆向工程