mybatisplus

1、selectById 根据主键查询一个对象，如果没有查到，则返回null：

GxySchoolDto isExist = gxySchoolMapper.selectById(schoolEntity.getSchoolId());

2、selectOne 根据 查询对象 queryWrapper 查询一个 对象， 没有查到则返回 null：

 QueryWrapper queryWrapper = new QueryWrapper();
            queryWrapper.eq("is_deleted",0);
            queryWrapper.eq("school_code",schoolEntity.getSchoolCode());
            GxySchoolDto isByCode = gxySchoolMapper.selectOne(queryWrapper);

 

@Mapper
public interface GxySchoolMapper extends BaseMapper<GxySchoolDto> {
}

@Data
@TableName("gxy_school")
public class GxySchoolDto extends BaseDto implements Serializable {
    private static final long serialVersionUID = 1L;


    /**
     * 唯一主键
     */
    @TableId(value = "school_id", type = IdType.UUID)
    private String schoolId;
    /**
     * 学校名称
     */
    private String schoolName;
    /**
     * 学校代码
     */
    private String schoolCode;

 

3.  list 查询列表：

QueryWrapper queryWrapper = new QueryWrapper();
queryWrapper.eq("plan_temp_id",planTemplateEntity.getPlanTemplateId());
queryWrapper.eq("is_deleted",0);

List<GxyPlanTempAppraiseItemDto> planTempAppraiseItemDtos = gxyPlanTempAppraiseItemService.list(queryWrapper);

对应的sql：
SELECT plan_temp_appraise_item_id,is_deleted,create_time,plan_temp_id,appraise_item_id,prop,is_point,item_name,snow_flake_id FROM gxy_plan_temp_appraise_item WHERE plan_temp_id = ? AND is_deleted = ? 
Parameters: de42308f1617a837f98ba2507ac58d87(String), 0(Integer)

4. SQL 语句：
SELECT school_id,school_name,is_deleted,school_code FROM gxy_school WHERE is_deleted=0 AND is_deleted = ? AND ( school_name = ? OR school_code = ? ) 
对应的java 代码：

   QueryWrapper<GxySchoolDto> queryWrapper = new QueryWrapper<>();
            queryWrapper.eq("is_deleted",0);
            queryWrapper.and(wrapper -> wrapper.eq("school_name",schoolEntity.getSchoolName()).or().eq("school_code",schoolEntity.getSchoolCode()));
            GxySchoolDto isByName = gxySchoolMapper.selectOne(queryWrapper);

 

5. in 语句：

        QueryWrapper<NoticeTodoDto> queryWrapper = new QueryWrapper();
        queryWrapper.eq("snow_flake_id",gxyTodoDto.getSnowFlakeId());
        queryWrapper.eq("object_id",gxyTodoDto.getObjectId());
        queryWrapper.in("state",1,2);
        NoticeTodoDto   todoDto=  noticeTodoMapper.selectOne(queryWrapper);
        return todoDto;

 

6. 刪除

接口：

@Mapper
public interface GxyPlanDepMapper extends BaseMapper<GxyPlanDepDto> {

}

Bean：

@Data
@TableName("gxy_plan_dep")
public class GxyPlanDepDto implements Serializable {
    private static final long serialVersionUID = 1L;

    private int isDeleted;
    /**
     * 创建时间
     */
    private Date createTime;
    /**
     * 唯一主键
     */
    @TableId(value = "plan_dep_id",type = IdType.UUID)
    private String planDepId;
    /**
     * 计划Id
     */
    private String planId;
    /**
     * 系部id
     */
    private String depId;
}

实现层：

planDepMapper.delete(new QueryWrapper<GxyPlanDepDto>().eq("plan_id",teacherStudentEntity.getPlanId()));

SQL：

DELETE FROM gxy_plan_dep
WHERE plan_id = ? ::: [a7637ddc0cafd26278ffc4be12262054]

 

7 修改

 GxyPlanTeacherStudentDto dto = new GxyPlanTeacherStudentDto();
                dto.setBackup(teacherStudentEntity.getPlanId());
                dto.setPlanId(teacherStudentEntity.getNewPlanId());
                dto.setTeacherId(teacherStudentDto.getTeacherId());
                dto.setJobId(teacherStudentDto.getJobId());
                dto.setCompanyName(teacherStudentDto.getCompanyName());

                QueryWrapper wrapper = new QueryWrapper<GxyPlanTeacherStudentDto>().eq("plan_teacher_student_id", teacherStudentDto.getPlanTeacherStudentId()).eq("snow_flake_id", teacherStudentEntity.getSnowFlakeId());
                planTeacherStudentMapper.update(dto,wrapper);

 根据  wrapper  的条件，查出记录， 修改 dto 中的字段。

当在 bean 中加了 @TableField(strategy= FieldStrategy.IGNORED) 注解，当 dto 中 该字段是null 时，update 修改该字段， 表中该字段也改成null 。 如果不加这个注解，当dto 中 该字段是null 时， update 语句不修改该字段。

@TableField(strategy= FieldStrategy.IGNORED)
private String jobId;

8: 分页查询

Page page = new Page(teacherStudentEntity.getCurrPage(),teacherStudentEntity.getPageSize());

        IPage<GxyMultiStudentPlanTmpDto> page1 =
        multiStudentPlanTmpMapper.selectPage(page, new QueryWrapper<GxyMultiStudentPlanTmpDto>()
                .eq("create_by",teacherStudentEntity.getUserId())
                .eq("is_deleted",0));

        List<GxyMultiStudentPlanTmpDto> list =    page1.getRecords();
        return ListResponse.resList(list,page1.getTotal());

 

9： mybatis  查询可以返回Set 或者List：

　　Mapper：

Set<String> selectMyStudent(@Param("params") GxyPlanTeacherStudentEntity planTeacherStudentEntity);

XML：

<select id="selectMyStudent" resultType="java.lang.String">
    SELECT pstu.student_id
   from gxy_plan_teacher_student AS pstu

    LEFT JOIN gxy_plan AS p ON pstu.plan_id = p.plan_id
    WHERE pstu.is_deleted = 0
    <choose>
        <when test="params.planId != null and params.planId != ''">
            AND pstu.plan_id = #{params.planId}
        </when>
        <otherwise>
            and p.plan_state = 1
        </otherwise>
    </choose>

    <if test="params.teacherId !='' and params.teacherId!=null">
        AND pstu.teacher_id = #{params.teacherId}
    </if>

<if test="params.position != null and params.position !=''">
    AND FIND_IN_SET(#{params.position},t.position)
</if>

</select>

AND position LIKE  concat('%',#{roleName},'%')



10. 修改 ， 根据 QueryWrapper 条件查询出所有的记录 后 修改 dto 实体中设置的字段 

　　　　　　MoguDeliverDto dto= new MoguDeliverDto();
        QueryWrapper queryWrapper = new QueryWrapper();
        queryWrapper.eq("user_id",deliver.getUserId());
        dto.setAcceptOffer(0);
        moguDeliverMapper.update(dto,queryWrapper);

UPDATE mogu_deliver SET accept_offer=?, modified_time=? WHERE is_deleted=0 AND user_id = ?

dto