通过Hibernate的org.hibernate.Query将Clob数据加载为字符串（渴望） [英] Loading Clob data as a String (eager) via Hibernate's org.hibernate.Query

问题描述

我需要使用Hibernate 而不使用延迟加载并且不使用Criteria查询来从clob Oracle列中获取字符串。它目前返回一个代理类（例如$ Proxy30），但我需要一个字符串（或者我可以转换为一个字符串）。代理是根据调试器为oracle.sql.CLOB。

当我使用标准查询（只有常规列注解映射这个领域），这个工程正好。但是我们有一个地方，我们建立一个使用简单的org.hibernate.Query和AliasToEntityMapResultTransformer.INSTANCE的自定义SQL查询，这就是代理问题出现的地方。因为它只是一个org.hibernate.Query，所以我认为它不是指我的注释映射，所以我不认为搞乱注释会有所帮助。

从使用Criteria查询在这里不是一个选项（我们正在为一些高级搜索报告需求构建一个查询字符串），应该如何研究才能找到解决方法？或甚至更好，我的最小努力的路径是什么？

这也适用于Apache Flex应用程序 - 我需要的类型是一个字符串（而不是CLOB ）被用于数据传输对象到客户端..也许其他的Flex开发者已经解决了这个问题之前？
$ b $ pre code查询查询= getSessionFactory（）。getCurrentSession（）。createSqlQuery（sql）;
query.setResultTransformer（AliasToEntityMapResultTransformer.INSTANCE）;
List ... resultlist = ... query.list（）;
resultlist.iterator（）;
//迭代结果...
Object shouldBeAString =（Object）result.get（CLOB_COLUMN_NAME）;
//上面的对象应该是一个字符串，但是是$ Proxy30
//调试器显示类型为SerializableClobProxy的$ b $如果我需要一个自定义的ResultTransformer - 任何链接到体面的文档，这样做，将不胜感激...

我刚刚有同样的问题，各种链接建议将spring升级到4.0.1+，并将hibernate升级到4.3.x，但这并没有什么区别。然后我遇到了这个链接，解决了我的问题。作者为Clob编写了一个自定义的ResultTransformer，然后将其设置为您查询的转换器，而不是AliasToEntityMapResultTransformer。

http://javatechtricks.blogspot.co.uk/2012/12/hibernate-clob-to -string-conversion.html

