pgsql -- 批量插入

绝地灬酷狼 2023-07-13 12:13 7阅读 0赞

**工具：mybatis+pgsql**

详见[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

`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>

[pgsql]: https://www.postgresql.org/docs/