mybatis保存blob兼容mysql和oracle

 

首先是表结构：

然后是实体类，和文件表字段映射

package com.zhou.upms.dao.module;

import com.zhou.util.common.StreamUtil;

import java.io.*;
import java.sql.Blob;

public class Attachment implements Serializable {
    //文件表主键
    private Integer id;
    //关联的用户id
    private Integer userId;
    //文件名
    private String fileName;
    //使用字节流来缓冲
    private InputStream file;
    //文件后缀
    private String prefix;
    //文件大小(字节)
    private Long size;
    //文件来源
    private Integer source;
    //文件业务类型
    private Integer type;
    //描述
    private String description;
    //创建时间(mysql用long来存时间)
    private Long createDt;

    public Integer getId() {
        return id;
    }

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

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public String getFileName() {
        return fileName;
    }

    public void setFileName(String fileName) {
        this.fileName = fileName;
    }

    public InputStream getFile() {
        return file;
    }

    public void setFile(Object file){
        try{
            if(file instanceof Blob){
                //oracle读出来这里是blob类型
                Blob blob = (Blob) file;
                this.file = blob.getBinaryStream();
            }else if(file instanceof byte[]){
                //mysql读出来的这里是字节数组
                this.file = new ByteArrayInputStream((byte[]) file);
            }else if(file instanceof File){
                //保存附件的时候获取流
                this.file = fileToInputStream((File) file);
            }else if(file instanceof String){
                this.file = fileToInputStream(new File((String) file));
            }else{
                throw new RuntimeException("获取文件字节流失败，无法识别对象类型！");
            }
        }catch (Exception e){
            throw new RuntimeException(e);
        }
    }
    private InputStream fileToInputStream(File file) throws IOException {
        FileInputStream in = null;
        try{
            return new ByteArrayInputStream(StreamUtil.toByteArray((in = new FileInputStream(file))));
        }finally {
            StreamUtil.close(in);
        }
    }

    public String getPrefix() {
        return prefix;
    }

    public void setPrefix(String prefix) {
        this.prefix = prefix;
    }

    public Long getSize() {
        return size;
    }

    public void setSize(Long size) {
        this.size = size;
    }

    public Integer getSource() {
        return source;
    }

    public void setSource(Integer source) {
        this.source = source;
    }

    public Integer getType() {
        return type;
    }

    public void setType(Integer type) {
        this.type = type;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Long getCreateDt() {
        return createDt;
    }

    public void setCreateDt(Long createDt) {
        this.createDt = createDt;
    }
}

mybatis映射器：

页面列表展示的时候不建议把blob字段查出来，会严重影响性能
<select id="getAttachList" resultType="java.util.Map">
        SELECT a.id,a.user_id,a.file_name,a.prefix,a.type,a.description,a.size,a.source,a.createdt,b.realname,b.username FROM drea_user_file a
        left join drea_user b on b.userid=a.user_id
        WHERE 1=1
        <if test="fileName!='' and fileName!=null">and a.file_name like #{fileName}</if>
        <if test="source!='' and source!=null">and a.source = #{source}</if>
        <if test="userId!='' and userId!=null">and a.user_id = #{userId}</if>
        <if test="prefix!='' and prefix!=null">and upper(a.prefix) = upper(#{prefix})</if>
        ORDER BY a.createdt DESC
    </select>
    <select id="getAttach" resultMap="attachMap">
        select * from drea_user_file where 1=1
        <if test="id!='' and id!=null">and id=#{id}</if>
        <if test="prefix!='' and prefix!=null">and upper(prefix) = upper(#{prefix})</if>
        <if test="source!='' and source!=null">and source = #{source}</if>
        <if test="userId!='' and userId!=null">and a.user_id = #{userId}</if>

    </select>
    <resultMap id="attachMap" type="com.zhou.upms.dao.module.Attachment">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="user_id" jdbcType="INTEGER" property="userId"/>
        <result column="file_name" jdbcType="VARCHAR" property="fileName"/>
        <result column="file" jdbcType="BLOB" property="file"/>
        <result column="prefix" jdbcType="VARCHAR" property="prefix"/>
        <result column="type" jdbcType="INTEGER" property="type"/>
        <result column="description" jdbcType="VARCHAR" property="description"/>
        <result column="size" jdbcType="NUMERIC" property="size"/>
        <result column="source" jdbcType="INTEGER" property="source"/>
        <result column="createdt" jdbcType="NUMERIC" property="createDt"/>
    </resultMap>
    <insert id="addAttach" parameterType="com.zhou.upms.dao.module.Attachment">
        insert into drea_user_file(user_id,file_name,file,prefix,type,description,size,source,createdt)
        values(#{attach.userId,jdbcType=INTEGER},#{attach.fileName,jdbcType=VARCHAR},#{attach.file,jdbcType=BLOB},#{attach.prefix,jdbcType=VARCHAR},
        #{attach.type,jdbcType=INTEGER},#{attach.description,jdbcType=VARCHAR},#{attach.size,jdbcType=NUMERIC},#{attach.source,jdbcType=INTEGER},#{attach.createDt,jdbcType=NUMERIC})
    </insert>
    <insert id="updateAttach" parameterType="com.zhou.upms.dao.module.Attachment">
        update drea_user_file set file_name=#{attach.fileName,jdbcType=VARCHAR},file=#{attach.file,jdbcType=BLOB},prefix=#{attach.prefix,jdbcType=VARCHAR}
            ,size=#{attach.size,jdbcType=NUMERIC},createdt=#{attach.createDt,jdbcType=NUMERIC} where id=#{attach.id}
    </insert>