项目中由如下需求:
表A
a b c d
1 1 1c 1
1 1 2c 3
1 1 1c 2
2 1 1c 4
表B
c e
1c 2e
1c 3e
2c 1e
现在要写个sql,生成结果如下:
a b cc dd
1 1 2e,3e,1e 1,3,2
2 1 2e,3e 4
经过一番苦苦找寻和调试,终于写出如下sql得到结果。
1 select tt1.a,tt1.b,dd,ee from 2 ( 3 select a,b,c,WMSYS.WM_CONCAT(d) over (partition by a,b) as dd,row_number() over (partition by a,b,c order by c) as num FROM A 4 5 ) tt1 6 join 7 ( 8 select a,b,c, wmsys.wm_concat(e) over (partition by a,b) as ee, row_number() over (partition by a,b order by c) as num2 from 9 ( 10 select a,b,t1.c,e,row_number() over (partition by a,b,e order by e) as num1 from A t1 join B t2 on t1.c = t2.c 11 ) where num1 = 1 12 ) tt2 13 on tt1.a = tt2.a and tt1.b = tt2.b and tt1.c = tt2.c and tt2.num2 = 1 and tt1.num = 1;
为保万无一失,专门将上述sql又在测试环境和生产环境执行一遍,都没有问题。然后信息慢慢的提交了测试发布,结果在页面查看数据时,cc列所显示的结果为org.hibernate.lob.SerializableClob@23edfs,很明显这是个对象,我在从数据库获取到cc后toString转为了字符串。问题是cc是个本来就是个字符串,调用toString(),结果怎么回是个对象呢。后,在同事电脑上再次把上述sql跑了一遍,惊奇的发现查询结果中cc变成了CLOB类型。专门去问了下dba,dba说这是使用了WMSYS.WM_CONCAT函数的原因,开发环境和测试环境的版本号的第四位不一样,这样的怪事竟然也被我遇到了。于是强制将得到的CLOB数据转为字符串:
to_char( WMSYS.WM_CONCAT(d) over (partition by a,b) )
可是,又有了新的错误:nonexistent LOB value.而且只有第8行加了to_char函数会报这个错误,第二行没问题。
经比较发现:第8行可能出现空值,第二行没有空值。于是,又进行了如下处理:
to_char( wmsys.wm_concat(nvl(e,'')) over (partition by a,b) )
仍然没有任何效果。问了dba,也束手无策。
看来只能另想他法。
经调试发现,从数据库获取的CLOB类型数据会被hibernate自动转为SerializableClob类型,既然这样,就只好手动将Clob类型转为String了。
转换代码如下:
public static String clob2String(Clob clobField) { String clobText=""; Reader reader = null; if (clobField==null){ return ""; } try { int len = (int) clobField.length(); if (len==0){ return ""; } char[] buff = new char[len]; reader = clobField.getCharacterStream(); int rdchars = reader.read(buff); if (rdchars!=len){ throw new RuntimeException("Read clob field content error. Total length is "+len+", but read text length is "+rdchars); } clobText = String.valueOf(buff); } catch (SQLException e) { throw new RuntimeException(e); } catch (IOException e) { throw new RuntimeException(e); }finally{ if (reader!=null) try { reader.close(); } catch (IOException e) {} } return clobText; }
OK,终于大功告成!