mysql和Oracle在对clob和blob字段的处理

一、MySQL与Oracle数据库如何处理Clob，Blob数据类型

(1)不通数据库中对应clob,blob的类型如下：
   MySQL中：clob对应text，blob对应blob
   DB2/Oracle中：clob对应clob，blob对应blob

(2)domain中对应的类型：
   clob对应String，blob对应byte[]
   clob对应java.sql.Clob，blob对应java.sql.Blob

(3)hibernate配置文件中对应类型：
   clob-->clob ，blob-->binary
   也可以直接使用数据库提供类型，例如：oracle.sql.Clob，oracle.sql.Blob

二、jdbc操作clob(以oracle为例)
首先操作clob/blob不像操作varchar类型那样简单，插入步骤一般分为两步：第一步插入一个空值，第二步锁住此行，更新clob/blob字段。

//插入空值
conn.setAutoCommit(false);
String sql = "INSERT INTO T_FILE(NAME, FILE_CONTENT) VALUES ('Jambhala', EMPTY_CLOB())";
PreparedStatement pstmt = conn.prepareStatement(sql); 
pstmt.executeUpdate();
//锁住此行
String sql_lockstr = "SELECT FILE_CONTENT FROM T_FILE WHERE NAME='Jambhala' FOR UPDATE";
pstmt = conn.prepareStatement(sql_lockstr); 
ResultSet rs = pstmt.executeQuery(); 
oracle.sql.Clob clob = (oracle.sql.Clob)rs.getClob(1);
java.io.OutputStream writer = clob.getAsciiOutputStream(); 
byte[] temp = newFileContent.getBytes(); 
writer.write(temp); 
writer.flush(); 
writer.close();
pstmt.close();

读取内容：
oracle.sql.Clob clob = rs.getClob("FILE_CONTENT");
if(clob != null){
    Reader is = clob.getCharacterStream();
    BufferedReader br = new BufferedReader(is);
    String s = br.readLine();
    while(s != null){
    	content += s+"<br>";
        s = br.readLine();
    }
}

三、jdbc操作blob
conn.setAutoCommit(false);
String sql = "INSERT INTO T_PHOTO(NAME, PHOTO) VALUES ('Jambhala', EMPTY_BLOB())";
pstmt = conn.prepareStatement(sql); 
pstmt = conn.executeUpdate();
sql = "SELECT PHOTO FROM T_PHOTO WHERE NAME='Jambhala'";
pstmt = conn.prepareStatement(sql); 
rs = pstmt.executeQuery(sql);
if(rs.next()){
   oracle.sql.Blob blob = (oracle.sql.Blob)rs.getBlob(1);
}
//write to a file
File file=new File("C:\\test.rar");
FileInputStream fin = new FileInputStream(file);
OutputStream out = blob.getBinaryOutputStream();
int count=-1,total=0;
byte[] data = new byte[blob.getBufferSize()];
while((count=fin.read(data)) != -1){
   total += count;
   out.write(data, 0, count);
} 

四、hibernate处理clob
MyFile file = new MyFile();
file.setName("Jambhala");
file.setContent(Hibernate.createClob(""));
session.save(file);
session.flush();
session.refresh(file, LockMode.UPGRADE);
oracle.sql.Clob clob = (oracle.sql.Clob)file.getContent();
Writer pw = clob.getCharacterOutputStream();
pw.write(longText);   //写入长文本
pw.close();
session.close();

五、使用hibernate处理blob
原理基本相同：
Photo photo = new Photo();
photo.setName("Jambhala");
photo.setPhoto(Hibernate.createBlob(""));
session.save(photo);
session.flush();

session.refresh(photo, LockMode.UPGRADE);  //锁住此对象
oracle.sql.Blob blob = photo.getPhoto();   //取得此blob的指针
OutputStream out = blob.getBinaryOutputStream();
//写入一个文件
File f = new File("C:\\test.rar");
FileInputStream fin = new FileInputStream(f);
int count=-1,total=0;
byte[] data = new byte[(int)fin.available()];
out.write(data);
fin.close();
out.close();
session.flush();


