随着mybatisplus的广泛应用,mybatisplus确实比mybatis更加的方便,但是有时候根据业务的需求也要使用mybatis。
文章目录
检索sql查询数据库的数据条数
explain+sql语句
原文地址:https://blog.csdn.net/weixin_46152127/article/details/129925339
mybatis与mybatis-plus的结合写法
业务层写法:
QueryWrapper<SysCompany> wrapper = new QueryWrapper<>();
wrapper.and(StringUtils.isNotBlank(query.getKeyword()),
wra -> wra.like("company_id", query.getKeyword()).or().like("company_name", query.getKeyword()));
wrapper.eq(StringUtils.isNotEmpty(query.getCompanyType()), "company_type", query.getCompanyType());
wrapper.eq(StringUtils.isNotEmpty(query.getAuthStatus()), "auth_status", query.getAuthStatus());
wrapper.eq(query.getFrozen() != null, "frozen", query.getFrozen());
wrapper.orderByDesc("auth_status2","sc.gmt_modified");
IPage<ResCompany> page = sysCompanyMapper.selectCustomPage(new Page<>(pg.getCurrent(), pg.getSize()), wrapper);
return PageVO.to(page, ResCompany.class);
- List item
mapper层
IPage<ResCompany> selectCustomPage(@Param("pg") Page<ResCompany> pg, @Param("ew") QueryWrapper<SysCompany> wrapper);
这里注意一定需要使用ew
- List item
xml层
SELECT
*,
(SELECT member_id FROM sys_company_member_relation WHERE company_role = 0 AND company_id = sc.company_id limit 1) AS admin_account,
(SELECT COUNT(1) FROM sys_company_auth WHERE company_id = sc.company_id AND auth_status = '2') AS auth_status2
FROM sys_company sc
${ew.customSqlSegment}
like 查询
<if test="dto.commentContent != null and dto.commentContent !='' ">
and b.comment_content like concat('%',#{dto.commentContent},'%')
</if>
时间范围查询
<if test="dto.gmtStart != null and dto.gmtStart != ''">
AND a.gmt_sign<![CDATA[ < ]]> str_to_date(#{dto.gmtEnd},'%Y-%m-%d %H:%i:%s')
</if>
<if test="dto.gmtStart != null and dto.gmtStart != ''">
AND a.gmt_sign<![CDATA[ >= ]]> str_to_date(#{dto.gmtStart},'%Y-%m-%d %H:%i:%s')
</if>
<if test="startTime != null">
and gmt_create >= #{startTime}
</if>
<if test="endTime != null">
and gmt_create <= #{endTime}
</if>
if (ObjectUtils.allNotNull(gmtCreateStart, gmtCreateEnd)) {
wrapper.between(Conference::getGmtCreate, gmtCreateStart, gmtCreateEnd);
}
dto.activityTime >=c1.gmt_start ; dto.activityTime <= c1.gmt_end
<if test="dto.activityTime !=null and dto.activityTime !=''">
and (#{dto.activityTime} BETWEEN c1.gmt_start AND c1.gmt_end)
</if>
数量计算
根据group_id进分组计算人数 number
<select id="getGroupList" resultType="com.benwunet.ebc.pojo.vo.GroupMembersVO">
SELECT
group_id,
COUNT(*) as number
FROM
member_chat_person
WHERE group_id IN
<foreach collection="groupIds" item="id" open="(" separator="," close=")">
#{id}
</foreach>
group by group_id
</select>
软删除注解
mybatis-plus中软删除
/**
* 逻辑删除:0-未删除;1-已删除。
*
*/
@TableLogic(value = "0", delval = "1")
private Boolean isDeleted;
// 使用代码删除(这里使用mybtais-plus中的删除就可以了)
circleDynamicMapper.delete(new LambdaQueryWrapper<CircleDynamic>()
.eq(CircleDynamic::getGroupId, groupId)) > 0;
mybatis-plus 更新
// 使用sql的添加
conferenceMapper.update(null, new LambdaUpdateWrapper<Conference>()
.setSql("sign_num = sign_num + 1")
.eq(Conference::getConferenceId, conferenceId));
- mybatis-plus取消点赞
(当点赞错误的时候,点赞数不能为0)
this.update(null, new LambdaUpdateWrapper<SysCompanyDynamicComment>()
.setSql("like_num =IF( like_num - 1 >= 0, like_num - 1, 0 ) ")
.eq(SysCompanyDynamicComment::getId, commentId));
选择性查询
动态sql,如果不满足当前条件就执行另一句sql
对比示例if…else…
<choose>
<when test="dto.themeName !=null and dto.themeName !=''">
and c1.theme_name like concat('%',#{dto.themeName},'%')
</when>
<otherwise>
and c1.is_show = 1
</otherwise>
</choose>
mybatis-plus中进行sql排序
执行sql进行排序,更具排序字段升序排序,时间降序排序,排序字段为null的时候往后排序
Page<SysQuestion> page = this.page(new Page<>(pg.getCurrent(), pg.getSize()),
new LambdaQueryWrapper<SysQuestion>()
.like(StringUtils.isNotBlank(questionTitle), SysQuestion::getQuestionTitle, questionTitle)
.last("ORDER BY IF( ISNULL( sort ), 1, 0 ),sort ASC ,gmt_create DESC"));
sql拆分查询
例如数据库存的是
但是我需要查询 grade02,grade03只要包含其中一个就能查询出来
拆分位置grades 为一个list(List grades)
<select id="pageList" resultType="com.benwunet.bks.model.BksMaterials">
SELECT
*
FROM
bks_materials m
<where>
m.is_show =1
and m.is_delete =0
<if test="dto.materialsTagId != null and dto.materialsTagId != ''">
AND m.materials_tag_id =#{dto.materialsTagId}
</if>
<if test="dto.classifyId != null and dto.classifyId != ''">
AND m.classify_id =#{dto.classifyId}
</if>
<if test="dto.specialAreaType !=null and dto.specialAreaType != ''">
<if test="dto.specialAreaType ==0">
and m.price =0
</if>
<if test="dto.specialAreaType ==1">
and m.valuation=1 and m.vip_price =0
</if>
</if>
/*sql拆分*/
<if test="grades!=null and grades.size!=0">
AND (
<foreach item="item" index="index" collection="grades" separator="or">
<trim prefixOverrides="AND|OR" suffixOverrides="AND|OR">
or m.grade like concat('%',#{item},'%')
</trim>
</foreach>
)
</if>
</where>
Order by m.gmt_create DESC
</select>
执行结果sql打印的是
sql排序
sql排序根据指定中文字段排序
SELECT
a.company_id,
a.company_name
FROM
sys_company a
WHERE
a.company_name IN (
"青山控股集团有限公司",
"广西翅翼钢铁有限公司",
"四川吉瑞祥能源技术服务有限责任公司",
"蓝黛科技集团股份有限公司",
"京东方科技集团股份有限公司",
"重庆金世利航空材料有限公司",
"重庆巨能建设集团有限公司",
"泰山石膏有限公司",
"长城集团有限公司重庆分公司",
"台品重庆电子有限公司",
"重庆佳优达科技有限公司",
"重庆平洋工贸有限公司",
"艾森曼机械设备有限公司",
"冀东水泥重庆合川有限责任公司",
"浙商中拓集团物流科技公司",
"三亚亚龙湾开发股份有限公司",
"宁波金发新材料有限公司",
"重庆万汇注册安全工程师事务所有限公司",
"深圳市康哲药业有限公司",
"福建大东海实业集团有限公司",
"重庆高远物业管理有限公司",
"重庆集成自动化系统有限责任公司",
"四川德胜集团钒钛有限公司",
"重庆苏宁易购销售公司",
"山东立鼎石油科技有限公司",
"宁波华泰盛富聚合材料有限公司",
"重庆星亚文化传媒有限公司"
)
ORDER BY
FIELD(
a.company_name,
"青山控股集团有限公司",
"广西翅翼钢铁有限公司",
"四川吉瑞祥能源技术服务有限责任公司",
"蓝黛科技集团股份有限公司",
"京东方科技集团股份有限公司",
"重庆金世利航空材料有限公司",
"重庆巨能建设集团有限公司",
"泰山石膏有限公司",
"长城集团有限公司重庆分公司",
"台品重庆电子有限公司",
"重庆佳优达科技有限公司",
"重庆平洋工贸有限公司",
"艾森曼机械设备有限公司",
"冀东水泥重庆合川有限责任公司",
"浙商中拓集团物流科技公司",
"三亚亚龙湾开发股份有限公司",
"宁波金发新材料有限公司",
"重庆万汇注册安全工程师事务所有限公司",
"深圳市康哲药业有限公司",
"福建大东海实业集团有限公司",
"重庆高远物业管理有限公司",
"重庆集成自动化系统有限责任公司",
"四川德胜集团钒钛有限公司",
"重庆苏宁易购销售公司",
"山东立鼎石油科技有限公司",
"宁波华泰盛富聚合材料有限公司",
"重庆星亚文化传媒有限公司"
)
查询结果
FIELD函数根据了指定的中文进行了排序
sql中对null进行排序
根据时间和headImgUrl排序
示例一:
SELECT
*
FROM
app_user a
WHERE
1 = 1
ORDER BY
IF
( ISNULL( headImgUrl ), 0, 1 ),
createTime
查询结果
(headImgUrl为null排在了前面)
示例二:
SELECT
*
FROM
app_user a
WHERE
1 = 1
ORDER BY
IF
( ISNULL( headImgUrl ), 2, 1 ),
createTime
查询结果
(headImgUrl为null排在了后面)
SELECT
*
FROM
app_user a
WHERE
1 = 1
ORDER BY
IF
( ISNULL( headImgUrl ), 2, 1 ),
createTime
sql统计并分组
查询试卷包大于1的考试信息
SELECT
a.bag_code,
count(*)
FROM
pt_exam_activity a
WHERE
1 = 1
GROUP BY
a.bag_code
HAVING
count(*) > 1
sql统计分组并排序
SELECT
a.visitor_id AS cardId,
a.company_id,
b.real_name,
b.avatar,
b.gender,
COUNT(*) AS viewNum
FROM
member_behavior_log a
LEFT JOIN member_info b ON a.visitor_id = b.member_id
WHERE
a.member_id = "memb0000000006"
AND a.company_id = "QY0000000004"
AND a.member_type = 1
AND a.statistic_type = 1
GROUP BY
a.member_id,
a.visitor_id
ORDER BY
viewNum Desc
sql 统计分组并按时间排序
SELECT
a.visitor_id AS cardId,
a.company_id,
b.real_name,
b.avatar,
b.gender,
COUNT(*) AS viewNum,
MAX( a.gmt_create ) AS gmtCreate
FROM
member_behavior_log a
LEFT JOIN member_info b ON a.visitor_id = b.member_id
WHERE
a.member_id = "memb0000000006"
AND a.company_id = "QY0000000004"
AND a.member_type = 1
AND a.statistic_type = 1
GROUP BY
a.member_id,
a.visitor_id
ORDER BY
gmtCreate Desc
查询最新的一条数据
mybatis-plus
memberCardExchangeRequestMapper.selectOne(new LambdaQueryWrapper<MemberCardExchangeRequest>()
.eq(MemberCardExchangeRequest::getReceiverMemberId, cardId).eq(MemberCardExchangeRequest::getIsDelete, 0)
.eq(MemberCardExchangeRequest::getSenderMemberId, userId).orderByDesc(BaseEntity::getGmtModified).last("limit 1"));
sql
SELECT
sender_member_id,
receiver_member_id,
FROM
member_card_exchange_request
WHERE
( receiver_member_id = '1234567897' AND is_delete = 0 AND sender_member_id = '4567897987' )
ORDER BY
gmt_modified DESC
LIMIT 0,1;
EXISTS 或 NOT EXISTS 代替 in与 not in
select * from test1
where EXISTS (select * from test2 where id2 = id1 )
select * FROM test1
where NOT EXISTS (select * from test2 where id2 = id1 )
那我们死活都不能用 IN 和 NOT IN 了么?并没有,一位大神曾经说过,如果是确定且有限的集合时,可以使用。如 IN (0,1,2)。