Hibernate操作Oarcle中Clob、Blob字段小结

最近常碰到有人问如何运用Hibernate操作Oracle中的Clob、Blob字段,相关问题大概如下几种:

  • 如何读、写数据库中的Clob、Blob字段?
  • 注释方式或者映射文件两种方式是如何实现的?
  • Clob字段是否可当成一般String来操作?
  • 为什么Clob字段插入正常,但是读取时却为null
  • JDBC的驱动是不是需要最新的,才能支持Clob当成String?
  • JDBC驱动不更新,有没有办法实现Clob当成String操作?
  • 。。。。。等等这些问题

下面我将以不同的实现方式的分别来演示,上述提到的问题答案将在具体的实例中给出详细的答案。本文目录:

  • [一]、Clob字段注解方式的实现
  • [二]、Clob字段 – 映射文件的实现
  • [三]、Blob字段 - 注解方式的实现
  • [四]、Blob字段 – 映射文件的实现

测试的相关源代码bigstring_oracle_src.zip

[一]、Clob字段 – 注解 方式的实现

域对象:TbBigStrClob.java

Hibernate 配置文件:hibernate.cfg.xml

测试程序:

运行结果:

../../hibernate.cfg.xml configure
start test clob …
Hibernate: select tbbigstrcl0_.ID as ID2_, tbbigstrcl0_.NAME as NAME2_, tbbigstrcl0_.CONTENT as CONTENT2_ from MY_TB_BIG_STR_CLOB tbbigstrcl0_
start to init clob data
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: insert into MY_TB_BIG_STR_CLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: insert into MY_TB_BIG_STR_CLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: insert into MY_TB_BIG_STR_CLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: insert into MY_TB_BIG_STR_CLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: insert into MY_TB_BIG_STR_CLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: select tbbigstrcl0_.ID as ID2_, tbbigstrcl0_.NAME as NAME2_, tbbigstrcl0_.CONTENT as CONTENT2_ from MY_TB_BIG_STR_CLOB tbbigstrcl0_
query data list size = 5
TbBigStrClob :: id = [1813873],name = [test_1],content = [我是大字段<Clob>,at date:Mon Aug 22 13:10:38 CST 2011].
TbBigStrClob :: id = [1813874],name = [test_2],content = [我是大字段<Clob>,at date:Mon Aug 22 13:10:38 CST 2011].
TbBigStrClob :: id = [1813875],name = [test_3],content = [我是大字段<Clob>,at date:Mon Aug 22 13:10:38 CST 2011].
TbBigStrClob :: id = [1813876],name = [test_4],content = [我是大字段<Clob>,at date:Mon Aug 22 13:10:38 CST 2011].
TbBigStrClob :: id = [1813877],name = [test_5],content = [我是大字段<Clob>,at date:Mon Aug 22 13:10:38 CST 2011].

运行结果可见:注解方式下,插入Clob数据和读取Clob数据均正常。

Tips:

1. 在Oracle10g + 10g驱动(或者Oracle11g + 11g驱动)的环境下,只需要在Clob字段上,增加注解@Column(name = “CONTENT”, columnDefinition = “CLOB”),即可成功实现建表、Clob字段的读写操作。

2. 在Oracle9i+9i的驱动环境下,有很多种不同的情况:

仅增加注释@Column(name = “CONTENT”, columnDefinition = “CLOB”)时,如果该字段的长度比较小时,读写数据都能正常。如果该字段的长度太长,则无法写入数据。如果在Oracle9i下又必须用9i的驱动,可以在Clob字段中再增加一行注解@Lob 或者 @Type(type = “text”),某些驱动下好像可以正常,但是有些版本9i的驱动下如果clob字段的数据量很大 还是会有异常发生,一般在项目中借助spring的OracleLobHandler完成兼容。

运用spring中org.springframework.jdbc.support.lob.OracleLobHandler 实现对clob的操作的基本步骤:

增加配置信息如下:

在sessionFactory的配置中增加属性 lobHandler 的配置:

修改domain对象中clob 对应属性的getXxx()方法上的注解:

程序中对该字段的操作和String类型的一样,无需额外操作。

3. 在Oracle9i+10g的驱动环境下,增加注解@Column(name = “CONTENT”, columnDefinition = “CLOB”),同样可以成功实现建表、Clob字段的读写操作,可见9i的驱动对Clob支持不是很好,但10g、11g已经做了更新。

[二]、Clob字段 – 映射文件的实现

bean文件:BigStrClob.java

映射文件:tb_bigstr.hb.xml

hibernate.cfg.xml文件中增加域对象的映射文件:

测试程序:

运行结果如下:

