此实现为用java访问mysql的blob,对图片进行存取
/**
* Title:         BlobPros.java
* Project:       test
* Description:  把图片存入mysql中的blob字段，并取出
* Call Module:  mtools数据库中的tmp表
* File:         C:downloadsluozsh.jpg
* Copyright:    Copyright (c) 2003-2003
* Company:      uniware
* Create Date:  2002.12.5
* @Author:      ChenQH
* @version 1.0 版本*
*
*  Revision history
*  Name         Date     Description
*  ----      ----        -----------
* Chenqh   2003.12.5        对图片进行存取
*
* note:         要把数据库中的Blob字段设为longblob
*
*/
//package com.uniware;
import java.io.*;
import java.util.*;
import java.sql.*;
public class BlobPros
{
private static final String URL = "jdbc:mysql://10.144.123.63:3306/mtools?user=wind&password=123&useUnicode=true";
private Connection conn = null;
private PreparedStatement pstmt = null;
private ResultSet rs = null;
private File file = null;
public BlobPros()
{
}
/**
* 向数据库中插入一个新的BLOB对象(图片)
* @param infile 要输入的数据文件
* @throws java.lang.Exception
*/
public void blobInsert(String infile) throws Exception
{
FileInputStream fis = null;
try
{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
conn = DriverManager.getConnection(URL);
file = new File(infile);
fis = new FileInputStream(file);
//InputStream fis = new FileInputStream(infile);
pstmt = conn.prepareStatement("insert into tmp(descs,pic) values(?,?)");
pstmt.setString(1,file.getName());    //把传过来的第一个参数设为文件名
//pstmt.setBinaryStream(2,fis,(int)file.length());   //这种方法原理上会丢数据，因为file.length()返回的是long型
pstmt.setBinaryStream(2,fis,fis.available());  //第二个参数为文件的内容
pstmt.executeUpdate();
}
catch(Exception ex)
{
System.out.println("[blobInsert error : ]" + ex.toString());
}
finally
{
//关闭所打开的对像//
pstmt.close();
fis.close();
conn.close();
}
}
/**
* 从数据库中读出BLOB对象
* @param outfile 输出的数据文件
* @param picID 要取的图片在数据库中的ID
* @throws java.lang.Exception
*/
public void blobRead(String outfile,int picID) throws Exception
{
FileOutputStream fos = null;
InputStream is = null;
byte[] Buffer = new byte[4096];
try
{
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
conn = DriverManager.getConnection(URL);
pstmt = conn.prepareStatement("select pic from tmp where id=?");
pstmt.setInt(1,picID);         //传入要取的图片的ID
rs = pstmt.executeQuery();
rs.next();
file = new File(outfile);
if(!file.exists())
{
file.createNewFile();     //如果文件不存在，则创建
}
fos = new FileOutputStream(file);
is = rs.getBinaryStream("pic");
int size = 0;
/* while(size != -1)
{
size = is.read(Buffer);    //从数据库中一段一段的读出数据
//System.out.println(size);
if(size != -1)            //-1表示读到了文件末
fos.write(Buffer,0,size);
}  */
while((size = is.read(Buffer)) != -1)
{
//System.out.println(size);
fos.write(Buffer,0,size);
}
}
catch(Exception e)
{
System.out.println("[OutPutFile error : ]" + e.getMessage());
}
finally
{
//关闭用到的资源
fos.close();
rs.close();
pstmt.close();
conn.close();
}
}
public static void main(String[] args)
{
try
{
BlobPros blob = new BlobPros();
//blob.blobInsert("C:Downloadsluozsh1.jpg");
blob.blobRead("c:/downloads/1.jpg",47);
}
catch(Exception e)
{
System.out.println("[Main func error: ]" + e.getMessage());
}
}
}

