mybatis-plus_1
导入pom
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.3.1.tmp</version>
</dependency>
mysql配置
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mp?useUnicode=true&charcaterEncoding=uft8&serverTimezone=UTC
username: root
password: root
#设置日志级别
logging:
level:
root: warn
top.mgy.mybatistest.dao: trace #该包最低级别日志
pattern:
console: '%p%m%n' # 日志级别 日志内容 换行
SQL 语句
#创建用户表
CREATE TABLE USER (
id BIGINT(20) PRIMARY KEY NOT NULL COMMENT '主键',
NAME VARCHAR(30) DEFAULT NULL COMMENT '姓名',
age INT(11) DEFAULT NULL COMMENT '年龄',
email VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
manager_id BIGINT(20) DEFAULT NULL COMMENT '直属上级id',
create_time DATETIME DEFAULT NULL COMMENT '创建时间',
CONSTRAINT manager_fk FOREIGN KEY (manager_id)
REFERENCES USER (id)
) ENGINE=INNODB CHARSET=UTF8;
#初始化数据:
INSERT INTO USER (id, NAME, age, email, manager_id
, create_time)
VALUES (1087982257332887553, '大boss', 40, 'boss@baomidou.com', NULL
, '2019-01-11 14:20:20'),
(1088248166370832385, '王天风', 25, 'wtf@baomidou.com', 1087982257332887553
, '2019-02-05 11:12:22'),
(1088250446457389058, '李艺伟', 28, 'lyw@baomidou.com', 1088248166370832385
, '2019-02-14 08:31:16'),
(1094590409767661570, '张雨琪', 31, 'zjq@baomidou.com', 1088248166370832385
, '2019-01-14 09:15:15'),
(1094592041087729666, '刘红雨', 32, 'lhm@baomidou.com', 1088248166370832385
, '2019-01-14 09:48:16');
创建 User 实体类
package top.mgy.mybatistest.entity;
import lombok.Data;
import java.time.LocalDateTime;
@Data
public class User {
//主键
private Long id;
//姓名
private String name;
//年龄
private Integer age;
//邮箱
private String email;
//直属上级
private Long managerId;
//创建时间
private LocalDateTime createTime;
}
创建mapper
package top.mgy.mybatistest.dao;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import top.mgy.mybatistest.entity.User;
public interface UserMapper extends BaseMapper<User> {
}
在spring boot 启动类加上,包扫描
package top.mgy.mybatistest;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
@MapperScan("top.mgy.mybatistest.dao") //添加包扫描
public class MybatisTestApplication {
public static void main(String[] args) {
SpringApplication.run(MybatisTestApplication.class, args);
}
}
测试
package top.mgy.mybatistest.com.mp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import top.mgy.mybatistest.dao.UserMapper;
import top.mgy.mybatistest.entity.User;
import java.util.List;
@SpringBootTest
public class SimpleTest {
@Autowired
private UserMapper userMapper;
@Test
public void select(){
//查询全部
List<User> users = userMapper.selectList(null);
users.forEach(System.out::println);
}
}
新增方法
package top.mgy.mybatistest.com.mp;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import top.mgy.mybatistest.dao.UserMapper;
import top.mgy.mybatistest.entity.User;
import java.time.LocalDateTime;
@SpringBootTest
public class Insert {
@Autowired
private UserMapper userMapper;
@Test
public void Insert(){
User user = new User();
user.setName("溜溜");
user.setAge(26);
user.setManagerId(1088248166370832385L);
user.setCreateTime(LocalDateTime.now());
int row = userMapper.insert(user);
System.out.println("影响行数"+row);
}
}
常用注解
当实体类和数据库表不对应是使用该注解指定表名
- @TableName("mp_user")
当数据库中主键不为
id
时,指定数据库中的主键列
- @TableId
//主键
@TableId
private Long user_id;
指定实体类中字段在数据库中对应字段
- @TableField("name")
//姓名
@TableField("name")
private String t_name;
排除非表字段的三种方式
使用 transient
标注
transient
关键字表示,该字段不参与序列化过程
//备注
private transient String remark;
使用static
关键字
静态变量不会自动生成
get/set
需要手动生成
//备注
private static String remark;
public static String getRemark() {
return remark;
}
public static void setRemark(String remark) {
User.remark = remark;
}
使用@TableField(exist = false)
exist 设置为 false表示,该字段不是数据库字段
//备注
@TableField(exist = false)
private String remark;
查询
根据id
查询
@Test
public void selectById(){
User user = userMapper.selectById(1088248166370832385L);
System.out.println(user);
}
批量id查询
@Test
public void selectIds(){
List<Long> ids = Arrays.asList(1088248166370832385L, 1088250446457389058L, 1243755707546284033L);
List<User> users = userMapper.selectBatchIds(ids);
users.forEach(System.out::println);
}
条件查询
@Test
public void selectByMap(){
/**
* map.put("name","张三")
* map.put("age",20)
* where name = '张三' and age = 20
*/
Map<String,Object> map = new HashMap<>();
map.put("name","李艺伟");
map.put("age",28);
List<User> users = userMapper.selectByMap(map);
users.forEach(System.out::println);
}
以条件构造器的查询
@Test
public void selectByWrapper(){
/**
* 查询名字中包含雨并且年龄小于40
* where name like '%雨%' and age < 40
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.like("name","雨").lt("age",40);
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectByWrapper2(){
/**
*名字中包含 "雨" 并且年龄 >= 20 且 <=40 且 email 不为空
* where name '%雨%' and age between 20 and 40 and email not null
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.like("name","雨").between("age",20,40).isNotNull("email");
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectByWrapper3() {
/**
* 名字为王姓 或者 年龄 >=25 按照年龄降序排列 年龄相同按照id升序排列
* where like '王%' or age >=25 order by age desc,id asc
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.likeRight("name", "王").or().ge("age", 25)
.orderByDesc("age").orderByAsc("id");
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectByWrapper4() {
/**
* 创建日期为2019年2月14日并且直属上级名字为王姓
* date_format(create_time,'%Y-%m-%d') and manager_id in(select id from user where name like '王%')
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.apply("date_format(create_time,'%Y-%m-%d') ={0}","2019-02-14")
.inSql("manager_id","select id from user where name like '王%'");
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectByWrapper5() {
/**
* 名字为 王 姓, 且 (年龄小于40或邮箱不为空)
* where name '王%' and (age < 40 or email is not null)
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.likeRight("name","王")
.and(wq->wq.lt("age",40).or().isNotNull("email"));
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectByWrapper6() {
/**
* 名字为 王 姓 或者 (年龄<40 且 年龄 >20 且 邮箱不为空)
* WHERE (name LIKE ? OR (age < ? AND age > ? AND email IS NOT NULL))
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.likeRight("name","王").
or(wq->wq.lt("age",40).gt("age",20).isNotNull("email"));
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectByWrapper7() {
/**
* (年龄小于40 或邮箱不为空) 并且名字为王姓
* (age<40 or email is not null) and name like '王%'
*
* WHERE ((age < ? OR email IS NOT NULL) AND name LIKE ?)
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.nested(wq->wq.lt("age",40).or().isNotNull("email"))
.likeRight("name","王");
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectByWrapper8() {
/**
* 年龄为 30,31,34,35
*
* where age in (30,31,34,35)
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.in("age",30,31,34,35);
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectByWrapper9() {
/**
* 年龄为 30,31,34,35 返回满足条件的一条语句
*
* where age in (30,31,34,35) limit 1;
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.in("age",30,31,34,35).last("limit 1");
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
select 中不返回全部的列字段
@Test
public void selectByWrapperSuper(){
/**
* 查询名字中包含雨并且年龄小于40 只返回 id和name
* where name like '%雨%' and age < 40
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
userQueryWrapper.select("id","name").like("name","雨").lt("age",40);
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
like 中condition参数作用
//like 中 condition 的作用
//有如下场景:在前端要通过 name 或者 email 查询用户信息
//写法如下
@Test
public void conditionTest(){
String name = "王";
String email = "";
condition(name,email);
}
private void condition(String name,String email){
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
//常规写法
// if(StringUtils.isNotBlank(name)){
// userQueryWrapper.like("name",name);
// }
// if(StringUtils.isNotBlank(email)){
// userQueryWrapper.like("email",email);
// }
// condition写法
userQueryWrapper.like(StringUtils.isNotBlank(name),"name",name)
.like(StringUtils.isNotBlank(email),"email",email);
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
实体作为条件构造方法的参数
@Test
public void selectByWrapper10() {
/**
* 实体作为查询构造器参数
*/
User user = new User();
user.setName("向");
user.setAge(31);
//将实体作为查询条件时,会把不为null的值放到 where中 且等值查询
// 实体作为查询条件,不会影响到后面使用 API 作为查询条件,会把所有的条件拼接到 where中
// 如果认为等值查询不满足需求时:可在实体类字段上添加注解修改,如下所述
/**
* //姓名 姓名字段模糊匹配
* @TableField(condition = SqlCondition.LIKE)
* private String name;
*/
//如果认为 SqlCondition类提供的常量还不满足,可自定义
/**
* //年龄 年龄 大于 %s表示字段名 <表示大于 #{%s} 表示值
* @TableField(condition = "%s < #{%s}")
* private Integer age;
*/
QueryWrapper<User> userQueryWrapper = new QueryWrapper<>(user);
List<User> users = userMapper.selectList(userQueryWrapper);
users.forEach(System.out::println);
}
返回值是 Map的方式
@Test
public void selectByWrapper11() {
//返回泛型为Map的
//在返回的字段比较少时建议使用,因为如果返回字段比较少,使用实体类会有大量 null 值字段,不优雅
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("id","name");
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
maps.forEach(map->{
System.out.println(map.get("name"));
});
}
@Test
public void selectByWrapper12() {
/**
* 应用场景2 :返回不规范的统计结果数据
* 按照直属上级分组,查询每组的平均年龄,最大年龄,最小年龄
* 并且只取年龄总和小于500的组
*
* select avg(age) avg_age,max(age) max_age,mix(age) mix_age
* from user
* group by manager_id
* having sum(age) < 500
*/
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.select("avg(age) avg_age","max(age) max_age","min(age) min_age")
.groupBy("manager_id")
.having("sum(age) < {0}",500);
List<Map<String, Object>> maps = userMapper.selectMaps(queryWrapper);
maps.forEach(System.out::println);
}
Count 的使用
@Test
public void selectByWrapper13() {
//返回总条数
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
Integer count = userMapper.selectCount(queryWrapper);
System.out.println("count:"+ count);
}
查询一条记录
@Test
public void selectByWrapper14() {
User user = new User();
user.setName("刘红雨");
//返回总条数
QueryWrapper<User> queryWrapper = new QueryWrapper<>(user);
user = userMapper.selectOne(queryWrapper);
System.out.println(user);
}
查询构造器 Lambda 版
@Test
public void selectByWrapper15() {
/**
* 查询构造器 Lambda 版
*/
LambdaQueryWrapper<User> lambdaQuery = new QueryWrapper<User>().lambda();
lambdaQuery.like(User::getName,"雨").lt(User::getAge,40);
//where name like '%雨%' and age < 40
List<User> users = userMapper.selectList(lambdaQuery);
users.forEach(System.out::println);
}
@Test
public void selectByWrapper16() {
/**
* 查询构造器 Lambda 版
*
* 名字为 王 姓, 且 (年龄小于40或邮箱不为空)
* where name '王%' and (age < 40 or email is not null)
*/
LambdaQueryWrapper<User> lambdaQuery = new QueryWrapper<User>().lambda();
lambdaQuery.like(User::getName,"王")
.and(lqw->lqw.lt(User::getAge,40).or().isNotNull(User::getEmail));
List<User> users = userMapper.selectList(lambdaQuery);
users.forEach(System.out::println);
}
简化版(链式)查询构造器 Lambda 版
@Test
public void selectByWrapper17() {
/**
* 简化版(链式)查询构造器 Lambda 版
*
*/
List<User> users = new LambdaQueryChainWrapper<User>(userMapper)
.like(User::getName, "雨").ge(User::getAge, 20).list();
users.forEach(System.out::println);
}
自定义sql(sql写在注解中)
- Mapper
package top.mgy.mybatistest.dao;
import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import top.mgy.mybatistest.entity.User;
import java.util.List;
public interface UserMapper extends BaseMapper<User> {
/**
* 自定义 sql
* @param wrapper
* @return
*/
@Select("select * from user ${ew.customSqlSegment}")
List<User> selectAll(@Param(Constants.WRAPPER)Wrapper<User> wrapper);
}
@Test
public void selectByWrapper18() {
LambdaQueryWrapper<User> lambdaQuery = new QueryWrapper<User>().lambda();
lambdaQuery.like(User::getName,"王")
.and(lqw->lqw.lt(User::getAge,40).or().isNotNull(User::getEmail));
List<User> users = userMapper.selectAll(lambdaQuery);
users.forEach(System.out::println);
}
分页查询
@Test
public void selectPage() {
//分页 返回值是实体对象
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age",26);
// 当前页 每页返回多少条 第三个参数传入false只查询记录,不查询总条数
Page<User> userPage = new Page<>(2, 3);
Page<User> page = userMapper.selectPage(userPage, queryWrapper);
System.out.println("总页数:" +page.getPages());
System.out.println("总记录数:"+page.getTotal());
List<User> records = page.getRecords();
records.forEach(System.out::println);
//返回值是 Map
QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper.ge("age",26);
// 当前页 每页返回多少条
IPage<Map<String,Object>> page = new Page<>(2, 3);
IPage<Map<String, Object>> page1 = userMapper.selectMapsPage(page, queryWrapper);
System.out.println("总页数:" +page1.getPages());
System.out.println("总记录数:"+page1.getTotal());
List<Map<String, Object>> records = page1.getRecords();
records.forEach(System.out::println);
}