../../hibernate.cfg.xml configure
Hibernate: select bigstrblob0_.id as id1_, bigstrblob0_.NAME as NAME1_, bigstrblob0_.CONTENT as CONTENT1_ from MY_HB_BIG_STR_BLOB bigstrblob0_
start to init blob data
Hibernate: select max(id) from MY_HB_BIG_STR_BLOB
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: select bigstrblob0_.id as id1_, bigstrblob0_.NAME as NAME1_, bigstrblob0_.CONTENT as CONTENT1_ from MY_HB_BIG_STR_BLOB bigstrblob0_
Query data list size = 5
BigStrBlob :: id = [1],name = [test_1],content = [我是大字段<Blob>,at date:Mon Aug 22 13:28:51 CST 2011].
BigStrBlob :: id = [2],name = [test_2],content = [我是大字段<Blob>,at date:Mon Aug 22 13:28:51 CST 2011].
BigStrBlob :: id = [3],name = [test_3],content = [我是大字段<Blob>,at date:Mon Aug 22 13:28:51 CST 2011].
BigStrBlob :: id = [4],name = [test_4],content = [我是大字段<Blob>,at date:Mon Aug 22 13:28:51 CST 2011].
BigStrBlob :: id = [5],name = [test_5],content = [我是大字段<Blob>,at date:Mon Aug 22 13:28:51 CST 2011].

从运行结果可见:映射文件的方式下,插入Clob数据和读取Clob数据均正常

Tips: 如果是映射文件的,只需要把Clob字段的类型指定为text即:type=”text”

[三]、Blob字段 – 注解 方式的实现

域对象的文件:TbBigStrBlob.java

hibernate.cfg.xml中增加域对象:

运行测试程序:

运行结果如下:

../../hibernate.cfg.xml configure
start test clob …
Hibernate: select tbbigstrbl0_.ID as ID3_, tbbigstrbl0_.NAME as NAME3_, tbbigstrbl0_.CONTENT as CONTENT3_ from MY_TB_BIG_STR_BLOB tbbigstrbl0_
start to init blob data
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: select hibernate_sequence.nextval from dual
Hibernate: insert into MY_TB_BIG_STR_BLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: insert into MY_TB_BIG_STR_BLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: insert into MY_TB_BIG_STR_BLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: insert into MY_TB_BIG_STR_BLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: insert into MY_TB_BIG_STR_BLOB (NAME, CONTENT, ID) values (?, ?, ?)
Hibernate: select tbbigstrbl0_.ID as ID3_, tbbigstrbl0_.NAME as NAME3_, tbbigstrbl0_.CONTENT as CONTENT3_ from MY_TB_BIG_STR_BLOB tbbigstrbl0_
query data list size = 5
TbBigStrBlob :: id = [1813878],name = [test_1],content = [我是大字段<Blob>,at date:Mon Aug 22 13:40:56 CST 2011].
TbBigStrBlob :: id = [1813879],name = [test_2],content = [我是大字段<Blob>,at date:Mon Aug 22 13:40:56 CST 2011].
TbBigStrBlob :: id = [1813880],name = [test_3],content = [我是大字段<Blob>,at date:Mon Aug 22 13:40:56 CST 2011].
TbBigStrBlob :: id = [1813881],name = [test_4],content = [我是大字段<Blob>,at date:Mon Aug 22 13:40:56 CST 2011].
TbBigStrBlob :: id = [1813882],name = [test_5],content = [我是大字段<Blob>,at date:Mon Aug 22 13:40:56 CST 2011].

从运行结果可见:注解的方式下,插入Blob数据和读取Blob数据均正常

Tips: 对于byte[] 类型的字段如果要映射成Blob字段,只需要在该字段增加如下注解即可:

[四]、Blob字段 – 映射文件的实现

映射文件对象:BigStrBlob.java

映射文件:tb_bigstr.hb.xml

测试程序:

运行结果如下:

../../hibernate.cfg.xml configure
Hibernate: select bigstrblob0_.id as id1_, bigstrblob0_.NAME as NAME1_, bigstrblob0_.CONTENT as CONTENT1_ from MY_HB_BIG_STR_BLOB bigstrblob0_
start to init blob data
Hibernate: select max(id) from MY_HB_BIG_STR_BLOB
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: insert into MY_HB_BIG_STR_BLOB (NAME, CONTENT, id) values (?, ?, ?)
Hibernate: select bigstrblob0_.id as id1_, bigstrblob0_.NAME as NAME1_, bigstrblob0_.CONTENT as CONTENT1_ from MY_HB_BIG_STR_BLOB bigstrblob0_
Query data list size = 5
BigStrBlob :: id = [1],name = [test_1],content = [我是大字段<Blob>,at date:Mon Aug 22 13:47:50 CST 2011].
BigStrBlob :: id = [2],name = [test_2],content = [我是大字段<Blob>,at date:Mon Aug 22 13:47:50 CST 2011].
BigStrBlob :: id = [3],name = [test_3],content = [我是大字段<Blob>,at date:Mon Aug 22 13:47:50 CST 2011].
BigStrBlob :: id = [4],name = [test_4],content = [我是大字段<Blob>,at date:Mon Aug 22 13:47:50 CST 2011].
BigStrBlob :: id = [5],name = [test_5],content = [我是大字段<Blob>,at date:Mon Aug 22 13:47:50 CST 2011].

从运行结果可见:映射文件的方式下,插入Blob数据和读取Blob数据均正常

Tips: 对于是映射文件的方式,Blob字段的类型指定为binary即:type=” binary “

原创文章,转载请注明: 转载自micmiu – 软件开发+生活点滴[ http://www.micmiu.com/ ]

本文链接地址: http://www.micmiu.com/j2ee/hibernate/hibernate-oarcle-clob-blob/

发表评论?

0 条评论。

发表评论


注意 - 你可以用以下 HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">