注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

Mapping a Clob to a String  

2008-11-10 14:57:35|  分类: Hibernate |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: - 和申 - 和申的个人主页

Hibernate 1.2.3 has built-in support for clobs. Hibernate natively maps clob columns to java.sql.Clob. However, it's sometimes useful to read the whole clob into memory and deal with it as a String.

One approach for doing this to create a new UserType as follows.

I don't think this is the best implementation on some platforms - better to get/set character streams rather than get / set Clobs - GK

package mypackage; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import java.sql.Clob; import cirrus.hibernate.Hibernate; import cirrus.hibernate.HibernateException; import cirrus.hibernate.UserType; public class StringClobType implements UserType { public int[] sqlTypes() { return new int[] { Types.CLOB }; } public Class returnedClass() { return String.class; } public boolean equals(Object x, Object y) { return (x == y) || (x != null && y != null && (x.equals(y))); } public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { Clob clob = rs.getClob(names[0]); return clob.getSubString(1, (int) clob.length()); } public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { st.setClob(index, Hibernate.createClob((String) value)); } public Object deepCopy(Object value) { if (value == null) return null; return new String((String) value); } public boolean isMutable() { return false; } } 

The StringClobType will convert a clob into a String and back again.

Here's how to use it. First, define an entity that contains a String property:

public class TextValue { private long id; private String byte; public long getId() { return id; } public void setId(long id) { this.id = id; } public String getText() { return text; } public void setText(String text) { this.text = text; } } 

Then map a clob column onto the String property:

<class name="TextValue" table="TEXT_VALUE"> <id name="id/> <property name="text" column="TEXT" type="mypackage.StringClobType"/> </class> 

Notes:

1) Clobs aren't cachable. By converting the clob into a String, you can now cache the entity.

2) This approach reads the whole clob into memory at once.

3) The above type is known to work for reading clobs out of the db. Other usage patterns might also work.

4) See also Mapping a Blob to a byte[].

*****************************************************************************

Method 2

I could not get the above method to work.

This is based on the cirrus.hibernate.type.Clob source.

import java.io.BufferedReader; import java.io.IOException; import java.io.StringReader; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Types; import org.apache.commons.lang.ObjectUtils; import cirrus.hibernate.type.ImmutableType; public class TextType extends ImmutableType { public Object get(ResultSet rs, String name) throws SQLException { String line; String str = ""; BufferedReader b = new BufferedReader( rs.getCharacterStream( name ) ); try { while( (line = b.readLine()) != null ) { str += line; } } catch (IOException e) { throw new SQLException( e.toString() ); } return str; } public Class returnedClass() { return String.class; } public void set(PreparedStatement st, Object value, int index) throws SQLException { StringReader r = new StringReader( (String)value ); st.setCharacterStream( index, r, ((String)value).length() ); } public int sqlType() { return Types.CLOB; } public String getName() { return "string"; } public boolean hasNiceEquals() { return false; } public boolean equals(Object x, Object y) { return ObjectUtils.equals(x, y); } public String toXML(Object value) { return (String) value; } } 

********************************

Method 2 does not account for a null clob nor does it append newline charaters.

I would suggest replacing the get method with this.

public Object get(ResultSet rs, String name) throws HibernateException, SQLException { Reader reader = rs.getCharacterStream(name); if (reader == null) { return null; } StringBuffer sb = new StringBuffer(); try { char[] charbuf = new char[4096]; for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) { sb.append(charbuf, 0, i); } } catch (IOException e) { throw new SQLException( e.getMessage() ); } return sb.toString(); } 

  NEW COMMENT

Is that worked with strings>4k.
08 Jan 2004, 07:23
udoo
I'm using oracle 9.0.1, and ojdbc14/thin, but I can't insert strings > 4k, with such exception: java.sql.SQLException: No more data to read from socket at oracle.jdbc.dbaccess.DBError.throwSqlException (DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException (DBError.java:179) at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160) at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963) at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893) at oracle.jdbc.ttc7.Oclose.receive(Oclose.java:101) at oracle.jdbc.ttc7.TTC7Protocol.close(TTC7Protocol.java:683) at oracle.jdbc.driver.OracleStatement.close (OracleStatement.java:644) at oracle.jdbc.driver.OraclePreparedStatement.privateClose (OraclePreparedStatement.java:485) at oracle.jdbc.driver.OraclePreparedStatement.close (OraclePreparedStatement.java:393) at net.sf.hibernate.impl.BatcherImpl.closePreparedStatement (BatcherImpl.java:246) at net.sf.hibernate.impl.BatcherImpl.closeStatement (BatcherImpl.java:132) at net.sf.hibernate.impl.BatcherImpl.abortBatch (BatcherImpl.java:74) at net.sf.hibernate.persister.EntityPersister.insert (EntityPersister.java:519) at net.sf.hibernate.persister.EntityPersister.insert (EntityPersister.java:489) at net.sf.hibernate.impl.ScheduledInsertion.execute (ScheduledInsertion.java:28) at net.sf.hibernate.impl.SessionImpl.executeAll (SessionImpl.java:2303) at net.sf.hibernate.impl.SessionImpl.execute (SessionImpl.java:2256) at net.sf.hibernate.impl.SessionImpl.flush (SessionImpl.java:2182) any ideas?
 
