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

问题描述

我需要使用 Hibernate 从 Clob Oracle 列中获取字符串 延迟加载且不使用 Criteria 查询.它目前返回一个代理类(例如 $Proxy30)，但我需要一个字符串(或者我可以转换为字符串的东西).根据调试器，代理用于 oracle.sql.CLOB.

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.

当我使用 Criteria 查询时(仅使用该字段的常规 Column 注释映射)，这工作得很好.但是我们有一个区域可以构建"一个使用简单的 org.hibernate.Query 和 AliasToEntityMapResultTransformer.INSTANCE 的自定义 SQL 查询，这就是代理问题出现的地方.由于它只是一个 org.hibernate.Query，我认为它根本不是指我的注释映射，所以我认为弄乱注释不会有帮助.

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?

这也是针对 Apache Flex 应用程序的 - 我需要类型是字符串(而不是 CLOB)，以便在到客户端的数据传输对象中使用.也许其他 Flex 开发人员之前已经解决了这个问题?

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

如果我需要一个自定义的ResultTransformer" - 任何指向体面文档的链接都将不胜感激...

If I need a custom "ResultTransformer" - any links to decent docs for doing that would be appreciated...

推荐答案

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

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

来自以下文章的代码:

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;
 }
}

然后在你的代码中替换

query.setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);

与

query.setResultTransformer(MyResultTransformer.INSTANCE);

此外，另一种解决方案可能是更改列类型，我自己还没有尝试过.

In addition, an alternative solution could be to change the column type, I havn't tried it myself.

JDBC 大对象blob 和 clob 类型为java.sql 包中的 Blob 和 Clob 类.如果你正在处理真正的大值，最好的办法是将属性声明为Blob 或 Clob——即使这引入了显式 JDBC依赖于你的数据对象，它很容易让 Hibernate 利用JDBC 功能仅在需要时才延迟加载属性值.

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.

如果你不担心数据太大，可以省下自己这个直接JDBC接口，将属性类型声明为字符串或字节[ ]，并使用文本或二进制映射它.这些对应到 SQL 列类型的 CLOB 和 VARBINARY(Oracle 中的 RAW，BYTEA 中的PostgreSQL)，并且 值会立即加载到加载对象时的属性.

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.

来源:http://oreilly.com/java/excerpts/利用-hibernate/hibernate-types.html

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