mybatis 批量操作大全

原创已于 2022-08-18 22:56:52 修改 · 420 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #java #mysql

于 2022-08-04 17:36:03 首次发布

java程序实现专栏收录该内容

20 篇文章

订阅专栏

本文概述了批量插入、查询、更新和删除操作在数据库中的应用，涉及SQL语句，包括分组排序、使用foreach遍历和参数化查询。讨论了针对不同场景的Java和前端操作，如班级学生筛选和文章分类更新。

批量插入

<!--批量插入-->
 	<insert id="addBatch">
		INSERT INTO sys_file_extra_public (file_extra_id, dept_id)
		VALUES
		<foreach collection="publicDeptIdList" item="deptId" separator=",">
			(#{id}, #{deptId})
		</foreach>
	</insert>

List classIdList = classIds.split(“,”);
Student page(@Param(“classIdList”) List classIdList);

<!--批量插入-->
 	<insert id="addBatch">
		SELECT *
		FROM
			stu_table
		WHERE
			class_id
		IN
		<foreach collection="classIdList" item="classId" open="(" separator="," close=")">
			#{classId}
		</foreach>
		-- classIdList=[1,2,3]
		-- ( 1，2，3 )
	</insert>

Student page(@Param(“classIds” String classIds); // classIds = “1,2,3”;’

<!--批量插入-->
 	<insert id="addBatch">
		SELECT *
		FROM
			stu_table
		WHERE
			class_id
		FINd_IN_SET #{classIds}
		ORDER BY
			stu_table.name ASC, stu_table.create_time DESC
	</insert>

Set classIdList = classIds.split(“,”);

根据id列表批量更新单个字段

1 4
1 5
1 6
2 2
2 5
2 6
Student page(@Param(“categoryId”) Long categoryId, @Param(“articleIdList”) List articleIdList);

<!--批量修改文章专栏-->
	<update id="updateCategoryByIds">
		UPDATE cms_article SET category_id = #{categoryId} WHERE id IN
		<foreach collection="articleIdList" item="item" open="(" separator="," close=")">
		  #{item}
		</foreach>
</update>

批量更新对象列表

<!--批量更新-->
    <update id="updateBatch">
		<foreach collection="appsCategoryList" item="item" open="" separator=";" close="">
			UPDATE cms_category
			<set>
				parent_ids = #{item.parentIds},
			  update_by = #{item.updateBy}
			</set>
			WHERE id = #{item.id}
		</foreach>
	</update>

批量删除

<delete id="batchDelete" parameterType="_int[]">
	delete from products where productId IN
	<foreach collection="array" open="(" close=")" separator="," item="id">
		#{id}
	</foreach>
</delete>

先分组后排序

		GROUP BY
				t1.id
			ORDER BY
			    t1.sort_order DESC,
			    t1.create_time DESC

1  1
1  3
2  2

1  1
2  2
1  3

1  1
1  3
2  2