mybatis
# 一、批量修改等
数据库连接地址后记得加上**&allowMultiQueries=true**不然会报错
# 批量插入
<insert id="batchInsertTbProject">
insert into
tb_project(
id,
name,
type,
easy_id,
parent_id
)
values
<foreach collection="batchInsertTbProjectList" item="insertTbProject" separator="," close=";">
(
#{insertTbProject.id},
#{insertTbProject.name},
#{insertTbProject.type},
#{insertTbProject.easyId},
#{insertTbProject.parentId}
)
</foreach>
</insert>
# 批量修改
<update id="batchUpdateTbProject">
<foreach collection="batchUpdateTbProjectList" item="batchUpdateTbProject" index="index" open="" close=""
separator=";">
update tb_project
<set>
<if test="batchUpdateTbProject.name != null">
name = #{batchUpdateTbProject.name},
</if>
<if test="batchUpdateTbProject.type != null">
type = #{batchUpdateTbProject.type},
</if>
<if test="batchUpdateTbProject.easyId != null">
easy_id = #{batchUpdateTbProject.easyId},
</if>
<if test="batchUpdateTbProject.parentId != null">
parent_id = #{batchUpdateTbProject.parentId},
</if>
</set>
where id = #{batchUpdateTbProject.id}
</foreach>
</update>
# 批量删除
<delete id="batchDeleteTbSyncBuild" parameterType="java.util.List">
DELETE FROM tb_sync_build WHERE build_id IN
<foreach item="item" collection="batchDeleteTbSyncBuildList" open="(" separator="," close=")">
#{item}
</foreach>
</delete>
<delete id="deleteBatchRelation">
DELETE FROM pms_attr_attrgroup_relation WHERE
<foreach collection="relationEntities" item="item" separator=" OR ">
(attr_id=#{item.attrId} AND attr_group_id=#{item.attrGroupId})
</foreach>
</delete>
# 二、MyBatis分页查询
public Msg getHourseName(TbSynHouseQuery query) {
String projectId = query.getProjectId();
QueryprojectdataPo queryprojectdataPo = queryProjectDateReadMapper.queryById(projectId);
if (queryprojectdataPo ==null) {
return Msg.error().message("projectId不存在!!");
}
projectId = queryprojectdataPo.getEasyid();
query.setProjectId(projectId);
// 分页,传入页码以及每页大小
PageHelper.startPage(query.getPage(),query.getSize());
// 房屋信息
List<TbSynHouseVo> hoursePoList = tbSyncHourseReadMapper.queryHouses(query);
// 使用PageInfo包装查询后的结果
com.github.pagehelper.PageInfo page = new com.github.pagehelper.PageInfo(hoursePoList);
List<TbSynHouseVo> houses = page.getList();
Map<String,Object> map = new HashMap<>();
long total = page.getTotal();// 总记录数
int pageNum = page.getPageNum();// 当前页
int pageSize = page.getPageSize();// 每页大小
int currentSize = page.getSize();// 当前页大小
map.put("total",total);
map.put("pageNum",pageNum);
map.put("pageSize",pageSize);
map.put("currentSize",currentSize);
map.put("houses",houses);
return Msg.ok().add(map);
}
注意: // 分页,传入页码以及每页大小 PageHelper.startPage(query.getPage(),query.getSize()); 执行完这句后后面需要紧跟查询语句否则分页不会起效
# 三、SQL知识点
# ①SQL删除重复数据
-- 查询出所有重复数据的唯一id(不包括最小的id)
SELECT id,name
FROM stu s
WHERE (s.name) in (SELECT name FROM stu GROUP BY name HAVING count(*) > 1)
AND id not in (SELECT min(id) FROM stu GROUP BY name HAVING count(*) > 1);
-- 删除重复数据
DELETE
FROM stu
WHERE
1=1
AND name in (SELECT * FROM (SELECT name FROM stu GROUP BY name HAVING count(*) > 1) as s1)
AND id not in (SELECT * FROM (SELECT min(id) id FROM stu GROUP BY name HAVING count(*) > 1) as s2);
-- 优化版
SELECT id,name
FROM stu s
WHERE
EXISTS
(SELECT s1.id FROM stu s1 JOIN (SELECT min(id) id,name FROM stu GROUP BY name HAVING count(*)>1) as s2
WHERE s1.name = s2.name AND s1.id <> s2.id AND s.id = s1.id)
DELETE s
FROM stu s
WHERE
EXISTS(SELECT s1.id FROM stu s1 JOIN (SELECT min(id) id,name FROM stu GROUP BY name HAVING count(*)>1) as s2
WHERE s1.name = s2.name AND s1.id <> s2.id and s.id = s1.id)
DELETE
FROM stu
WHERE
id in (SELECT * FROM (SELECT s1.id FROM stu s1 JOIN (SELECT min(id) id,name FROM stu GROUP BY name HAVING count(*)>1) as s2
WHERE s1.name = s2.name AND s1.id <> s2.id) a)
# ②MySQL提示1040 TooMany Connections
##当前修改只在当前进程有效重启mysql服务后失效。如需永久修改这需要修改配置文件
#重启mysql服务器
1.service mysqld restart
2.mysql -u root -p 回车输入密码进入mysql
#查看最大连接数
3.show variables like "max_connections";
#修改最大连接数
4.set GLOBAL max_connections=1000;
5.退出mysql服务,exit
编辑 (opens new window)
上次更新: 2024/06/12, 02:20:36