Current Implementations of This?
30 Jun 2004, 20:47
diathesis
I had been hoping that these methods would get around my ahving to use the Clob directly, and to keep me away from using Oracle-specific code. Method 1 didn't get me past the 4k character limit. Something like 'too much data for this datatype (4000)' (I didn't record the error). Method 2 needed extra implementation to work with the current version of Hibernate (fromStringValue(), toString(), getReturnedClass()), which I implemented in the way that seemed best -- there's not a lot of documentation on the nature of these methods. That got me as far as getting 'No more data to read from socket.' Disabling JDBC batches got me to 'No more data to read from socket.' All the references to that error point me to the more complicated Oracle/Clob handling routines, so I'm going to have to go that way for the time being, and then circle back into figuring out what's necessary to support a direct string. If anyone has additional pointers, I'd be happy to hear 'em.
 
Re: Current Implementations of This?
01 Jul 2004, 11:00
130g
It seems that with the current UserType framework of Hibernate it is not possible to "correctly" implement the clob handling algorithms from Oracle. Based on http://download- west.oracle.com/docs/cd/B10501_01/java.920/a96654/oralob.htm#1043220 it seems that to write a clob you need to select the clob handler first, and then modify the clob handler. As far as I can see this is not possible to do in the set() method of af user type, as the inputs are a prepared statement to which we should bind the value to be inserted. Or is it just me being daft? :)
 
Null value should be checked in the returned clob
27 Aug 2004, 22:38
barryku
I was getting NullPointer exception with null clob, and the following is my fix, <code> public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { Clob clob = rs.getClob(names[0]); return (clob==null? null :clob.getSubString(1, (int) clob.length ())); } </code>
 
