删除
删除通过ID
@Test
public void testDeleteById(){
//通过id删除用户信息
//DELETE FROM user WHERE id=?
int result = userMapper.deleteById(1475754982694199298L);
System.out.println("受影响行数："+result);
}
删除通过map
@Test
public void testDeleteByMap(){
Map<String,Object> map = new HashMap<String,Object>();
map.put("name","张三");
map.put("age",23);
//满足A和B
userMapper.deleteByMap(map);
}
批量删除
@Test
public void testDeleteBatchIds(){
//通过多个id批量删除
//DELETE FROM user WHERE id IN ( ? , ? , ? )
List<Long> idList = Arrays.asList(1L, 2L, 3L);
int result = userMapper.deleteBatchIds(idList);
System.out.println("受影响行数："+result);
}
修改
@Test
public void testUpdate() {
// 修改用户信息
// UPDATE user SET name=?, email=? WHERE id=?
User user = new User();
user.setId(4L);
user.setName("李四");
user.setEmail("lisi@qq.com");
userMapper.updateById(user);
System.out.println("user = " + user);
}
查询
根据ID查询
@Test
public void testSelectById(){
// 通过id查询用户信息
// SELECT id,name,age,email FROM user WHERE id=?
User user = userMapper.selectById(1L);
System.out.println("user = " + user);
}
批量查询
@Test
public void testSelectBatchIds(){
// 根据多个id查询多个用户信息
// SELECT id,name,age,email FROM user WHERE id IN ( ? , ? , ? )
List<Long> list = Arrays.asList(1L, 2L, 3L);
List<User> users = userMapper.selectBatchIds(list);
users.forEach(System.out::println);
}
根据Map查询
@Test
public void testSelectByMap(){
// 根据map集合中的条件查询用户信息
// SELECT id,name,age,email FROM user WHERE name = ? AND age = ?
Map<String, Object> map = new HashMap<>();
map.put("name", "Jack");
map.put("age", 18);
List<User> users = userMapper.selectByMap(map);
users.forEach(System.out::println);
}
返回Map类型数据
/** 根据id查询用户信息为map集合 **/
Map<String,Object> selectMapById(Long id);
<!--Map<String,Object> selectMapById(Long id);-->
<select id="selectMapById" resultType="map">
select id,name,age,email from t_user where id = #{id}
</select>
条件构造器查询
Wrapper ： 条件构造抽象类，最顶端父类
-
AbstractWrapper ： 用于查询条件封装，生成 sql 的 where 条件
-
- QueryWrapper ： 查询条件封装
- UpdateWrapper ： Update 条件封装
- AbstractLambdaWrapper ： 使用Lambda 语法
-
-
- LambdaQueryWrapper ：用于Lambda语法使用的查询Wrapper
- LambdaUpdateWrapper ： Lambda 更新封装Wrapper
-
/** 查询用户包含a,年龄在20到30之间，邮箱信息不为null的用户信息 **/
@Test
public void test01() {
/*
SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user
WHERE is_deleted=0
AND (
user_name LIKE ?
AND age BETWEEN ?
AND ?
AND email IS NOT NULL)
*/
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("user_name", "a")
.between("age", 20, 30)
.isNotNull("email");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
/** 组装排序条件 查询用户信息，按照年龄的降序排序，若年龄相同，则按照id升序排序 **/
@Test
public void test02() {
/*
SELECT uid AS id,user_name AS name,age,email,is_deleted
FROM t_user
WHERE is_deleted=0
ORDER BY age DESC,uid ASC
*/
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("age")
.orderByAsc("uid");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
/** 组装删除条件 删除邮箱地址为null的用户信息 **/
@Test
public void test03() {
/*
UPDATE t_user SET is_deleted=1 WHERE is_deleted=0 AND (email IS NOT NULL)
*/
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.isNull("email");
int result = userMapper.delete(queryWrapper);
System.out.println("result = " + result);
}
/** 组装修改条件 将(年龄大于20并且用户名中包含有a)或邮箱为null的用户信息修改 **/
@Test
public void test04() {
/*
UPDATE t_user SET user_name=?, email=?
WHERE is_deleted=0 AND (age > ? AND user_name LIKE ? OR email IS NOT NULL)
*/
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.gt("age", 20)
.like("user_name", "a")
.or()
.isNotNull("email");
User user = new User();
user.setName("小明");
user.setEmail("test@atguigu.com");
int result = userMapper.update(user, queryWrapper);
System.out.println("result = " + result);
}
/** 条件优先级 将用户名中包含a并且(年龄大于20或邮箱为null)的用户信息修改 **/
@Test
public void test05() {
// lambda中条件优先级
/*
UPDATE t_user SET user_name=?, email=?
WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
*/
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like("user_name", "a")
.and(i -> i.gt("age", 20).or().isNull("email"));
User user = new User();
user.setName("小红");
user.setEmail("test@atguigu.com");
int result = userMapper.update(user, queryWrapper);
System.out.println("result = " + result);
}
/** 组装select字句 查询用户名的用户名、年龄、邮箱信息 **/
@Test
public void test06() {
/*
SELECT user_name,age,email FROM t_user WHERE is_deleted=0
*/
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("user_name", "age", "email");
List<Map<String, Object>> users = userMapper.selectMaps(queryWrapper);
users.forEach(System.out::println);
}
/** 组装子查询 查询id小于100的用户信息**/
@Test
public void test07() {
/*
SELECT uid AS id,user_name AS name,age,email,is_deleted
FROM t_user
WHERE is_deleted=0
AND (uid IN (select uid from t_user where uid <= 100))
*/
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.inSql("uid", "select uid from t_user where uid <= 100");
List<User> list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
UpdateWrapper
/** 使用UpdateWrapper实现修改功能 将用户名中包含a并且(年龄大于20或邮箱为null)的用户信息修改 **/
@Test
public void test08() {
/*
UPDATE t_user SET user_name=?,email=?
WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
*/
UpdateWrapper<User> updateWrapper = new UpdateWrapper<>();
updateWrapper.like("user_name", "a")
.and(i -> i.gt("age", 20).or().isNull("email"));
updateWrapper.set("user_name", "小黑").set("email", "abc@atguigu.com");
int result = userMapper.update(null, updateWrapper);
System.out.println("result = " + result);
}
创建Service接口和实现类
查询总记录数
// 接口 UserService继承IService模板提供的基础功能
public interface UserService extends IService<User> {
}
// 实现类
/*
ServiceImpl实现了IService，提供了IService中基础功能的实现
若ServiceImpl无法满足业务需求，则可以使用自定的UserService定义方法，
并在实现类中实现
*/
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
@Autowired
private UserService userService;
/** 查询总记录数 **/
@Test
public void testGetCount() {
// 查询记录数
// SELECT COUNT( * ) FROM user
long count = userService.count();
System.out.println("总记录数 = " + count);
}
/** 批量添加的功能 **/
@Test
public void testInsertMore() {
// SQL长度有限制，海量数据插入单条SQL无法实行，
// 因此MP将批量插入放在了通用Service中实现，而不是通用Mapper
// 批量添加
// INSERT INTO user ( id, name, age ) VALUES ( ?, ?, ? )
List<User> users = new ArrayList<>();
for (int i = 1; i <= 10; i++) {
User user = new User();
user.setName("lxg" + i);
user.setAge(20 + i);
users.add(user);
}
boolean b = userService.saveBatch(users);
System.out.println(b);
}
业务层判断查询
/** 模拟开发中组装条件的情况 **/
@Test
public void test09() {
/*
SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user
WHERE is_deleted=0 AND (user_name LIKE ? AND age <= ?)
*/
String username = "a";
Integer ageBegin = null;
Integer ageEnd = 30;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
if (StringUtils.isNotBlank(username)) {
// isNotBlank判断某个字符串是否不为空字符串、不为null、不为空白符
queryWrapper.like("user_name", username);
}
if (ageBegin != null) {
queryWrapper.gt("age", ageBegin);
}
if (ageEnd != null) {
queryWrapper.le("age", ageEnd);
}
List<User> list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
判断
/** 使用condition组装条件 **/
@Test
public void test10() {
/*
SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user
WHERE is_deleted=0 AND (user_name LIKE ? AND age <= ?)
*/
String username = "a";
Integer ageBegin = null;
Integer ageEnd = 30;
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.like(StringUtils.isNotBlank(username), "user_name", username)
.gt(ageBegin != null, "age", ageBegin)
.le(ageEnd != null, "age", ageEnd);
List<User> list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
LambdaQueryWrapper
/** LambdaQueryWrapper **/
@Test
public void test11() {
/*
SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user
WHERE is_deleted=0 AND (user_name LIKE ? AND age <= ?)
*/
String username = "a";
Integer ageBegin = null;
Integer ageEnd = 30;
//组装set子句
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();
//避免使用字符串表示字段，防止运行时错误
queryWrapper.like(StringUtils.isNotBlank(username), User::getName, username)
.gt(ageBegin != null, User::getAge, ageBegin)
.le(ageEnd != null, User::getAge, ageEnd);
List<User> list = userMapper.selectList(queryWrapper);
list.forEach(System.out::println);
}
LambdaUpdateWrapper
/** LambdaUpdateWrapper **/
@Test
public void test12() {
/*
UPDATE t_user SET user_name=?,email=?
WHERE is_deleted=0 AND (user_name LIKE ? AND (age > ? OR email IS NULL))
*/
LambdaUpdateWrapper<User> updateWrapper = new LambdaUpdateWrapper<>();
updateWrapper.like(User::getName, "a")
//lambda表达式内的逻辑优先运算
.and(i -> i.gt(User::getAge, 20).or().isNull(User::getEmail));
updateWrapper.set(User::getName, "小黑").set(User::getEmail, "abc@atguigu.com");
int result = userMapper.update(null, updateWrapper);
System.out.println("result = " + result);
}
类映射
@TableName
@TableField("username")
@Tableld(value="uid",type=IdType.AUTO) 主键
@TableLogic 逻辑删除
# 设置MyBatis-Plus的全局配置
global-config:
db-config:
# 设置实体类所对应的表的统一前缀
table-prefix: t_
# 设置统一的主键生成策略
id-type: auto
分页
SpringBoot配置类
@Configuration
@MapperScan("scan.your.mapper.package")
public class MybatisPlusConfig {
/**
* 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
*/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
return interceptor;
}
@Bean
public ConfigurationCustomizer configurationCustomizer() {
return configuration -> configuration.setUseDeprecatedExecutor(false);
}
}
@Autowired
private ProductMapper productMapper;
/** MyBatis-Plus分页插件的配置和使用和分页相关数据的获取 **/
@Test
public void testPage() {
/*
SELECT uid AS id,user_name AS name,age,email,is_deleted FROM t_user
WHERE is_deleted=0 LIMIT ?
*/
//当前1页 每页显示3条
Page<User> page = new Page<>(1, 3);
userMapper.selectPage(page, null);
//数据
System.out.println( page.getRecords());
System.out.println("总页数:" + page.getPages());
System.out.println("总记录数" + page.getTotal());
System.out.println("是否有上一页" + page.hasNext());
System.out.println("是否有下一页:" + page.hasPrevious());
}
自定义分页
/**
* 根据年龄查询用户列表，分页显示
* @param page 分页对象,xml中可以从里面进行取值,传递参数 Page 即自动分页,必须放在第一位 * @param age 年龄
* @return
*/
Page<User> selectPageVo(@Param("page") Page<User> page, @Param("age") Integer age);
<!--Page<User> selectPageVo(@Param("page") Page<User> page, @Param("age") Integer age);-->
<select id="selectPageVo" resultType="User">
select uid as `id`,user_name as `name`,age,email from t_user where age > #{age}
</select>
@Test
public void testSelectPageVo(){ //设置分页参数
Page<User> page = new Page<>(1, 5);
userMapper.selectPageVo(page, 20);
//获取分页数据
List<User> list = page.getRecords();
list.forEach(System.out::println);
System.out.println("当前页:"+page.getCurrent());
System.out.println("每页显示的条数:"+page.getSize());
System.out.println("总记录数:"+page.getTotal());
System.out.println("总页数:"+page.getPages());
System.out.println("是否有上一页:"+page.hasPrevious());
System.out.println("是否有下一页:"+page.hasNext());
}
乐观锁
类
package com.atguigu.mybatisplus.entity;
import lombok.Data;
@Data
public class Product {
private Long id;
private String name;
private Integer price;
@Version
private Integer version;
}
Mapper
@Repository
public interface ProductMapper extends BaseMapper<Product> {
}
@Test
public void testProduct01() {
// 1.小李查询商品价格
Product productLi = productMapper.selectById(1);
System.out.println("小李查询的商品价格:" + productLi.getPrice());
// 2.小王查询商品价格
Product productWang = productMapper.selectById(1);
System.out.println("小王查询的商品价格:" + productWang.getPrice());
// 3.小李商品价格+50
productLi.setPrice(productLi.getPrice() + 50);
productMapper.updateById(productLi);
// 4.小王将商品价格-30
productWang.setPrice(productWang.getPrice() - 30);
int result = productMapper.updateById(productWang);
// 5.老板查询商品价格
Product productBoss = productMapper.selectById(1);
System.out.println("老板查询的商品价格:" + productBoss.getPrice());
}
配置类
@Configuration
// 扫描mapper接口所在的包
@MapperScan("com.atguigu.mybatisplus.mapper")
public class myBatisPlusConfig {
/** 添加MyBatisPlus分页插件 **/
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
// 添加分页插件
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
// 添加乐观锁插件
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return interceptor;
}
}
枚举
@Getter // 因为枚举里面都是常量
public enum SexEnum {
MALE(1, "男"),
FEMALE(2, "女");
@EnumValue // 将注解所标识的属性的值存储到数据库中
private Integer sex;
private String sexName;
SexEnum(Integer sex, String sexName) {
this.sex = sex;
this.sexName = sexName;
}
}
配置application.yml
# 加入日志功能
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# 设置MyBatis-Plus的全局配置
global-config:
db-config:
# 设置实体类所对应的表的统一前缀
table-prefix: t_
# 设置统一的主键生成策略
id-type: auto
# 配置类型别名所对应的包
type-aliases-package: com.atguigu.mybatisplus.pojo
# 扫描枚举的包
type-enums-package: com.atguigu.mybatisplus.enums
@SpringBootTest
public class MyBatisPlusEnumTest {
@Autowired
private UserMapper userMapper;
@Test
public void test() {
User user = new User();
user.setName("admin");
user.setAge(33);
user.setSex(SexEnum.MALE);
int result = userMapper.insert(user);
System.out.println("result = " + result);
}
}
逆向工程
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>3.5.1</version>
</dependency>
<dependency>
<groupId>org.freemarker</groupId>
<artifactId>freemarker</artifactId>
<version>2.3.31</version>
</dependency>
public class FastAutoGeneratorTest {
public static void main(String[] args) {
// 设置我们需要创建在哪的路径
String path = "/Users/luxiaogen/Documents/RoadTo2w/Java/尚硅谷/MyBatisPlus-2022/demo";
// 这里我是mysql8 5版本可以换成 jdbc:mysql://localhost:3306/mybatis_plus?characterEncoding=utf-8&useSSL=false
FastAutoGenerator.create("jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8&characterEncoding=utf-8&useSSL=false", "root", "root")
.globalConfig(builder -> {
builder.author("atguigu") // 设置作者
// .enableSwagger() // 开启 swagger 模式
.fileOverride() // 覆盖已生成文件
.outputDir(path); // 指定输出目录
})
.packageConfig(builder -> {
builder.parent("com.atguigu") // 设置父包名
.moduleName("mybatisplus") // 设置父包模块名
.pathInfo(Collections.singletonMap(OutputFile.mapperXml, path)); // 设置mapperXml生成路径
})
.strategyConfig(builder -> {
builder.addInclude("t_user") // 设置需要生成的表名
.addTablePrefix("t_", "c_"); // 设置过滤表前缀
}).templateEngine(new FreemarkerTemplateEngine()) // 使用Freemarker 引擎模板，默认的是Velocity引擎模板
.execute();
}
}
主从数据源
@Service
@DS("master") // 指定所操作的数据源 类或方法
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