String DRIVER = "oracle.jdbc.driver.OracleDriver";
//Oracle连接用URL
private static final String URL = "jdbc:oracle:thin:@testora:1521:orac";
//用户名
private static final String USER = "scott";
//密码
private static final String PASSWORD = "pswd";
//数据库连接
private static Connection conn = null;
//SQL语句对象
private static Statement stmt = null;
//@roseuid 3EDA089E02BC
public LobPros(){}

//往数据库中插入一个新的Clob对象
//@param infile  数据文件
//@throws java.lang.Exception
//@roseuid 3EDA089E02BC
public static void clobInsert(String infile) throws Exception {
   //设定不自动提交
   boolean defaultCommit = conn.getAutoCommit(); 
   conn.setAutoCommit(false);
   try{
	//插入一个空的Clob对象
        stmt.executeUpdate("INSERT INTO TEST_CLOB VALUES ('111', EMPTY_CLOB())");
        //查询此Clob对象并锁定
        ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
        while(rs.next()){
	   //取出此Clob对象
           oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
           //向Clob对象中写入数据
           BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream()); 
           BufferedReader in = new BufferedReader(new FileReader(infile));
           int c;
           while((c=in.read()) != -1){
	      out.write(c);
           }
           in.close();
           out.close();
        }
        //正式提交
        conn.commit();
   }catch(Exception e){
      //出错回滚
      conn.rollback();
      throw e;
   }

   //恢复原提交状态
   conn.setAutoCommit(defaultCommit);
}

//修改Clob对象(是在原Clob对象基础上进行覆盖式的修改)
//@param infile  数据文件
//@throws java.lang.Exception
//@roseuid 3EDA089E02BC
public static void clobModify(String infile) throws Exception {
   //设定不自动提交
   boolean defaultCommit = conn.getAutoCommit(); 
   conn.setAutoCommit(false);
   try{
	//查询Clob对象并锁定
        ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
        while(rs.next()){
	   //获取此Clob对象
           oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
           //进行覆盖式修改
           BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream()); 
           BufferedReader in = new BufferedReader(new FileReader(infile)); 
           int c;
           while ((c=in.read())!=-1) { 
              out.write(c); 
           } 
           in.close(); 
           out.close(); 
        }
        //正式提交
        conn.commit();
   }catch(Exception e){
      //出错回滚
      conn.rollback();
      throw e;
   }
   //恢复原提交状态
   conn.setAutoCommit(defaultCommit);
}

//替换CLOB对象（将原CLOB对象清除，换成一个全新的CLOB对象
//@param infile  数据文件
//@throws java.lang.Exception
//@roseuid 3EDA04BF01E1
public static void clobReplace(String infile) throws Exception {
   //设定不自动提交
   boolean defaultCommit = conn.getAutoCommit();
   conn.setAutoCommit(false);
   try{
	//清空原CLOB对象
        stmt.executeUpdate("UPDATE TEST_CLOB SET CLOBCOL=EMPTY_CLOB() WHERE ID='111'");
        //查询CLOB对象并锁定
        ResultSet rs = stmt.executeQuery("SELECT CLOBCOL FROM TEST_CLOB WHERE ID='111' FOR UPDATE");
        while (rs.next()) {
	   //获取此CLOB对象
           oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
           //更新数据
           BufferedWriter out = new BufferedWriter(clob.getCharacterOutputStream());
           BufferedReader in = new BufferedReader(new FileReader(infile)); 
           int c;
           while ((c=in.read())!=-1) { 
	      out.write(c); 
	   } 
	   in.close(); 
	   out.close();
        }
	//正式提交
	conn.commit();
   }catch(Exception e){
	//出错回滚
	conn.rollback(); 
	throw e;
   }
   //恢复原提交状态
   conn.setAutoCommit(defaultCommit);
}

