MyBatisPlus插件的学习1
简化开发, 活用插件. 拥抱变化,持续学习.
首先是配置1:
<dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.2.0</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency>
配置2 :
server: port: 8008 servlet: context-path: /mp spring: datasource: username: root password: 123 url: jdbc:mysql://127.0.0.1:3306/mp?serverTimezone=GMT%2B8&useUnicode=true&characterEncoding=utf-8 driver-class-name: com.mysql.cj.jdbc.Driver logging: level: root: warn com.example.demo.dao: trace pattern: console: '%p%m%n' #xml自定义sql mybatis-plus: mapper-locations: - com/example/demo/mapper/*
常用注解 @TableName 类名<===>表名
@TableId 主键映射
@TableField 列名映射 ps (驼峰命名对应_, managerId <==> manager_id)
当 exist = false 数据不会插入数据库
来自BaseMapper常规API:
/** * 插入一条记录 * * @param entity 实体对象 */ int insert(T entity); /** * 根据 ID 删除 * * @param id 主键ID */ int deleteById(Serializable id); /** * 根据 columnMap 条件,删除记录 * * @param columnMap 表字段 map 对象 (key数据库的列名,value比较的值) */ int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap); /** * 删除(根据ID 批量删除) * * @param idList 主键ID列表(不能为 null 以及 empty) */ int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList);
条件构造器AbstractWrapper :
以查询方法举例子(构造实例): QueryWrapper<User> query = Wrappers.query(); @Param(Constants.WRAPPER) Wrapper<T> queryWrapper new LambdaQueryChainWrapper<>(userMapper) LambdaQueryWrapper<User> query = Wrappers.lambdaQuery();
详细案例:
1 // WHERE (username LIKE ? AND age BETWEEN ? AND ? OR age >= ?) ORDER BY id DESC 2 query.likeRight("username", "张").between("age", 20, 30).or().ge("age", 20).orderByDesc("id"); 3 // 子查询,时间格式 4 // WHERE (date_format(create_time,'%Y-%m-%d') = ? AND manager_id IN (select id from mp_user where username like '大%')) 5 query.apply("date_format(create_time,'%Y-%m-%d') = {0}", "2019-09-28").inSql("manager_id","select id from mp_user where username like '大%'");
and or 的使用
// WHERE (username LIKE ? AND ( (email IS NOT NULL OR age <= ?) )) query.likeLeft("username", "翔").and(wp -> wp.isNotNull("email").or().le("age", 80)); // WHERE (username LIKE ? OR ( (email IS NOT NULL AND age >= ?) )) query.like("username", "雨").or(wp -> wp.isNotNull("email").ge("age", 89));
() 优先的设定 和 last取值
// WHERE (( (email IS NOT NULL OR age <= ?) ) AND username LIKE ?) ORDER BY age ASC limit 2 query.nested(wp -> wp.isNotNull("email").or().le("age", 31)).like("username", "张") .orderByAsc("age").last("limit 2");
// (age IN (?,?,?)) query.in("age", Arrays.asList(25,28,32));
// id, username 排除属性: 用!和&&进行修饰 query.select(User.class , wp -> wp.getColumn().equals("id")||wp.getColumn().equals("username")).le("age", 100); // 当属性和列名不一致的时候不要使用下面的方式 // query.select("id","username").le("age", 100);
//condition 判断条件是否执行 public Children like(boolean condition, R column, Object val) query.like(StringUtils.isNotEmpty(name), "username",name) query.select("manager_id","avg(age) as avg", "min(age) as min", "max(age) as max") .groupBy("manager_id").having("sum(age) < {0}", 500);
List<Map<String, Object>> list = userMapper.selectMaps(query); selectObject 返回一列 selectOne 返回一行,多了报错 selectCount int
selectMaps 适用于非全量数据的返回