mybatis基础操作之批量操作

    Mybatis批量操作，包括批量新增、修改、删除等，本次使用Oracle数据库进行操作。

    首先新建表

   

-- Create table
create table TEST_INSERT
(
  ID        NUMBER not null,
  NAME      VARCHAR2(12),
  AGE       NUMBER(3),
  STARTDATE DATE
)
-- Create/Recreate primary, unique and foreign key constraints 
alter table TEST_INSERT
  add primary key (ID)

 

   2新建JavaBean

   

package bean;

import java.util.Date;

public class TestInsert {
	private long id;
	
	public String toString() {
		return "t [id=" + id + ", age=" + age + ", startDate="
				+ startDate.toLocaleString()+ ", name=" + name + "]";
	}

	private int age;
	private Date startDate;
	private String name;

	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public int getAge() {
		return age;
	}

	public void setAge(int age) {
		this.age = age;
	}

	public Date getStartDate() {
		return startDate;
	}

	public void setStartDate(Date startDate) {
		this.startDate = startDate;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public TestInsert(long id, int age, Date startDate, String name) {
		super();
		this.id = id;
		this.age = age;
		this.startDate = startDate;
		this.name = name;
	}

	public TestInsert() {
		super();
	}

	public TestInsert(int age, Date startDate, String name) {
		super();
		this.age = age;
		this.startDate = startDate;
		this.name = name;
	}

}

   首先是Mybatis使用注解方式批量新增

  

public interface TestInsertMapper {

	@Insert("insert into test_insert (id, name, age, startDate) values (#{id}, #{name}, #{age}, #{startDate}) ")
	public void insertTest(TestInsert test);
}

   mybatis.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>
	 <settings>
		 <setting name="cacheEnabled" value="true" /> 
		<setting name="lazyLoadingEnabled" value="true" /> 
		<setting name="multipleResultSetsEnabled" value="true" />
		<setting name="useColumnLabel" value="true" />
		<!-- <setting name="useGeneratedKeys" value="true" /> -->
		<setting name="defaultExecutorType" value="SIMPLE" />
		<setting name="defaultStatementTimeout" value="25000" />
	</settings> 
	
	<environments default="development">
		<environment id="development">
			<transactionManager type="JDBC" />
			<dataSource type="POOLED">
				<property name="driver" value="oracle.jdbc.driver.OracleDriver" />
				<property name="url"
					value="jdbc:oracle:thin:@localhost:1521:xe" />
				<property name="username" value="admin" />
				<property name="password" value="123" />
			</dataSource>
		</environment>
	</environments>

	<mappers>
		<!-- <mapper resource="config/score.xml" /> -->
		<mapper resource="config/testinsert-mapper.xml"/>
		<mapper class="mapper.TestInsertMapper"/>  
	</mappers>
</configuration>

    对应的例子为:

  

public void batchInsert1() throws Exception {
		SqlSession sqlSession = ssf.openSession(ExecutorType.BATCH);
		TestInsertMapper pmapper = sqlSession.getMapper(TestInsertMapper.class);
		long start = System.currentTimeMillis();
		try {
			for (int i = 0; i < 16; i++) {
				pmapper.insertTest(testList.get(i));			}
			sqlSession.commit();
		} finally {
			sqlSession.close();
			System.out.println((System.currentTimeMillis() - start) + " ms---");
		}
	}

     下面是for-each进行插入，对应的配置为：

    

<insert id="OracleBatchInsert" parameterType="java.util.List">
		insert into test_insert(id, name, age, startDate)(
		<foreach collection="list" item="test" index="index"
			separator="union all">
			select
			#{test.id}, #{test.name}, #{test.age},
			#{test.startDate}
			from dual
		</foreach>
		)
	</insert>

    测试例子和上面的差不多

  

public void batchInsert2() throws Exception {
		SqlSession sqlSession = ssf.openSession();
		long start = System.currentTimeMillis();
		try {
			sqlSession.insert("test.OracleBatchInsert", testList);
			sqlSession.commit();
		} finally {
			sqlSession.close();
			System.out.println((System.currentTimeMillis() - start) + " ms---");
		}
	}

     上面例子是主键自己指定的，接下来是使用Oracle的序列作为主键，对应的配置为

   

<insert id="OracleBatchSeqInsert" parameterType="java.util.List">
		insert into test_insert(id, name, age, startDate)(
		select
		STUDENT_ID_SEQUENCE.NEXTVAL,A.* from(
		<foreach collection="list" item="test" index="index"
			separator="union all">
			select #{test.name}, #{test.age},
			#{test.startDate}
			from dual
		</foreach>
		)A)
	</insert>

    测试例子和上面一样

    接下来是批量更新,对应配置为

  

<update id="updateByIds" parameterType="map">
		update test_insert
		set startDate = #{startDate,jdbcType=TIMESTAMP} where id in
		<foreach collection="list" item="item" index="index" open="("
			close=")" separator=",">
			#{item}
		</foreach>

	</update>

   测试例子为：

   

public void batchUpdate() {
		SqlSession sqlSession = ssf.openSession();
		long start = System.currentTimeMillis();
		try {
			Map map = new HashMap();
			map.put("startDate", new Date());
			map.put("list", idlist);
			sqlSession.update("test.updateByIds", map);
			sqlSession.commit();
		} finally {
			sqlSession.close();
			//
			System.out.println((System.currentTimeMillis() - start) + " ms---");
		}

	}

   最后是批量删除，配置如下：

   

<delete id="deleteBatch" parameterType="list">
		delete from test_insert where id in
		<foreach collection="list" item="item" index="index" open="("
			close=")" separator=",">
			#{item}
		</foreach>
	</delete>

    测试例子如下：

   

public void batchDelete() {
		SqlSession sqlSession = ssf.openSession();
		long start = System.currentTimeMillis();
		try {
			sqlSession.delete("test.deleteBatch", idlist);
			sqlSession.commit();
		} finally {
			sqlSession.close();
			//
			System.out.println((System.currentTimeMillis() - start) + " ms---");
		}
	}

    全文完，源码就不打包了，上面都有，写的不好的地方，请多包涵，谢谢！

     --------------------------------------------------------------------------------------------

     Ibatis2 批量插入配置为：

    

<insert id="oraclebatchinsert" parameterClass="java.util.List">
		insert into test_insert(id, name, age, startDate)(
		<iterate conjunction="union all">
			select
			#idsList[].id#, #idsList[].name#, #idsList[].age#,
			#idsList[].startDate#
			from dual
		</iterate>
		)
	</insert>

	<insert id="oracleseqbatchinsert" parameterClass="java.util.List">
		insert into test_insert(id, name, age, startDate)(
		select
		STUDENT_ID_SEQUENCE.NEXTVAL,A.* from(
		<iterate conjunction="union all">
			select
			#idsList[].name#, #idsList[].age#,
			#idsList[].startDate#
			from dual
		</iterate>
		)A)
	</insert>

 

   

     

评论

1 楼 fordsupr 2014-03-10  

很清楚
很受用的文章,谢谢您～