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 fileFile 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连接用URLprivate 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 3EDA089E02BCpublic LobPros(){}//往数据库中插入一个新的Clob对象//@param infile  数据文件//@throws java.lang.Exception//@roseuid 3EDA089E02BCpublic 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 3EDA089E02BCpublic 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 3EDA04BF01E1public 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 3EDA04D80116public 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 3EDA04E300F6public 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); }