[转]BLOB 和 CLOB 简单用法

如果将要记录写入数据库,可以在字段上使用BLOB或CLOB类型字段,BLOB(Binary Large Object)用于存储大量的二进制位数据,CLOB(Character Large Object)用于存储大量的文字资料.
在JDBC中也提供了Blob与Clob两个类型分别代表BLOB与CLOB类型数据,JDBC并没有提供直接存取BLOB或CLOB的API(诸如setBlob(),setClob()等,但从JDBC4.0开始已经提供了这两个方法),但可以使用PrepareStatement的setBinaryStream(),setObject(),setAsciiStream(),setUnicodeStream()等方法来代替,例如可以用下方法取得一个文件,并将其存入数据库中:
           File file=new File(".//logo.jpg");
           int length=(int)file.length();
           InputStream fin=new FileInputStream(file);
           PreparedStatement pstmt=conn.preparedStatement("INSERT INTO files VALUES(?,?)");
           pstmt.setString(1,"LOGO");
           pstmt.setBinaryStream(2,fin,length);   //将文件流写入
           pstmt.executeUpdate();
           pstmt.clearParameters();
           pstmt.close();
           fin.close();
如果要从数据库中取得BLOB和 CLOB类型数据,可以用以下方法:
          Blob blob=result.getBlob(2); 
          Clob clob=result.getClob(2);

Blob拥有getBinaryStream(),getBytes()等方法,可以取得二进制串流或byte等资料,同样,Clob可以通过getCharacterStream(),getSubString()等方法,可以取得字元串流或子字串等数据(详细查看API文件)

下面举例说明操作Blob,Clob数据
import java.io.*;
import java.sql.*;
public class BLOBCLOBDemo{
         public static void main(String[] args){
                   String driver="com.mysql.jdbc.Driver";
                   String url="jdbc:mysql://localhost:3306/upload?";
                   String username="myname";
                   String password="123456";
                  try{
                          Connection conn=conn.getConnection(url,username,password);
                          File file=new File(".//logo.jpg");
                          int length=(int) file.length();
                          InputStream fin=new FileInputStream(file);
                          PreparedStatement pstmt=new PreparedStratement("INSERT  INTO files VALUSE(?,?)");
                          pstmt.setString(1,"LOGO");
                          pstmt.setBinaryStream(2,fin,length);
                          pstmt.executeUpdate();
                          pstmt.clearParameters();
                          pstmt.close();
                          fin.close();
                          //取出资料并存档
                          Statement stmt=conn.createStatement();
                          ResultSet result=stmt.executeQuery("SELECT * FROM files");
                          result.next();
                          String description=result.getString(1);
                          Blob blob=result.getBlob(2);
                        
                          System.out.println("文件描述:"+description);
                         FileOutputStream fout=new FileOutputStream(".//logo2.jpg");
                          fout.write(blob.getBytes(1,(int)blob.length()));
                          fout.flush();
                          fout.close();
                          stmt.close();
                          conn.close();
                   }
                    catch(ClassNotFoundException e) {
                           System.out.println("找不到驱动程序");
                           e.printStackTrace();
                   }
                   catch(SQLException e) {
                           e.printStackTrace();
                   }
                  catch(IOException e) {
                        e.printStackTrace();
                   }
          }
}