>
```
三、统计查询：
11、按照直属上级分组，查询每组的平均年龄、最大年龄、最小年龄。
并且只取年龄总和小于500的组。
select avg(age) avg_age,min(age) min_age,max(age) max_age
from user
group by manager_id
having sum(age) <500
```java
/**
* 按照直属上级分组，查询每组的平均年龄、最大年龄、最小年龄。
*
* 并且只取年龄总和小于500的组。
*
Darian
**/
@Test
public void selectByWrapperAvg() {
QueryWrapper queryWrapper = Wrappers.query();
queryWrapper.select(" avg(age) avg_age ",
"min(age) min_age",
"max(age) max_age")
.groupBy("manager_id")
.having("sum(age)<{0}", 500);
userMapper.selectMaps(queryWrapper)
.forEach(item -> log.info(item.toString()));
}
// {max_age=40, avg_age=40.0000, min_age=40}
// {max_age=25, avg_age=25.0000, min_age=25}
// {max_age=32, avg_age=26.1429, min_age=23}
```
```java
@Test
public void selctByWrapperObjs(){
QueryWrapper queryWrapper = Wrappers.query();
queryWrapper.like("name", "雨")
.lt("age", 40)
.select("id", "name");
userMapper.selectObjs(queryWrapper)
.forEach(item -> log.info(item.toString()));
}
```
- `#selectObjs` 只返回第一列
```java
@Test
public void selctByWrapperObjs(){
QueryWrapper queryWrapper = Wrappers.query();
queryWrapper.like("name", "雨")
.lt("age", 40);
Integer integer = userMapper.selectCount(queryWrapper);
System.out.println("总记录数：" + integer);
}
```
- `#selectCount` count(1)
```
@Test
public void selctByWrapperOne() {
QueryWrapper queryWrapper = Wrappers.query();
queryWrapper.like("name", "雨")
.lt("age", 40);
User user = userMapper.selectOne(queryWrapper);
System.out.println(user);
}
```
- `#selectOne` 返回空 或者一个。
#### `LambdaQueryWrapper` lambda 构造器减少编写的错误
```java
@Test
public void selectByWrapperLambda() {
LambdaQueryWrapper lambdaQuery = Wrappers.lambdaQuery();
// LambdaQueryWrapper lambdaQuery = new QueryWrapper().lambda();
// LambdaQueryWrapper lambdaQuery = new LambdaQueryWrapper<>();
lambdaQuery.like(User::getName, "雨")
.lt(User::getAge, 40);
userMapper.selectList(lambdaQuery)
.forEach(System.out::println);
}
@Test
public void selectByWrapperLambda1() {
LambdaQueryWrapper lambdaQuery = Wrappers.lambdaQuery();
lambdaQuery.likeRight(User::getName, "王")
.and(lqw ->
lqw.lt(User::getAge, 40)
.or().isNotNull(User::getCreateTime)
);
userMapper.selectList(lambdaQuery)
.forEach(System.out::println);
}
@Test
public void selectByWrapperLambda2() {
new LambdaQueryChainWrapper(userMapper)
.like(User::getName, "王")
.ge(User::getAge, 20)
.list()
.stream()
.forEach(System.out::println);
}
```
#### 使用条件构造器的自定义 Sql
- version >= 3.0.7
```java
@Mapper
public interface UserMapper extends BaseMapper {
@Select("slect * from user ${eq.customSqlSegment}")
List selectAll(@Param(Constants.WRAPPER) Wrapper wrapper);
}
```
也可以自定义 xml
```java
```
**不用加 where ** mybagis-plus 会自动加
#### mybatis 分页查询
MP 分页插件实现物理分页
```java
@Configuration
public class MybatisPlusConfig {
@Bean
public PaginationInterceptor paginationInterceptor(){
return new PaginationInterceptor();
}
}
```
```java
@Test
public void selectByWrapperByPage() {
LambdaQueryWrapper lambdaQuery = Wrappers.lambdaQuery();
lambdaQuery.like(User::getName, "雨")
.lt(User::getAge, 40);
Page userPage = new Page<>(1, 2);
IPage userIPage = userMapper.selectPage(userPage, lambdaQuery);
System.out.println(userIPage.getPages());
System.out.println(userIPage.getTotal());
System.out.println(userIPage.getRecords());
}
@Test
public void selectByWrapperByPage2() {
LambdaQueryWrapper lambdaQuery = Wrappers.lambdaQuery();
lambdaQuery.like(User::getName, "雨")
.lt(User::getAge, 40);
Page userPage = new Page<>(1, 2);
IPage> userIPage = userMapper.selectMapsPage(userPage, lambdaQuery);
System.out.println(userIPage.getPages());
System.out.println(userIPage.getTotal());
System.out.println(userIPage.getRecords());
}
```
- `#selectPage`
- `#selectMapsPage`
- ```java
Page userPage = new Page<>(1, 2, false);
```
不查询返回总数
### 更新
#### 根据 id 更新
#### 以条件构造器中作为参数的更新方法
#### 条件构造器中 set 方法使用
```java
@Test
public void updateTest() {
User user = new User();
user.setId(1088248166370832385L);
user.setAge(26);
int i = userMapper.updateById(user);
System.out.println(i);
}
@Test
public void updateTest2() {
UpdateWrapper updateWrapper = Wrappers.update();
updateWrapper.eq("name", "雨");
User user = new User();
user.setId(1088248166370832385L);
user.setAge(26);
int i = userMapper.update(user, updateWrapper);
System.out.println(i);
}
@Test
public void updateTest3() {
UpdateWrapper updateWrapper = Wrappers.update();
updateWrapper.eq("name", "雨")
.set("age", 30);
int i = userMapper.update(null, updateWrapper);
System.out.println(i);
}
@Test
public void updateTest4() {
LambdaUpdateWrapper updateWrapper = Wrappers.lambdaUpdate();
updateWrapper.eq(User::getName, "雨")
.set(User::getAge, 30);
int i = userMapper.update(null, updateWrapper);
System.out.println(i);
}
@Test
public void updateTest5() {
boolean update = new LambdaUpdateChainWrapper(userMapper)
.eq(User::getName, "雨")
.set(User::getAge, 30)
.update();
System.out.println(update);
}
```
### 删除方法
#### 根据id删除
#### 其它普通删除方法
#### 根据条件构造器删除方法
```
@Autowired
private UserMapper userMapper;
@Test
public void deleteById() {
int i = userMapper.deleteById(1L);
System.out.println(i);
}
@Test
public void deleteByMapTest() {
Map paramMap = new HashMap<>();
paramMap.put("name", "sssss");
int i = userMapper.deleteByMap(paramMap);
System.out.println(i);
}
@Test
public void deleteBatchIds() {
int i = userMapper.deleteBatchIds(Arrays.asList(1L, 2L, 3L));
System.out.println(i);
}
@Test
public void deleteByWrapper() {
LambdaQueryWrapper deleteQuery = Wrappers.lambdaQuery();
deleteQuery.eq(User::getAge, 456);
int i = userMapper.delete(deleteQuery);
System.out.println(i);
}
```
## ActiveRecord 模式
demo-3
AR 探索
- AR 模式简介
- MP 中 AR 模式的实现
```java
public class User extends Model {
// ....
}
```
```java
@Test
public void insert() {
User user = new User();
user.setId(1L);
user.setName("sdag");
boolean insert = user.insert();
System.out.println(insert);
}
@Test
public void selecyById() {
User user = new User();
user.setId(1L);
user.setName("sfdagadddf");
User selectById = user.selectById();
System.out.println(user == selectById);
}
@Test
public void updateById() {
User user = new User();
user.setId(1L);
user.setName("sfdadf");
boolean row = user.updateById();
System.out.println(row);
}
@Test
public void deleteById() {
User user = new User();
user.setId(1L);
user.setName("sfdadf");
boolean row = user.deleteById();
System.out.println(row);
}
@Test
public void insertOrUpdateId(){
User user = new User();
user.setId(1L);
user.setName("sfdasdfsddf");
boolean row = user.insertOrUpdate();
System.out.println(row);
}
```
## MP主键策略
### MP支持的主键策略介绍
### 局部主键策略实现
### 全局主键策略实现
```java
// 默认主键为id
@TableId(type = IdType.AUTO) // 标记主键
private Long id;
```
```java
/**
* 生成ID类型枚举类
*
* @author hubin
* @since 2015-11-10
*/
@Getter
public enum IdType {
/**
* 数据库ID自增
*/
AUTO(0),
/**
* 该类型为未设置主键类型
*/
NONE(1),
/**
* 用户输入ID
* 该类型可以通过自己注册自动填充插件进行填充
*/
INPUT(2),
/* 以下3种类型、只有当插入对象ID 为空，才自动填充。 */
/**
* 全局唯一ID (idWorker)
*/
ID_WORKER(3),
/**
* 全局唯一ID (UUID)
*/
UUID(4),
/**
* 字符串全局唯一ID (idWorker 的字符串表示)
*/
ID_WORKER_STR(5);
private final int key;
IdType(int key) {
this.key = key;
}
}
```
- 主键会给你回写。
```java
@Autowired
private UserMapper userMapper;
@Test
public void insertTest() {
User user = new User();
user.setName("sdfasdgasdg");
user.setAge(23);
user.setManagerId(1088248166370832385L);
user.setCreateTime(LocalDateTime.now());
Integer insert = userMapper.insert(user);
log.info(insert.toString());
System.out.println(user.getId());
}
```
```properties
# 全局id配置
mybatis-plus.global-config.db-config.id-type=auto
```
局部策略优于全局策略
## MP 配置
### 基本配置
https://mybatis.plus/config/#%E5%9F%BA%E6%9C%AC%E9%85%8D%E7%BD%AE
`configuration` 和 `configLocation` 不能同时出现
- fieldStrategy : 默认 not_null，为空的时候，不拼接在 SQL 语句中
全局，局部都可以配置，局部优于全局
##### 表明前缀
### 通用 Service
#### 基本方法
#### 批量方法
#### 链式调用方法
service 可以解决异常问题，解决过长的 SQL 的问题。
```java
@Test
public void getOne() {
User one = userService.getOne(Wrappers.lambdaQuery()
.gt(User::getAge, 25));
System.out.println(one);
}
@Test
public void saveBatch() {
// batchSize 默认 1000 条一次提交
boolean b = userService.saveBatch(
Arrays.asList(new User("sdfsd"),
new User("sdfs")),
23);
System.out.println(b);
}
@Test
public void saveOrUpdateBatch() {
// batchSize 默认 1000 条一次提交
boolean b = userService.saveOrUpdateBatch(
Arrays.asList(new User("sddffsd"),
new User("sdfdfs")),
23);
System.out.println(b);
}
@Test
public void chain() {
userService.lambdaQuery()
.gt(User::getAge, 23)
.like(User::getName, "雨")
.list()
.forEach(System.out::println);
}
@Test
public void chain1() {
boolean update =userService.lambdaUpdate()
.gt(User::getAge, 23)
.like(User::getName, "雨")
.update();
System.out.println(update);
}
@Test
public void chain3() {
boolean update =userService.lambdaUpdate()
.gt(User::getAge, 23)
.like(User::getName, "雨")
.remove(); // 删除， update remove
System.out.println(update);
}
```