Oracle中插入图片并显示(用BLOB类型)

Oracle中插入图片并显示(用BLOB类型)
要在oracle里面存入图片 用 blob类型


首先在数据库里建立：

--连接到管理员
conn sys/tbsoft as sysdba;

--为scott用户授权

grant create any directory to scott;

--回到scott用户

conn scott/tiger;


--创建存储图片的表
CREATE TABLE IMAGE_LOB (T_ID VARCHAR2 (5) NOT NULL,T_IMAGE BLOB NOT NULL);

--创建存储图片的目录
CREATE OR REPLACE DIRECTORY IMAGES AS 'C:\picture';

--在c：下自己建一个叫picture的文件夹

CREATE OR REPLACE PROCEDURE IMG_INSERT (TID VARCHAR2,FILENAME VARCHAR2) AS
F_LOB BFILE;--文件类型
B_LOB BLOB;
BEGIN
iNSERT INTO IMAGE_LOB (T_ID, T_IMAGE)
VALUES (TID,EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB;
--插入空的blob
F_LOB:= BFILENAME ('IMAGES', FILENAME);
--获取指定目录下的文件
DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY);
--以只读的方式打开文件
DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,DBMS_LOB.GETLENGTH (F_LOB));
--传递对象
DBMS_LOB.FILECLOSE (F_LOB);
--关闭原始文件
COMMIT;
END;
/


--在C:\picture下放一张图片1.gif

--将该图片存入表
call IMG_INSERT('1','1.gif');


然后创建一个web项目 连接数据库后 创建一个BlobDAO类 用来取出表中的blob类型图片

public class BlobDAO {

    private static final BlobDAO instance = new BlobDAO();

    private Connection conn = null;

    private BlobDAO() {

    }

    public static BlobDAO getInstance() {
        return instance;
    }

    private void initConn() {
        conn = DBAccess.getInstance().getConn();
    }

    
    public byte[] getImage(String imgname) {
        BufferedInputStream ins;//取得BLOB的IO流
        byte[] bt = null;

        initConn();
        Blob bo = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "select T_IMAGE from IMAGE_LOB where t_id=?";
        try {
              ps = conn.prepareStatement(sql);
              ps.setString(1, imgname);
              rs = ps.executeQuery();
              if (rs.next()) {
                  bo = rs.getBlob("T_IMAGE");

                  try {
                      ins = new BufferedInputStream(bo.getBinaryStream());
                      int bufferSize = (int) bo.length();//取得BLOB的长度
                      bt = new byte[bufferSize];
                      try {
                            ins.read(bt, 0, bufferSize);
                      } catch (IOException e) {
                            // TODO Auto-generated catch block
                            e.printStackTrace();
                      }
                      //建立字节缓存
                  } catch (SQLException e) {
                      // TODO Auto-generated catch block
                      e.printStackTrace();
                  }

              }
        } catch (SQLException e) {
              // TODO Auto-generated catch block
              e.printStackTrace();
        } finally {
              try {
                  rs.close();
                  ps.close();
                  conn.close();
              } catch (SQLException e) {
                  // TODO Auto-generated catch block
                  e.printStackTrace();
              }
        }

        return bt;
    }
}

在action里面调用getImage()方法并显示图片在页面上

public ActionForward execute(ActionMapping mapping, ActionForm form,
              HttpServletRequest request, HttpServletResponse response) {
        // TODO Auto-generated method stub
        
        BlobDAO blobDAO = BlobDAO.getInstance();
        
        byte[] bs = blobDAO.getImage("1");
        
        try {
              
              response.getOutputStream().write(bs);
              
        } catch (IOException e) {
              // TODO Auto-generated catch block
              e.printStackTrace();
        }
        
        return null;
    }

添加图片到数据库

请在c盘下放入图片--c:\\4.gif

public void savaImg(String imgId) {
           //传的是存入数据库图片的id
           initConn();
           Statement st = null;
           BLOB blob = null; //图片类型
           OutputStream outputStream = null; //输出流
           File file = null; //文件
           InputStream inputStream = null; //输入流
           ResultSet rs = null;
           try {
                 conn.setAutoCommit(false); //事物由程序员操作
                 st = conn.createStatement();
                 st.executeQuery("insert into IMAGE_LOB values('"+ imgId +"',empty_blob())");
                 rs = st.executeQuery("select T_IMAGE from IMAGE_LOB where t_id='"+ imgId +"' for update");
                 if (rs.next()) {
                       blob = (BLOB) rs.getBlob(1);
                       outputStream = blob.getBinaryOutputStream();
                       file = new File("c:\\4.gif");
                       inputStream = new FileInputStream(file);
                       byte[] b = new byte[blob.getBufferSize()];
                       int len = 0;
                       while ((len = inputStream.read(b)) != -1) {
                             System.out.println(len);
                             outputStream.write(b, 0, len);
                       }
                 }

           } catch (SQLException e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
           } catch (FileNotFoundException e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
           } catch (IOException e) {
                 // TODO Auto-generated catch block
                 e.printStackTrace();
           } finally {
                 try {
                       inputStream.close();
                       outputStream.flush();
                       outputStream.close();
                       rs.close();
                       st.close();
                       conn.commit();
                       conn.close();
                 } catch (IOException e) {
                       // TODO Auto-generated catch block
                       e.printStackTrace();
                 } catch (SQLException e) {
                       // TODO Auto-generated catch block
                       e.printStackTrace();
                 }

           }
     }

操作完毕！

评论

2 楼 minma_2009 2009-06-18  

minma_2009 写道

好像用不起来吗？按照以上的试过了，向数据库中插入图片，总是报错，该有的权限我都给分配了，就是不行，错误提示如下：
ORA-22288: file or LOB operation FILEOPEN failed
No such file or directory
ORA-06512: at "SYS.DBMS_LOB", line 523
ORA-06512: at "STAT.IMG_INSERT", line 10

1 楼 minma_2009 2009-06-18  

好像用不起来吗？按照以上的试过了，向数据库中插入图片，总是报错，该有的权限我都给分配了，就是不行，错误提示如下：