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>