mybatis-plus:3 - 初级入门
mybatis-plus-3初级入门(MP)
一、快速入门
1、Spring Boot 整合
1、pom.xml
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.2.0</version> <!-- mybatis-plus-3版本使用,Spring Boot 2.x -->
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.17</version>
</dependency>
2、application.yml
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://127.0.0.1:3306/mp?useSSL=false&serverTimezone=GMT%2B8
username: root
password: root
3、启动类
@MapperScan("com.demo.dao")
@SpringBootApplication
public class Application {
public static void main(String[] args) {
SpringApplication.run(Application.class, args);
}
}
4、实体
@Data
public class User {
private Long id; // 主键
private String name; // 姓名
private Integer age; // 年龄
private String email; // 邮箱
private Long managerId; // 直属上级
private LocalDateTime createTime; // 创建时间
}
5、dao
public interface UserMapper extends BaseMapper<User> {
}
6、测试
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void select(){
// 参数为null,查询全部
List<User> users = userMapper.selectList(null);
// 断言(Assert):断言的条件如果返回错误,则终止程序执行
// 判断一下返回的users的长度是否为5
Assert.assertEquals(5,users.size());
users.forEach(System.out::println);
}
}
二、通用Mapper
1、新增方法
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void insert(){
User user = new User();
user.setName("小明");
user.setAge("23");
user.setManagerId("1");
user.setCreateTime(LocalDateTime.now());
// 插入单条数据,返回影响记录数
int rows = userMapper.insert(user);
System.out.println("影响记录数:" + rows);
}
}
2、常用注解
// 表名称
@TableName("表名")
public class User {
// 主键
@TableId
private Long id; // 主键
// 指定数据库中对应的列
@TableField("name")
private String realName; // 姓名
// condition的查询控制
@TableField(condition=SqlCondition.LIKE)
private Integer age; // 年龄
private String email; // 邮箱
private Long managerId; // 直属上级
private LocalDateTime createTime; // 创建时间
}
3、排除非表字段的三种方式
-
添加关键字transient(不能序列化)
private transient String remark; // 备注
-
添加关键字static
private static String remark; // 备注 // 省略 get set
-
@TableField(exist=false)
@TableField(exist=false) private String remark; // 备注
4、查询
4.1、基本查询
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
// where id = ''
public void selectById(){
User user = userMapper.selectById(Id);
}
@Test
// where id in ('','','')
public void selectBatchIds(){
List<Long> ids = Arrays.asList(id1,id2,id3);
List<User> users = userMapper.selectBatchIds(Id);
users.forEach(System.out::println);
}
@Test
// where name = '' and age = ''
public void selectByMap(){
Map<String,Object> map = new HashMap<>();
// key是数据库表中的列名
map.put("name","张三");
map.put("age","25");
List<User> users = userMapper.selectByMap(map);
users.forEach(System.out::println);
}
}
4.2、条件构造器查询
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
// name like '%%' and age < ''
public void selectList1(){
// QueryWrapper<User> queryWrapper = Wrappers.<User>query(); 效果与下等同
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
// .like() .lt():第一个参数都是 数据库表中的字段名
queryWrapper.like("name","张").lt("age",30);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// name like '%%' and (age between '' and '') and email is not null
public void selectList2(){
// QueryWrapper<User> queryWrapper = Wrappers.<User>query(); 效果与下等同
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
// .like() .between() .isNotNull():第一个参数都是 数据库表中的字段名
queryWrapper.like("name","张").between("age",20,30).isNotNull("email");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// name like '张%' or age >= '' order by age desc,id asc
public void selectList3(){
// QueryWrapper<User> queryWrapper = Wrappers.<User>query(); 效果与下等同
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
// .likeRight() .ge() .orderByDesc() .orderByAsc():第一个参数都是 数据库表中的字段名
queryWrapper.likeRight("name","张").or().ge("age",25).orderByDesc("age")
.orderByAsc("id");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// date_format(create_time,'%Y-%m-%d') and manager_id in (select id from user where name like '王%')
public void selectList4(){
// QueryWrapper<User> queryWrapper = Wrappers.<User>query(); 效果与下等同
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
// apply:拼接sql。{0}代表后边的参数的索引。并且避免sql注入。
queryWrapper.apply("date_format(create_time,'%Y-%m-%d')={0}","2019-02-14")
.inSql("manager_id","select id from user where name like '王%'");
.orderByAsc("id");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// name like '张%' and (age < ''or email is not null)
public void selectList5(){
// QueryWrapper<User> queryWrapper = Wrappers.<User>query(); 效果与下等同
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.likeRight("name","张")
.and(
qw->qw.lt("age",40).or().isNotNull("email")
);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// name like '张%' or (age < '' and age > '' and email is not null)
public void selectList6(){
// QueryWrapper<User> queryWrapper = Wrappers.<User>query(); 效果与下等同
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.likeRight("name","张")
.or(qw->qw.lt("age",40).gt("age",20).isNotNull("email"));
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// (age < '' or email is not null) and name like '张%'
public void selectList7(){
// QueryWrapper<User> queryWrapper = Wrappers.<User>query(); 效果与下等同
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.nested(qw->qw.lt("age",40).or().isNotNull("email"))
.likeRight("name","张");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// age in (30,31,34,35)
public void selectList8(){
// QueryWrapper<User> queryWrapper = Wrappers.<User>query(); 效果与下等同
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.in("age",Arrays.asList(30,31,34,35));
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// limit 1
public void selectList9(){
// QueryWrapper<User> queryWrapper = Wrappers.<User>query(); 效果与下等同
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
// 只能调用一次,多次调用以最后一次为准,有sql注入的风险,请谨慎使用
queryWrapper.in("age",Arrays.asList(30,31,34,35)).last("limit 1");
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
}
4.3、select 不列出全部字段
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void selectList1(){
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
// .select():只查询指定的列
queryWrapper.select("id","name").like("name","张").lt("age",30);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectList2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
// .select():!info.getColumn().equals("create_time"):排除指定列
queryWrapper
.select(
User.class,info->
!info.getColumn().equals("create_time")
&& !info.getColumn().equals("manager_id")
);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
}
4.4、condition作用
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void testCondition(){
String name = "王";
String email = "";
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
// condition:控制加不加入到where条件中
queryWrapper.like(StringUtils.isNotEmpty(name),"name",name)
like(StringUtils.isNotEmpty(email),"email",email);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
}
4.5、条件构造器(实体参数)
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void selectByWrapperEntity(){
User whereUser = new User();
whereUser.setName("张三");
whereUser.setAge(20);
// 将whereUser传入到QueryWrapper
QueryWrapper<User> queryWrapper = new QueryWrapper<User>(whereUser);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
}
4.6、allEq
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void selectByWrapperAllEq(){
Map<String,Object> map = new HashMap<>();
// key是数据库表中的列名
map.put("name","张三");
map.put("age","25");
// 将whereUser传入到QueryWrapper
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
// 默认true,传入Null age is null
queryWrapper.allEq(map);
// 传入false,值为null被忽略
queryWrapper.allEq(map,false);
// 过滤掉name列,注意'!'
queryWrapper.allEq((k,v)->!k.equals("name"),map);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
}
4.7、其他
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void selectMaps1(){
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.like("name","张").lt("age",30);
List<Map<String,Object>> users = userMapper.selectMaps(queryWrapper);
users.forEach(System.out::println);
}
@Test
// select avg(age) avg_age,min(age) min_age,max(age) max_age
// from user
// group by manager_id
// having sum(age) < 500
public void selectMaps2(){
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.select("avg(age) avg_age","min(age) min_age","max(age) max_age")
.groupBy("manager_id").having("sum(age)<{0}",500);
List<Map<String,Object>> users = userMapper.selectMaps(queryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectObjs(){
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.like("name","张").lt("age",30);
// 只返回一列数据时使用
List<Object> users = userMapper.selectObjs(queryWrapper);
users.forEach(System.out::println);
}
@Test
public void selectCount(){
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.like("name","张").lt("age",30);
// 查询记录数
Integer count = userMapper.selectCount(queryWrapper);
System.out.print("总记录数:" + count);
}
@Test
public void selectOne(){
QueryWrapper<User> queryWrapper = new QueryWrapper<User>();
queryWrapper.like("name","张").lt("age",30);
// 只能查询一条记录或者查询不到。查询多会报错。
User user = userMapper.selectOne(queryWrapper);
System.out.print("总记录数:" + count);
}
}
4.8、lambda条件构造器
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void selectLambda1(){
// 防误写
// 构造方式1
LambdaQueryWrapper<User> queryWrapper = new QueryWrapper<User>().lambda();
// 构造方式2
LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<User>();
// 构造方式3
LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery();
queryWrapper.like(User::getName,"张").lt(User::getAge,30);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// name like '张%' and (age < ''or email is not null)
public void selectLambda2(){
LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery();
queryWrapper.likeRight(User::getName,"张")
.and(
qw->qw.lt(User::getAge,40).or().isNotNull(User::getEmail)
);
List<User> users = userMapper.selectList(queryWrapper);
users.forEach(System.out::println);
}
@Test
// name like '张%' and (age < ''or email is not null)
public void selectLambda3(){
// 构造方式4:放入 userMapper
List<User> users =
new LambdaQueryChainWrapper<User>(userMapper)
.likeRight(User::getName,"张")
.and(
qw->qw.lt(User::getAge,40).or().isNotNull(User::getEmail)
).list();
users.forEach(System.out::println);
}
}
4.9、自定义sql
-
条件
- 版本 >= 3.0.7
-
接口中用注解
public interface UserMapper extends BaseMapper<User> { @Select("select * from user ${ew.customSqlSegment}") List<User> selectAll(@Param(Constants.WRAPPER) Wrapper<User> wrapper); } @RunWith(SpringRunner.class) @SpringBootTest public class SimpleTest{ @Autowired private UserMapper userMapper; @Test public void selectAll(){ LambdaQueryWrapper<User> queryWrapper = Wrappers.<User>lambdaQuery(); queryWrapper.likeRight(User::getName,"张") .and( qw->qw.lt(User::getAge,40).or().isNotNull(User::getEmail) ); List<User> users = userMapper.selectAll(queryWrapper); users.forEach(System.out::println); } }
-
xml配置
spring: datasource: driver-class-name: com.mysql.cj.jdbc.Driver url: jdbc:mysql://127.0.0.1:3306/mp?useSSL=false&serverTimezone=GMT%2B8 username: root password: root mybatis-plus: mapper-locations: - com/mp/mapper/*
<select id="selectAll" resultType="com.mp.entity.User"> select * from user ${ew.customSqlSegment} </select>
4.10、分页查询
-
增加配置类
@Configuration public class MybatisPlusConfig { @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); } }
-
测试类
@RunWith(SpringRunner.class) @SpringBootTest public class SimpleTest{ @Autowired private UserMapper userMapper; @Test public void selectPage(){ QueryWrapper<User> queryWrapper = new QueryWrapper<User>(); queryWrapper.ge("age",30); // false 不返回总记录数 // Page<User> page = new Page<User>(1,2,false); Page<User> page = new Page<User>(1,2); IPage<User> iPage = userMapper.selectPage(page,queryWrapper); System.out.println("总页数:" + iPage.getPages()); System.out.println("总记录数:" + iPage.getTotal()); List<User> users = iPage.getRecords(); users.forEach(System.out::println); } @Test public void selectMapPage(){ QueryWrapper<User> queryWrapper = new QueryWrapper<User>(); Page<User> page = new Page<User>(1,2); queryWrapper.ge("age",30); IPage<Map<String,Object>> iPage = userMapper.selectMapPage(page,queryWrapper); System.out.println("总页数:" + iPage.getPages()); System.out.println("总记录数:" + iPage.getTotal()); List<Map<String,Object>> users = iPage.getRecords(); users.forEach(System.out::println); } }
4.11、多表联查时的分页查询
-
mapper类
public interface UserMapper extends BaseMapper<User> { IPage<User> selectUserPage(Page<User> page,@Param(Constants.WRAPPER) Wrapper<User> wrapper); }
-
xml
<select id="selectUserPage" resultType="com.mp.entity.User"> select * from user ${ew.customSqlSegment} left join ... </select>
-
测试类
@RunWith(SpringRunner.class) @SpringBootTest public class SimpleTest{ @Autowired private UserMapper userMapper; @Test public void selectUserPage(){ QueryWrapper<User> queryWrapper = new QueryWrapper<User>(); queryWrapper.ge("age",30); Page<User> page = new Page<User>(1,2); IPage<User> iPage = userMapper.selectUserPage(page,queryWrapper); System.out.println("总页数:" + iPage.getPages()); System.out.println("总记录数:" + iPage.getTotal()); List<User> users = iPage.getRecords(); users.forEach(System.out::println); } }
5、更新
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void updateById(){
User user = new User();
user.setId(id);
user.setAge(26);
user.setEmail("zhangsan@baomili.com");
int rows = userMapper.updateById(user);
System.out.println("影响记录数:" + rows);
}
@Test
public void update(){
UpdateWrapper<User> updateWrapper = new UpdateWrapper<User>();
updateWrapper.eq("name","李艺伟").eq("age",28);
User user = new User();
user.setEmail("zhangsan2020@baomili.com");
user.setAge(27);
int rows = userMapper.update(user,updateWrapper);
System.out.println("影响记录数:" + rows);
}
@Test
public void update2(){
User whereUser = new User();
whereUser.setName("张三");
UpdateWrapper<User> updateWrapper = new UpdateWrapper<User>(whereUser);
User user = new User();
user.setEmail("zhangsan2020@baomili.com");
user.setAge(27);
int rows = userMapper.update(user,updateWrapper);
System.out.println("影响记录数:" + rows);
}
@Test
public void update3(){
UpdateWrapper<User> updateWrapper = new UpdateWrapper<User>();
updateWrapper.eq("name","李艺伟").eq("age",28).set("age",30);
int rows = userMapper.update(null,updateWrapper);
System.out.println("影响记录数:" + rows);
}
@Test
public void lambdaUpdate(){
LambdaUpdateWrapper<User> lambdaUpdate = Wappers.<User>LambdaUpdate();
lambdaUpdate.eq(User:getName,"张").eq(User::getAge,30).set(User::getAge,31);
int rows = userMapper.update(null,updateWrapper);
System.out.println("影响记录数:" + rows);
}
@Test
public void lambdaUpdate(){
boolean status = new LambdaUpdateChainWrapper<User>(userMapper).
.eq(User:getName,"张").eq(User::getAge,30).set(User::getAge,31).update();
System.out.println("是否修改成功:" + status);
}
}
6、删除
@RunWith(SpringRunner.class)
@SpringBootTest
public class SimpleTest{
@Autowired
private UserMapper userMapper;
@Test
public void deleteById(){
int rows = userMapper.deleteById(id);
System.out.println("影响记录数:" + rows);
}
@Test
public void deleteByMap(){
Map<String,Object> map = new HashMap<>();
// key是数据库表中的列名
map.put("name","张三");
map.put("age","25");
int rows = userMapper.deleteByMap(map);
System.out.println("影响记录数:" + rows);
}
@Test
public void deleteBacthIds(){
int rows = userMapper.deleteBacthIds(Arrays.asList(id1,id2,id3));
System.out.println("影响记录数:" + rows);
}
@Test
public void delete(){
LambdaQueryWrapper<User> lambdaQueryWrapper = Wrappers.<User>lambdaQuery();
lambdaQueryWrapper.eq(User::getName,"张三").eq(User:getAge,31);
int rows = userMapper.delete(lambdaQueryWrapper);
System.out.println("影响记录数:" + rows);
}
}
三、ActiveRecord模式
-
实体类
@Data // 如果不需要继承父类,则callSuper=false,压制警告 @EqualsAndHashCode(callSuper=false) public class User extends Model<User> { private static final long serialVersionUID = 1L; private Long id; // 主键 private String name; // 姓名 private Integer age; // 年龄 private String email; // 邮箱 private Long managerId; // 直属上级 private LocalDateTime createTime; // 创建时间 }
-
测试类
@RunWith(SpringRunner.class) @SpringBootTest public class SimpleTest{ @Autowired private UserMapper userMapper; @Test public void insert(){ User user = new User(); user.setName("小明"); user.setAge("23"); user.setManagerId("1"); user.setCreateTime(LocalDateTime.now()); // 插入单条数据,返回影响记录数 boolean status = user.insert(); System.out.println("是否插入成功:" + status); } @Test public void selectById(){ // 查询出来的是个新对象 User selectUser = user.selectById(id); System.out.println(selectUser); } @Test public void selectById2(){ User user = new User(); user.setId(id); // 查询出来的是个新对象 User selectUser = user.selectById(); System.out.println(selectUser); System.out.println(user); } @Test public void updateById(){ User user = new User(); user.setId(id); user.setName("小明"); boolean status = user.updateById(); System.out.println("是否修改成功:" + status); } @Test public void deleteById(){ User user = new User(); user.setId(id); boolean status = user.deleteById(); System.out.println("是否删除成功:" + status); } @Test public void insertOrUpdate(){ User user = new User(); user.setName("小明"); user.setAge("23"); user.setManagerId("1"); user.setCreateTime(LocalDateTime.now()); // 插入单条数据,返回影响记录数 boolean status = user.insertOrUpdate(); System.out.println("是否插入成功:" + status); } }
四、主键策略
1、局部策略
@Data
public class User {
// 主键自增 -- IdType.AUTO
// 默认策略(跟随全局) -- IdType.NONE
// UUID -- IdType.UUID
// 雪花算法的自增ID -- IdType.ID_WORKER_STR
@TableId(type=IdType.AUTO)
private Long id; // 主键
private String name; // 姓名
private Integer age; // 年龄
private String email; // 邮箱
private Long managerId; // 直属上级
private LocalDateTime createTime; // 创建时间
}
2、全局策略
mybatis-plus:
global-config:
db-config:
id-type: 策略
局部策略优先于全局策略
五、MP配置
六、通用service
-
service
public interface UserService extends IService<User>{ }
-
serviceImpl
@Service public class UserServiceImpl extends ServiceImpl<UserMapper,User> implements UserService{ }
-
测试类
@RunWith(SpringRunner.class) @SpringBootTest public class SimpleTest{ @Autowired private UserService userService; @Test public void getOne(){ // 可多传false,取回多条数据,报警告,取第一条数据。 User one = userService.getOne(Wrappers.<User>lambdaQuery().gt(User::getAge,25)); System.out.println(one); // 多个报错 } @Test public void getBatch(){ User user1 = new User(); user.setName("小明"); user.setAge("23"); User user2 = new User(); user.setName("大明"); user.setAge("23"); List<User> users = Arrays.asList(user1,user2); boolean status = userService.saveBatch(users); System.out.println(status); // 不报错就返回true } @Test public void getBatch2(){ User user1 = new User(); user.setName("小明"); user.setAge("23"); User user2 = new User(); user.setId("1"); // 多插入一个id user.setName("大明2"); user.setAge("23"); List<User> users = Arrays.asList(user1,user2); boolean status = userService.saveOrUpdageBatch(users); System.out.println(status); // 不报错就返回true } @Test public void chain(){ List<User> users = userService.lambdaQuery().gt(User::getAge,25).like(User::getName,"张").list(); users.forEach(System.out::println); } @Test public void chain2(){ boolean status = userService.lambdaUpdate().eq(User::getAge,25).set(User::getAge,26).update(); System.out.println(status); } @Test public void chain3(){ boolean status = userService.lambdaUpdate().eq(User::getAge,25).remove(); System.out.println(status); } }
本文内容学习自 慕课网 -- MyBatis-Plus入门