mybatis用法(三)mybatis保存CLOB类型到oracle数据库实例

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);-- commitcommit;

效果图:

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);}

WmsInPoolServiceImpl.java

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;@Servicepublic class WmsInPoolServiceImpl implements WmsInPoolService {private static final Logger logger = LoggerFactory.getLogger(WmsInPoolServiceImpl.class);@Autowiredprivate WmsInPoolMapper wmsInPoolMapper;@Override@Transactionalpublic 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;}}

2.6 spring配置

Spring整合MyBatis使用自动扫描的方式,即可；代码 略...

2.7 测试数据

检查配置文件,启动项目后,即可走正常的测试流程。