读取oracle库中的blob图片

环境说明

oracle数据库，jdk1.8，springboot后端+mybatis

相关文件

• 引入流操作工具

		<!--io流数据信息copy工具-->
        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.4</version>
        </dependency>

数据准备

• 创建测试表

-- Create table
create table tb_test_img
(
  id   varchar2(64) not null,
  name varchar2(64),
  img  blob
);
-- Add comments to the table 
comment on table tb_test_img
  is '测试图片';
-- Add comments to the columns 
comment on column tb_test_img.id
  is 'id主键';
comment on column tb_test_img.name
  is '姓名';
comment on column tb_test_img.img
  is '头像';
-- Create/Recreate primary, unique and foreign key constraints 
alter table tb_test_img
  add constraint tb_test_img_id primary key (ID);

• 添加数据
  
  

添加测试接口

package com.zwh.boot.controller;

import com.zwh.boot.dto.base.BaseResultRespDto;
import com.zwh.boot.dto.resp.CommonResp;
import com.zwh.boot.entity.SysUser;
import com.zwh.boot.exception.ExceptionController4JSON;
import com.zwh.boot.mapper.CommonMapper;
import com.zwh.boot.mapper.SysUserMapper;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Blob;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Create By zwh
 * 2020/12/16 10:12
 */
@RestController
@RequestMapping(value = "test")
@Slf4j
public class Test extends ExceptionController4JSON {

    @Autowired
    private SysUserMapper sysUserMapper;
    @Autowired
    private CommonMapper commonMapper;
    @RequestMapping(value = "test")
    public BaseResultRespDto test(){
        CommonResp<List<SysUser>> commonResp = new CommonResp<List<SysUser>>();
        List<SysUser> list = sysUserMapper.selectList(null);
        commonResp.setData(list);
        return commonResp.success();
    }

    @RequestMapping(value = "getImgInfo")
    public void getImgInfo(String id, HttpServletResponse response){
        log.info("id:{}",id);
        String sql = "select * from TB_TEST_IMG where id = #{params.id}";
        Map<String,Object> params = new HashMap<>();
        params.put("id",id);
        List<Map<String, Object>> list = commonMapper.selectList(sql,params);
        InputStream in = null;
        ServletOutputStream out = null;
        try {
            response.setContentType("image/jpeg, image/jpg, image/png, image/gif");
            if (list == null || list.size()==0) {
                log.info("数据不存在");
            } else {
                Map<String,Object> map = list.get(0);
                if (map.get("IMG")!=null) {
                    log.info("图片存在");
                    Blob blob = (Blob) map.get("IMG");
                    in = blob.getBinaryStream();
                }
            }
            if (in ==null) {
                log.info("不存在图片，设置默认值");
                String defaultUrl = "images/icon.jpg";
                in = Thread.currentThread().getContextClassLoader().getResourceAsStream(defaultUrl);
            }
            out = response.getOutputStream();
            IOUtils.copy(in,out);
        } catch (Exception e) {
            log.info("e",e);
        } finally {
            if (in!=null) {
                log.info("关闭输入流");
                try {
                    in.close();
                } catch (IOException e) {
                    log.info("关闭输入流异常");
                }
            }
            if (out!=null) {
                log.info("关闭输出流");
                try {
                    out.close();
                } catch (IOException e) {
                    log.info("关闭输出流异常");
                }
            }
        }
     }
}

• 默认图片放在resources下
  

验证

• 查询库图片展示
  
• 未查到返回默认图片