1.背景
近期项目中遇到了用mybatis保存数据库CLOB类型到oracle库的操作,记录一下,方便以后查阅。
2.实例代码
2.1 表结构
--判断表是否存在，如果存在则删除
-- drop table WMS_IN_POOL;
-- Create table
create table WMS_IN_POOL
(
POOL_PK_NO NUMBER NOT NULL,
BIG_DATA CLOB default NULL,
CREATE_TIME DATE default SYSDATE,
UPDATE_TIME DATE
);
-- Add comments to the table
comment on table VENDOR_CHECK_WAY is 'CLOB测试表';
-- Add comments to the columns
COMMENT ON COLUMN WMS_IN_POOL.POOL_PK_NO IS '主键ID(自增)';
COMMENT ON COLUMN WMS_IN_POOL.BIG_DATA IS '存储json字符串,大数据值';
COMMENT ON COLUMN VENDOR_CHECK_WAY.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN VENDOR_CHECK_WAY.UPDATE_TIME IS '修改时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table WMS_IN_POOL
add constraint WMS_IN_POOL primary key (POOL_PK_NO);
-- Create sequence
create sequence SEQ_POOL_PK_NO
minvalue 1 -- 最小值=1
maxvalue 999999999999999999999999999 -- 指定最大值
start with 1 -- 从1开始
increment by 1 -- 每次递增1
cache 20;
-- Create Index --> clob can not create index
-- create index index_big_data on WMS_IN_POOL(BIG_DATA);
-- commit
commit;效果图:
2.2 实体类 WmsInPool.java
import java.math.BigDecimal;
import java.util.Date;
public class WmsInPool implements java.io.Serializable {
private static final long serialVersionUID = 1L;
/** 主键id*/
private BigDecimal poolPkNo;
/** clob类型数据字段*/
private String bigData;
/** 创建时间*/
private Date createTime;
/** 更新时间*/
private Date updateTime;
public BigDecimal getPoolPkNo() {
return poolPkNo;
}
public void setPoolPkNo(BigDecimal poolPkNo) {
this.poolPkNo = poolPkNo;
}
public String getBigData() {
return bigData;
}
public void setBigData(String bigData) {
this.bigData = bigData;
}
public Date getCreateTime() {
return createTime;
}
public void setCreateTime(Date createTime) {
this.createTime = createTime;
}
public Date getUpdateTime() {
return updateTime;
}
public void setUpdateTime(Date updateTime) {
this.updateTime = updateTime;
}
}2.3 mybatis映射文件 WmsInPoolMapper.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.wlw.mgt.dao.WmsInPoolMapper">
<resultMap id="BaseResultMap" type="cn.wlw.mgt.entity.WmsInPool">
<id column="POOL_PK_NO" jdbcType="DECIMAL" property="poolPkNo" />
<result column="BIG_DATA" property="bigData" jdbcType="CLOB" javaType = "java.lang.String"/>
<result column="CREATE_TIME" jdbcType="TIMESTAMP" property="createTime" />
<result column="UPDATE_TIME" jdbcType="TIMESTAMP" property="updateTime" />
</resultMap>
<sql id="Base_Column_List">
POOL_PK_NO,BIG_DATA,CREATE_TIME,UPDATE_TIME
</sql>
<select id="selectByPrimaryKey" parameterType="java.math.BigDecimal" resultMap="BaseResultMap">
select
<include refid="Base_Column_List" />
from WMS_IN_POOL
where POOL_PK_NO = #{poolPkNo,jdbcType=DECIMAL}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.math.BigDecimal">
delete from WMS_IN_POOL
where POOL_PK_NO = #{poolPkNo,jdbcType=DECIMAL}
</delete>
<insert id="insert" parameterType="cn.wlw.mgt.entity.WmsInPool">
<selectKey resultType="Decimal" keyProperty="poolPkNo" order="BEFORE">
SELECT nvl(max(POOL_PK_NO),0)+1 from WMS_IN_POOL
</selectKey>
insert into WMS_IN_POOL (POOL_PK_NO,BIG_DATA,CREATE_TIME,UPDATE_TIME)
values (
#{poolPkNo,jdbcType=DECIMAL},#{bigData,jdbcType=CLOB},
#{createTime,jdbcType=TIMESTAMP},#{updateTime,jdbcType=TIMESTAMP}
)
</insert>
<insert id="insertSelective" parameterType="cn.wlw.mgt.entity.WmsInPool">
insert into WMS_IN_POOL
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="poolPkNo != null">
POOL_PK_NO,
</if>
<if test="bigData != null">
BIG_DATA,
</if>
<if test="createTime != null">
CREATE_TIME,
</if>
<if test="updateTime != null">
UPDATE_TIME,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="poolPkNo != null">
#{poolPkNo,jdbcType=DECIMAL},
</if>
<if test="bigData != null">
#{bigData,jdbcType=CLOB},
</if>
<if test="createTime != null">
#{createTime,jdbcType=TIMESTAMP},
</if>
<if test="updateTime != null">
#{updateTime,jdbcType=TIMESTAMP},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="cn.wlw.mgt.entity.WmsInPool">
update WMS_IN_POOL
<set>
<if test="bigData != null">
BIG_DATA = #{bigData,jdbcType=CLOB},
</if>
<if test="createDate != null">
UPDATE_TIME = #{updateTime,jdbcType=TIMESTAMP},
</if>
</set>
where POOL_PK_NO = #{poolPkNo,jdbcType=DECIMAL}
</update>
<update id="updateByPrimaryKey" parameterType="cn.wlw.mgt.entity.WmsInPool">
update WMS_IN_POOL
set BIG_DATA = #{bigDdata,jdbcType=CLOB},
UPDATE_TIME = #{updateTime,jdbcType=TIMESTAMP}
where POOL_PK_NO = #{poolPkNo,jdbcType=DECIMAL}
</update>
<insert id="batchInsertWmsInPool" parameterType="cn.wlw.mgt.entity.WmsInPool">
insert into WMS_IN_POOL(
POOL_PK_NO,BIG_DATA,CREATE_TIME,UPDATE_TIME)
select SEQ_POOL_PK_NO.NEXTVAL, A.* from(
<foreach collection="wmsInPools" item="item" index="index" separator="UNION ALL">
SELECT
#{item.bigData,jdbcType=CLOB},
#{item.createTime,jdbcType=TIMESTAMP},
#{item.updateTime,jdbcType=TIMESTAMP}
from dual
</foreach>
) A
</insert>
</mapper>2.4 dao层接口(没有实现类的哦)
import cn.wlw.mgt.entity.WmsInPool;
import java.math.BigDecimal;
import java.util.List;
import org.apache.ibatis.annotations.Param;
public interface WmsInPoolMapper {
/**
*
* @param poolPkNo
* @return
*/
public int deleteByPrimaryKey(BigDecimal poolPkNo);
/**
*
* @param record
* @return
*/
public int insert(WmsInPool record);
/**
*
* @param record
* @return
*/
public int insertSelective(WmsInPool record);
/**
*
* @param poolPkNo
* @return
*/
public WmsInPool selectByPrimaryKey(BigDecimal poolPkNo);
/**
*
* @param record
* @return
*/
public int updateByPrimaryKeySelective(WmsInPool record);
/**
*
* @param record
* @return
*/
public int updateByPrimaryKey(WmsInPool record);
/**
* @param wmsInPools
* @return
*/
public int batchInsertWmsInPool(@Param("wmsInPools") List<WmsInPool> wmsInPools);
}2.5 service层接口及实现类
WmsInPoolService.java
import java.util.List;
import cn.wlw.mgt.entity.WmsInPool;
public interface WmsInPoolService {
/**
* 批量插入数据
* @param list
* @return
*/
public Integer batchInsertWmsInPool(List<WmsInPool> list);
}import java.util.List;
import org.apache.commons.lang3.builder.ReflectionToStringBuilder;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import cn.wlw.mgt.dao.WmsInPoolMapper;
import cn.wlw.mgt.entity.WmsInPool;
import cn.wlw.mgt.service.WmsInPoolService;
@Service
public class WmsInPoolServiceImpl implements WmsInPoolService {
private static final Logger logger = LoggerFactory.getLogger(WmsInPoolServiceImpl.class);
@Autowired
private WmsInPoolMapper wmsInPoolMapper;
@Override
@Transactional
public Integer batchInsertWmsInPool(List<WmsInPool> list) {
int addFlag = 0;
long start1 = System.currentTimeMillis();
if(list != null && list.size() > 0){
for(WmsInPool addWmsInPool:list){
try {
logger.info("新增wms_in_pool表参数=["+ReflectionToStringBuilder.toString(addWmsInPool)+"]");
addFlag = wmsInPoolMapper.insert(addWmsInPool);
} catch (Exception e) {
logger.error("单个新增wms_in_pool表失败,原因为:"+e.getMessage(),e);
throw new RuntimeException("单个新增wms_in_pool表失败,原因为:"+e.getMessage());
}
//判断新增结果
if(addFlag != 1){
logger.error("单个新增wms_in_pool表失败,原因为:数据库没有执行任何操作!");
throw new RuntimeException("单个新增wms_in_pool表失败,原因为:数据库没有执行任何操作!");
}
}//END for
/*try {
addFlag = wmsInPoolMapper.batchInsertWmsInPool(list);
} catch (Exception e) {
logger.error("批量新增wms_in_pool表失败,原因为:" + e.getMessage(), e);
throw new RuntimeException("批量新增wms_in_pool表失败,原因为:" + e.getMessage());
}*/
//重新赋值
addFlag = 1;
}
long start2 = System.currentTimeMillis();
logger.info("批量新增wms_in_pool表成功,一共耗时="+(start2-start1)+"毫秒！");
//返回结果
return addFlag;
}
}