pgsql -- 批量插入

最新推荐文章于 2025-11-02 08:53:05 发布

原创最新推荐文章于 2025-11-02 08:53:05 发布 · 3.8k 阅读

5 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #pgsql

mybatis 同时被 2 个专栏收录

20 篇文章

订阅专栏

pgsql

19 篇文章

订阅专栏

本文介绍了使用Mybatis在PgSQL中进行批量插入数据的方法，包括通过Set集合、List集合和Map进行操作。详细展示了不同场景下的DAO层代码和SQL语句，参照了pgsql官方文档。

工具：mybatis+pgsql

详见pgsql官方文档

案例1 – Set集合

dao层

void insert(Set<User> userSet);

sql

<insert id = "insert" parameterType = "java.util.Set">
	insert into user(username,address,sex,create_date)
	values
	<foreach collection="collection" separator="," item="val">
	(#{val.username}, #{val.address},#{val.sex},now())
	</foreach>
</insert>

案例2 – List集合

dao层

void insert(List<User> userList);

sql

<insert id = "insert" parameterType="java.util.List">
	insert into user(username,address,sex,create_date)
	values
	<foreach collection="list" separator="," item="val">
	(#{val.username}, #{val.address},#{val.sex},now())
	</foreach>
</insert>

sql的parameterType也可以省略不写，如下案例

dao层

void insert(List<User> userList);

sql

<insert id = "insert">
	insert into user(username,address,sex,create_date)
	values
	<foreach collection="list" separator="," item="val">
	(#{val.username}, #{val.address},#{val.sex},now())
	</foreach>
</insert>

sql的collection值默认是collection/list，如下案例

dao层

void insert(List<User> userList);

sql

<insert id = "insert">
	insert into user(username,address,sex,create_date)
	values
	<foreach collection="collection" separator="," item="val">
	(#{val.username}, #{val.address},#{val.sex},now())
	</foreach>
</insert>

sql

<insert id = "insert">
	insert into user(username,address,sex,create_date)
	values
	<foreach collection="list" separator="," item="val">
	(#{val.username}, #{val.address},#{val.sex},now())
	</foreach>
</insert>

sql的collection值也可以自定义，通过@Param注解实现。如下案例

dao层

void insert(@Param("userList")List<User> userList);

sql

<insert id = "insert">
	insert into user(username,address,sex,create_date)
	values
	<foreach collection="userList" separator="," item="user">
	(#{user.username}, #{user.address},#{user.sex},now())
	</foreach>
</insert>

案例3 – Map

dao层

void insert(@param("depId") String depId,
            @Param("userList") List<User> userList);

sql

<insert id = "insert" parameterType="java.util.HashMap">
	insert into user(username,address,depId,create_date)
	values
	<foreach collection="userList" separator="," item="user">
	(#{user.username}, #{user.address},#{depId},now())
	</foreach>
</insert>