简单记录Oracle中blob存储文件

本文介绍：使用Mybaits Oracle BLOB存储，以PDF文件为例

一、将PDF文件存储到BLOB的接口

二、读取BLOB并返回PDF格式的接口

话不多说，直接上代码

1. 创建表

CREATE TABLE "TEST_PDF" (
  "ID" VARCHAR2(50 BYTE) VISIBLE NOT NULL,
  "PDF" BLOB VISIBLE
)
-- ----------------------------
-- Primary Key structure for table TEST_PDF
-- ----------------------------
ALTER TABLE "TEST_PDF" ADD CONSTRAINT "SYS_C0014432" PRIMARY KEY ("ID");

2. mybatis配置

<?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.mapper.TestPdfMapper">

    <resultMap id="BaseResultMap" type="com.bean.TestPdf">
        <!--@mbg.generated-->
        <!--@Table TEST_PDF-->
        <id column="ID" jdbcType="VARCHAR" property="id" />
        <result column="PDF" jdbcType="BLOB" property="pdf" />
    </resultMap>

    <select id="getone" resultMap="BaseResultMap">
        select ID, PDF
        from test_pdf where rownum =1
    </select>

    <insert id="insertOne" parameterType="com.bean.TestPdf">
        <!--@mbg.generated-->
        insert into TEST_PDF (ID
        <if test="savePdf.binaryStream != null">
            , PDF
        </if>
        )
        values (#{id,jdbcType=VARCHAR}
        <if test="savePdf.binaryStream != null">
            , #{savePdf.binaryStream,jdbcType=BLOB}
        </if>
        )
    </insert>
</mapper>

3. mapper层

package com.mapper;

import com.bean.TestPdf;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface TestPdfMapper {
    TestPdf getone();
    int insertOne(TestPdf testPdf);
}

4. Java bean

package com.bean;
import lombok.Data;

import java.sql.Blob;

@Data
public class TestPdf {
    private  String id;
    private  byte[] pdf;
    private Blob savePdf;
}

5. controller层

package com.action;

import com.bean.TestPdf;
import com.mapper.TestPdfMapper;
import com.sys.utils.UUIDUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.sql.rowset.serial.SerialBlob;
import java.io.IOException;
import java.sql.SQLException;

@Controller
@RequestMapping("/testblob")
public class TestPdfAction {
    @Autowired
    TestPdfMapper testPdfMapper;

	/*PDF文件上传存储到oracle blob接口*/
    @RequestMapping("/insertpdf")
    public void insertpdf(@RequestParam("file") MultipartFile multipartFile) throws IOException, SQLException {
        TestPdf testPdf = new TestPdf();
        testPdf.setId(UUIDUtils.getUUID());
        testPdf.setSavePdf(new SerialBlob(multipartFile.getBytes()));
        testPdfMapper.insertOne(testPdf);
    }

	/*PDF文件blob读取*/
    @RequestMapping("/showpdf")
    public ResponseEntity showpdf(HttpServletResponse response){
        TestPdf getone = testPdfMapper.getone();
        HttpHeaders headers = new HttpHeaders();
        headers.setContentType(MediaType.APPLICATION_PDF);
        return  new ResponseEntity(getone.getPdf(), headers, HttpStatus.OK);
    }
}

end 完结！！！