//CLOB对象读取
//@param outfile  输出文件名
//@throws java.lang.Exception
//@roseuid 3EDA04D80116
public static void clobRead(String outfile) throws Exception {
   //设定不自动提交
   boolean defaultCommit = conn.getAutoCommit();
   conn.setAutoCommit(false);
   try{
        //查询CLOB对象
        ResultSet rs = stmt.executeQuery("SELECT * FROM TEST_CLOB WHERE ID='111'");
        while (rs.next()) {
	   //获取CLOB对象
           oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("CLOBCOL");
           //以字符形式输出
           BufferedReader in = new BufferedReader(clob.getCharacterStream()); 
           BufferedWriter out = new BufferedWriter(new FileWriter(outfile)); 
           int c;
           while ((c=in.read())!=-1) {
              out.write(c);
           }
	   out.close(); 
	   in.close();
        }
   }catch(Exception e){
       conn.rollback(); 
       throw e;
   }
   //恢复原提交状态
   conn.setAutoCommit(defaultCommit);
}

//向数据库中插入一个新的BLOB对象 
//@param infile  数据文件 
//@throws java.lang.Exception 
//@roseuid 3EDA04E300F6
public static void blobInsert(String infile) throws Exception { 
   //设定不自动提交
   boolean defaultCommit = conn.getAutoCommit(); 
   conn.setAutoCommit(false); 
   try { 
	//插入一个空的BLOB对象 
	stmt.executeUpdate("INSERT INTO TEST_BLOB VALUES ('222', EMPTY_BLOB())"); 
	//查询此BLOB对象并锁定 
	ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"); 
	while (rs.next()) { 
	   //取出此BLOB对象 
	   oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL"); 
	   //向BLOB对象中写入数据 
	   BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream()); 
	   BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile)); 
	   int c; 
	   while ((c=in.read())!=-1) { 
		out.write(c); 
	   } 
	   in.close(); 
	   out.close(); 
	} 
	//正式提交 
	conn.commit(); 
    } catch (Exception e) { 
	//出错回滚 
	conn.rollback(); 
	throw e; 
    } 
    //恢复原提交状态 
    conn.setAutoCommit(defaultCommit); 
} 

//修改BLOB对象（是在原BLOB对象基础上进行覆盖式的修改） 
//@param infile  数据文件 
//@throws java.lang.Exception 
//@roseuid 3EDA04E90106 
public static void blobModify(String infile) throws Exception { 
    //设定不自动提交 
    boolean defaultCommit = conn.getAutoCommit(); 
    conn.setAutoCommit(false); 
    try { 
	//查询BLOB对象并锁定 
	ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"); 
	while (rs.next()) { 
	   //取出此BLOB对象 
	   oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL"); 
	   //向BLOB对象中写入数据 
	   BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream()); 
	   BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile)); 
	   int c; 
	   while ((c=in.read())!=-1) { 
	      out.write(c); 
	   } 
	   in.close(); 
	   out.close(); 
	} 
	//正式提交 
	conn.commit(); 
    } catch (Exception e) { 
	//出错回滚 
	conn.rollback(); 
	throw e; 
    } 
    //恢复原提交状态 
    conn.setAutoCommit(defaultCommit); 
} 

