Mybatis-Plus基础
表名字映射规则---->对应实体类名字。字段对应实体类对应属性
引入依赖
<!-- mybatis-plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
application.yml
# DataSource Config
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mybatis-plus_test?serverTimezone=GMT&useSSL=false&allowPublicKeyRetrieval=true
username: root
password: 123
mybatis-plus:
configuration:
map-underscore-to-camel-case: true
global-config:
banner: false
db-config:
logic-delete-value: 1
logic-not-delete-value: 0
type-enums-package: cn.tfs.mybatisplus.enums
实体类
package cn.tfs.mybatisplus.bean;
import cn.tfs.mybatisplus.enums.StatusEnum;
import com.baomidou.mybatisplus.annotation.*;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.Date;
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "user")
@Builder
public class User {
@TableId(value = "id",type = IdType.AUTO)
private Integer id;
private String name;
private Integer age;
private String email;
@TableField(fill = FieldFill.INSERT)
private Date createTime;
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime;
@Version
private Integer version;
private StatusEnum status;
@TableLogic
private Integer deleted;
}
@TableName(value = “xxx”)
映射数据库的表名
@TableId(value = “xxx”,type = Idtype.xxx)
主键字段映射,value映射主键字段名
type设置主键类型,主键的生成策略
AUTO(0),
NONE(1),
INPUT(2),
ASSIGN_ID(3),
ASSIGN_UUID(4),
/** @deprecated */
@Deprecated
ID_WORKER(3),
/** @deprecated */
@Deprecated
ID_WORKER_STR(3),
/** @deprecated */
@Deprecated
UUID(4);
值 | 描述 |
---|---|
AUTO | 数据库自增,手动赋值无效 |
NONE | MP set主键,雪花算法实现(默认) |
INPUT | 需要开发者手动复制,若没有复制,则采用主键自增 |
ASSIGN_ID | MP分配ID,Long、Integer、String |
ASSIGN_UUID | 分配UUID,String |
ASSIGN_ID: 自动复制,雪花算法
ASSIGN_UUID: 主键数据类型必须是String,自动生成UUID进行复制
@TableField
数据库字段映射
value 表示映射的数据库字段
exist 表示是否为数据库字段,默认为true。如果实体类中的成员变量在数据库中没有对应的字段,则可以使用exist,VO、DTO
select 表示是否查询该字段
fill 表示是否自动填充,讲对象存入数据库的时候,由MyBatis Plus 自动给某些字段赋值(create_time、update_time)。通过创建自动填充处理器来完成
自动填充处理器
package cn.tfs.mybatisplus.handler;
import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;
import org.apache.ibatis.reflection.MetaObject;
import org.springframework.stereotype.Component;
import java.util.Date;
/**
* @author fwh
* @version 1.0
* @date 2020/11/20 10:51
* @description
* 成员变量名,时间,成员对象
*/
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
this.setFieldValByName("createTime",new Date(),metaObject);
this.setFieldValByName("updateTime",new Date(),metaObject);
}
@Override
public void updateFill(MetaObject metaObject) {
this.setFieldValByName("updateTime",new Date(),metaObject);
}
}
实体类:
@Data
@AllArgsConstructor
@NoArgsConstructor
@TableName(value = "user")
//将实体类account和数据库表user进行绑定映射
public class Account {
@TableId(value="id",type= IdType.AUTO)
private Integer id;
@TableField(value = "username")
private Integer age;
private String email;
@TableField(fill = FieldFill.INSERT)
private Date createTime;
@TableField(fill = FieldFill.INSERT_UPDATE)
private Date updateTime;
@Version
private Integer version;
private Integer status;
}
@Version
标记乐观锁,通过一个version字段来保证数据的安全性。当修改数据的时候,会以version作为条件,当条件成立的时候才会修改成功。
version = 1
线程1 : update … set version = 2 where version = 1
线程2 : update … set version = 2 where version = 1
保证只有一个线程执行数据修改。通过逻辑去控制,又MP自动帮助生成
1、数据库表增加version字段,默认值为1
2、实体类添加version成员变量,并添加@Version字段
3、注册一个配置类
package cn.tfs.mybatisplus.config;
import com.baomidou.mybatisplus.extension.plugins.OptimisticLockerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
/**
* @author fwh
* @version 1.0
* @date 2020/11/20 11:21
* @description 乐观锁
*/
@Configuration
public class MyBatisPlustConfig {
@Bean
public OptimisticLockerInterceptor optimisticLockerInterceptor(){
return new OptimisticLockerInterceptor();
}
}
@EnumValue
通用枚举类注解,将数据库字段映射成实体类的枚举类型成员变量
package cn.tfs.mybatisplus.enums;
import com.baomidou.mybatisplus.annotation.EnumValue;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
/**
* @author fwh
* @version 1.0
* @date 2020/11/20 13:03
* @description
*/
@AllArgsConstructor
@NoArgsConstructor
public enum StatusEnum {
WORK(1,"上班"),
REST(0,"休息");
@EnumValue
private Integer code;
private String msg;
}
根据对应字段进行映射
application.yml 加上对应的类路径
mybatis-plus:
type-enums-package: cn.tfs.mybatisplus.enums
实现接口的方式
package cn.tfs.mybatisplus.enums;
import com.baomidou.mybatisplus.annotation.EnumValue;
import lombok.AllArgsConstructor;
import lombok.NoArgsConstructor;
/**
* @author fwh
* @version 1.0
* @date 2020/11/20 13:03
* @description
*/
@AllArgsConstructor
@NoArgsConstructor
public enum StatusEnum implements IEnum<Integer> {
WORK(1,"上班"),
REST(0,"休息");
@EnumValue
private Integer code;
private String msg;
@Override
public Integer getValue() {
return this.code;
}
}
@TableLogic
映射逻辑删除
1.数据表添加deleted字段
2.实体类添加注解
3.application.yml配制
mybatis-plus:
db-config:
logic-delete-value: 1
logic-not-delete-value: 0
注:走的是更新操作,而不是删除操作。将deleted更改为1,表示已经删除(逻辑层面),但数据依然还在
操作
查询方式 | 说明 |
---|---|
setSqlSelect | 设置 SELECT 查询字段 |
where | WHERE 语句,拼接 +?WHERE 条件 |
and | AND 语句,拼接 +?AND 字段=值 |
andNew | AND 语句,拼接 +?AND (字段=值) |
or | OR 语句,拼接 +?OR 字段=值 |
orNew | OR 语句,拼接 +?OR (字段=值) |
eq | 等于= |
allEq | 基于 map 内容等于= |
ne | 不等于<> |
gt | 大于> |
ge | 大于等于>= |
lt | 小于< |
le | 小于等于<= |
like | 模糊查询 LIKE |
notLike | 模糊查询 NOT LIKE |
in | IN 查询 |
notIn | NOT IN 查询 |
isNull | NULL 值查询 |
isNotNull | IS NOT NULL |
groupBy | 分组 GROUP BY |
having | HAVING 关键词 |
orderBy | 排序 ORDER BY |
orderAsc | ASC 排序 ORDER BY |
orderDesc | DESC 排序 ORDER BY |
exists | EXISTS 条件语句 |
notExists | NOT EXISTS 条件语句 |
between | BETWEEN 条件语句 |
notBetween | NOT BETWEEN 条件语句 |
addFilter | 自由拼接 SQL |
last | 拼接在最后,例如:last(“LIMIT 1”) |
Select
//条件构造器
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//单条件查询 拼接where
List<User> userList = userMapper.selectList(queryWrapper.eq("deleted",1));
userList.forEach(System.out::println);
//多条件查询
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> map = new HashMap()<>;
map.put("name","fwh");
map.put("age",20);
List<User> userList = userMapper.selectList(queryWrapper.alleq(map));
userList.forEach(System.out::println);
其他方法相似
联合查询
//inSql
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("id","select id from user where id < 5");
queryWrapper.inSql("age","select age from user where age > 18");
userMapper.selectList(queryWrapper);
sql执行:
select id,name,age,email,....deleted from user where (id IN(select id from user where id < 5) AND age IN (select age from user where age > 18))
//其他
Map<String,Object> map = new HashMap()<>;
map.put("name","fwh");
map.put("age",20);
//map只能做等值查询 逻辑判断则需要用Wrapper
userMapper.selectByMap(map);
//将查询的结果集封装到map里,采用<key,value>的形式
userMapper.selectMaps(map);
分页查询
1.添加分页拦截器(在这里自动处理分页逻辑)
@Bean
public PaginationInterceptor paginationInterceptor(){
return new PaginationInterceptor();
}
2.编写分页操作
//分页查询 current页数,size每页的记录数
Page<User> page = new Page<>(1,3);
Page<User> result = userMapper.selectPage(page,null);
//每页取的数量
System.out.println(result.getSize());
//总记录数
System.out.println(result.getTotal());
result.getRecords().forEach(System.out::println);
//使用map进行封装查询
Page<Map<String,Object>> page1 = new Page<>(1,4);
userMapper.selectMapsPage(page1,null).getRecords().forEach(System.out::println);
//result 控制台打印
3
8
User(id=2, name=zjh, age=20, email=test@qq.com, createTime=null, updateTime=null, version=1, status=WORK, deleted=0)
User(id=3, name=xz, age=18, email=test1@qq.com, createTime=null, updateTime=null, version=1, status=WORK, deleted=0)
User(id=4, name=admin, age=18, email=admin@firefox.cn, createTime=null, updateTime=null, version=1, status=WORK, deleted=0)
{deleted=0, name=admin, id=4, version=1, age=18, email=admin@firefox.cn, status=1}
{deleted=0, name=admin, id=5, version=1, age=18, email=admin@firefox.cn, status=1}
{deleted=0, name=admin, id=6, version=1, age=18, email=admin@firefox.cn, status=1}
{deleted=0, name=admin, id=7, version=1, age=18, email=admin@firefox.cn, status=1}
自定义SQL(多表关联查询)
使用@Select注解
package cn.tfs.mybatisplus.mapper;
import cn.tfs.mybatisplus.bean.User;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.springframework.stereotype.Component;
@Component("userMapper")
public interface UserMapper extends BaseMapper<User> {
@Select("select p.*,u.name from product p,user u where p.user_id = u.id")
List<UserDTO> userDTOlist;
}
Insert
@Test
void insertTest() {
System.out.println("insert功能");
User user = new User();
user.setName("fuwnehao");
user.setAge(18);
user.setEmail("admin@firefox.cn");
int count = userMapper.insert(user);
System.out.println("执行了"+count+"条insert");
}
delete
@Test
void deteleTest(){
//根据id删除
userMapper.deleteById(1);
//根据id批量删除
userMapper.deleteBatchIds(Arrays.asList(1,2,3));
//通过map批量删除
Map<String,Object> map = new HashMap<>();
map.put("name","fwh");
map.put("age","21");
System.out.println(map);
System.out.println(userMapper.deleteByMap(map));
}
update
@Test
void updateTest(){
//接受 or 修改参数
User user = new User();
user.setName("fwh的老婆");
//实例化条件构造器
UpdateWrapper<User> updateWrapper = new UpdateWrapper<User>();
updateWrapper.eq("name","yyb");
userMapper.update(user,updateWrapper);
// 需要给user赋值id userMapper.updateById(user);
}
MyBatisPlus自动生成
根据数据表自动生成实体类、Mapper、Service、ServiceImpl、Controller
1.pom.xml导入MyBatis Plus Generator
<!--MP代码自动生成器-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.3.1.tmp</version>
</dependency>
<!--代码生成器模板-->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity</artifactId>
<version>1.7</version>
</dependency>
velocity(默认)、Freemarker、Beetl
2.配制
package cn.tfs.mybatisplus;
import com.baomidou.mybatisplus.annotation.DbType;
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.NamingStrategy;
/**
* @author fwh
* @version 1.0
* @date 2020/11/21 9:07
* @description
*/
public class Main {
public static void main(String[] args) {
//创建generator对象
AutoGenerator autoGenerator = new AutoGenerator();
//数据源
DataSourceConfig dataSourceConfig = new DataSourceConfig();
dataSourceConfig.setDbType(DbType.MYSQL);
//jdbc:mysql://localhost:port/db?serverTimezone=GMT&useSSL=false&allowPublicKeyRetrieval=true
dataSourceConfig.setUrl("jdbc:mysql://localhost:3306/lib?serverTimezone=GMT&useSSL=false&allowPublicKeyRetrieval=true");
dataSourceConfig.setUsername("root");
dataSourceConfig.setPassword("123");
dataSourceConfig.setDriverName("com.mysql.cj.jdbc.Driver");
autoGenerator.setDataSource(dataSourceConfig);
//全局配制
GlobalConfig globalConfig = new GlobalConfig();
//文件创建的路径
globalConfig.setOutputDir(System.getProperty("user.dir")+"/src/main/java");
//文件创建好默认不打开
globalConfig.setOpen(false);
globalConfig.setAuthor("fwh");
//globalConfig.setServiceName("%Service");
autoGenerator.setGlobalConfig(globalConfig);
//包信息
PackageConfig packageConfig = new PackageConfig();
//父包路径
packageConfig.setParent("cn.tfs.mybatisplus");
//新建一个module,将新建的包都放进这个文件夹里面
packageConfig.setModuleName("generator");
packageConfig.setController("controller");
packageConfig.setService("service");
packageConfig.setServiceImpl("service.impl");
packageConfig.setMapper("mapper");
packageConfig.setEntity("pojo");
autoGenerator.setPackageInfo(packageConfig);
//配制策略
StrategyConfig strategyConfig = new StrategyConfig();
//生成的实体类默认加上@Date注解
strategyConfig.setEntityLombokModel(true);
//数据库字段名_自动转驼峰
strategyConfig.setNaming(NamingStrategy.underline_to_camel);
strategyConfig.setColumnNaming(NamingStrategy.underline_to_camel);
autoGenerator.setStrategy(strategyConfig);
//配置完成,运行
autoGenerator.execute();
}
}