How can member variable in class automatically mapped to String
06 Oct 2004, 07:45
garpinc
How can member variable in class automatically mapped to String with hbm2class even though type is user type. (whatever it's called, I use hibernate synchronzer)
 
FYI: Found answer about mapping
06 Oct 2004, 07:52
garpinc
With hibernate synchronizer I use <meta attribute="property-type">String</meta>
 
Has anyone found a UserType solution that works with 8i
11 Oct 2004, 18:38
garpinc
Has anyone found a UserType solution that works with 8i? I'm getting a ClassCastException when using the recomended solution.
 
re: UserType solution that works with 8i
14 Oct 2004, 21:53
Lukasz (Qr)
"UserType solution that works with 8i" - see the "Updated Clobs handling for Oracle and Hibernate": http://www.hibernate.org/56.html
 
method 1 and mysql..
02 Dec 2004, 09:38
hooverphonique
I can confirm that the first method (using Clob#getSubString) does NOT work for MySQL, since the Hibernate ClobImpl class does not allow extraction of data using getSubString, which is exactly the method MySQL uses to get to the character data (which actually causes the 2nd method to bomb as well, since the stream methods are not used by the MySQL JDBC driver (v 3.0.14) in this case).
 
Re: method 1 and mysql..
02 Dec 2004, 09:57
hooverphonique
On 02 Dec 2004 09:38, hooverphonique wrote: >I can confirm that the first method (using Clob#getSubString) does NOT >work for MySQL, since the Hibernate ClobImpl class does not allow >extraction of data using getSubString, which is exactly the method >MySQL uses to get to the character data Using method 1 (UserType implementation) with the following code in place of nullSafeSet and nullSafeGet works for me for both reading and writing CLOBs.. public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { Reader reader = rs.getCharacterStream(names[0]); if (reader == null) return null; StringBuffer sb = new StringBuffer(); try { char[] charbuf = new char[4096]; for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) { sb.append(charbuf, 0, i); } } catch (IOException e) { throw new SQLException( e.getMessage() ); } return sb.toString(); } public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { if (value != null) { StringReader r = new StringReader( (String) value ); st.setCharacterStream( index, r, ((String) value).length() ); } else { st.setNull(index, sqlTypes()[0]); } }
 
Re: method 1 and mysql..
10 Aug 2005, 22:41
monoxxx
POST QUESTIONS ON THE FORUM! COMMENTS HERE SHOULD ADD VALUE TO THE PAGE!On 02 Dec 2004 09:57, hooverphonique wrote: >On 02 Dec 2004 09:38, hooverphonique wrote: >>I can confirm that the first method (using Clob#getSubString) does >NOT >>work for MySQL, since the Hibernate ClobImpl class does not allow >>extraction of data using getSubString, which is exactly the method >>MySQL uses to get to the character data >Using method 1 (UserType implementation) with the following code in >place of nullSafeSet and nullSafeGet works for me for both reading and >writing CLOBs.. > public Object nullSafeGet(ResultSet rs, String[] names, >Object owner) throws HibernateException, SQLException { > Reader reader = rs.getCharacterStream(names[0]); > if (reader == null) return null; > StringBuffer sb = new StringBuffer(); > try { > char[] charbuf = new char[4096]; > for (int i = reader.read(charbuf); i > 0; i >= reader.read(charbuf)) { > sb.append(charbuf, 0, i); > } > } > catch (IOException e) { > throw new SQLException( e.getMessage () ); > } > return sb.toString(); > } > public void nullSafeSet(PreparedStatement st, Object value, >int index) throws HibernateException, SQLException { > if (value != null) { > StringReader r = new StringReader( (String) >value ); > st.setCharacterStream( index, r, ((String) >value).length() ); > } else { > st.setNull(index, sqlTypes()[0]); > } > } public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { Clob clob = rs.getClob(names[0]); return clob.getSubString(1, (int) clob.length()); } public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { st.setClob(index, Hibernate.createClob((String) value)); } Above two method I can't insert the data into Oracle 9i database; But When I change below this two method(Use characterStream to char[]) It worked !! That's amazing !! Great Thank You hooverphonique public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws HibernateException, SQLException { Reader reader = rs.getCharacterStream(names[0]); if (reader == null) return null; StringBuffer sb = new StringBuffer(); try { char[] charbuf = new char[4096]; for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) { sb.append(charbuf, 0, i); } } catch (IOException e) { throw new SQLException( e.getMessage () ); } return sb.toString(); } public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { if (value != null) { StringReader r = new StringReader( (String) value ); st.setCharacterStream( index, r, ((String) value).length() ); } else { st.setNull(index, sqlTypes()[0]); } }
 
Shouldn't this get removed?
13 Apr 2006, 12:39
koehn
Now that Hibernate supports the "text" type natively, shouldn't this be noted at the very top of this article?
 
This works both for reading or writing with Informix 10:
16 May 2008, 04:48
ivaylodd
========================================================================= public class StringClobType implements UserType { private static final int READ_BUFFER_SIZE = 4096; public Object assemble(Serializable cached, Object owner) { return null; } public Object deepCopy(Object value) { if (value == null) { return null; } return new String((String) value); } public Serializable disassemble(Object value) { return null; } public boolean equals(Object x, Object y) { return (x == y) || ((x != null) && (y != null) && (x.equals(y))); } public int hashCode(Object x) { return x.hashCode(); } public boolean isMutable() { return false; } public Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws SQLException { Clob clob = rs.getClob(names[0]); Reader reader = clob.getCharacterStream(); if (reader == null) { return null; } StringBuffer sb = new StringBuffer(); try { char[] charbuf = new char[READ_BUFFER_SIZE]; for (int i = reader.read(charbuf); i > 0; i = reader.read(charbuf)) { sb.append(charbuf, 0, i); } } catch (IOException e) { throw new SQLException(e.getMessage()); } return sb.toString(); } public void nullSafeSet(PreparedStatement st, Object value, int index) throws SQLException { if (value != null) { StringReader r = new StringReader((String) value); st.setCharacterStream(index, r, ((String) value).length()); } else { st.setNull(index, sqlTypes()[0]); } } public Object replace(Object original, Object target, Object owner) { return null; } public Class returnedClass() { return String.class; } public int[] sqlTypes() { return new int[] { Types.CLOB }; } ========================================================================= Note that in the nullSafeGet() method, the following code: Reader reader = rs.getCharacterStream(names[0]); doesn't work with Informix and throws an SQLException: java.sql.SQLException: Can't convert tonull Instead, I changed it to: Clob clob = rs.getClob(names[0]); Reader reader = clob.getCharacterStream(); and now it works.
 
Re: This works both for reading or writing with Informix 10:
22 Sep 2008, 06:44
Surya Mithra
This class worked for me however I was facing a class cast exception when I used the nullSafeSet method with st.setClob(index, Hibernate.createClob((String) value)); I had to modify it to the following public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException { if (value != null) { StringReader((String)value ); st.setString(index, (String)value); } else { st.setNull(index, sqlTypes()[0]); } } It works for me now
 

org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: - 和申 - 和申的个人主页

  评论这张
 
阅读(2688)| 评论(2)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016