目录
一、Maven
<properties>
<lombok.version>1.18.10</lombok.version>
<mysql.version>8.0.25</mysql.version>
<druid.version>1.2.8</druid.version>
<fastjson.version>1.2.75</fastjson.version>
<mybatis-plus.version>3.3.2</mybatis-plus.version>
</properties>
<!-- 依赖 -->
<!-- mysql -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
</dependency>
<!-- lombok -->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>${lombok.version}</version>
<optional>true</optional>
</dependency>
<!-- mybatis plus -->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
二、配置
# application.yml
spring:
# 数据库配置
datasource:
type: com.alibaba.druid.pool.DruidDataSource
url: jdbc:mysql://127.0.0.1:3306/xxxxx?useUnicode=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
username: xxxx
password: xxxx
mybatis-plus:
type-aliases-package: edu.tiangong.model # model 的 package
mapper-locations: classpath*:mapper/*Mapper.xml # xml位置
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 日志
map-underscore-to-camel-case: true
auto-mapping-behavior: full
global-config:
# 逻辑删除配置
db-config:
# 删除前
logic-not-delete-value: 0
# 删除后
logic-delete-value: 1
# 表名前缀
table-prefix: ma_
# configuration-properties:
# prefix: ma_ # 设置自定义sql中表名带前缀 select * from ${prefix}user
/**
* MyBatis相关配置
*/
@Configuration
public class MyBatisPlusConfig {
/**
* 分页配置
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
@Bean
public ISqlInjector sqlInjector(){
return new DefaultSqlInjector();
}
}
三、model层(配置插入/更新时设置自动填充字段)
1、对model中字的字段用注解设置插入填充/更新填充
- 插入时填充:@TableField(value = "deleted",fill = FieldFill.INSERT)
- 更新时填充:@TableField(value = "字段名",fill = FieldFill.UPDATE)
- 插入和更新时都填充: @TableField(value = "updatetime",fill = FieldFill.INSERT_UPDATE)
/**
*
* @TableName ma_user
*/
@NoArgsConstructor
@AllArgsConstructor
@Data
@Builder
public class User implements Serializable {
@TableId(type = IdType.AUTO)
@NotNull(message="[id]不能为空")
@ApiModelProperty("id")
private Integer id;
/**
* 用户名
*/
@NotBlank(message="[用户名]不能为空")
@Size(max= 50,message="编码长度不能超过50")
@ApiModelProperty("用户名")
@Length(max= 50,message="编码长度不能超过50")
private String username;
/**
* 年龄
*/
@NotNull(message="[年龄]不能为空")
@ApiModelProperty("年龄")
private Integer age;
/**
* 密码
*/
@NotBlank(message="[密码]不能为空")
@Size(max= 255,message="编码长度不能超过255")
@ApiModelProperty("密码")
@Length(max= 255,message="编码长度不能超过255")
private String password;
/**
* 姓名
*/
@NotBlank(message="[姓名]不能为空")
@Size(max= 100,message="编码长度不能超过100")
@ApiModelProperty("姓名")
@Length(max= 100,message="编码长度不能超过100")
private String realname;
/**
* 电话
*/
@Size(max= 13,message="编码长度不能超过13")
@ApiModelProperty("电话")
@Length(max= 13,message="编码长度不能超过13")
private String phone;
/**
* 邮件
*/
@Size(max= 50,message="编码长度不能超过50")
@ApiModelProperty("邮件")
@Length(max= 50,message="编码长度不能超过50")
private String email;
/**
* 登录时间
*/
@ApiModelProperty("登录时间")
@JsonDeserialize(using = LocalDateTimeDeserializer.class)
@JsonSerialize(using = LocalDateTimeSerializer.class)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime loginTime;
/**
* 创建时间
*/
@NotNull(message="[创建时间]不能为空")
@ApiModelProperty("创建时间")
@TableField(value = "createtime",fill = FieldFill.INSERT)
@JsonDeserialize(using = LocalDateTimeDeserializer.class)
@JsonSerialize(using = LocalDateTimeSerializer.class)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime createtime;
/**
* 更新时间
*/
@ApiModelProperty("更新时间")
@TableField(value = "updatetime",fill = FieldFill.INSERT_UPDATE)
@JsonDeserialize(using = LocalDateTimeDeserializer.class)
@JsonSerialize(using = LocalDateTimeSerializer.class)
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
@DateTimeFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime updatetime;
/**
* 是否删除
*/
@NotNull(message="[是否删除]不能为空")
@ApiModelProperty("是否删除")
@TableLogic
@TableField(value = "deleted",fill = FieldFill.INSERT)
private Integer deleted;
}
2、设置全局配置填充值
/**
* Mybatis Plus Model 插入操作和更新操作默认值
*/
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
this.strictInsertFill(metaObject, "deleted", Integer.class, 0);
this.strictInsertFill(metaObject, "createtime", LocalDateTime.class, LocalDateTime.now());
this.strictInsertFill(metaObject, "updatetime", LocalDateTime.class, LocalDateTime.now());
}
@Override
public void updateFill(MetaObject metaObject) {
this.strictUpdateFill(metaObject, "updatetime", LocalDateTime.class, LocalDateTime.now());
}
}
四、Mapper、Service示例
Mapper、Service继承了大部分基本查询方法
/**
* @description 针对表【ma_user】的数据库操作Mapper
* @Entity xxx.model.User
*/
@Mapper
public interface UserMapper extends BaseMapper<User> {
}
/**
* @description 针对表【ma_user】的数据库操作Service
*/
public interface UserService extends IService<User> {
}
@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {
}
五、Service层使用
1、链式构建查询语句
常用条件项
eq | 等于 |
ne | 不等于 |
gt | 大于 |
lt | 小于 |
ge | 大于等于 |
le | 小于等于 |
in | 包含 |
isNull | 等于null |
between | 介于,包括边界值 |
like | 模糊查询 |
(1)列表查询
各条件项之间默认and连接,可显式使用and()或or()连接条件项。
public List<User> listUser() {
return this.lambdaQuery()
// 查询名字为张三的
.eq(User::getRealname, '张三')
// 或者年龄小于22
.or().lt(User::getAge, 22)
.list();
}
(2)条件嵌套查询
public List<User> listUser() {
return this.lambdaQuery()
// 查询名字为张三的
.eq(User::getRealname, "张三")
// 且(年龄小于22或35岁的)
.and(ele -> ele.lt(User::getAge, 20).or().gt(User::getAge, 35))
.list();
}
(3)分页查询及分页返回类型转换
/**
* @param current 当前页码 size 一页大小
*/
public IPage<User>> listUser(Integer current, Integer size) {
Page<User> page = new Page<>();
return this.lambdaQuery()
// 查询名字为张三的
.eq(User::getRealname, '张三')
.page(page);
}
// 需要转换成DTO,则可用convert
public IPage<UserDto>> listUser(Integer current, Integer size) {
Page<User> page = new Page<>();
return this.lambdaQuery()
// 查询名字为张三的
.eq(User::getRealname, '张三')
.page(page)
.convert(ele -> {
UserDto dto = new UserDto();
BeanUtils.copyProperties(ele, dto);
return dto;
});
}
(4)条件构建多条件查询
@Override
public CommonResult<IPage<UserDto>> listUser(UserCondition condition, Integer current, Integer size) {
Page<User> page = new Page<>();
return CommonResult.success(
this.lambdaQuery()
// 构建查询条件,满足第一个参数,这个条件项才会被填入查询sql
.eq(!Objects.isNull(condition.getRealname())),User::getRealname,condition.getRealname())
// 分页
.page(page)
.convert(ele -> {
UserDto dto = new UserDto();
BeanUtils.copyProperties(ele, dto);
return dto;
})
);
}
2、链式构建更新语句
this.lambdaUpdate()
.eq(User::getUsername, username)
.set(User::getLoginTime, LocalDateTime.now())
.set(User::getLoginTime, LocalDateTime.now())
.update();
3、链式构建删除语句
userService.remove(
new QueryWrapper<User>()
.lambda()
.eq(User::getRealname, realname)
);
六、Mapper层注解SQL
1、分页+自定义queryWrapper
分页只需要在mapper接口的参数里加上IPage<ClassName> page
即可。
${ew.customSqlSegment} 会拼接where语句
@Select("select * from tb_user ${ew.customSqlSegment}")
IPage<UserDto> getDoScDtoPage (IPage<?> page, @Param(Constants.WRAPPER) Wrapper<?> queryWrapper);
注意:如果不需要拼接wehere 只拼接条件,可以使用${ew.sqlSegment} 。如:
@Select("<script>" +
"select * from tb_user where id = #{id} and deleted = 0" +
" <if test=\"ew.sqlSegment != null\">" +
" and ${ew.sqlSegment}" +
" </if>" +
"</script>")
IPage<UserDto> getDoScDtoPage (IPage<?> page, @Param("id") Integer id, @Param(Constants.WRAPPER) Wrapper<?> queryWrapper);
2、动态sql
@Select("<script>" +
"select sum(use_count) as count, md.de_name as item " +
"from ma_schedule ms " +
" left join (select de_name, md.de_id, md.id " +
" from ma_doctor md " +
" left join ma_department m on md.de_id = m.id " +
" where md.deleted = 0) md on ms.do_id = md.id " +
"where ms.deleted = 0" +
" <if test=\"scDay != null\">" +
" and ms.sc_day >= #{scDay} " +
" </if>" +
"group by de_id" +
"</script>")
List<PieVO> getCountGroupByDe(@Param("scDay") LocalDate scDay);