Clob和Blob对象的存取

    技术2022-05-12  11

    作为测试,我们新建一个TUser对象,其image属性中,保存了一个图片文件的二进制内容。而其resume属性,我们以一个简单的字符串作为填充。

     

     1 TUser user  = new  TUser();   2  3 user.setAge( new  Integer( 20 ));   4  5 user.setName( " Shark " );   6  7 FilelnputStream imgis  = new  FileinputStream( " C://inimage.jpg "    8  9 Blob img  =  Hibernate.createBlob(imgis);  10 11 user.setlmage(img);  12 13 Clob resume  =  Hibernate.createClob( " This is Clob " );  14 15 user. setResume(resume);  16 17 Transaction tx  =  session.beginTransaction();  18 19 session.save(user);  20 21 tx.commit();  22 23

     

    上面的代码中,我们通过Hibemate.createBlobHibemate.createClob创建了对应的BlobClob对象。其中Blob对象基于一个FileInputStream构建,而Clob对象基于一个字符串构建。

     

    完成了写入操作,对应的读取操作代码如下:

     

     1 //  假设库表记录的id字段等于3   2 TUser user = (TUser)  session.load(TUger.elaa., load(TUser. class ,   new  Integer( 3 ));   3 Clob resume = user.getResume();   4 //  通过Clob.getSubString()方法获取Clob字段内容   5 System.out.println( " User resume=> " + resume.getSubString( 1 ,( int )resume.length()));   6 Blob img  =  user.getImage();   7 //  通过Blob.getBinaryS=ream()方法获取二进制流   8 InputStream is  =  img.getBinaryStream();   9 FileOutputStream fos = new  FileOutputStream( " C://outimage.jpg " );  10 byte [] buf = new   byte ( 102400 );  11 int  len;  12 while ((len  =  is.read(buf)) !=- 1 ) 13     fos.write(buf, 0 ,len);  14 }   15 fos.close();  16 is.close();  17 18

     

    通过上面的代码,我们完成了针对SQLServerBlob/Clob型字段操作.看起来非常简单,不过,并非侮种数据库都如此友善。让我们接着来看看Oracle数据库下的Blob/Clob字段读写,

    通过修改hibernate.cfg.xml中的Dialect配置完成数据库切换后,我们再次运行上面的TUser对象保存例程。

    程序运行期间抛出异常:

     

    Hibernate:select hibernate_sequence.nextval from dual

    Hibernate:insert into T_USER (name, age,  image,resume. id) values(?, ?, ?, ?, ?)

    17:27:24,161 ERROR JDBCExceptionReporter:58 - - 不允许的操作: Streams type cannot be used in batching

    17:27:24,171 ERROR Sessionlmpl:2399 - Could not synchronize database state with session

    net.sf.hibernate.exception.GenericJDBCException:could not insert:[com.redsaga.hibernate.db.entity.TUser#6]

    ...

     

    观察异常信息:streams type cannot be used in batching.这意味着Oracle JDBC不允许流操作以批量方式执行(Oracle CLOB采用流机制作为数据读写方式)

    这种错误一般发生在hibernate.cfg.xml中的hibernate jdbc.batch_size设定大于0的情况,将hibernate.jdbc.batch_size修改为0即可消除。

     

    <hibernate-configuration>

    <session-factory>

                 ...

                 <property name='hibernate. jdbc.batch_size">0</property>

                 ...

    </session-factory>

    </hibernate-configuration>

     

    再次尝试启动代码,发现依然无法成功运行,抛出异常如下:

     

    Hibernate: select hibernate_sequence.nextval from dual

    Hibernate: insert into T--USER  (name,  age,  image,resume,id) values(?,?,?,?,?)

    19:02:21,054 ERROR JDBCExceptionReporter:58 IO异常:Connection reset bypeer: socket write error

    19:02:21,054 ERROR JDBCExceptionReporter:58 I。异常:Connection reset by peer:socket write error

    19:02:21 064 ERROR JDBCExceptionReporter:58Io异常:Connection reset by peer: socket wrto error

    19:02:21,064 ERROR Sessionlrnpl:2399 Could not synchronize database state with session

    net.sf.hibernate.exception.GenericJDSCException:  could not insert:[com.redsaga.hibernate.db.entity.TUser#27]

    ...

     

    为什么会出现这样的情况?

    问题出在OracceBlob/Clob字段独特的访问方式,Oracle Blob/Clob字段本身拥有一个游标(cursor) , JDBC必须通过游标对Blob/ Clob字段进行操作,在Blob/Clob字段被创建之前,我们无法获取其游标句柄,这也就意味着,我们必须首先创建一个空Blob/Clob字段,再从这个空Blob/Clob字段获取游标,写入我们所期望保存的数据。

    如果用JDBC代码来表示这个过程,则得到如下代码:

     

    //... 获取JDBC连接

    dbconn.setAutoCommit(falee);

    // =======插入数据,BLOB CLOB字段插入空值

    PreparedStatenent preStmt=

    dbconn.prepareStatement(

        "insert into T_USER (name, age,  id,image,resume) values

         (?,?,?,?,?)");

    preStmt.setString(1,"Shark");

    preStmt.setInt(2,20);

    preStmt.setInt(3,5); 

    // 通过oracle.sgl.BLOB/CLOB.empty_lob()方法构造空Blob/Clob对象

    preStmt.setBlob(4 oracle.sgl.BLOB.empty_lob());

    preStmt.setClob(5,oracle.sgl.CLOB.empty_lob());

    preStmt.executeUpdate();

    preStmt.close():

    //========== 再次从库表读出,获得Blob/Clob句柄

    preStmt=

      dbconn.prepareStatement(

          "select  image,resume from T_USER where id=?for update');

    preStmt.setint(l,5);

    ResultSet rset=preStmt.executeQuery();

    // 注意我们这里需要引用Oracle原生BLOB定义,如果使用了Weblogic JDBC Vendor

    // 则应使用weblogic.jdbc.vendor.oracle. OracleThinBLob/OracleThinCLOb

    rset.next();

    oracle.sql.BLOB imqBlob = (oracle.sql.BLOB) rset.getBlob(1);

    oracle.sql.CLOB resClob = (oracle.sql.CLOB) rset.getClob(2);

    //======= 将二进创数据写入Blob

    FileInputStream inStream = new FileinputStream("c//inimage.jpg");

    OutputStream outStream = imgBlob.getBinaryOutputStream();

    byte[] buf=new byte[10240];//10K 读取缓存

    int len;

    while((len=inStream.read(buf))>0){

       outStream.write(buf,0,len);

    }

    inStream.close();

    outStream.close():

    //======= 将字符串写入Clob

    resClob.putString(1 "This is my Glob"

    //======= Blob/Clob字段更新到数据序

    preStmt= dbconn.prepareStatement("update T_USER set  image=?,  resume=? where id=?");

    preStmt.setBlob(1,imgBlob);

    preStmt.setClob(2,resClob):

    preStmt.setlnt(3 5);

    preStmt.executeUpdate();

    preStmt.close():

    dbconn.commit();

    dbconn.close():

     

    上面的代码说明了OracleBlob/Clob字段操作的一般机制,那么,基于Hibernate的持久层实现中,应该如何对Blob/Clob字段进行处理?

    我们知道,Hibernate底层数据访问机制仍然是基于JDBC实现,那么也就意味着我们必须在Hibernate中模拟JDBC的访问流程:

    TUser user=new TUser();

    user.setAge(new Integer(20));

    user.setName("Shark');

    user.setImage(Hibernate.createSlob(new byte [1])):

    user.setResume(Hibernate.createClob(" "));// 注意这里的参教是一个空格

    Transaction tx=session.beginTransaction();

    session.save(user):

    // 调用flush方法,强制Hibernate立即执行insert sql

    session.flush();

    // 通过refresh方法,强制Hibernate执行select for update

    session.refresh(user, LockMode.UPGRADE);

    // Blob写入实际内容

    oracle.sql.BLOB blob=(oracle.sql.BLOB)user.getImage();

    OutputStream out=blob. getBinaryOutputStream();

    FileInputStream imgis=new FileInputStream("C://inimage.jpg");

    byte[] buf=new byte[10240];//10K 缓存

    int len;

    while((len=imgis.read(buf))>0){

      out.write(buf,0,len);

    }

    imgis.close();

    out.close();

    // Clob写入实际内容

    oracle.sql.CLOB clob=(oracle.sgl.CLOB)  user.getResume();

    java.io.Writer writer = clob.getCharacterOutputStream();

    writer.write("this is my  resume');

    writer.close();

    session.save(user);

    tx.commit();

    实际应用中,对于Clob字段,我们可以简单地将其映射为String类型,不过在这种情况下需要注意,Oracle Thin DriverClob字段支持尚有欠缺,当Clob内容超出4000字节时将无法读取,而Oracle OCI Driver(需要在本地安装Oracle客户端组件)则可以成功地完成大容量Clob字段的操作。

    上面的代码中,我们通过Session.save/flush/refresh方法的组合使用,实现了上面JDBC代码中的Blob/Clob访问逻辑。

    Blob/Clob 字段的Hibernate保存实现如上所述,相对来讲,读取则没有太多的障碍,之前的读取代码依然可以正常运行。

    对于上面的实现,相信大家都感觉到了一些Bad Smell,如果Blob/Clob字段普遍存在,那么我们的持久层逻辑中可能遍布如此复杂的数据存储逻辑、并与数据库原生类紧密祸

    如何解决这些问题?

    回忆之前关于自定义数据类型的讨论。通过自定义数据类型我们可以对数据的通用特征进行抽象,那么,对于OracleBlob/Clob字段,我们是否可以也对其进行抽象,并以其作为所有Oracle Blob/Clob字段的映射类型?

    下面的StringClobType实现了这一目标:

    public class StringClobType implements UserType{

        private static final String ORACLE_DRIVER_NAME="Oracle JDBC driver";

           private static final int ORACLE_DRIVER_MAJOR_VERSION=9;

        private static final int ORACLE_DRIVER_MINOR_VERSION=0;

        public int[] sqlTypes(){

           return new int[] {Types.CLOB};

        }

        public Class returnedClass{

           return String.class;

        }

        public boolean equals(Object x, object y){

           return org.apache.commons.lang.ObjectUtils.equals(x, y);

        }

        public Object nullSafeGet(ResultSet rs, String[] names, Object owner)

        throws HibernateException,SQLException{

           Clob clob=rs.getClob(names(O]);

           return(clob==null ? null:clob.getSubString(l,  (int) clob.length())):

        }

        public void nullSafeSet(PreparedStatement st Object value, int index) 

        throws HibernateException, SQLException{

           DatabaseMetaData dbMetaData=st.getConnection().getMetaData();

           if (value==null)

               st.setNull(index,  sqiTypes()(0));

           else

    //         本实现仅仅适用于Oracle数据序9.0以上版本

               if

               (ORACLE_DRIVER_NAME.equals(dbMetaData.getDriverName( ))(

                  if((dbMetaData.getDriverMajorVersion() 

                         >=ORACLE-DRIVER-MAJOR-VERSION)

                      &&(dbMetaData.getDriverMinorVersion()  

                         >=ORACLE-DRIVER-MINOR-VERSION))

                             try

    //                通过动态加载方式进免编译期对Oracle JDBC的依赖

                             Class oracleClobClass=Class.forName('oracle.sgl.CLOB");

    //                              动态调用createTemporary方法

                                    Class partypes[]=new Class[3];

                                    partypes[0]=Connection.class;

                                    partypes[1]=Boolean.TYPE;

                                    partypes(2]=Integer.TYPE;

                                    Method createTemporaryMethod=

                                       oracleClobClass.getDeclaredMethod(

                                              "createTemporaxy “,

                                              partypes);

                                    Field durationSessionField=

                                       oracleClobClass.getField("DURATION-SESSION");

                                    Object arglist[]=new 0bject[3]:

                                       Connection conn=

                                           st.getConnection().getMetaData().getConnection();

    //                              数据库连接类型必须为OracleConnection

    //                              莱些应用服务器会使用自带Oracle JDBC Wrapper,如Weblogic

    //                              这里需要特别注意

                                    Class oracleConnectionClass=

                                       Class.forName("oracle.jdbc.OracleConnection");

                                    if(!oracleConnectionClass

                                           .isAssignableFrom(conn.getClass())){

                                       throw new HibernateException(

                                              "Must be a oracle.jdbc.OracleConnection:.

                                              +conn.getClass().getName());

                                    }

                                    arglist[0] = conn;

                                    arglist(1] = Boolean.TRUE;

                                    arolist[2] = durationSessionField.get(null);

                                    Object tempClob =createTemporaryMethod.invoke(null,arglist);

                                    partypes=new Class[l];

                                    partypes[0]=Integer.TYPE;

                                    Method openMethod =oracleClobClass.getDeclaredMethod("open",partypes);

                                    Field modeReadWriteField =oracleClobClass.getField("MODE_READWRITE");

                                    arglist = new Object(l];

                                    arglis[0] = modeReadWriteField.get(null);

                                    openMethod.invoke(tempClob, arglist);

                                    Method getCharacterOutputStreamMethod=oracleClobClass.getDeclaredMethod("getCharacterOutputStream',null) ;

    //                                     call the getCharacterOutpitStream method

                                           Writer tempClobWriter =(Writer)getCharacterOutputStreamMethod.invoke(tempClob,null);

    //                                     将参数写入Clob

                                           tempClobwriter.write((String) value);

                                           tempClobWriter.flush();

                                           tempClobWriter.Close();

                                          

    //                                     close  clob

                                           Method closeMethod=oracleClobClass.getDeclaredMethod("close", null);

                                           closeMethod.invoke(tempClob, null);

                                           st.setClob(index,  (Clob) tempClob);

                                    )catch  (ClassNotFoundException e){

                                       throw new HibernateException("Unable to find a required class./n"+e.getMessage()):

                                    }catch (NOSuchMethodException e){

                                       throw new HibernateException("Unable to find a required method./n"+e.getMessage()):

                                    }catch (NoSuchFieldException e){

                                       throw new HibernateException("Unable to find a required field./n"+e.getMessage());

                                    }catch (IllegalAccessException e){

                                       throw new HibernateException("Unable to access a required method or field./n"+e.getMessage());

                                       catch (InvocationTargetException e){

                                           throw new HibernateException(e.getMessage());

                                           {  catch (IOException e){

                                              throw new HibernateException(e.getMessage());

                                           }

                                           else

                                           throw new HibernateException(

                                                  "No CLOBS support.Use driver version"

                                                  +ORACLE_DRIVER_MAJOR_VERSION

                                                  +" minor"

                                                  +ORACLE_DRIVER_MINOR_VERSION);

                                           }

                                       }else

                                       String str = (String)  value;

                                        StrinaReader r = new StringReader(str);

                                       St.setCharacterStream(index, r, str.length());

                                    }

        }

        public Object deepCopy(Object value){

           if(value==null)

               return null;

           return new String((String)  value);

        }

        public boolean isMutable(){

           return false

        }

    }

    上面这段代码,重点在于nullSafeSet方法的实现,nullSafeSet中通过Java Reflection机制,解除了编译期的Oralce JDBC原生类依赖。同时,借助Oracle JDBC提供的原生功能完成了Clob字段的写入,Clob字段的写入操作由于涉及特定数据库内部实现细节,这里就不多费唇舌,大家可参见Oracle JDBC Java Doc.

    这段代码是由笔者根据Ali Ibrahim, Scott Miller的代码修改而来的(原版请参见httpJ/www.hibemate org /56.html ),支持Oracle 9以上版本,Oracle 8对应的实现请参见上述网址。

    同样的道理,读者可以根据以上例程,编写自己的ByteBlobType以实现byte[]Blob的映射。

    另外,此代码必须运行在最新版的Oracle JDBC Driver(笔者所用版本为Oracle9i9.2.0.5 for JDK1.4,如果使用9.2.0.3或之前版本则在新建l更却删除数据时可能会遇到“nomore data read from socket”错误)


    最新回复(0)