使用存储过程(PL/SQL)向数据库中存储BLOB对象
Using Create directory & UTL_FILE in Oracle
C:\>sqlplus "/ as sysdba"
SQL*Plus: Release 10.1.0.3.0 - Production on Tue Apr 26 07:11:51 2005
Copyright (c) 1982, 2004, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
SQL> create user eygle identified by eygle default tablespace users;
User created.
SQL> grant connect ,resource,dba to eygle;
Grant succeeded.
SQL> connect / as sysdba
Connected.
SQL> create or replace directory BLOBDIR as 'D:\oradata\Pic';
Directory created.
SQL> grant read on directory BLOBDIR to eygle;
Grant succeeded.
SQL>
2.创建测试表
SQL> connect eygle/eygle
Connected.
SQL> CREATE TABLE eygle_blob (
2  fid    number,
3  fname       varchar2(50),
4  fdesc  varchar2(200),
5  fpic        BLOB)
6  /
Table created.
SQL>
SQL> create sequence S_EYGLE_SEQ
2  start with 1
3  increment by 1
4  /
Sequence created.
SQL>
3.创建存储过程
SQL> CREATE OR REPLACE PROCEDURE eygle_load_blob (pfname VARCHAR2,pdesc varchar2)
2  IS
3  src_file BFILE;
4  dst_file BLOB;
5  lgh_file BINARY_INTEGER;
6  BEGIN
7     src_file := bfilename('BLOBDIR', pfname);
8
9     INSERT INTO eygle_blob (fid,fname,fdesc,fpic)
10     VALUES (S_EYGLE_SEQ.Nextval,pfname,pdesc,EMPTY_BLOB())
11     RETURNING fpic INTO dst_file;
12
13     SELECT fpic INTO dst_file
14     FROM eygle_blob  WHERE fname = pfname FOR UPDATE;
15
16     dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
17     lgh_file := dbms_lob.getlength(src_file);
18     dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
19
20    UPDATE eygle_blob  SET fpic = dst_file
21    WHERE fname = pfname;
22
23    dbms_lob.fileclose(src_file);
24    commit;
25  END eygle_load_blob;
26  /
Procedure created.
SQL> col segment_name for a30
SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';
SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
SYS_IL0000050545C00004$$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$$      LOBSEGMENT                   .0625
EYGLE_BLOB                     TABLE                        .0625
4.加载Blob对象
SQL> exec eygle_load_blob('ShaoLin.jpg','少林寺-康熙手书');
PL/SQL procedure successfully completed.
SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';
SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
SYS_IL0000050545C00004$$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$$      LOBSEGMENT                       4
EYGLE_BLOB                     TABLE                        .0625
SQL> exec eygle_load_blob('DaoYing.jpg','倒映');
PL/SQL procedure successfully completed.
SQL> select segment_name,segment_type,bytes/1024/1024 from dba_segments where owner='EYGLE';
SEGMENT_NAME                   SEGMENT_TYPE       BYTES/1024/1024
------------------------------ ------------------ ---------------
SYS_IL0000050545C00004$$       LOBINDEX                     .0625
SYS_LOB0000050545C00004$$      LOBSEGMENT                       7
EYGLE_BLOB                     TABLE                        .0625
SQL> col fname for a20
SQL> col fdesc for a30
SQL> select fid,fname,fdesc,dbms_lob.getlength(fpic) siz from eygle_blob;
FID FNAME                FDESC                                 SIZ
---------- -------------------- ------------------------------ ----------
1 ShaoLin.jpg          少林寺-康熙手书                   1768198
2 DaoYing.jpg          倒映                              2131553
D:\oradata\Pic>ls -l
-rwxrwxrwa   1 gqgai           None            2131553 Apr 19 10:12 DaoYing.jpg
-rwxrwxrwa   1 gqgai           None            1768198 Apr 19 10:12 ShaoLin.jpg
通过以上方式，我们可以很容易的把大对象存储到数据库中。
历史上的今天...
>>
2009-04-28文章:
2008-04-28文章:
2006-04-28文章:
By eygle on 2005-04-28 10:12 |
Comments (11) |
SQL.PLSQL | 274 |
11 Comments
eygle :
能再进一步,把本地xml文件,导入到数据库中(ver>9i).
RETURNING fpic INTO dst_file;
SELECT fpic INTO dst_file
FROM eygle_blob  WHERE fname = pfname FOR UPDATE;
是否有些重复?如果没有,为什么?对于Blob类型的处理必须这样吗?
如何使用存储过程(入参是blob类型)java客户端调用存储过程将客户端的文件保存到数据库中呢?
http://www.oracle.com/technology/global/cn/sample_code/tech/java/codesnippet/jdbc/lob/LobToSP.html
这是个关于clob的例子
老大，怎么更新这个blob字段呢？
单表上传的例子，我已经搞定了。
能否把把表和字段也做成参数传递进过程？
我这里有多个表具有BLOB字段，想通过一个过程来实现上传文件。不知道行不行！
D:\oradata\Pic>ls -l
ls不是unix下的命令吗？D:\oradata\Pic>看上去像windows啊？
Windows上也是可以安装类Unix软件包，来获得这些命令增强。
大侠 你好
我按你的步骤怎么出现错误呢：
--建立目录:
create or replace directory BLOB_DIR as 'F:\oradata';
--创建存储过程
CREATE OR REPLACE PROCEDURE microle_load_blob (pfname VARCHAR2,pdesc varchar2)
IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := bfilename('BLOB_DIR', pfname);
INSERT INTO microle_blob(fid,fname,fdesc,fpic)
VALUES (S_MICROLE_SEQ.Nextval,pfname,pdesc,EMPTY_BLOB())
RETURNING fpic INTO dst_file;
SELECT fpic INTO dst_file
FROM microle_blob
WHERE fname = pfname
FOR UPDATE;
dbms_lob.fileopen(src_file, dbms_lob.file_readonly);
lgh_file := dbms_lob.getlength(src_file);
dbms_lob.loadfromfile(dst_file, src_file, lgh_file);
UPDATE microle_blob  SET fpic = dst_file
WHERE fname = pfname;
dbms_lob.fileclose(src_file);
commit;
END microle_load_blob;
--调用
exec microle_load_blob('P5140116.JPG','CS');
--出现错误如下
ORA-22288: 文件或 LOB 操作FILEOPEN失败
设备未就绪。
这个方法应该适用于BLOB,CLOB这两种类型。
对于Long,Long Raw,Raw可能需要考虑采用别的途径来进行解决 。
这个方法应该适用于BLOB,CLOB这两种类型。
对于Long,Long Raw,Raw可能需要考虑采用别的途径来进行解决 。

