MybatisPlus基础用法(采用SpringBoot项目进行整合)

1.创建一个SpringBoot项目

2.导入相关依赖


	4.0.0
	com.qbb
	mybatis-plus
	0.0.1-SNAPSHOT
	
		org.springframework.boot
		spring-boot-starter-parent
		2.5.6
		 
	

	
		
			org.springframework.boot
			spring-boot-starter-web
		

		
			org.springframework.boot
			spring-boot-devtools
			runtime
			true
		
		
			org.springframework.boot
			spring-boot-configuration-processor
			true
		
		
			org.projectlombok
			lombok
			true
		

		
			junit
			junit
			4.12
			test
		
		
			org.springframework.boot
			spring-boot-starter-test
			test
		
		
		
			com.baomidou
			mybatis-plus-boot-starter
			3.4.2
		
		
		
			mysql
			mysql-connector-java
			8.0.26
		
	

	
		
			
				org.springframework.boot
				spring-boot-maven-plugin
				
					
						
							org.projectlombok
							lombok

3.修改配置文件

spring:
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatisplus?serverTimezone=GMT%2B8&nullNamePatternMatchesAll=true
    username: root
    password: root
 
# mybatis-plus的配置
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    call-setters-on-nulls: true
    default-enum-type-handler: org.apache.ibatis.type.EnumOrdinalTypeHandler
  mapper-locations: classpath*:/mapper/*.xml
  type-aliases-package: com.qbb.mybatisplus.domain

4.创建数据库表

/*
 Navicat Premium Data Transfer

 Source Server         : Qbbit
 Source Server Type    : MySQL
 Source Server Version : 50540
 Source Host           : localhost:3306
 Source Schema         : mybatisplus

 Target Server Type    : MySQL
 Target Server Version : 50540
 File Encoding         : 65001

 Date: 24/02/2022 17:04:18
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名',
  `age` int(11) NULL DEFAULT NULL COMMENT '年龄',
  `email` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '邮箱',
  `manager_id` bigint(20) NULL DEFAULT NULL COMMENT '直属上级id',
  `create_time` datetime NULL DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime NULL DEFAULT NULL COMMENT '修改时间',
  `version` int(11) NULL DEFAULT 1 COMMENT '版本',
  `is_delete` int(1) NULL DEFAULT 0 COMMENT '逻辑删除(0:未删除 1:删除)',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `manager_fk`(`manager_id`) USING BTREE,
  CONSTRAINT `manager_fk` FOREIGN KEY (`manager_id`) REFERENCES `user` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB AUTO_INCREMENT = 1496769090553995267 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

SET FOREIGN_KEY_CHECKS = 1;

5.创建实体类

package com.qbb.mybatisplus.domain;

import java.time.LocalDateTime;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;

@Data
@AllArgsConstructor
@NoArgsConstructor
@Getter
@Setter
@SuppressWarnings("all")
public class User {
	// 主键
	private Long id;
	// 姓名
	private String name;
	// 年龄
	private Integer age;
	// 邮箱
	private String email;
	// 上级领导
	private Long managerId;
	// 创建时间
	private LocalDateTime createTime;
}

6.创建Mapper接口继承BaseMapper

package com.qbb.mybatisplus.mapper;

import org.apache.ibatis.annotations.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.qbb.mybatisplus.domain.User;

@Mapper
public interface UserMapper extends BaseMapper {

}

7.开始测试

普通查询

selectList:查询所有

@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest {

	@Autowired
	private UserMapper userMapper;

	@Test
	public void selectTest() {
		List userList = userMapper.selectList(null);
		System.out.println(userList);
	}
}

selectById:根据ID查询

@Test
	public void selectByIdTest() {
		User user = userMapper.selectById(1496774916538716162L);
		System.out.println(user);
	}

selectByMap:根据Map中的键值对查询,key:对应数据库字段 value:对应数据库中的字段值

@Test
	public void selectByMapTest() {
		Map map = new HashMap();
		map.put("name", "王天风");
		map.put("age", 25);
		List list = userMapper.selectByMap(map);
		System.out.println(list);
	}

QueryWrapper:强大的条件构造器

创件QueryWrapper条件构造器的两种方式
QueryWrapper wrapper = new QueryWrapper();
QueryWrapper query = Wrappers.query();

like , lt

@Test
public void selectByWrapperTest() {
	QueryWrapper wrapper = new QueryWrapper();
	//		QueryWrapper query = Wrappers.query();
	wrapper.like("name", "雨").lt("age", 40);
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

like , between , is not null

@Test
public void selectByWrapperTest2() {
	QueryWrapper wrapper = new QueryWrapper();
	wrapper.like("name", "雨")
		.between("age", 20, 40)
		.isNotNull("email");
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

likeRight , or , ge , orderByDesc , orderByAsc

@Test
public void selectByWrapperTest3() {
	QueryWrapper wrapper = new QueryWrapper();
	wrapper.likeRight("name", "王")
		.or()
		.ge("age", 40)
		.orderByDesc("age")
		.orderByAsc("id");
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

apply:拼接SQL,insql:子查询

@Test
public void selectByWrapperTest4() {
	QueryWrapper wrapper = new QueryWrapper();
	wrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2021-12-19")
		.inSql("manager_id", "select id from user where name like '王%'");
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

例子:
// 拼接：id = 100
apply("id = 100");
 
// 拼接：date_format(create_time,'%Y-%m-%d') = '2018-08-08'")
apply("date_format(create_time,'%Y-%m-%d') = '2018-08-08'") 
会存在sql注入的风险 apply("date_format(create_time,'%Y-%m-%d') = '2018-08-08' or true or true")
 
// 拼接：date_format(create_time,'%Y-%m-%d') = '2018-08-08'")
apply("date_format(create_time,'%Y-%m-%d') = {0}", "2018-08-08")

sql注入风险

推荐用法

likeRight,and(函数式接口),lt,or,is not null

@Test
public void selectByWrapperTest5() {
	QueryWrapper wrapper = Wrappers.query();
	wrapper.likeRight("name", "王")
		.and(q -> q.lt("age", 40).or().isNotNull("email"));
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

likeRight,or(函数式接口),between,is not null

@Test
public void selectByWrapperTest6() {
	QueryWrapper wrapper = Wrappers.query();
	wrapper.likeRight("name", "王")
		.or(q -> q.between("age", 20, 40).isNotNull("email"));
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

nested:不以and开头的查询,or,is not null,likeRight;(and逻辑运算符优先级高于or)

@Test
public void selectByWrapperTest7() {
	QueryWrapper wrapper = Wrappers.query();
	wrapper.nested(q -> q.lt("age", 40).or().isNotNull("email")).likeRight("name", "王");
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

in:与sql中的in用法一致

@Test
public void selectByWrapperTest8() {
	QueryWrapper wrapper = Wrappers.query();
	wrapper.in("age", Arrays.asList(30, 31, 34, 35));
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

lsat:无视sql优化器,强制凭借sql语句,有sql注入风险;谨慎使用

@Test
public void selectByWrapperTest9() {
	QueryWrapper wrapper = Wrappers.query();
	wrapper.last("limit 2");
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

select:查询指定的列

@Test
public void selectByWrapperTest10() {
	QueryWrapper wrapper = Wrappers.query();
	wrapper.like("name", "雨").lt("age", 40).select("id","name");
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
}

condition:条件判断,当条件为false时不拼接条件

@Test
public void selectByWrapperTest11() {
	String name = "";
	String email = "";
	conditionTest(name, email);
}

@SuppressWarnings("deprecation")
private List conditionTest(String name, String email) {
	QueryWrapper wrapper = Wrappers.query();
	wrapper.like(!StringUtils.isEmpty(name), "name", name).like(!StringUtils.isEmpty(email), "email", email);
	List list = userMapper.selectList(wrapper);
	return list;
}

QueryWrapper wrapper = Wrappers.query(Entity:实体对象);

当我们创建对象的时候传入对应实体对象,那么它的对应属性会转换成以and拼接的查询条件

@Test
	public void selectByWrapperTest12() {
	User user = new User();
	user.setName("向西");
	user.setAge(22);
	QueryWrapper wrapper = Wrappers.query(user);
	List list = userMapper.selectList(wrapper);
	System.out.println(list);
	}

当我们给构造器传递实体,并且设置wrapper查询条件,有什么影响么?答案是:两个都起作用

@Test
	public void selectByWrapperTest12() {
		User user = new User();
		user.setName("向西");
		user.setAge(22);
		QueryWrapper wrapper = Wrappers.query(user);
		wrapper.like(!StringUtils.isEmpty(user.getName()), "name", user.getName()).like(!StringUtils.isEmpty(user.getAge()), "age", user.getAge());
		List list = userMapper.selectList(wrapper);
		System.out.println(list);
	}

从上面的sql语句我们发现,实体类中有值的属性是以"="进行拼接的,那有点死板,所以我们能想到的,MP的作者也能想到,他使用了注解解决此问题.如下:

@TableField(condition=SqlCondition.LIKE)
	private String name;

@Test
	public void selectByWrapperTest12() {
		User user = new User();
		user.setName("向西");
		user.setAge(22);
		QueryWrapper wrapper = Wrappers.query(user);
//		wrapper.like(!StringUtils.isEmpty(user.getName()), "name", user.getName()).like(!StringUtils.isEmpty(user.getAge()), "age", user.getAge());
		List list = userMapper.selectList(wrapper);
		System.out.println(list);
	}


MP作者提供的@TableField(condition=SqlCondition.LIKE)只有5种取值,但是他也提供了我们自定义的方式,写表达式就好了

// 年龄
	@TableField(condition="%s>#{%s}")
	private Integer age;
	
@Test
	public void selectByWrapperTest13() {
		User user = new User();
		user.setName("向西");
		user.setAge(22);
		QueryWrapper wrapper = Wrappers.query(user);
		List list = userMapper.selectList(wrapper);
		System.out.println(list);
	}

allEq:

allEq(Map params)
allEq(Map params, boolean null2IsNull)
allEq(boolean condition, Map params, boolean null2IsNull)
官方解释:
params : key为数据库字段名,value为字段值
null2IsNull : 为true则在map的value为null时调用 isNull 方法,为false时则忽略value为null的
例1: allEq({id:1,name:"老王",age:null})--->id = 1 and name = '老王' and age is null
例2: allEq({id:1,name:"老王",age:null}, false)--->id = 1 and name = '老王'
测试:
@Test
	public void selectByWrapperTest14() {
		QueryWrapper wrapper = Wrappers.query();
		Map map = new HashMap();
		map.put("name", "向东");
		map.put("age", 25);
		wrapper.allEq(map);
		List list = userMapper.selectList(wrapper);
		System.out.println(list);
	}

当参数为空的时候,默认是以"参数 is null",如果想排除直接在wrapper.allEq(map,false);

selectMaps:返回List> 适用于特殊场景

@Test
	public void selectByWrapperTest15() {
		QueryWrapper wrapper = Wrappers.query();
		wrapper.like("name", "雨").lt("age", 40);
		List> list = userMapper.selectMaps(wrapper);
		System.out.println(list);
	}


@Test
	public void selectByWrapperTest16() {
		QueryWrapper wrapper = Wrappers.query();
		wrapper.select("avg(age) avg_age","min(age) min_age","max(age) max_age")
				.groupBy("manager_id").having("sum(age)<{0}", 500);
		List> list = userMapper.selectMaps(wrapper);
		System.out.println(list);
	}

selectCount:按条件查询记录数

@Test
	public void selectByWrapperTest18() {
		QueryWrapper wrapper = Wrappers.query();
		wrapper.like("name", "雨").lt("age", 40);
		Integer count = userMapper.selectCount(wrapper);
		System.out.println(count);
	}

selectOne:查询一条结果集,超过一条记录将报错,但是结果集会查询出来

@Test
	public void selectByWrapperTest19() {
		QueryWrapper wrapper = Wrappers.query();
		wrapper.like("name", "雨").lt("age", 40);
		User one = userMapper.selectOne(wrapper);
		System.out.println(one);
	}

Lambda条件构造器:可以防止我们误写,直接通过实体映射到数据库的字段

创建方式:

LambdaQueryWrapper wrapper = new QueryWrapper().lambda();
LambdaQueryWrapper wrapper2 = new LambdaQueryWrapper();
LambdaQueryWrapper wrapper3 = Wrappers.lambdaQuery();

@Test
	public void selectByWrapperTest20() {
//		LambdaQueryWrapper wrapper3 = new QueryWrapper().lambda();
//		LambdaQueryWrapper wrapper2 = new LambdaQueryWrapper();
		LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
		
		wrapper.like(User::getName, "雨").lt(User::getAge, 40);
		
		List list = userMapper.selectList(wrapper);
		list.forEach(System.out::println);
		
	}

selectObjs:返回结果集的第一列

@Test
	public void selectByWrapperTest17() {
		QueryWrapper wrapper = Wrappers.query();
		wrapper.like("name", "雨").lt("age", 40).select("name","age");
		List list = userMapper.selectObjs(wrapper);
		System.out.println(list);
	}


@Test
	public void selectByWrapperTest21() {
		LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
		
		wrapper.likeRight(User::getName, "王").and(q -> q.lt(User::getAge, 40).or().isNotNull(User::getEmail));
		
		List list = userMapper.selectList(wrapper);
		list.forEach(System.out::println);
	}





new LambdaQueryChainWrapper(userMapper):条件构造器链,内置了一些方法


@Test
	public void selectByWrapperTest22() {
		List list = new LambdaQueryChainWrapper(userMapper).like(User::getName, "雨").gt(User::getAge, 20).list();
		
		list.forEach(System.out::println);
	}





insertUser:添加


@Test
	public void insertTest() {
		User user = new User();
		user.setName("张三");
		user.setAge(23);
		user.setEmail("zs@qq.com");
		user.setManagerId(1088248166370832385L);
		user.setCreateTime(LocalDateTime.now());
		int rows = userMapper.insert(user);
		System.out.println(rows);
	}





update:修改方法


updateById:根据ID修改,属性为空则不会出现在sql语句中,不会修改


	@Test
	public void selectByWrapperTest27() {
		User user = new User();
		user.setAge(18);
		user.setId(1496774916538716162L);
		int rows = userMapper.updateById(user);
		System.out.println(rows);
	}	





update:传入的实体不为null的字段会出现在set中,而updateWrapper里的条件则会出现在where后


@Test
	public void selectByWrapperTest28() {
		UpdateWrapper wrapper = new UpdateWrapper();
		wrapper.eq("name", "向西").eq("age", 18);
		User user = new User();
		user.setEmail("xiangxi@qq.com");
		user.setAge(19);
		int rows = userMapper.update(user, wrapper);
		System.out.println(rows);
	}	
	





如果构造器中传入了实体对象,则实体对象不为null的属性也会出现在where条件中,和前面的一样


@Test
	public void selectByWrapperTest29() {
		User user = new User();
		user.setEmail("xiangxi@qq.com");
		user.setAge(19);
		UpdateWrapper wrapper = new UpdateWrapper(user);
		wrapper.eq("name", "向西").eq("age", 18);
		int rows = userMapper.update(user, wrapper);
		System.out.println(rows);
	}	





前面两种方式我们每次修改都需要创建一个实体对象,其实MP的作者给我们提供了一个更简便的方式修改记录:set方式,可以多个set


@Test
	public void selectByWrapperTest30() {
		UpdateWrapper wrapper = new UpdateWrapper();
		wrapper.eq("name", "向西").eq("age", 18).set("age", 39);
		int rows = userMapper.update(null, wrapper);
		System.out.println(rows);
	}	





Lambda条件过滤器链的修改形式:new LambdaUpdateChainWrapper<>(userMapper)


@SuppressWarnings("deprecation")
	@Test
	public void selectByWrapperTest31() {
		boolean flag = new LambdaUpdateChainWrapper<>(userMapper).eq(User::getName, "向西").eq(User::getAge, 39).set(User::getAge, 19).update();
		System.out.println(flag);
	}	





删除方法


deleteById


@Test
	public void selectByWrapperTest32() {
		int rows = userMapper.deleteById(1496769090553995266L);
		System.out.println(rows);
	}





deleteByMap:根据map中的键值对删除


@Test
	public void selectByWrapperTest33() {
		Map map = new HashMap();
		map.put("name", "向后");
		map.put("age", 22);
		int rows = userMapper.deleteByMap(map);
		System.out.println(rows);
	}
	





deleteByIds:根据ID列表批量删除


@Test
	public void selectByWrapperTest34() {
		int rows = userMapper.deleteBatchIds(Arrays.asList(1497009915565223937L,1496774916538716162L));
		System.out.println(rows);
	}





根据wrapper条件构造器删除,条件构造器和前面的使用方式类似


@Test
	public void selectByWrapperTest35() {
		LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
		wrapper.eq(User::getName,"张飞");
		int rows = userMapper.delete(wrapper);
		System.out.println(rows);
	}





自定义SQL和条件构造器配合使用



	@Select("select * from user ${ew.customSqlSegment}")
	List selectAll(@Param(Constants.WRAPPER) Wrapper wrapper);

@Test
	public void selectByWrapperTest23() {
		LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
		wrapper.lt(User::getAge, 30);
		List list = userMapper.selectAll(wrapper);
		
		list.forEach(System.out::println);
	}





也可以通过xml文件的方式进行配置


# mybatis-plus的配置
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    call-setters-on-nulls: true
    default-enum-type-handler: org.apache.ibatis.type.EnumOrdinalTypeHandler
  mapper-locations: classpath*:/mapper/*.xml   # 配置mapper映射文件位置
  type-aliases-package: com.qbb.mybatisplus.domain
  
# UserMapper.xml
<?xml version="1.0" encoding="UTF-8" ?>


    


@Test
	public void selectByWrapperTest23() {
		LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
		wrapper.lt(User::getAge, 30);
		List list = userMapper.selectAll(wrapper);
		list.forEach(System.out::println);
	}





分页查询


首先需要写一个分页的配置类,这样分页插件才能生效,可以直接官网复制:分页插件


package com.qbb.mybatisplus.config;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;

@Configuration
public class MybatisPlusConfig {

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


@Test
	public void selectByWrapperTest24() {
		LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
		Page page = new Page(1, 3);
		Page selectPage = userMapper.selectPage(page, wrapper);
		
		List records = selectPage.getRecords();
		System.out.println("数据:"+records);
		long total = selectPage.getTotal();
		System.out.println("总记录数:"+total);
	}





从上面的我们可以看出发粗了两条sql,一天查询总记录数,一天分页查询,如果我不需要总记录数,那查询出来也是浪费性能,如何不查呢?


Page page = new Page(1, 3, false); 创建page对象是将是否查询总数设置为false



多表查询分页



/**
	 * 多表查询分页
	 * 
	 * @param page
	 * @param wrapper
	 * @return
	 */
	Page selectUserPage(Page page, @Param(Constants.WRAPPER) Wrapper wrapper);



