文章目录
MyBatis-Plus基础知识
MyBatis-Plus(简称 MP)是一个 MyBatis 的增强工具,在 MyBatis 的基础上只做增强不做改变,为简化开发、提高效率而生。
一、环境搭建
1.导入依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.1</version>
</dependency>
2.加入配置
server:
port: 9999
spring:
application:
name: gof23-server # 应用名称
datasource:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://120.48.28.131:3306/zhongxu-db?useSSL=false&serverTimezone=GMT%2B8&characterEncoding=utf8&allowMultiQueries=true&autoReconnect=true
hikari:
username: root
password: root
connection-test-query: select 1
maximum-pool-size: 5
#数据库映射配置,与日志输出配置
mybatis-plus:
global-config:
db-config:
logic-delete-field: delete_state #全局逻辑删除字段值
logic-delete-value: 1 # 逻辑已删除值(默认为 1)
logic-not-delete-value: 0 # 逻辑未删除值(默认为 0)
mapper-locations:
- classpath*:mapper/*.xml
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
3.代码生成
- 添加需要依赖
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.3.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>dom4j</groupId>
<artifactId>dom4j</artifactId>
<version>1.6.1</version>
</dependency>
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
</dependency>
- 核心代码
package com.anjile.test;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.core.toolkit.StringPool;
import com.baomidou.mybatisplus.core.toolkit.StringUtils;
import com.baomidou.mybatisplus.generator.AutoGenerator;
import com.baomidou.mybatisplus.generator.InjectionConfig;
import com.baomidou.mybatisplus.generator.config.*;
import com.baomidou.mybatisplus.generator.config.builder.ConfigBuilder;
import com.baomidou.mybatisplus.generator.config.rules.DateType;
import com.baomidou.mybatisplus.generator.config.rules.FileType;
import com.baomidou.mybatisplus.generator.config.rules.NamingStrategy;
import com.baomidou.mybatisplus.generator.engine.FreemarkerTemplateEngine;
import freemarker.template.Configuration;
import freemarker.template.Template;
import org.dom4j.Document;
import org.dom4j.Element;
import org.dom4j.io.SAXReader;
import org.springframework.util.FileCopyUtils;
import org.xml.sax.EntityResolver;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import java.io.*;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
public class MysqlGenerator {
//自动生成作者
private static String author = "zx";
//项目父级包名
private static String parent = "com.anjile";
//实体类包名
private static String entity = "pojo";
//mapper接口包名
private static String mapper = "dao";
//数据库配置
private static String dbDriverName = "com.mysql.cj.jdbc.Driver";
private static String dbUrl = "jdbc:mysql://192.168.0.123:8023/ajl_project_manager?useSSL=false&characterEncoding=utf8&serverTimezone=GMT%2B8";
private static String dbUsername = "root";
private static String dbPassword = "123456";
//生成的xml保存到目录
private static String xmlPath = "/src/main/resources";
private static String xmlPackage = parent+".mapper";
//是否生成service接口和service实现
private static boolean isCreateService = true;
//是否生成controller类
private static boolean isCreateController = false;
//需要生成的数据库表(多个以逗号分隔)
private static String tables = "project_safeguarding_rights_info";
public static void main(String[] args) {
// 代码生成器
AutoGenerator generator = new AutoGenerator();
//设置模板为(Freemarker)
generator.setTemplateEngine(new MysqlGenerator().new CustomFreemarkerTemplateEngine());
// 全局配置
GlobalConfig gc = new GlobalConfig();
String projectPath = System.getProperty("openuserdao.dir");
gc.setOutputDir(projectPath + "/src/main/java");
gc.setAuthor(author);
gc.setIdType(IdType.ASSIGN_ID); //设置ID类型
gc.setDateType(DateType.ONLY_DATE); //设置时间用 java.util.date
gc.setOpen(false);
gc.setServiceName("%sService"); //配置service接口名字 %s 代替实体类名
generator.setGlobalConfig(gc);
// 数据源配置
DataSourceConfig dsc = new DataSourceConfig();
dsc.setDriverName(dbDriverName);
dsc.setUrl(dbUrl);
dsc.setUsername(dbUsername);
dsc.setPassword(dbPassword);
generator.setDataSource(dsc);
// 配置模板
TemplateConfig template = new TemplateConfig();
generator.setTemplate(template);
// 包配置
PackageConfig pc = new PackageConfig();
pc.setParent(parent);
pc.setEntity(entity);
pc.setMapper(mapper);
//自定义路径信息
Map<String, String> configPathInfo = new HashMap<String, String>();
setPathInfo(configPathInfo, template.getEntity(gc.isKotlin()), gc.getOutputDir(), ConstVal.ENTITY_PATH, joinPackage(pc.getParent(), pc.getEntity()));
setPathInfo(configPathInfo, template.getMapper(), gc.getOutputDir(), ConstVal.MAPPER_PATH, joinPackage(pc.getParent(), pc.getMapper()));
setPathInfo(configPathInfo, template.getXml(), projectPath + xmlPath , ConstVal.XML_PATH, xmlPackage);
if(isCreateService) {
setPathInfo(configPathInfo, template.getService(), gc.getOutputDir(), ConstVal.SERVICE_PATH, joinPackage(pc.getParent(), pc.getService()));
setPathInfo(configPathInfo, template.getServiceImpl(), gc.getOutputDir(), ConstVal.SERVICE_IMPL_PATH, joinPackage(pc.getParent(), pc.getServiceImpl()));
}
if(isCreateController) {
setPathInfo(configPathInfo, template.getController(), gc.getOutputDir(), ConstVal.CONTROLLER_PATH, joinPackage(pc.getParent(), pc.getController()));
}
pc.setPathInfo(configPathInfo);
generator.setPackageInfo(pc);
// 自定义配置
InjectionConfig cfg = new InjectionConfig() {
@Override
public void initMap() {
// to do nothing
}
};
//自定义规则是否创建覆盖文件
cfg.setFileCreate(new IFileCreate() {
@Override
public boolean isCreate(ConfigBuilder configBuilder, FileType fileType, String filePath) {
if(fileType == FileType.ENTITY) { //判断mapper接口类是否可以覆盖
return true;
}else {
File file = new File(filePath);
return !file.exists();
}
}
});
generator.setCfg(cfg);
// 策略配置
StrategyConfig strategy = new StrategyConfig();
strategy.setNaming(NamingStrategy.underline_to_camel);
strategy.setColumnNaming(NamingStrategy.underline_to_camel);
strategy.setEntityLombokModel(true);
strategy.setRestControllerStyle(true);
// 写于父类中的公共字段
strategy.setInclude(tables.replaceAll(" ", "").split(","));
strategy.setControllerMappingHyphenStyle(true);
generator.setStrategy(strategy);
generator.execute();
}
private static void setPathInfo(Map<String, String> pathInfo, String template, String outputDir, String path, String packageName) {
if (StringUtils.isNotEmpty(template)) {
pathInfo.put(path, joinPath(outputDir, packageName));
}
}
/**
* 连接路径字符串
*
* @param parentDir 路径常量字符串
* @param packageName 包名
* @return 连接后的路径
*/
private static String joinPath(String parentDir, String packageName) {
if (StringUtils.isEmpty(parentDir)) {
parentDir = System.getProperty(ConstVal.JAVA_TMPDIR);
}
if (!StringUtils.endsWith(parentDir, File.separator)) {
parentDir += File.separator;
}
packageName = packageName.replaceAll("\\.", StringPool.BACK_SLASH + File.separator);
return parentDir + packageName;
}
/**
* 连接父子包名
*
* @param parent 父包名
* @param subPackage 子包名
* @return 连接后的包名
*/
private static String joinPackage(String parent, String subPackage) {
if (StringUtils.isEmpty(parent)) {
return subPackage;
}
return parent + StringPool.DOT + subPackage;
}
/**
* 自定义Freemarker模板(会重新xml单不会全覆盖)
* @author 彭柳
*/
public class CustomFreemarkerTemplateEngine extends FreemarkerTemplateEngine{
private Configuration configuration;
@Override
public CustomFreemarkerTemplateEngine init(ConfigBuilder configBuilder) {
super.init(configBuilder);
configuration = new Configuration(Configuration.DEFAULT_INCOMPATIBLE_IMPROVEMENTS);
configuration.setDefaultEncoding(ConstVal.UTF8);
configuration.setClassForTemplateLoading(FreemarkerTemplateEngine.class, StringPool.SLASH);
return this;
}
@Override
public void writer(Map<String, Object> objectMap, String templatePath, String outputFile) throws Exception {
File file = new File(outputFile);
if(templatePath.contains("mapper.xml") && file.exists()) {
Template template = configuration.getTemplate(templatePath);
StringWriter stringWriter = new StringWriter();
template.process(objectMap, stringWriter);
String sw = stringWriter.toString();
Map<String,Element> addElements = new HashMap<String,Element>();
SAXReader saxReader = new SAXReader(false);
saxReader.setEntityResolver(new EntityResolver() {
@Override
public InputSource resolveEntity(String publicId, String systemId) throws SAXException, IOException {
return new InputSource(new ByteArrayInputStream("<?xml version='1.0' encoding='UTF-8'?>".getBytes()));
}
});
Document document = saxReader.read(file);
if (document != null) {
Element mapperElement = document.getRootElement();
List<Element> elements = mapperElement.elements();
for (Element element : elements) {
if(
!element.attributeValue("id").equals("BaseResultMap") &&
!element.attributeValue("id").equals("Base_Column_List")
) {
addElements.put(element.attributeValue("id"), element);
continue;
}
}
}
if(null == document) {
return;
}
document = saxReader.read(new InputSource(new StringReader(sw)));
if (document != null) {
Element mapperElement = document.getRootElement();
for (Entry<String, Element> addElement : addElements.entrySet()) {
Element add = (Element) addElement.getValue().clone();
mapperElement.add(add);
}
}
sw = document.asXML();
sw = sw.replaceAll("<mapper", "\r\n<mapper");
sw = sw.replaceAll("</mapper>", "\r\n</mapper>");
sw = sw.replaceAll("<select", " <select");
sw = sw.replaceAll("<delete", " <delete");
sw = sw.replaceAll("<update", " <update");
sw = sw.replaceAll("<insert", " <insert");
FileCopyUtils.copy(sw.getBytes(), file);
}else {
Template template = configuration.getTemplate(templatePath);
try (FileOutputStream fileOutputStream = new FileOutputStream(outputFile)) {
template.process(objectMap, new OutputStreamWriter(fileOutputStream, ConstVal.UTF8));
}
}
logger.debug("模板:" + templatePath + "; 文件:" + outputFile);
}
}
}
Mybatis-Plus可以干什么?
- MyBatis-Plus 在容器启动的时候注入sql .无侵入、损耗小、强大的CRUD操作
- 支持Lambda形式调用、支持多种多种数据库
- 支持主键自动生成
- 支持自定义全局操作、支持关键词自动转义
- 内置代码生成器、内置分页插件、内置性能分析插件
- 内置全局拦截插件、内置sql注入剥离器
Gitee地址:http://gitee.com/zhongxu/templateproject
二、基本使用
1.Mapper中的保存
@Autowired
private UserMapper userMapper;
/**
* 保存用户
*/
@Test
public void insertUser() {
User user = new User();
user.setName("kevin");
user.setPhone("18883598153");
user.setEmail("2763275463@163.com");
user.setAboutme("我是一名java开发人员");
user.setPasswd("123456");
user.setAvatar("http://localhost:8768/a.png");
user.setCreateTime(new Date());
user.setEnable(false);
user.setAgencyId(5);
user.setType(false);
int resultRow = userMapper.insert(user);
System.out.println("影响的行数:"+resultRow);
}
注意:
如果数据库字段 使用
tinyint
类型 ,比如:enable tinyint(1) NOT NULL COMMENT '是否启用,1启用,0停用
,代码生成器中生成的实体属性private Boolean enable;
需要注意如果设置为true
,插入数据库的值是1
,如果设置的是false
,插入数据库的值是0
,所以在约定的时候,使用1
/0
.有跟多的表示就不建议采用tinyint
类型作为数据库字段类型
2.常用注解
- TableName 表名注解,指定数据库表名, @TableName(“tb_user”) | com.example.mpdemo.pojo.User
- @TableId 主键注解 [忽略某个字段,需要加入属性exist = false ,就可以忽略了。]
- @TableFiled 字段注解(非主键)
- 自动转换成驼峰。比如:属性名:private String userName;数据库字段: user_name varchar(32)
三、Mybatis-Plus查询
1.普通查询
@SpringBootTest
public class MpGenerialQuery {
@Autowired
private UserMapper userMapper;
/**
* 根据ID查询
*/
@Test
public void selectUserById(){
User user = userMapper.selectById(15);
System.out.println(user);
}
/**
* 根据多个id查询
*/
@Test
public void selectUserByIds(){
List<Integer> ids = Arrays.asList(12, 10, 9, 8, 7);
List<User> users = userMapper.selectBatchIds(ids);
users.forEach(System.out::println);
}
/**
* 根据Map查询
*/
@Test
public void selectUserByMap(){
Map<String,Object> map = new HashMap<>();
map.put("name","tom");
map.put("create_time","2021-01-01");
userMapper.selectByMap(map).forEach(System.out::println);
}
}
2.条件构造器查询
2.1 QueryWrapper条件构造器
AbstractWrapper
条件构造器
/**
* 根据用户名查询
* 简单querywrapper查询,如果是复杂的查询建议还是在mapper.xml中写sql语句
*/
@Test
public void selectUserByName(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.eq("name","kevin");
//SELECT id,name,phone,email,aboutme,passwd,avatar,type,create_time,enable,agency_id FROM user WHERE (name = ?) limit 1
queryWrapper.last("limit 1");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
eq
等于like
模糊查询 。比如:queryWrapper.like(“name”,“tom”)likeRight
相当于 name like “h%”lt
小于 。两个条件同时满足,即and
.就可以这样写: queryWrapper.like(“name”,“t”).lt(“age”,”22”); 直接点就可以了相当于and
or
orge
相当于>=
between
相当于between….andisNotNull
is not nullorderByDesc
order by ……descorderByAsc
相当于 order by …ascqueryWrapper.likeRight(“name”,“王”).or().orderByDesc(“age”).orderByAsc(“id”);
data_format(create_time,'%y-%m-%d') and manager_id in(select id from user where name like '王%')
====>
queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}","2020-01-01").inSql("manager_id","select id from user where name like '王%'")
name like '王%' and (age < 40 or email is not null)
====>
//函数式接口
queryWrapper.like("name","王").and(m->m.lt("age",25).or().isNotNull("emaill"))
name like '王%' or (age<40 and age >20 and email is not null)
====>
queryWrapper.likeRight("name","王").or(m->m.lt("age",40).gt("age","20").isNotNull("emaill"))
(age<40 or email is not null) and name like '王%'
====>
queryWrapper.nested(w->m.lt("age","40").or().isNotNull("emaill")).likeRight("name","王")
age in (12,21,22,32)
===>
queryWrapper.in("age",Arrays.asList(12,21,22,32));
2.2 select 不列出全部字段
- 通过select 可变参数指定要查询的字段名
/**
* 查询出自己想要的字段
* 通过制定要查询的字段名
*/
@Test
public void selectCloumn(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id","name","phone").eq("name","tom");
User user = userMapper.selectOne(queryWrapper);
System.out.println(user);
}
DEBUG==> Preparing: SELECT id,name,phone FROM user WHERE (name = ?)
DEBUG==> Parameters: tom(String)
TRACE<== Columns: id, name, phone
TRACE<== Row: 32, tom, 18883598153
DEBUG<== Total: 1
User(id=32, name=tom, phone=18883598153, email=null, aboutme=null, passwd=null, avatar=null, type=null, createTime=null, enable=null, agencyId=null)
- predicate函数式方式
/**
* 查询出自己想要的字段
* 通过predicate函数式方式
*/
@Test
public void selectCloumnByPredicate(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
//除了phone字段其他字段都查询出来
queryWrapper.eq("name","tom").select(User.class,user->!user.getColumn().equals("phone"));
User user = userMapper.selectOne(queryWrapper);
System.out.println(user);
}
2.3 condition的作用
condition
条件 如果设置为true
,会在sql
条件中加入条件
//比如 eq("","")
/**
* 等于 =
*
* @param condition 执行条件 如果为true ,才把条件加入到sql的条件where中
* @param column 字段
* @param val 值
* @return children
*/
Children eq(boolean condition, R column, Object val);
default Children eq(R column, Object val) {
return eq(true, column, val);
}
/**
* 根据条件 是否加入到where语句中
*/
@Test
public void selectByCondition(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
String name = "tom";
// String name = "";
queryWrapper.eq(StringUtils.isNotBlank(name),"name",name);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
2.4 实体作为条件构造器方法参数法
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
这个是无参构造器。也可以使用有参构造器,参数是实体类。它会把实体非空字段作为查询条件
@Test
public void selectByCondition2(){
User user = new User();
user.setName("tom");
// SELECT id,name,phone,email,aboutme,passwd,avatar,type,create_time,enable,agency_id FROM user WHERE name=?
QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
注意:
如果使用了有参构造方式进行查询,就不要使用
queryWrapper.eq("name","tome")
,如果使用了有参构造同时也是用查询条件这种方式,他会在where子句中追加条件。
SQL 比较条件常量定义类。当我们想达到name like ‘王%’
同时使用QueryWrapper的有参构造条件查询。那么需要结合@TableFiled
注解condition
属性
@TableFiled(condition=SqlCondition.LIKE)
private String name;
同时可以自定义比较条件常量
@TableFiled(condition="%s%lt;#{%s}")
private Integer age; //age < xxx
public class SqlCondition {
public static final String EQUAL = "%s=#{%s}";
public static final String NOT_EQUAL = "%s<>#{%s}";
public static final String LIKE = "%s LIKE CONCAT('%%',#{%s},'%%')";
public static final String LIKE_LEFT = "%s LIKE CONCAT('%%',#{%s})";
public static final String LIKE_RIGHT = "%s LIKE CONCAT(#{%s},'%%')";
public SqlCondition() {
}
}
练习代码:
**
* <p>
* 用户实体类
* </p>
*
* @author zx
* @since 2021-01-01
*/
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
public class User implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键
*/
@TableId(value = "id", type = IdType.AUTO)
private Long id;
/**
* 姓名
*/
private String name;
/**
* 手机号
*/
private String phone;
/**
* 电子邮件
*/
private String email;
/**
* 自我介绍
*/
private String aboutme;
/**
* 经过MD5加密的密码
*/
private String passwd;
/**
* 头像图片
*/
private String avatar;
/**
* 1:普通用户,2:房产经纪人 true = 1 false = 0
*/
private Boolean type;
/**
* 创建时间
*/
@TableField(condition = "%s<#{%s}")
private Date createTime;
/**
* 是否启用,1启用,0停用
*/
private Boolean enable;
/**
* 所属经纪机构
*/
private Integer agencyId;
}
/**
* SQL 比较条件常量定义类
*/
@Test
public void selectByConditonSQL(){
User user = new User();
user.setCreateTime(new Date());
QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
2.5 AllEq用法
里面传递的参数是一个Map<String,Object> ,key 是数据库字段名,value就是值
。如果value为空。 MP 会把空字段的值变成is null
@Test
public void selectByConditionAlleq(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> map = new HashMap();
map.put("name","tom");
map.put("phone","18883598153");
queryWrapper.allEq(map);
// SELECT id,name,phone,email,aboutme,passwd,avatar,type,create_time,enable,agency_id FROM user WHERE (phone = ? AND name = ?)
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectByConditionAlleq(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> map = new HashMap();
map.put("name","tom");
map.put("phone",null);
queryWrapper.allEq(map);
//SELECT id,name,phone,email,aboutme,passwd,avatar,type,create_time,enable,agency_id FROM user WHERE (phone IS NULL AND name = ?)
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
allEq
还有一个重载方法,allEq(map,boolean null2IsNull)
,设置为false
,如果字段对应的值位空,就会忽略掉
@Test
public void selectByConditionAlleq(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> map = new HashMap();
map.put("name","tom");
map.put("phone",null);
queryWrapper.allEq(map,false);
//SELECT id,name,phone,email,aboutme,passwd,avatar,type,create_time,enable,agency_id FROM user WHERE (name = ?)
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
allEq(BiPredicate<R, V> filter, Map<R, V> params)
//fileter 过滤函数,是否允许字段传入比对条件中
allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull)
@Test
public void selectByConditionAlleq2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Map<String,Object> map = new HashMap();
map.put("name","tom");
map.put("phone",null);
//是否允许字段传入比对条件中
// true 加入 ; false 不加入
// name 相等的不加入
//SELECT id,name,phone,email,aboutme,passwd,avatar,type,create_time,enable,agency_id FROM user WHERE (phone IS NULL)
queryWrapper.allEq((k,v)->!k.equals("name"),map,true);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
2.6 lambda条件构造器查询
lambda 条件构造器
@Test public void selectLambda(){ LambdaQueryWrapper<User> lambda = new QueryWrapper<User>().lambda(); LambdaQueryWrapper<User> userLambdaQueryWrapper = new LambdaQueryWrapper<>(); LambdaQueryWrapper<User> userLambdaQueryWrapper1 = Wrappers.<User>lambdaQuery(); lambda.like(User::getName,"tom"); List<User> users = userMapper.selectList(lambda); users.forEach(System.out::println); }
@Test public void selectLambda2(){ List<User> users = new LambdaQueryChainWrapper<User>(userMapper).like(User::getName, "h").lt(User::getCreateTime, "2020-01-01").list(); users.forEach(System.out::println); }
四、分页查询
1.IPage | Page 分页查询
物理分页** | 配置分页插件
//Spring boot方式
@Configuration
@MapperScan("com.baomidou.cloud.service.*.mapper*")
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor() {
PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
// 设置请求的页面大于最大页后操作, true调回到首页,false 继续请求 默认false
// paginationInterceptor.setOverflow(false);
// 设置最大单页限制数量,默认 500 条,-1 不受限制
// paginationInterceptor.setLimit(500);
// 开启 count 的 join 优化,只针对部分 left join
paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
return paginationInterceptor;
}
}
IPage
| Page
----> public class Page<T> implements IPage<T>
@Test
public void selectPage() {
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("name", "h");
Page<User> page = new Page<>(1, 2);
//Page<User> page1 = userMapper.selectPage(page, queryWrapper,false); 表示不查询总记录
Page<User> page1 = userMapper.selectPage(page, queryWrapper);
System.out.println(page1.getTotal());
System.out.println(page1.getRecords());
}
2.自定义分页查询
- UserMapper内容
public interface UserMapper {//可以继承或者不继承BaseMapper
/**
* <p>
* 查询 : 根据state状态查询用户列表,分页显示
* </p>
*
* @param page 分页对象,xml中可以从里面进行取值,传递参数 Page 即自动分页,必须放在第一位(你可以继承Page实现自己的分页对象)
* @param state 状态
* @return 分页对象
*/
IPage<User> selectPageVo(Page<?> page, Integer state);
}
- UserMapper.xml内容
select id="selectPageVo" resultType="com.baomidou.cloud.entity.UserVo">
SELECT id,name FROM user WHERE state=#{state}
</select>
- UserService内容
public IPage<User> selectUserPage(Page<User> page, Integer state) {
// 不进行 count sql 优化,解决 MP 无法自动优化 SQL 问题,这时候你需要自己查询 count 部分
// page.setOptimizeCountSql(false);
// 当 total 为小于 0 或者设置 setSearchCount(false) 分页插件不会进行 count 查询
// 要点!! 分页返回的对象与传入的对象是同一个
return userMapper.selectPageVo(page, state);
}
五、更新Update
1.根据ID修改
2.UpdateWrapper 修改构造器
@Test
public void updateTest(){
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
userUpdateWrapper.eq("name","tom");
//默认情况下实体变量不为null 会出现在set中.这个都可以在配置文件中进行配置
User user = new User();
user.setName("tom_");
user.setCreateTime(null);
userMapper.update(user,userUpdateWrapper);
}
DEBUG==> Preparing: UPDATE user SET name=? WHERE (name = ?)
DEBUG==> Parameters: tom_(String), tom(String)
DEBUG<== Updates: 1
参数构造器-----参考 查询构造器
更新 先查询然后在更新
3.使用set方式进行更新
@Test
public void updateSetTest(){
UpdateWrapper<User> userUpdateWrapper = new UpdateWrapper<>();
//使用set方式进行更新,适合更新字段比较少的场景
userUpdateWrapper.eq("name","tom_").set("name","i love you");
userMapper.update(null,userUpdateWrapper);
}
DEBUG==> Preparing: UPDATE user SET name=? WHERE (name = ?)
DEBUG==> Parameters: i love you(String), tom_(String)
DEBUG<== Updates: 1
六、删除Delete
1.根据ID删除
2.deletByMap
3.根据ID集合进行批量删除
4.根据条件构造器删除
比如Lambda表达式进行删除
@Test
public void delete(){
LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery().eq(User::getName, "i love you");
int delete = userMapper.delete(queryWrapper);
}
DEBUG==> Preparing: DELETE FROM user WHERE (name = ?)
DEBUG==> Parameters: i love you(String)
DEBUG<== Updates: 1
八、主键策略
@TableId(value = "ID_STR", type = IdType.ASSIGN_ID)
private String idStr;
需要配合 注解@TableId
中属性type,使用了枚举IdType
,选择自己需要的生成策略, 默认使用雪花算法
九、基本配置
官网地址:https://baomidou.com/config/generator-config.html
globl-config:
db-config:
id-type: uui
field-strategy: ignored # 修改 | 插入的时候如果字段为null ,也会插入到数据库中 这个是全局配置 not-null | not-empty
局部策略 配置某个实体字段 是否为空插入数据库 。需要使用注解@TableFiled
中相关属性
十、通用service
IService<T>
| ServiceImpl<M extends BaseMapper<T>,T>