封装好的ImageUtil类：目的读取本地的图片文件并存入数据库，然后读出数据库中以Blob形式存储的图片保存到指定目录。
package org.blog.util;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
public class ImageUtil {
private static File file = null;
/**
* 读取图像的二进制流
*
* @param infile
* @return
*/
public static FileInputStream getByteImage(String infile) {
FileInputStream inputImage = null;
file = new File(infile);
try {
inputImage = new FileInputStream(file);
} catch (FileNotFoundException e) {
e.printStackTrace();
}
return inputImage;
}
/**
* 输出图片
* @param inputStream
* @param path
*/
public static void readBlob(FileInputStream inputStream, String path) {
try {
FileOutputStream fileOutputStream = new FileOutputStream(path);
byte[] buf = new byte[1024];
int len = 0;
while ((len = inputStream.read(buf)) != -1) {
fileOutputStream.write(buf, 0, len);// 写
}
inputStream.close();
fileOutputStream.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
}
从数据库中读出二进制流显示到jsp页面：
servlet源码：
package servlet;
import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.io.PrintWriter;
import java.sql.Blob;
import javax.servlet.ServletException;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.blog.util.ImageUtil;
import org.hibernate.Hibernate;
public class Image extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
this.doPost(req, resp);
}
@Override
protected void doPost(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
try {
FileInputStream in = ImageUtil.getByteImage("D:\\me.jpg");
Blob blob = Hibernate.createBlob(in);
InputStream inputStream = blob.getBinaryStream();// IO流
int length = (int) blob.length();
byte[] b = new byte[length];
inputStream.read(b, 0, length);
PrintWriter out = resp.getWriter();
InputStream is = new ByteArrayInputStream(b);
int a = is.read();
while (a != -1) {
out.print((char) a);
a = is.read();
}
out.flush();
out.close();
/*OutputStream outputStream = resp.getOutputStream();// 从response中获取getOutputStream
outputStream.write(b);// 写
inputStream.close();
outputStream.close();*/
} catch (Exception e) {
System.out.println("error");
}
}
}
jsp源码：
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
My JSP 'image.jsp' starting page
 
jsp从数据库中读取二进制图片显示在页面上，要求图片在规定的长宽中显示，大的缩写，小的还是原图大小代码
200)this.width=200;if(this.height>80)this.height=80" src='GetPhoServlet.do?objectFlag=2&photo_no=' />

http://bbs.chinaunix.net/archiver/tid-2289421.html
1把图片当成一个二进制流就可以了
。mysql中有可以存储很大的2进制流文件。用的类型是：
我们要做的是将一张图片存入Mysql中,在Mysql中用Blob
来存储图片和音频等大的数据项.Blob 按其容量可分为四种,分别为:tinyblob,blob ,mediumblob,
longblob.他们的大小分别为:256B,64KB,16MB,4GB.除了容量不同外,这四种的用法一个样
2 将一个图片文件输入到mysql
：
FileInputStream fin = new FileInputStream(image);//生成的流
PreparedStatement ps = con.prepareStatement("insert into id_image values(?, ?)");//
ps.setInt(1, i);
ps.setBinaryStream(2, fin, len);
ps.executeUpdate();
插入就完成了。。
3 将一个图片读出来
，以流的形式存放。
Statement stm = conn.createStatement();
String sql = "select * from id_image where id = "
+ new Integer(id).toString();
ResultSet rs = stm.executeQuery(sql);
Blob blob = (Blob)rs.getBlob("image");
InputStream is = blob.getBinaryStream();
然后就可以利用is进行想要的操作了。
先用Blob格式存储，然后变为
分享到：
      
      
2011-04-26 11:23
浏览 871
评论