@Test
	public void selectByWrapperTest26() {
		LambdaQueryWrapper wrapper = Wrappers.lambdaQuery();
		Page page = new Page(1, 3);
		Page selectPage = userMapper.selectUserPage(page, wrapper);

		List records = selectPage.getRecords();
		System.out.println("数据:" + records);
		long total = selectPage.getTotal();
		System.out.println("总记录数:" + total);
	}



AR模式:简单来说,就是直接通过实体来进行表的增删改查操作


使用步骤


// 实体类继承Model
@Data
@EqualsAndHashCode(callSuper=false)
@AllArgsConstructor
@NoArgsConstructor
@SuppressWarnings("all")
public class User extends Model{

// 必须要有一个mapper接口继承BaseMapper
@Mapper
public interface UserMapper extends BaseMapper {



@Test
	public void insertTest1() {
		User user = new User();
		user.setName("秋宝宝");
		user.setAge(21);
		user.setEmail("qbb@qq.com");
		user.setManagerId(1088248166370832385L);
		user.setCreateTime(LocalDateTime.now());
		boolean flag = user.insert();
		System.out.println(flag);
	}





selectById:根据ID查询


@Test
	public void selectByIdTest2() {
		User user = new User();
//		user.setId(1497182096731529218L);
		User user2 = user.selectById(1497182096731529218L);
		System.out.println(user2);
	}





updateById:根据ID修改


@Test
	public void updateByIdTest() {
		User user = new User();
		user.setId(1497182096731529218L);
		user.setEmail("qiuqiu@qq.com");
		boolean flag = user.updateById();
		System.out.println(flag);
	}





deleteById


@Test
	public void deleteByIdTest() {
		User user = new User();
		boolean flag = user.deleteById(1472506514241613830L);
		System.out.println(flag);
	}





参考慕课网:老猿的MybatisPlus入门到精通,讲的非常的好

						  
					  
						
							


						

							
							
			7、Mybatis-plus							
						



						

						
						


						

						
					
					

					
					

						
相关

						

				
						
			            
            
            
                                    
           
  			
						

					

					
				

				

														
					
					

					
									

					
									   
标签

										

										
    
										

									

										
										

									

					

					
				


			
		
		
		
			


						





	

	
一品网 冀ICP备14022925号-6