1. mybatis sql查询返回Map
https://blog.csdn.net/m0_52789121/article/details/126462541
案例1: @MapKey(“k_code”) Map<String,Map>
/**
* 根据appId集合统计运行端应用空间数--kCode维度
*
* @return
*/
@SqlParser(filter = true)
@MapKey("k_code")
Map<String, Map> statEngineRuntimeData(@Param("map") Map<String, Object> param);
<select id="statEngineRuntimeData" parameterType="map" resultType="map">
SELECT
a.`k_code` as k_code,
COUNT(DISTINCT a.`k_code`) as count
FROM `engine_runtime_data` AS a
INNER JOIN `app_kcode` AS b
ON b.`is_delete` = 0
AND b.`k_code` = a.`k_code`
<if test="map.allRealAppIdList != null and map.allRealAppIdList.size > 0">
AND b.`app_id` in
<foreach collection="map.allRealAppIdList" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<if test="map.searchAppIdList != null and map.searchAppIdList.size > 0">
AND b.`app_id` in
<foreach collection="map.searchAppIdList" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
WHERE a.`is_delete` = 0
<if test="map.start != null and map.end != null and map.start != '' and map.end != '' ">
AND a.`gmt_create` BETWEEN #{map.start} AND #{map.end}
</if>
<if test="(map.end == null or map.end == '') and (map.start != null and map.start != '') ">
AND a.`gmt_create` <![CDATA[ < ]]> #{map.start}
</if>
GROUP BY a.`k_code`
</select>
方法使用
Map<String, Map> preCountMap = appKcodeMapper.statEngineRuntimeData(param);
int preCount = 0;
if (MapUtils.isNotEmpty(preCountMap)) {
preCount = preCountMap.values().stream().map(item ->
Integer.valueOf(String.valueOf(item.get("count"))))
.reduce(0, Integer::sum);
}
Set<String> preCountMapKeyList = preCountMap.keySet();
Map<String, Map> currentCountMap = appKcodeMapper.statEngineRuntimeData(param);
int currentCount = 0;
if (MapUtils.isNotEmpty(currentCountMap)) {
for (Map.Entry<String, Map> entry : currentCountMap.entrySet()) {
// 去除之前重复的数据
if (!preCountMapKeyList.contains(entry.getKey())) {
currentCount += Integer.valueOf(String.valueOf(entry.getValue().get("count")));
}
}
}
案例2: @MapKey(“app_id”) Map<Long, Map> 同案例1
/**
* 分组统计业务物料数量
*
* @return
*/
@SqlParser(filter = true)
@MapKey("app_id")
Map<Long, Map> statMaterialInfoGroupCount(@Param("map") Map<String, Object> param);
<select id="statMaterialInfoGroupCount" parameterType="map" resultType="map">
SELECT
a.`app_id` as app_id,COUNT(DISTINCT a.`identifier`) AS count
FROM `material_info` AS a
WHERE a.`is_delete` = 0
<if test="map.allRealAppIdList != null and map.allRealAppIdList.size > 0">
AND a.`app_id` in
<foreach collection="map.allRealAppIdList" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
AND a.`identifier` NOT IN
(SELECT b.`key` FROM kuai_console.init_material AS b WHERE b.`is_delete` = 0)
<if test="map.start != null and map.end != null and map.start != '' and map.end != '' ">
AND a.`gmt_create` BETWEEN #{map.start} AND #{map.end}
</if>
<if test="(map.end == null or map.end == '') and (map.start != null and map.start != '') ">
AND a.`gmt_create` <![CDATA[ < ]]> #{map.start}
</if>
<!-- 去除掉已经删除的分支所属的物料数据-->
AND
a.`branch_id` IN (-1,
( SELECT
IF(b.`is_delete` = 0,b.id,0) AS branch_id
FROM `branch` AS b
WHERE b.id = a.`branch_id`
)
)
<!-- AND-->
<!-- (-->
<!-- (a.`branch_id` = -1)-->
<!-- OR-->
<!-- (-->
<!-- a.`branch_id` IN-->
<!-- (-->
<!-- SELECT-->
<!-- IF(b.`is_delete` = 0,b.id,0) AS branch_id-->
<!-- FROM `branch` AS b-->
<!-- WHERE b.id = a.`branch_id`-->
<!-- )-->
<!-- )-->
<!-- )-->
GROUP BY a.`app_id`
</select>
方法调用
// 分组统计业务物料数量
Map<Long, Map> businessMaterialCountMap = materialInfoMapper.statMaterialInfoGroupCount(param);
if (CollectionUtils.isNotEmpty(preList)) {
List<Long> appIdList = preList.stream().map(StatRealApp::getAppId).collect(Collectors.toList());
List<Long> dbAppIdList = getDbAppIdList(appIdList);
int dbAppIdListSize = dbAppIdList.size();
int preSize = preList.size();
preList.stream().forEach(item -> {
Long appId = item.getAppId();
Long businessMaterialCount = businessMaterialCountMap.get(appId) != null ? (Long) businessMaterialCountMap.get(appId).get("count") : 0L;
item.setBusinessMaterialCount(Integer.valueOf(businessMaterialCount.toString()));
});
}
案例3: List<Map<String,Object>>
/**
* 四套环境发布次数--分组统计
* @param param
* @return
*/
List<Map<String,Object>> statVersionByEnv(@Param("map") Map<String, Object> param);
<select id="statVersionByEnv" parameterType="map" resultType="java.util.HashMap">
select
b.app_id as app_id,
b.env as env,
count(b.id) as count
from assembler.app_version b
inner join app a on a.is_delete =0 and a.id = b.app_id
where b.is_delete =0
<if test="map.allRealAppIdList != null and map.allRealAppIdList.size > 0">
AND b.`app_id` in
<foreach collection="map.allRealAppIdList" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<if test="map.start != null and map.end != null and map.start != '' and map.end != '' ">
AND b.`gmt_create` BETWEEN #{map.start} AND #{map.end}
</if>
<if test="(map.end == null or map.end == '') and (map.start != null and map.start != '') ">
AND b.`gmt_create` <![CDATA[ < ]]> #{map.start}
</if>
<if test="map.searchAppIdList != null and map.searchAppIdList.size > 0">
AND b.`app_id` in
<foreach collection="map.searchAppIdList" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</if>
<!-- 去除掉已经删除的分支所属的数据-->
<!-- AND-->
<!-- b.`branch_id` IN (-1,-->
<!-- ( SELECT-->
<!-- IF(b1.`is_delete` = 0,b1.id,0) AS branch_id-->
<!-- FROM `branch` AS b1-->
<!-- WHERE b1.id = b.`branch_id`-->
<!-- )-->
<!-- )-->
group by b.`app_id`,b.env
</select>
方法使用
List<Map<String, Object>> currentCountMapList = appVersionMapper.statVersionByEnv(param);
if (CollectionUtils.isNotEmpty(currentCountMapList)) {
for (Map<String, Object> map : currentCountMapList) {
String env = (String) map.get("env");
long count = (Long) map.get("count");
}
}
接口返回数据
数据库数据
2. Mybatis批量更新
2.1 batchUpdate 多个字段
serviceImpl
List<DataField> dataFieldList = selectList(wrapper);
if (CollectionUtils.isNotEmpty(dataFieldList)) {
int initialValue = 1;
if (null != newDataFieldId) {
initialValue = 2;
}
AtomicInteger sort = new AtomicInteger(initialValue);
dataFieldList.stream().forEach(item -> {
DataField dataField = new DataField();
dataField.setId(item.getId());
dataField.setName(item.getName());
dataField.setGmtModified(item.getGmtModified());
dataField.setSortNum(sort.getAndIncrement());
dataFieldIdSortList.add(dataField);
});
}
// 更新
boolean flag = true;
if (CollectionUtils.isNotEmpty(dataFieldIdSortList)) {
flag = baseMapper.updateSort(dataFieldIdSortList);
}
mapper
public interface DataFieldMapper extends BaseMapper<DataField> {
/**
* 批量排序
* @param list
* @return
*/
boolean updateSort(List<DataField> list);
}
xml
<update id="updateSort">
update `data_field`
<trim prefix="set" suffixOverrides=",">
<trim prefix=" `sort_num` = case " suffix=" end, ">
<foreach collection="list" item="item">
<if test="item.sortNum != null">
when id = #{item.id} then #{item.sortNum}
</if>
</foreach>
</trim>
<trim prefix=" `gmt_modified` = case " suffix=" end, ">
<foreach collection="list" item="item">
<if test="item.gmtModified != null">
when id = #{item.id} then #{item.gmtModified}
</if>
</foreach>
</trim>
</trim>
where is_delete = 0
and id in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item.id}
</foreach>
</update>
sql
UPDATE
`data_field`
SET
`sort_num` =
CASE
WHEN id = 1774708821746188290
THEN 1
WHEN id = 1774708790284713985
THEN 2
WHEN id = 1774708750107475970
THEN 3
WHEN id = 1774708715722571777
THEN 4
WHEN id = 1774708677101420546
THEN 5
WHEN id = 1774708203107319809
THEN 6
WHEN id = 1774708203124097025
THEN 7
WHEN id = 1774708203195400193
THEN 8
WHEN id = 1774708203031822337
THEN 9
WHEN id = 1774708203065376769
THEN 10
WHEN id = 1774708203077959682
THEN 11
WHEN id = 1774708203094736898
THEN 12
WHEN id = 1774708203048599554
THEN 13
WHEN id = 1774708203182817282
THEN 14
END,
`gmt_modified` =
CASE
WHEN id = 1774708821746188290
THEN '2024-04-01 16:02:10.0'
WHEN id = 1774708790284713985
THEN '2024-04-01 16:02:03.0'
WHEN id = 1774708750107475970
THEN '2024-04-01 16:01:53.0'
WHEN id = 1774708715722571777
THEN '2024-04-01 16:01:45.0'
WHEN id = 1774708677101420546
THEN '2024-04-01 16:01:36.0'
WHEN id = 1774708203107319809
THEN '2024-04-01 15:59:43.0'
WHEN id = 1774708203124097025
THEN '2024-04-01 15:59:43.0'
WHEN id = 1774708203195400193
THEN '2024-04-01 15:59:43.0'
WHEN id = 1774708203031822337
THEN '2024-04-01 15:59:43.0'
WHEN id = 1774708203065376769
THEN '2024-04-01 15:59:43.0'
WHEN id = 1774708203077959682
THEN '2024-04-01 15:59:43.0'
WHEN id = 1774708203094736898
THEN '2024-04-01 15:59:43.0'
WHEN id = 1774708203048599554
THEN '2024-04-01 15:59:43.0'
WHEN id = 1774708203182817282
THEN '2024-04-01 15:59:43.0'
END
WHERE is_delete = 0
AND id IN (
1774708821746188290,
1774708790284713985,
1774708750107475970,
1774708715722571777,
1774708677101420546,
1774708203107319809,
1774708203124097025,
1774708203195400193,
1774708203031822337,
1774708203065376769,
1774708203077959682,
1774708203094736898,
1774708203048599554,
1774708203182817282
)
2.2 batchUpdate 嵌套查询id
错误信息
Query: update msg_detail set `is_read` = 1 where id in ( SELECT a.id FROM msg_detail AS a INNER JOIN msg_info AS b ON b.`is_delete` = 0...
Error Code: 1093
You can't specify target table 'msg_detail' for update in FROM clause
参考
https://blog.csdn.net/lvoelife/article/details/130225971
错误sql
错误:
UPDATE msg_detail
SET `is_read` = 1
WHERE id IN
(
SELECT a.id
FROM msg_detail AS a
INNER JOIN msg_info AS b
ON b.`is_delete` = 0
AND b.id = a.`msg_info_id`
INNER JOIN `msg_second_type` AS c
ON c.`is_delete` = 0
AND c.`id` = b.`msg_second_type_id`
AND c.`msg_first_type_id` = 3
WHERE a.`is_delete` = 0
AND a.`is_read` = 0
AND a.`user_code` = '20004855'
)
正确sql
正确:
UPDATE msg_detail
SET `is_read` = 1
WHERE id IN
(
SELECT id
FROM(
SELECT a.id
FROM msg_detail AS a
INNER JOIN msg_info AS b
ON b.`is_delete` = 0
AND b.id = a.`msg_info_id`
INNER JOIN `msg_second_type` AS c
ON c.`is_delete` = 0
AND c.`id` = b.`msg_second_type_id`
AND c.`msg_first_type_id` = 3
WHERE a.`is_delete` = 0
AND a.`is_read` = 0
AND a.`user_code` = '20004855'
) AS tmp
)
serviceImpl
@Override
public void clickMsg4AllSecondType(Long msgFirstTypeId){
String userCode = UserUtil.getLoginUser().getUserCode();
baseMapper.clickMsg4AllSecondType(userCode, msgFirstTypeId);
}
mapper
void clickMsg4AllSecondType(@Param("userCode") String userCode, @Param("msgFirstTypeId") Long msgFirstTypeId);
xml
<update id="clickMsg4AllSecondType">
UPDATE msg_detail
SET `is_click` = 1
WHERE id IN
(
SELECT id
FROM
(
SELECT a.id
FROM msg_detail AS a
INNER JOIN msg_info AS b
ON b.`is_delete` = 0
AND b.id = a.`msg_info_id`
INNER JOIN `msg_second_type` AS c
ON c.`is_delete` = 0
AND c.`id` = b.`msg_second_type_id`
AND c.`msg_first_type_id` = #{msgFirstTypeId}
WHERE a.`is_delete` = 0
AND a.`is_click` = 0
AND a.`user_code` = #{userCode}
) AS tmp
)
</update>
3. 批量插入
mapper
void batchInsert(List<MsgDetail> list);
xml
<insert id="batchInsert">
insert into msg_detail
(`id`, `msg_info_id`, `user_code`, `rid`, `is_read`,
`is_delete`, `gmt_create`,`gmt_modified`, `created_by`, `modified_by`, `created_name`, `modified_name`)
values
<foreach collection="list" item="item" separator=",">
(
#{item.id},
#{item.msgInfoId},
#{item.userCode},
#{item.rid},
#{item.isRead},
0,#{item.gmtCreate},#{item.gmtModified},#{item.createdBy},#{item.modifiedBy},#{item.createdName},#{item.modifiedName}
)
</foreach>
</insert>