代码来自以下文章：

  public class MyResultTransformer extends BasicTransformerAdapter {
 
 public final static MyResultTransformer INSTANCE; 
 static {
 INSTANCE = new MyResultTransformer（）; 
 
 $ b $ private MyResultTransformer（）{
 
} 
 private static final long serialVersionUID = 1L; 
 $ b $ @Override 
 public Object transformTuple（Object [] tuple，String [] aliases）{
 Map< String，Object> map = new HashMap< String，Object>（）; 
 for（int i = 0; i< aliases.length; i ++）{
 Object t = tuple [i]; 
 if（t！= null&& t instanceof Clob）{
 Clob c =（Clob）tuple [i]; 
尝试{
 ByteArrayOutputStream bos = new ByteArrayOutputStream（）; 
 IOUtils.copy（c.getAsciiStream（），bos）; 
 t = new String（bos.toByteArray（））; 
} catch（SQLException e）{
 e.printStackTrace（）; 
} catch（IOException e）{
 e.printStackTrace（）; 
 
 
 map.put（aliases [i]，t）; 
} 
 return map; 
 
 
 
 
 
 
 
然后在你的代码中替换
 
 
  query.setResultTransformer（AliasToEntityMapResultTransformer.INSTANCE）; 
  
与
 
 
  query.setResultTransformer（MyResultTransformer.INSTANCE）; 
  
此外，另一种解决方案可能是更改列类型，我自己也没有尝试过。
 $ b 
 
 
  JDBC大对象 
类型blob和clob为
提供映射java.sql包中的Blob和Clob类。如果您正在处理
真正的大数值，最好的办法是将这些属性声明为
 Blob或Clob  - 尽管这会为您的数据对象引入一个显式的JDBC 
依赖项，它很容易允许Hibernate利用
 JDBC特性仅在需要时才延迟加载属性值。
 
 
如果您不担心数据太大了，你可以省去
这个直接的JDBC接口，声明属性类型为
 String或者byte []，并且用文本或者二进制映射它。这些分别对应于CLOB和VARBINARY（Oracle中的RAW，
 PostgreSQL中的BYTEA）的SQL列类型，
和这些值会立即被加载到
对象中加载。
 
 
来源： http://oreilly.com/java/excerpts/harnessing-hibernate/hibernate-types.html  
 
I need to fetch a String from a clob Oracle column using Hibernate without lazy loading and without using a Criteria query. It currently returns a proxy class (ex. $Proxy30) but I need a String (or something I can convert to a String).  The proxy is for oracle.sql.CLOB according to the debugger.

When I use a Criteria query (with just the regular Column annotation mapping for this field), this works just fine. But we have one area where we "build" a custom SQL query which uses a simple org.hibernate.Query and AliasToEntityMapResultTransformer.INSTANCE, and this is where the proxy problem arises. Since it's just an org.hibernate.Query, I assume it's not referring to my annotation mappings at all, so I don't think messing with annotations will help.

Since using a Criteria query is not an option here (we're building a query string for some advanced search reporting requirements), what should I be researching to find a workaround?  Or even better, what's my path of least effort to do this?

Also this is for an Apache Flex application - I need the type to be a String (rather than CLOB) to be used in a data transfer object to the client.. perhaps other Flex devs have solved this problem before?
Query query = getSessionFactory().getCurrentSession().createSqlQuery(sql);
query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
List... resultlist = ...query.list();
resultlist.iterator();
//iterate results...
Object shouldBeAString = (Object)result.get("CLOB_COLUMN_NAME");
//The object above should be a String but is a $Proxy30
//Debugger shows an "h" property of type SerializableClobProxy
If I need a custom "ResultTransformer" - any links to decent docs for doing that would be appreciated...
解决方案
I just had the same problem, various links suggested upgrading spring to 4.0.1+ and hibernate to 4.3.x but this didn't make any difference. Then I came across this link which resolved my issue. The author writes a custom ResultTransformer for the Clob and then setting this as the as the transformer for you query instead of AliasToEntityMapResultTransformer.

http://javatechtricks.blogspot.co.uk/2012/12/hibernate-clob-to-string-conversion.html

Code from the article below:
public class MyResultTransformer extends BasicTransformerAdapter {

 public final static MyResultTransformer INSTANCE;
 static {
  INSTANCE = new MyResultTransformer();
 }

 private MyResultTransformer() {

 }
 private static final long serialVersionUID = 1L;

 @Override
 public Object transformTuple(Object[] tuple, String[] aliases) {
  Map<String, Object> map = new HashMap<String, Object>();
  for (int i = 0; i < aliases.length; i++) {
   Object t = tuple[i];
   if (t != null && t instanceof Clob) {
    Clob c = (Clob) tuple[i];
    try {
     ByteArrayOutputStream bos = new ByteArrayOutputStream();
     IOUtils.copy(c.getAsciiStream(), bos);
     t = new String(bos.toByteArray());
    } catch (SQLException e) {
     e.printStackTrace();
    } catch (IOException e) {
     e.printStackTrace();
    }
   }
   map.put(aliases[i], t);
  }
  return map;
 }
}
Then in your code replace 
query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
with
query.setResultTransformer(MyResultTransformer.INSTANCE);
In addition, an alternative solution could be to change the column type, I havn't tried it myself.

  
JDBC large objects 
  The types blob and clob provide mappings for the
  Blob and Clob classes in the java.sql package. If you are dealing with
  truly large values, your best bet is to declare the properties as
  either Blob or Clob—even though this introduces an explicit JDBC
  dependency to your data object, it easily allows Hibernate to leverage
  JDBC features to lazily load the property value only if you need it.
  
  
If you are not worried that the data is too huge, you can spare
  yourself this direct JDBC interface, declare the property type as
  String or byte[ ], and map it using text or binary. These correspond
  to SQL column types of CLOB and VARBINARY (RAW in Oracle, BYTEA in
  PostgreSQL), respectively, and the values are loaded immediately into
  the properties when the object is loaded.
Source: http://oreilly.com/java/excerpts/harnessing-hibernate/hibernate-types.html

                        
这篇关于通过Hibernate的org.hibernate.Query将Clob数据加载为字符串（渴望）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！