//替换BLOB对象（将原BLOB对象清除，换成一个全新的BLOB对象） 
//@param infile  数据文件 
//@throws java.lang.Exception 
//@roseuid 3EDA0505000C 
public static void blobReplace(String infile) throws Exception { 
   //设定不自动提交 
   boolean defaultCommit = conn.getAutoCommit(); 
   conn.setAutoCommit(false); 
   try { 
	//清空原BLOB对象 
	stmt.executeUpdate("UPDATE TEST_BLOB SET BLOBCOL=EMPTY_BLOB() WHERE ID='222'"); 
	//查询此BLOB对象并锁定 
	ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222' FOR UPDATE"); 
	while (rs.next()) { 
	   //取出此BLOB对象 
	   oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL"); 
	   //向BLOB对象中写入数据 
	   BufferedOutputStream out = new BufferedOutputStream(blob.getBinaryOutputStream()); 
	   BufferedInputStream in = new BufferedInputStream(new FileInputStream(infile)); 
	   int c; 
	   while ((c=in.read())!=-1) { 
	      out.write(c); 
	   } 
	   in.close(); 
	   out.close(); 
        } 
	//正式提交 
	conn.commit(); 
   } catch (Exception e) { 
	//出错回滚 
	conn.rollback(); 
	throw e; 
   } 
   //恢复原提交状态 
   conn.setAutoCommit(defaultCommit); 
} 

//BLOB对象读取 
//@param outfile  输出文件名 
//@throws java.lang.Exception 
//@roseuid 3EDA050B003B 
public static void blobRead(String outfile) throws Exception { 
   //设定不自动提交 
   boolean defaultCommit = conn.getAutoCommit(); 
   conn.setAutoCommit(false); 
   try { 
         //查询BLOB对象 
	 ResultSet rs = stmt.executeQuery("SELECT BLOBCOL FROM TEST_BLOB WHERE ID='222'"); 
	 while (rs.next()) { 
	    //取出此BLOB对象 
	    oracle.sql.BLOB blob = (oracle.sql.BLOB)rs.getBlob("BLOBCOL"); 
	    //以二进制形式输出 
	    BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(outfile)); 
	    BufferedInputStream in = new BufferedInputStream(blob.getBinaryStream()); 
	    int c; 
	    while ((c=in.read())!=-1) { 
	       out.write(c); 
            } 
	    in.close(); 
	    out.close(); 
         } 
	 //正式提交 
         conn.commit(); 
   } catch (Exception e) { 
	//出错回滚 
	conn.rollback(); 
	throw e; 
   } 
   //恢复原提交状态 
   conn.setAutoCommit(defaultCommit); 
} 

//建立测试用表格 
//@throws Exception 
public static void createTables() throws Exception { 
   try { 
	stmt.executeUpdate("CREATE TABLE TEST_CLOB (ID NUMBER(3), CLOBCOL CLOB)"); 
	stmt.executeUpdate("CREATE TABLE TEST_BLOB (ID NUMBER(3), BLOBCOL BLOB)"); 
   } catch (Exception e) { } 
} 

//@param args - 命令行参数 
//@throws java.lang.Exception 
//@roseuid 3EDA052002AC 
public static void main(String[] args) throws Exception { 
   //装载驱动,建立数据库连接 
   Class.forName(DRIVER); 
   conn = DriverManager.getConnection(URL,USER,PASSWORD); 
   stmt = conn.createStatement(); 
   //建立测试表格 
   createTables(); 
   //CLOB对象插入测试 
   clobInsert("c:/clobInsert.txt"); 
   clobRead("c:/clobInsert.out"); 
   //CLOB对象修改测试 
   clobModify("c:/clobModify.txt"); 
   clobRead("c:/clobModify.out"); 
   //CLOB对象替换测试 
   clobReplace("c:/clobReplace.txt"); 
   clobRead("c:/clobReplace.out"); 
   //BLOB对象插入测试 
   blobInsert("c:/blobInsert.doc"); 
   blobRead("c:/blobInsert.out"); 
   //BLOB对象修改测试 
   blobModify("c:/blobModify.doc"); 
   blobRead("c:/blobModify.out"); 
   //BLOB对象替换测试 
   blobReplace("c:/blobReplace.doc"); 
   blobRead("c:/bolbReplace.out"); 
   //关闭资源退出 
   conn.close(); 
   System.exit(0); 
}

 

评论

1 楼 Power_Song 2015-04-18