Mybatis批量操作有两种方式:
(1) 在mapper.xml文件动态生成多条sql语句,生成的sql语句类似如下:
<insert id="insert" parameterType="sdc.mybatis.test.Student">
<!-- WARNING - @mbggenerated This element is automatically generated by
MyBatis Generator, do not modify. This element was generated on Mon May 09
11:09:37 CST 2011. -->
insert into student (id, name, sex,
address, telephone, t_id
)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR},
#{sex,jdbcType=VARCHAR},
#{address,jdbcType=VARCHAR}, #{telephone,jdbcType=VARCHAR}, #{tId,jdbcType=INTEGER}
)
</insert>
(2) 使用sqlSessionFactory.openSession(ExecutorType.BATCH, false)开启一个批量操作的session,后续使用从session中获取的mapper对象进行数据库操作时,自动会采用JDBC的批量操作,但前提是:必须连续调用某一个mapper对象的方法,否则会产生多个PrepareStatement对象。数量过大后会导致“超出打开游标的最大数”异常
Mybatis代码举例:
结论:batchInserts1方法只使用两个Cursor而batchInserts2会使用6000个Cursour。区别在于方法2中对mapper的方法进行了交叉调用
代码:
public void batchInserts1() {
String clientId = "102101000004";
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
try {
DeviceDataMapper deviceDataMapper = sqlSession.getMapper(DeviceDataMapper.class);
RfGprsTagDataMapper tagDataMapper = sqlSession.getMapper(RfGprsTagDataMapper.class);
for (int i = 0; i < 3000; i++) {
DeviceData deviceData = new DeviceData();
deviceData.setClientId(clientId);
deviceData.setDeviceId("6");
deviceData.setRecId(BigDecimal.valueOf(UIDUtils.getInstance().generateUID(UIDTypeCode.DEVICEDATA).longValue()));
deviceData.setCreatedBy(4L);
deviceDataMapper.insert(deviceData);
}
for (int i = 0; i < 3000; i++) {
RfGprsTagData tagData = new RfGprsTagData();
tagData.setClientId(clientId);
tagData.setTagId("F01000000000000000000194");
tagData.setRecId(BigDecimal.valueOf(UIDUtils.getInstance().generateUID(UIDTypeCode.RFGPRSTAGDATA)
.longValue()));
tagData.setCreatedBy(4L);
tagDataMapper.insert(tagData);
}
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
} finally {
sqlSession.close();
}
}
public void batchInserts2() {
String clientId = "102101000004";
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
try {
DeviceDataMapper deviceDataMapper = sqlSession.getMapper(DeviceDataMapper.class);
RfGprsTagDataMapper tagDataMapper = sqlSession.getMapper(RfGprsTagDataMapper.class);
for (int i = 0; i < 3000; i++) {
DeviceData deviceData = new DeviceData();
deviceData.setClientId(clientId);
deviceData.setDeviceId("6");
deviceData.setRecId(BigDecimal.valueOf(UIDUtils.getInstance().generateUID(UIDTypeCode.DEVICEDATA).longValue()));
deviceData.setCreatedBy(4L);
deviceDataMapper.insert(deviceData);
RfGprsTagData tagData = new RfGprsTagData();
tagData.setClientId(clientId);
tagData.setTagId("F01000000000000000000194");
tagData.setRecId(BigDecimal.valueOf(UIDUtils.getInstance().generateUID(UIDTypeCode.RFGPRSTAGDATA).longValue()));
tagData.setCreatedBy(4L);
tagDataMapper.insert(tagData);
}
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
} finally {
sqlSession.close();
}
}
PS: 游标说明
1.对于oracle中Cursor分隐式和显式的两种,具体的定义可以自行去网上查下,Cursor数量在oracle里有限制的,而且也有一定量的内存消耗,同时打开的数量越大,内存的消耗肯定越大。
2. JDBC里对Cursor打开和关闭对应如下:
a. 只要创建一个statement对象就会打开一个Cursor
b. 调用statement对象的close方法将会关闭相应的Cursor;
c. 在执行Connection对象的close方法时也会去关闭该Connection对象所打开的所有Cursor(在忘执行statement对象的close方法时产生的)
(1) 在mapper.xml文件动态生成多条sql语句,生成的sql语句类似如下:
<insert id="insert" parameterType="sdc.mybatis.test.Student">
<!-- WARNING - @mbggenerated This element is automatically generated by
MyBatis Generator, do not modify. This element was generated on Mon May 09
11:09:37 CST 2011. -->
insert into student (id, name, sex,
address, telephone, t_id
)
values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR},
#{sex,jdbcType=VARCHAR},
#{address,jdbcType=VARCHAR}, #{telephone,jdbcType=VARCHAR}, #{tId,jdbcType=INTEGER}
)
</insert>
(2) 使用sqlSessionFactory.openSession(ExecutorType.BATCH, false)开启一个批量操作的session,后续使用从session中获取的mapper对象进行数据库操作时,自动会采用JDBC的批量操作,但前提是:必须连续调用某一个mapper对象的方法,否则会产生多个PrepareStatement对象。数量过大后会导致“超出打开游标的最大数”异常
Mybatis代码举例:
结论:batchInserts1方法只使用两个Cursor而batchInserts2会使用6000个Cursour。区别在于方法2中对mapper的方法进行了交叉调用
代码:
public void batchInserts1() {
String clientId = "102101000004";
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
try {
DeviceDataMapper deviceDataMapper = sqlSession.getMapper(DeviceDataMapper.class);
RfGprsTagDataMapper tagDataMapper = sqlSession.getMapper(RfGprsTagDataMapper.class);
for (int i = 0; i < 3000; i++) {
DeviceData deviceData = new DeviceData();
deviceData.setClientId(clientId);
deviceData.setDeviceId("6");
deviceData.setRecId(BigDecimal.valueOf(UIDUtils.getInstance().generateUID(UIDTypeCode.DEVICEDATA).longValue()));
deviceData.setCreatedBy(4L);
deviceDataMapper.insert(deviceData);
}
for (int i = 0; i < 3000; i++) {
RfGprsTagData tagData = new RfGprsTagData();
tagData.setClientId(clientId);
tagData.setTagId("F01000000000000000000194");
tagData.setRecId(BigDecimal.valueOf(UIDUtils.getInstance().generateUID(UIDTypeCode.RFGPRSTAGDATA)
.longValue()));
tagData.setCreatedBy(4L);
tagDataMapper.insert(tagData);
}
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
} finally {
sqlSession.close();
}
}
public void batchInserts2() {
String clientId = "102101000004";
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH, false);
try {
DeviceDataMapper deviceDataMapper = sqlSession.getMapper(DeviceDataMapper.class);
RfGprsTagDataMapper tagDataMapper = sqlSession.getMapper(RfGprsTagDataMapper.class);
for (int i = 0; i < 3000; i++) {
DeviceData deviceData = new DeviceData();
deviceData.setClientId(clientId);
deviceData.setDeviceId("6");
deviceData.setRecId(BigDecimal.valueOf(UIDUtils.getInstance().generateUID(UIDTypeCode.DEVICEDATA).longValue()));
deviceData.setCreatedBy(4L);
deviceDataMapper.insert(deviceData);
RfGprsTagData tagData = new RfGprsTagData();
tagData.setClientId(clientId);
tagData.setTagId("F01000000000000000000194");
tagData.setRecId(BigDecimal.valueOf(UIDUtils.getInstance().generateUID(UIDTypeCode.RFGPRSTAGDATA).longValue()));
tagData.setCreatedBy(4L);
tagDataMapper.insert(tagData);
}
sqlSession.commit();
} catch (Exception e) {
e.printStackTrace();
sqlSession.rollback();
} finally {
sqlSession.close();
}
}
PS: 游标说明
1.对于oracle中Cursor分隐式和显式的两种,具体的定义可以自行去网上查下,Cursor数量在oracle里有限制的,而且也有一定量的内存消耗,同时打开的数量越大,内存的消耗肯定越大。
2. JDBC里对Cursor打开和关闭对应如下:
a. 只要创建一个statement对象就会打开一个Cursor
b. 调用statement对象的close方法将会关闭相应的Cursor;
c. 在执行Connection对象的close方法时也会去关闭该Connection对象所打开的所有Cursor(在忘执行statement对象的close方法时产生的)