Mybatis 连接Oracle 存取图片到blob字段

项目上碰到了blob存取图片视频到大字段blob的问题，网上一对办法基本都不能满足要求，没办法，不自己动手的咸鱼不是好咸鱼

 

首先创建一张表

 

使用mybatis-generator插件自动生成 mapper.xml,dao,entity

PicInfoMapper.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="com.zkyt.lyggis.dao.PicInfoMapper" >
  <resultMap id="BaseResultMap" type="com.zkyt.lyggis.model.PicInfo" >
    <id column="ID" property="id" jdbcType="VARCHAR" />
  </resultMap>
  <resultMap id="ResultMapWithBLOBs" type="com.zkyt.lyggis.model.PicInfo" extends="BaseResultMap" >
    <result column="IMG" property="img" jdbcType="BLOB" />
    <result column="VIDEO" property="video" jdbcType="BLOB" />
  </resultMap>
  <sql id="Base_Column_List" >
    ID
  </sql>
  <sql id="Blob_Column_List" >
    IMG, VIDEO
  </sql>
  <select id="selectByPrimaryKey" resultMap="ResultMapWithBLOBs" parameterType="java.lang.String" >
    select 
    <include refid="Base_Column_List" />
    ,
    <include refid="Blob_Column_List" />
    from PIC_INFO
    where ID = #{id,jdbcType=VARCHAR}
  </select>
  <delete id="deleteByPrimaryKey" parameterType="java.lang.String" >
    delete from PIC_INFO
    where ID = #{id,jdbcType=VARCHAR}
  </delete>
  <insert id="insert" parameterType="com.zkyt.lyggis.model.PicInfo" >
    insert into PIC_INFO (ID, IMG, VIDEO)
    values (#{id,jdbcType=VARCHAR}, #{img,jdbcType=BLOB}, #{video,jdbcType=BLOB})
  </insert>
  <insert id="insertSelective" parameterType="com.zkyt.lyggis.model.PicInfo" >
    insert into PIC_INFO
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        ID,
      </if>
      <if test="img != null" >
        IMG,
      </if>
      <if test="video != null" >
        VIDEO,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="id != null" >
        #{id,jdbcType=VARCHAR},
      </if>
      <if test="img != null" >
        #{img,jdbcType=BLOB},
      </if>
      <if test="video != null" >
        #{video,jdbcType=BLOB},
      </if>
    </trim>
  </insert>
  <update id="updateByPrimaryKeySelective" parameterType="com.zkyt.lyggis.model.PicInfo" >
    update PIC_INFO
    <set >
      <if test="img != null" >
        IMG = #{img,jdbcType=BLOB},
      </if>
      <if test="video != null" >
        VIDEO = #{video,jdbcType=BLOB},
      </if>
    </set>
    where ID = #{id,jdbcType=VARCHAR}
  </update>
  <update id="updateByPrimaryKeyWithBLOBs" parameterType="com.zkyt.lyggis.model.PicInfo" >
    update PIC_INFO
    set IMG = #{img,jdbcType=BLOB},
      VIDEO = #{video,jdbcType=BLOB}
    where ID = #{id,jdbcType=VARCHAR}
  </update>
</mapper>

PicInfoMapper.java(dao):

public interface PicInfoMapper {
    int deleteByPrimaryKey(String id);

    int insert(PicInfo record);

    int insertSelective(PicInfo record);

    PicInfo selectByPrimaryKey(String id);

    int updateByPrimaryKeySelective(PicInfo record);

    int updateByPrimaryKeyWithBLOBs(PicInfo record);
}

PicInfo.java(entity)

public class PicInfo {
    private String id;

    private byte[] img;

    private byte[] video;

    public String getId() {
        return id;
    }

    public void setId(String id) {
        this.id = id == null ? null : id.trim();
    }

    public byte[] getImg() {
        return img;
    }

    public void setImg(byte[] img) {
        this.img = img;
    }

    public byte[] getVideo() {
        return video;
    }

    public void setVideo(byte[] video) {
        this.video = video;
    }
}

service（接口）

public interface PicInfoService {
    PicInfo findById(String id);

    int addPic(PicInfo picInfo);
}

serviceImpl（service实现）

@Service
public class PicInfoServiceImpl implements PicInfoService {
    @Resource
    private PicInfoMapper picInfoMapper;
    @Override
    public PicInfo findById(String id) {
        return picInfoMapper.selectByPrimaryKey(id);
    }

    @Override
    public int addPic(PicInfo picInfo) {
        return picInfoMapper.insertSelective(picInfo);
    }
}

controller

@Controller
@RequestMapping("/pic")
public class PicInfoController extends BaseController {
    @Resource
    private PicInfoService picInfoService;

    @RequestMapping("add")
    @ResponseBody
    public void add(PicInfo picInfo){
        JSONObject json = new JSONObject();
        String pic = "F:\\1.jpg";//这里暂时放一张本地图片，也可以让前台传过来
        File file = new File(pic);
        try{
            InputStream is=new FileInputStream(file);//得到文件流
            byte[] bytes = FileCopyUtils.copyToByteArray(is);//得到byte
            picInfo.setId(UUIDsupport.getUUIDWithoutMinus());
            picInfo.setImg(bytes);
            int add =  picInfoService.addPic(picInfo);//添加到数据库中
            if (add > 0) {
                json.put("add", add);
                json.put("msg","上传成功");
            }else {
                json.put("msg","上传失败");
            }
            toClient(json.toString());
        }catch (IOException e) {
            logger.info("异常信息",e);
        }
    }
}

BaseController

/**
 * @ClassName BaseController
 * @Description controller(基类)
 * @author tangjp
 */
public abstract class BaseController<T> {
    protected final Logger logger = LoggerFactory.getLogger(this.getClass());
    /**
     * ThreadLocal确保高并发下每个请求的request，response都是独立的
     */
    private static ThreadLocal<ServletRequest> currentRequest = new ThreadLocal<ServletRequest>();
    private ThreadLocal<ServletResponse> currentResponse = new ThreadLocal<ServletResponse>();
    private ThreadLocal<BasePage<T>> currentPagination = new ThreadLocal<BasePage<T>>();
    // 获得请求对象
    public HttpServletRequest request;
    // 获取取响应对象
    public HttpServletResponse response;
    // 设置mvc常用变量
    @ModelAttribute
    public void setReqAndRes(HttpServletRequest request, HttpServletResponse response) {
        response.setHeader("Access-Control-Allow-Origin", "*");
        this.request = request;
        this.response = response;
    }
    protected BasePage<T> getPagination() {
        return currentPagination.get();
    }

    public void toClient(String str) {
        PrintWriter out = null;
        try {
            response.setHeader("Content-Type", "text/html;charset=UTF-8");
            out = response.getWriter();
            out.print(str);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                out.flush();
                out.close();
                out = null;
            }
        }
    }

}

这样就可以插入图片了，读取等下次来写吧。。。。