MyBatis插入和批量插入

注意事项：

批量插入的时候，需要注意动态SQL的写法：即拼接出来的SQL语句和常规的SQL语句语法一致。

批量插入的时候，需要注意，在后台要写成user.name或者map.name，否则会出错

插入返回主键需要通过entity.getId()来进行返回。

概述

使用sqlsession来进行删除的时候，只有两个重载的方法 insert(String statement); int insert(String statement, Object parameter);，两者均返回受影响的条数，底层使用的是update方法（这个在后面会有说明）

本实例包含以下插入：

没有参数 根据实体类插入（一个参数，实体类） 根据Map插入（一个参数，是Map类型） 批量插入（一个参数，参数是数组List。使用了动态SQL） 批量插入（一个参数，参数是List<map）

动态SQL说明：

<!-- 批量插入 ,传递过来的参数是list类型 --> <foreach collection="list" item="user" separator=",">( #{user.name} ,#{user.age}) </foreach>

最后拼接的sql语句是：

insert into user(name,age) values ( ? ,?) , ( ? ,?) , ( ? ,?)

mybatis-config.xml: 在src/main/resources/chapter03/mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd"> <configuration> <properties resource="jdbc.properties"> </properties> <environments default="development"> <environment id="development"> <transactionManager type="JDBC" /> <dataSource type="POOLED"> <property name="driver" value="${driver}" /> <property name="url" value="${url}" /> <property name="username" value="${username}" /> <property name="password" value="${password}" /> </dataSource> </environment> </environments> <mappers> <mapper resource="mapper/chapter03/UserMapper.xml" /> </mappers> </configuration>

注意，在批量插入的时候，有一点不好的地方在于，如果传过来的是List<map> 类型，在进行插入的时候，两边的字段类型不对等，不好搞。因为trim的语法不好在上面用。

映射文件UserMapper.xml: src/main/resources/mapper/chapter03/UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd"> <mapper namespace="cn.liuyiyou.mybatis.mapper.chapter03.UserMapper"> <!-- 没有参数的插入 --> <insert id="insertUser"> insert into user(name) values ('test') </insert> <!-- 参数是实体 --> <insert id="insertUserEntity" parameterType="cn.liuyiyou.mybatis.domain.chapter03.User"> insert into user(name,age) values (#{name},#{age}) </insert> <!-- 插入返回主键1： --> <insert id="insertReturnId" parameterType="cn.liuyiyou.mybatis.domain.chapter03.User"> insert into user(name,age) values (#{name},#{age}) <selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer"> SELECT LAST_INSERT_ID() </selectKey> </insert> <!-- 插入返回主键2： --> <insert id="insertReturnId2" parameterType="cn.liuyiyou.mybatis.domain.chapter03.User" useGeneratedKeys="true"> insert into user(name,age) values (#{name},#{age}) </insert> <!-- 参数是Map --> <insert id="insertUserMap" parameterType="java.util.Map"> insert into user(name,age) values (#{name},#{age}) </insert> <!-- 批量插入，传递过来的参数是List<User> --> <insert id="batchInsertList" parameterType="cn.liuyiyou.mybatis.domain.chapter03.User"> insert into user(name,age) values <foreach collection="list" item="user" separator=",">( #{user.name} ,#{user.age}) </foreach> </insert> <!-- 批量插入，传递过来的参数是List<Map<String,Object> --> <insert id="batchInsertMap"> insert into user(name,age) values <foreach collection="map" item="map" separator=","> ( #{map.name}<!-- 不能直接是name，因为myBatis无法识别 --> ,#{map.age} ) </foreach> </insert> </mapper>

测试类：UserMapperTest

package cn.liuyiyou.mybatis.chapter03; import java.io.IOException; import java.io.InputStream; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import org.apache.ibatis.io.Resources; import org.apache.ibatis.session.SqlSession; import org.apache.ibatis.session.SqlSessionFactory; import org.apache.ibatis.session.SqlSessionFactoryBuilder; import org.junit.Assert; import org.junit.Before; import org.junit.Test; import cn.liuyiyou.mybatis.domain.chapter02.User; public class UserMapperTest { private static SqlSessionFactory sqlSessionFactory; @Before public void setUp() throws Exception { String resource = "chapter03/mybatis-config.xml"; InputStream inputStream = null; try { inputStream = Resources.getResourceAsStream(resource); } catch (IOException e) { e.printStackTrace(); } sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); } /** * 没有参数 */ @Test public void insertUserTest() { SqlSession session = sqlSessionFactory.openSession(); try { int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.insertUser"); Assert.assertEquals(1, result); session.commit(); } finally { session.close(); } } /** * 参数是实体类型： */ @Test public void insertUserEntityTest() { SqlSession session = sqlSessionFactory.openSession(); try { User user =new User(); user.setName("insert1"); user.setAge(1); int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.insertUserEntity",user); Assert.assertEquals(1, result); session.commit(); } finally { session.close(); } } /** * 参数是实体类型： */ @Test public void insertUserEntityAndReturnIdTest() { SqlSession session = sqlSessionFactory.openSession(); UserMapper userMapper = session.getMapper(UserMapper.class); try { User user =new User(); user.setName("insert1"); user.setAge(1); int result = userMapper.insertReturnId(user); System.out.println("result::"+result); System.out.println("id::"+ user.getId()); Assert.assertEquals(1, result); session.commit(); } finally { session.close(); } } /** * 参数是实体类型： */ @Test public void insertUserEntityAndReturnIdTest2() { SqlSession session = sqlSessionFactory.openSession(); UserMapper userMapper = session.getMapper(UserMapper.class); try { User user =new User(); user.setName("insert1"); user.setAge(1); int result = userMapper.insertReturnId(user); System.out.println("result::"+result); System.out.println("id::"+ user.getId()); Assert.assertEquals(1, result); session.commit(); } finally { session.close(); } } /** * 参数是Map类型： */ @Test public void insertUserMapTest() { SqlSession session = sqlSessionFactory.openSession(); try { Map<String,Object> user =new HashMap<String,Object>(); user.put("name","insert2"); user.put("age",2); int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.insertUserMap",user); Assert.assertEquals(1, result); session.commit(); } finally { session.close(); } } /** * 批量插入，参数是List<User>数组 */ @Test public void batchInsertListTest() { SqlSession session = sqlSessionFactory.openSession(); try { List<User> users =new ArrayList<User>(); for(int i=3; i<=5;i++){ User user =new User(); user.setName("insert"+i); user.setAge(i); users.add(user); } int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.batchInsertList",users); Assert.assertEquals(3, result); session.commit(); } finally { session.close(); } } /** * 批量插入，参数是Map */ @Test public void batchInsertMapTest() { SqlSession session = sqlSessionFactory.openSession(); try { List<Map<String,Object>> users =new ArrayList<Map<String,Object>>(); for(int i=6; i<=8;i++){ Map<String,Object> user =new HashMap<String,Object>(); user.put("name","insert"+i); user.put("age",i); users.add(user); } int result = session.update("cn.liuyiyou.mybatis.mapper.chapter03.UserMapper.batchInsertMap",users); Assert.assertEquals(3, result); session.commit(); } finally { session.close(); } } }