MyBatis常用代码集

一、MyBatis按条件自增

    public Boolean incr(Long attendId, Long memberId, Integer week, String bulletkingId, Integer incr) {
        LambdaUpdateWrapper<H5ActivityAttendBulletking> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(H5ActivityAttendBulletking::getAttendId, attendId);
        updateWrapper.eq(H5ActivityAttendBulletking::getMemberId, memberId);
        updateWrapper.eq(H5ActivityAttendBulletking::getWeek, week);
        updateWrapper.eq(H5ActivityAttendBulletking::getBulletkingId, bulletkingId);
        updateWrapper.setSql("liked_count = liked_count + " + incr);
        return super.update(updateWrapper);
    }

二、MyBatis修改

    public Boolean updateStudentCart(Long id, String cardUrl) {
        LambdaUpdateWrapper<H5ActivityAttend> updateWrapper = new LambdaUpdateWrapper<>();
        updateWrapper.eq(H5ActivityAttend::getId, id).set(H5ActivityAttend::getStudentIdCard,cardUrl);
        return super.update(updateWrapper);
    }

三、按:年-月查(2021-12)

 and DATE_FORMAT(attend_date,"%Y-%m")=#{attendMonth}

四、按：年-月-日查（2021-12-21）

 and DATE_FORMAT(pen_check_time,""%Y-%m-%d"")=#{vo.penCheckTime}

六、排序、取有一条数据

    public LuckySubIntegralLog getLastLucyDrawRecord(Long memberId, Long activityId) {
        LambdaQueryWrapper<LuckySubIntegralLog> wrapper = new LambdaQueryWrapper();
        wrapper.eq(LuckySubIntegralLog::getMemberId, memberId);
        wrapper.eq(LuckySubIntegralLog::getRelationId, activityId);
        wrapper.orderByDesc(LuckySubIntegralLog::getCreateTime);
        wrapper.last("limit 1");
        return mapper.selectOne(wrapper);
    }

七、根据参数删除

   public boolean deletedByAdvertId(Long advertId) {
        LambdaQueryWrapper<AdvertScope> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(AdvertScope::getAdvertId, advertId);
        return mapper.delete(queryWrapper) > 0;
    }

八、Service继承mybatisplus-IService，继承增删改查功能

import com.baomidou.mybatisplus.extension.service.IService;

/**
 * 广告可见范围Service
 */
public interface AdvertScopeService extends IService<AdvertScope> {
    
}

九、Mapper继承mybatisplus-BaseMapper，继承增删改查功能

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

/**
 * 广告可见范围 Mapper 接口
 */
public interface AdvertScopeMapper extends BaseMapper<AdvertScope> {
    
}

十、前缀模糊查询

private List<OssMember> listMembers(){
        LambdaQueryWrapper<OssMember> wrapper = new LambdaQueryWrapper();
        wrapper.apply(" mobile not like {0}", "122%");
        wrapper.apply(" mobile not like {0}", "123%");
        return this.list(wrapper);
    }

生成SQL

SELECT id,mobile,icon_url,`deleted`,create_time,modify_time FROM t_mem_member WHERE `deleted`=0 AND ( mobile not like '122%' AND mobile not like '123%') 

十一、前后模糊查询

private List<OssMember> listMembers(){
        LambdaQueryWrapper<OssMember> wrapper = new LambdaQueryWrapper();
        wrapper.notLike(OssMember::getMobile, "122");
        wrapper.notLike(OssMember::getMobile, "123");
        return this.list(wrapper);
    }

生成SQL

SELECT id,mobile,icon_url,`deleted`,create_time,modify_time FROM t_mem_member WHERE `deleted`=0 AND ( mobile not like '%122%' AND mobile not like '%123%') 

十二、条件值判断

         <if test="vo.activityType!=null">
                <if test="vo.activityType==1">
                    and m.can_lucky_award=1 and m.can_exchange=1
                </if>
                <if test="vo.activityType==2">
                    and m.can_lucky_award=1 
                </if>
                <if test="vo.activityType==3">
                     and m.can_exchange=1
                </if>
            </if>

十三、mybatis-plus配置控制台打印完整带参数SQL语句

#mybatis-plus配置控制台打印完整带参数SQL语句
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

十四、大于等于、小于等于

select * from t_user  where 
   create_date <![CDATA[ >= ]]> #{beginDate}
   and create_date <![CDATA[ <= ]]> #{endDate}