mybatis基础操作之批量操作

QQ叫紫珊

　　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>