一、批量查找：
1、参数是一个
List<T> getList(@Param("param") String param);
<select id="getList" parameterType="java.lang.String"
resultType="com.xxx.entity.T">
SELECT
ve.name name,
ve.age age
FROM
user ve
WHERE
DATE_FORMAT(ve.update_time, '%Y-%m-%d') = DATE_FORMAT(#{param}, '%Y-%m-%d')
AND ve.delete_tag = 0
</select>
2、参数是list
List<T> getList(@Param("list") List<T> list);
<select id="getList" parameterType="list"
resultType="com.xxx.entity.T">
SELECT
t.id,
t.name,
t.age,
m.empl
FROM
user t
LEFT JOIN dept m ON t.uid = m.id
WHERE
t.isDelete = 0
AND m.name IS NOT NULL
AND (
t.state = 4
OR t.state = 2
)
AND (
<foreach item="item" index="index" collection="list" separator="OR">
m.name = #{item.name}
AND date_format(#{item.birth}, '%Y-%m-%d') <![CDATA[ >= ]]> date_format(t.startBirth,'%Y-%m-%d')
AND date_format(#{item.birth}, '%Y-%m-%d') <![CDATA[ <= ]]> date_format(t.endBirth, '%Y-%m-%d')
</foreach>
)
</select>
3、参数是对应属性（list）
List<T> selectList(UserRequest req);
<select id="selectList" resultMap="BaseResultMap" parameterType="com.xxx.UserRequest">
select
name,
age
from user iu
where iu.delete_tag = 0
<if test="idList != null and idList.size() > 0">
and iu.id in
<foreach collection="idList" index="index" item="id"
open="(" separator="," close=")">
#{id}
</foreach>
</if>
</select>
4、参数是对象多个列表
List<T> selectByName(User user);
<select id="selectByName" parameterType="com.xxx.entity.User" resultType="com.xxx.entity.User">
select *,CONCAT(name,'(',code,')') nameCode
from user
where
isDelete=0
<if test="labelOrgModel.orgTypeLabelList != null and labelOrgModel.orgTypeLabelList.size() > 0">
and (
<foreach item="item" index="index" collection="labelOrgModel.orgTypeLabelList" separator="OR">
deptNo like
CONCAT('%',#{item.labelCode},'%')
</foreach>
)
</if>
<if test="labelOrgModel.locationLabelList != null and labelOrgModel.locationLabelList.size() > 0">
and (
<foreach item="item" index="index" collection="labelOrgModel.locationLabelList" separator="OR">
workAddrNo like
CONCAT('%',#{item.labelCode},'%')
</foreach>
)
</if>
ORDER BY code ASC
</select>
// 参数model
public class LabelOrgModel {
// 标签：部门类别
private List<IamLabelDto> orgTypeLabelList;
// 标签：工作地点
private List<IamLabelDto> locationLabelList;
}
二、批量更新
1、更新一个字段
int batchUpdate(@Param("list") List<T> list);
<update id="batchUpdate" parameterType="java.util.List">
update user
<trim prefix="set" suffixOverrides=",">
<trim prefix="updateTime =case" suffix="end,">
<foreach collection="list" item="i" index="index">
when id=#{i.id} then (SELECT NOW() from dual)
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i" index="index">
id=#{i.id}
</foreach>
</update>
2、更新多个字段
int batchUpdate(@Param("list") List<T> list);
<update id="batchUpdate" parameterType="java.util.List">
update user
<trim prefix="set" suffixOverrides=",">
<trim prefix="name =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.name != null">
when id=#{i.id} then #{i.name}
</if>
</foreach>
</trim>
<trim prefix="age =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.age != null">
when id=#{i.id} then #{i.age}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i" index="index">
id=#{i.id}
</foreach>
</update>
3、参数是多个
int updateStatus(@Param("list") List<String> list, @Param("status")Integer status);
<update id="updateStatus">
UPDATE user SET status = #{status}
where
<if test="list != null and list.size() > 0">
name in
<foreach collection="list" index="index" item="name" open="(" separator="," close=")">
#{name}
</foreach>
</if>
</update>
三、删除重复项
UPDATE user
SET delete_tag = 1
WHERE
(name,birth,age) IN (
SELECT t.name,t.birth,t.age
FROM (SELECT name,birth,age FROM user WHERE delete_tag = 0 AND code != '003' GROUP BY name,DATE_FORMAT(birth,'%Y'),age
HAVING count(1) > 1) t)
AND id NOT IN (
SELECT dt.id FROM
(SELECT min(id) AS id FROM user GROUP BY name,DATE_FORMAT(birth,'%Y'),age HAVING count(1) > 1) dt
);
UPDATE user
SET delete_tag = 1
WHERE
(name,age) IN (
SELECT t.name,t.age
FROM (SELECT name,age FROM user WHERE delete_tag = 0 AND code = '003' GROUP BY name,age
HAVING count(1) > 1) t)
AND id NOT IN (
SELECT dt.id FROM
(SELECT min(id) AS id FROM user GROUP BY name,age HAVING count(1) > 1) dt
);
四、批量插入
1、批量插入
int batchInsertImportedDetail(List<T> listImportedDetail);
<insert id="batchInsertImportedDetail">
insert into user (
name,
id,
dept_name
age
)
values
<foreach collection="list" item="ImportedDetail" separator="," index="index">
(
#{ImportedDetail.name,jdbcType=VARCHAR},
(select replace(uuid(), '-', '') as trans_no from dual),
(SELECT id from dept where dept_name=#{ImportedDetail.deptName} and is_del = 1 limit 1),
#{ImportedDetail.age,jdbcType=INTEGER}
)
</foreach>
</insert>
2、插入时候校验
int insertRecordsData(User user);
<insert id="insertRecordsData" parameterType="com.xxx.entity.User">
insert into user (
name,age,birth
)
SELECT
#{name,jdbcType=VARCHAR},
#{age,jdbcType=INTEGER},
#{birth,jdbcType=TIMESTAMP}
FROM DUAL WHERE NOT EXISTS(
SELECT name,age,birth
FROM user
WHERE name = #{name}
and age = #{age}
and birth = #{birth}
)
</insert>
public List<User> insertUserList(List<User> user) {
//List 需要导入的数据
int count = 1000;//每批次导入的数目
int insertLength = users.size();
List<User> errorList = new ArrayList<>();
int i = 0;
while (insertLength > count) {
try {
ipo.insertUsers(users.subList(i, i + count));
} catch (Exception e) {
errorList.addAll(users.subList(i, i + count));
log.error("导入数据失败2！" + e);
log.error("错误信息！" + e.getMessage());
}
i = i + count;
insertLength = insertLength - count;
}
if (insertLength > 0) {
try {
ipo.insertUsers(users.subList(i, i + insertLength));
} catch (Exception e) {
errorList.addAll(users.subList(i, i + count));
log.error("导入数据失败3！");
}
}
return errorList;
}
