<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>2.6.1</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-webflux</artifactId>
<version>2.6.1</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.27</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.18</version>
<scope>provided</scope>
</dependency>
<dependency>
<groupId>org.assertj</groupId>
<artifactId>assertj-core</artifactId>
<version>3.21.0</version>
<scope>compile</scope>
</dependency>
</dependencies>
server:
port: 8080
spring:
application:
name: demo04
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=UTF-8
username: root
password: 123456
main:
allow-circular-references: true
devtools:
restart:
enabled: true
mybatis:
configuration:
map-underscore-to-camel-case: true
logging:
level:
com:
ychen:
mybatis: debug
@Configuration
@MapperScan("com.ychen.mybatis.mapper")
public class MybatisPlusConfig {
}
@Data
@Accessors(chain = true)
public class User {
private Long id;
private String name;
private Integer age;
private String email;
@TableField(exist = false)
private String ignoreColumn = "ignoreColumn";
@TableField(exist = false)
private Integer count;
}
@Data
@Accessors(chain = true)
public class User2 {
private Long id;
@TableField(condition = SqlCondition.LIKE, jdbcType = JdbcType.VARCHAR)
private String name;
private Integer age;
}
public interface UserMapper extends BaseMapper<User> {
}
public interface User2Mapper extends BaseMapper<User2> {
}
@RestController
public class TestController {
@Resource
private UserMapper mapper;
@Resource
private User2Mapper user2Mapper;
/**
* 添加操作
*/
@RequestMapping(value = "/test")
@ResponseBody
public String test(){
// 创建对象
User user = new User();
user.setName("小羊test");
user.setAge(31);
user.setEmail("abcdef@mp.com");
// 添加操作
// is匹配符表明如果前面待测的object等于后面给出的objec，插入操作返回的结果是否等于0
assertThat(mapper.insert(user)).isGreaterThan(0);
// 成功直接拿回写的 ID
assertThat(user.getId()).isNotNull();
System.out.println("user_id: " + user.getId());
return "success";
}
/**
* 删除操作
*/
@RequestMapping(value = "/test0")
@ResponseBody
public String test0(){
// 根据id删除，执行删除操作的返回值是否为0
//assertThat(mapper.deleteById(3L)).isGreaterThan(0);
// 删除name为Sandy的数据
mapper.delete(new QueryWrapper<User>()
.lambda().eq(User::getName, "Sandy"));
return "success";
}
/**
* 更新数据
*/
@RequestMapping(value = "/test1")
@ResponseBody
public String test1(){
// 根据id更新
assertThat(mapper.updateById(new User().setId(1L).setEmail("ab@c.c"))).isGreaterThan(0);
// 根据id更新
assertThat(
mapper.update(
// 新建对象name设置为mp
new User().setName("mp"),
// 将id为2的数据，age设置为3，name设置为mp
Wrappers.<User>lambdaUpdate()
.set(User::getAge, 3)
.eq(User::getId, 2)
)
).isGreaterThan(0);
// 根据id获取对象，判断是否修改成功
User user = mapper.selectById(2);
assertThat(user.getAge()).isEqualTo(3);
assertThat(user.getName()).isEqualTo("mp");
// 将id为2的对象邮箱设置为null
mapper.update(
null,
Wrappers.<User>lambdaUpdate().set(User::getEmail, null).eq(User::getId, 2)
);
// 查找id为1的对象的邮箱地址
assertThat(mapper.selectById(1).getEmail()).isEqualTo("ab@c.c");
// 查找id为2的对象邮箱是否为null
user = mapper.selectById(2);
assertThat(user.getEmail()).isNull();
assertThat(user.getName()).isEqualTo("mp");
// 将id为2的对象邮箱设置为miemie@baomidou.com
mapper.update(
new User().setEmail("miemie@baomidou.com"),
new QueryWrapper<User>()
.lambda().eq(User::getId, 2)
);
// 验证是否设置成功
user = mapper.selectById(2);
assertThat(user.getEmail()).isEqualTo("miemie@baomidou.com");
// 将id为2的对象age设置为null，邮箱设置为miemie2@baomidou.com
mapper.update(
new User().setEmail("miemie2@baomidou.com"),
Wrappers.<User>lambdaUpdate()
.set(User::getAge, null)
.eq(User::getId, 2)
);
// 验证
user = mapper.selectById(2);
assertThat(user.getEmail()).isEqualTo("miemie2@baomidou.com");
assertThat(user.getAge()).isNull();
// end
return "success";
}
/**
* 查询操作
*/
@RequestMapping(value = "/test2")
@ResponseBody
public String test2(){
mapper.selectList(new QueryWrapper<User>().select("id", "name"))
.forEach(x -> {
System.out.println(x.getId() + ":" + x.getName() + ":" + x.getAge() + ":" + x.getEmail() );
});
return "success";
}
/**
* 查询操作
*/
@RequestMapping(value = "/test3")
@ResponseBody
public String test3(){
// 插入一个对象，并查询验证
mapper.insert(
new User().setId(10086L)
.setName("miemie")
.setEmail("miemie@baomidou.com")
.setAge(3));
assertThat(mapper.selectById(10086L).getEmail()).isEqualTo("miemie@baomidou.com");
// 根据id查询
User user = mapper.selectOne(new QueryWrapper<User>().lambda().eq(User::getId, 10086));
assertThat(user.getName()).isEqualTo("miemie");
assertThat(user.getAge()).isEqualTo(3);
// 查询所有
mapper.selectList(Wrappers.<User>lambdaQuery().select(User::getId))
.forEach(x -> {
assertThat(x.getId()).isNotNull();
assertThat(x.getEmail()).isNull();
assertThat(x.getName()).isNull();
assertThat(x.getAge()).isNull();
System.out.println("demo: " + x.getId());
});
// 查询所有
mapper.selectList(new QueryWrapper<User>().select("id", "name"))
.forEach(x -> {
assertThat(x.getId()).isNotNull();
assertThat(x.getEmail()).isNull();
assertThat(x.getName()).isNotNull();
assertThat(x.getAge()).isNull();
System.out.println("test: " + x.getId());
});
// end
return "success";
}
/**
* 排序
*/
@RequestMapping(value = "/test4")
@ResponseBody
public String test4(){
// 查询所有，根据age升序
List<User> users = mapper.selectList(Wrappers.<User>query().orderByAsc("age"));
System.out.println(users);
assertThat(users).isNotEmpty();
//多字段排序
List<User> users2 = mapper.selectList(Wrappers.<User>query().orderByAsc("age", "name"));
System.out.println(users2);
assertThat(users2).isNotEmpty();
//先按age升序排列，age相同再按name降序排列
List<User> users3 = mapper.selectList(Wrappers.<User>query().orderByAsc("age").orderByDesc("name"));
System.out.println(users3);
assertThat(users3).isNotEmpty();
return "success";
}
/**
* 将查询到的结果放到map集合，打印出第一个位置的值
*/
@RequestMapping(value = "/test5")
@ResponseBody
public String test5(){
List<Map<String, Object>> mapList = mapper.selectMaps(Wrappers.<User>query().orderByAsc("age"));
assertThat(mapList).isNotEmpty();
assertThat(mapList.get(0)).isNotEmpty();
System.out.println("mapList: " + mapList.get(0));
return "success";
}
/**
* 分页查询，放到map集合
*/
@RequestMapping(value = "/test6")
@ResponseBody
public String test6(){
IPage<Map<String, Object>> page = mapper.selectMapsPage(
new Page<>(1, 5, true), Wrappers.<User>query().orderByAsc("age")
);
System.out.println("total: " + page.getTotal());
System.out.println("pages: " + page.getPages());
System.out.println("pageList: " + page.getRecords());
return "success";
}
/**
* 排序
*/
@RequestMapping(value = "/test7")
@ResponseBody
public String test7(){
// 查询所有，根据age升序排序
List<User> users = mapper.selectList(Wrappers.<User>lambdaQuery().orderByAsc(User::getAge));
assertThat(users).isNotEmpty();
System.out.println("test7: " + users);
//多字段排序
List<User> users2 = mapper.selectList(Wrappers.<User>lambdaQuery().orderByAsc(User::getAge, User::getName));
assertThat(users2).isNotEmpty();
//先按age升序排列，age相同再按name降序排列
List<User> users3 = mapper.selectList(Wrappers.<User>lambdaQuery().orderByAsc(User::getAge).orderByDesc(User::getName));
assertThat(users3).isNotEmpty();
return "success";
}
/**
* 最大值
*/
@RequestMapping(value = "/test8")
@ResponseBody
public String test8(){
// 创建wapper，查询最大的id
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.select("max(id) as id");
User user = mapper.selectOne(wrapper);
System.out.println("maxId=" + user.getId());
return "success";
}
/**
* 分组
*/
@RequestMapping(value = "/test9")
@ResponseBody
public String test9(){
// 根据age分组
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.select("age, count(*)")
.groupBy("age");
List<Map<String, Object>> maplist = mapper.selectMaps(wrapper);
for (Map<String, Object> mp : maplist) {
System.out.println(mp);
}
// 写法2
LambdaQueryWrapper<User> lambdaQueryWrapper = new QueryWrapper<User>().lambda()
.select(User::getAge)
.groupBy(User::getAge)
.orderByAsc(User::getAge);
for (User user : mapper.selectList(lambdaQueryWrapper)) {
System.out.println(user);
}
return "success";
}
/**
* 求和
*/
@RequestMapping(value = "/test10")
@ResponseBody
public String test10(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.select("age, count(age) as count")
.groupBy("age");
List<User> list = mapper.selectList(wrapper);
list.forEach(System.out::println);
list.forEach(x -> {
System.out.println(x.getId());
});
return "success";
}
/**
* 模糊查询
*/
@RequestMapping(value = "/test11")
@ResponseBody
public String test11(){
// SELECT id,name,age FROM user2 WHERE name LIKE CONCAT('%',?,'%')
System.out.println(user2Mapper.selectList(Wrappers.<User2>query().setEntity(new User2().setName("n"))).size());
// SELECT id,name,age FROM user2 WHERE (name LIKE ?)
System.out.println(user2Mapper.selectList(Wrappers.<User2>query().like("name", "J")).size());
// SELECT id,name,age FROM user2 WHERE name LIKE CONCAT('%',?,'%') AND (age > ?)
System.out.println(user2Mapper.selectList(Wrappers.<User2>query().gt("age", 18).setEntity(new User2().setName("J"))).size());
// end
return "success";
}
}
