1.分页查询
使用mybatis 自带的IPage进行分页，需要加以下配置
@Configuration
public class MybatisPlusConfig {
/**
* 单页分页条数限制(默认无限制)
*/
private static final Long MAX_LIMIT = 1000L;
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor() {
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
//分页插件: PaginationInnerInterceptor
PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
paginationInnerInterceptor.setMaxLimit(MAX_LIMIT);
interceptor.addInnerInterceptor(paginationInnerInterceptor);
// 乐观锁插件
interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
return interceptor;
}
}
查询的时候直接放进去iPage即可。例如
controller:
@GetMapping("/list")
public IPage<ConfigVo> getConfigList(@RequestParam("pageIndex") Integer pageIndex, @RequestParam("pageSize") Integer pageSize) {
return configService.getconfigVoList();
}
service:
public IPage<ConfigVo> getConfigVoList(Integer pageIndex, Integer pageSize) {
LambdaQueryWrapper<ConfigDo> wrapper = new LambdaQueryWrapper<>();
wrapper.eq(ConfigDo::getProjectCode, projectCode);
wrapper.orderByDesc(ConfigDo::getUpdateTime);
IPage<ConfigDo> iPage = new Page(pageIndex, pageSize);
IPage<ConfigDo> slaConfigDos = slaConfigMapper.selectPage(iPage, wrapper);
IPage<Config> page = new Page<>();
BeanUtils.copyProperties(iPage, page);
page.setRecords(ConfigConvert.INSTANCE.getConfigDo2VoList(ConfigDos.getRecords()));
return page;
}
手写sql
直接放ipage参数即可
IPage<ConfigDO> getConfig(IPage<ConfigDO> iPage, @Param("projectCode") String projectCode);
1.1 坑
1.1.1 分页排序
分页的过程中存在排序
对于值为null 或者 值存在重复的值 存在重复查询的情况，即第一页出现的数据，第二页有概率继续出现
解决：再加一个唯一的值 作为第二排序的条件 最好是直接用唯一值作为排序条件
1.1.2 left join 加分页
一对多的情况，多中存在查询条件，使用left join连接 业务期望按照一(一对多中的一)进行分页
实际上分页通过limit实现分页 left join 会按照多的展示数量 导致分页效果与期望不符合
2.vo存在list或者对象
2.1 对象
do中添加对应的类型
user作为班级的一份子，查询user的时候带上class具体信息
@Data
@TableName("user")
public class UserDo implements Serializable {
@TableId(type = IdType.AUTO)
private Long id;
@TableField("name")
private String name;
@TableField("age")
private Integer age;
@TableField("class_id")
private Long classId;
@TableField("teacher_id")
private Long teacherId;
@TableField(exist = false)
private ClassStuDo classStuDo;
}
sql语句使用association关联
//<resultMap>标签id 已经对应的类型
<resultMap id="resultMap1" type="com.example.test.doobject.UserDo">
//column对应列 jdbctype字段类型 property实体对象的name
<id column="id" jdbcType="BIGINT" property="id"></id>
<result column="name" jdbcType="VARCHAR" property="name"></result>
<result column="age" jdbcType="INTEGER" property="age"></result>
<result column="class_id" jdbcType="BIGINT" property="classId"></result>
<association property="classStuDo" javaType="com.example.test.doobject.ClassStuDo">
<id column="id" jdbcType="BIGINT" property="id"></id>
<result column="class_name" jdbcType="VARCHAR" property="className"></result>
<result column="class_no" jdbcType="VARCHAR" property="classNo"></result>
</association>
</resultMap>
//resultMap代表使用的<resultMap>标签
<select id="selectPageWithClass" resultMap="resultMap1">
select user.*,class_stu.*
from user left join class_stu
on user.class_id = class_stu.id
where class_stu.class_name = #{className}
</select>
2.2list
//查询在这个班级教学的所有教师
@Data
@TableName("class_stu")
public class ClassStuDo {
@TableId(type = IdType.AUTO)
private Long id;
private String className;
private String classNo;
@TableField(exist = false)
sql语句使用collection关联
<resultMap id="resultMap2" type="com.example.test.doobject.ClassStuDo">
<id column="id" jdbcType="BIGINT" property="id"></id>
<result column="class_name" jdbcType="VARCHAR" property="className"></result>
<result column="class_no" jdbcType="VARCHAR" property="classNo"></result>
//ofType代表list中的实体对象类型
<collection property="teacherDoList" ofType="com.example.test.doobject.TeacherDo">
<id column="tid" jdbcType="BIGINT" property="id"></id>
<result column="name" jdbcType="VARCHAR" property="name"></result>
<result column="age" jdbcType="INTEGER" property="age"></result>
</collection>
</resultMap>
<select id="userPageByClassName" resultMap="resultMap2">
select class_stu.*,teacher.id as tid,teacher.name,teacher.age
from class_stu left join teacher
on class_stu.id = teacher.class_id
where class_name = #{className}
</select>
3.update
//字段策略中为null的不修改 field-strategy: NOT_NULL
teacherDoMapper.updateById()
//LambdaUpdateWrapper中可以设置值为null，筛选条件 实体中不为null的值即为更新的值
LambdaUpdateWrapper<TeacherDo> wrapper = new LambdaUpdateWrapper();
//设置年龄为null
wrapper.set(TeacherDo::getAge, null);
//筛选id为1的行
wrapper.eq(TeacherDo::getId, 1);
//将id为1的行设值classId为2
teacherDo.setClassId(2L);
teacherDoMapper.update(teacherDo, wrapper);
4.自动填充内容
自动填充 更新时间 更新人 创建时间 创建人这些
@TableField(fill = FieldFill.*INSERT*) insertFill存在的内容
@TableField(fill = FieldFill.*UPDATE*) updateFill存在的内容
@TableField(fill = FieldFill.*INSERT_UPDATE*) nsertFill，updateFill都存在的内容
//用于实现自动填充
@Configuration
public class MetaHandler implements MetaObjectHandler {
@Override
public void insertFill(MetaObject metaObject) {
//判断属性中的字段
Object createTime = getFieldValByName("createTime", metaObject);
Object createByName = getFieldValByName("createByName", metaObject);
Object createBy = getFieldValByName("createBy", metaObject);
Object updateTime = getFieldValByName("updateTime", metaObject);
Object updateByName = getFieldValByName("updateByName", metaObject);
Object updateBy = getFieldValByName("updateBy", metaObject);
//添加字段值如果传了这个字段则不会更新
if (Objects.isNull(updateTime)) {
setFieldValByName("updateTime", LocalDateTime.now(), metaObject);
}
if (Objects.isNull(updateByName)) {
setFieldValByName("updateByName", UserProvider.getUserInfo().getUserName(), metaObject);
}
if (Objects.isNull(updateBy)) {
setFieldValByName("updateBy", UserProvider.getUserInfo().getUserCode(), metaObject);
}
if (Objects.isNull(createTime)) {
setFieldValByName("createTime", LocalDateTime.now(), metaObject);
}
if (Objects.isNull(createByName)) {
setFieldValByName("createByName", UserProvider.getUserInfo().getUserName(), metaObject);
}
if (Objects.isNull(createBy)) {
setFieldValByName("createBy", UserProvider.getUserInfo().getUserCode(), metaObject);
}
}
@Override
public void updateFill(MetaObject metaObject) {
Object updateTime = getFieldValByName("updateTime", metaObject);
Object updateByName = getFieldValByName("updateByName", metaObject);
Object updateBy = getFieldValByName("updateBy", metaObject);
if (Objects.isNull(updateTime)) {
setFieldValByName("updateTime", LocalDateTime.now(), metaObject);
}
if (Objects.isNull(updateByName)) {
setFieldValByName("updateByName", UserProvider.getUserInfo().getUserName(), metaObject);
}
if (Objects.isNull(updateBy)) {
setFieldValByName("updateBy", UserProvider.getUserInfo().getUserCode(), metaObject);
}
}
}
5.批量插入
controller:
List<TeacherDo> list = new ArrayList<>();
for (int i = 0; i < 10; i++) {
TeacherDo teacherDo = new TeacherDo();
teacherDo.setName("teacher" + i);
teacherDo.setAge(31);
list.add(teacherDo);
}
teacherService.saveBatch(list);
service:
public interface TeacherService extends IService<TeacherDo> {
}
serviceImpl:
@Service
public class TeacherServiceImpl extends ServiceImpl<TeacherDoMapper, TeacherDo> implements TeacherService {
}
6.@TableLogic
查询的时候会默认带上删除字段条件
更新值也不会更新删除字段
@TableLogic
表示逻辑删除 value 未删除值 dvalue 删除值
未设置读取全局配置 application.yaml
mybatis-plus:
mapper-locations: classpath*:/mapper/*.xml
configuration:
map-underscore-to-camel-case: true
global-config:
db-config:
logic-delete-value: 1
logic-not-delete-value: 0
