Hibernate 对象查询生成的SQL的疑问[已解决]
? 惟一外键关键的情况(many-to-one):我用了二个这样的例子进行测试
- ????????<id?name="id"?type="java.lang.Integer">??
- ????????????<column?name="id"?/>??
- ????????????<generator?class="assigned"?/>??
- ????????id>??
- ????????<property?name="gzzh"?type="java.lang.String">??
- ????????????<column?name="gzzh"?length="20"?/>??
- ????????property>??
- ????????<property?name="www"?type="java.lang.String">??
- ????????????<column?name="www"?length="30"?/>??
- ????????property>??
- ??
- <many-to-one?name="wnote"?class="test.db.Www"?column="gzzh"?lazy="false"?update="false"?insert="false"/>??
环境:Oracle8i,Hibernate3.1.3
2张表都是三个字段，然后进行对象查询
2种情况的映射文件都是相似的，只是属性具体名称稍有不同
??
?
加黑加粗的是man-to-one对应的外键.
A.
Hibernate: select this_.id as id2_0_, this_.gzzh as gzzh2_0_, this_.www as www2_0_ from test_user this_
B
Hibernate: select this_.id as id0_0_, this_.SQR as SQR0_0_, this_.DWDM as DWDM0_0_, this_.sqr as sqr0_0_ from NDZJXX this_
?
B.看加红的地方,却凭空多出来了一个字段
这个是因为什么呢？
这二种情况下的语句都能执行，也不影响最后取出来的结果。
大家知道Criteria的限制结果记录数目的时候? setFirstResult(0).setMaxResults(100)
生成的SQL是形如 select * from ( … ) where rownum <= ?
A情况当然是没有任何问题的。
B情形如下：
select * from ( select this_.id as id0_0_, this_.SQR as SQR0_0_, this_.DWDM as DWDM0_0_, this_.sqr as sqr0_0_ from NDZJXX this_ ) where rownum <= ?
这个时候就有问题了:
ORA-00918: column ambiguously defined
?
对B情况下使用HQL也是出现同样的问题。
?
这个让我很疑惑，目前我在B情况下只好规避使用Criteria的限制结果记录数目的操作，改用Native Sql,
1 楼 yb31 2007-04-17 没看明白你还是二种分开写吧.把代码贴完整点 2 楼 moogle 2007-04-17 谢谢关注我的帖子。
具体情况如下:
A.Test_User(n)--(1)Test_Www
字段gzzh是外键,对应Test_Www的主键
B.Ndzjxx(n)--(1)User
字段sqr是外键，对应User的主键
---------------------------
就是分别对Test_User和Ndzjxx进行最简单的查询
Criteria.forClass(XXXX.class)
criteria.list();
就是这个操作hibernate生成的sql语句如上,但是Ndzjxx的查询多出来了一个字段
3 楼 janh 2007-04-17 你可真行，难道没发现sqr是SQR的小写吗？
肯定是你两个地方写的不一样，产生这样的现象就不奇怪了。 4 楼 moogle 2007-04-17 多谢janh提醒。我测试了一下终于发现:<br/>
<br/>
<div class='code_title'>xml 代码</div>
<div class='dp-highlighter'>
<div class='bar'>?</div>
<ol class='dp-xml' start='1'>
<li class='alt'><span><span class='tag'><</span><span class='tag-name'>property</span><span>?</span><span class='attribute'>name</span><span>=</span><span class='attribute-value'>"gzzh"</span><span>?</span><span class='attribute'>type</span><span>=</span><span class='attribute-value'>"java.lang.String"</span><span class='tag'>></span><span>????</span></span></li>
<li class=''><span>?????????????<span class='tag'><</span><span class='tag-name'>column</span><span>?</span><span class='attribute'>name</span><span>=</span><span class='attribute-value'>"gzzh"</span><span>?</span><span class='attribute'>length</span><span>=</span><span class='attribute-value'>"20"</span><span>?</span><span class='tag'>/></span><span>????</span></span></li>
<li class='alt'><span>?<span class='tag'><!----><span class='tag-name'>property</span><span class='tag'>></span><span>???</span></span></span></li>
<li class=''><span>?<span class='tag'><</span><span class='tag-name'>many-to-one</span><span>?</span><span class='attribute'>name</span><span>=</span><span class='attribute-value'>"wnote"</span><span>?</span><span class='attribute'>class</span><span>=</span><span class='attribute-value'>"test.db.Www"</span><span>?</span><span class='attribute'>column</span><span>=</span><span class='attribute-value'>"gzzh"</span><span>?</span><span class='attribute'>lazy</span><span>=</span><span class='attribute-value'>"false"</span><span>?</span><span class='attribute'>update</span><span>=</span><span class='attribute-value'>"false"</span><span>?</span><span class='attribute'>insert</span><span>=</span><span class='attribute-value'>"false"</span><span class='tag'>/></span><span>??</span></span></li>
</ol>
</div>
Hibernate生成的sql<br/>
<span class='postbody'>select this_.id as id2_0_, <font color='#ff00ff'><span style='font-weight: bold;'>this_.gzzh as gzzh2_0_</span></font>, this_.www as www2_0_ from test_user this_<br/>
<br/>
如果将<br/>
</span><span><span class='tag'><</span><span class='tag-name'>column</span><span>?</span><span class='attribute'>name</span><span>=</span><span class='attribute-value'>"gzzh"</span><span>?</span><span class='attribute'>length</span><span>=</span><span class='attribute-value'>"20"</span><span>?</span><span class='tag'>/></span><span> <br/>
改为<br/>
</span></span><span><span class='tag'><</span><span class='tag-name'>column</span><span>?</span><span class='attribute'>name</span><span>=</span><span class='attribute-value'>"GZZH"</span><span>?</span><span class='attribute'>length</span><span>=</span><span class='attribute-value'>"20"</span><span>?</span><span class='tag'>/></span><span> <br/>
sql将变为<br/>
</span></span><span class='postbody'>select this_.id as id2_0_, <font color='#ff00ff'><span style='font-weight: bold;'>this_.GZZH as gzzh2_0_</span></font>, this_.www as www2_0_ from test_user this_<br/>
<br/>
<strong style='background-color: rgb(255, 255, 255);'><font color='#000000' style='background-color: rgb(255, 204, 153);'>但是many-to-one的column如果大小写和外键那个属性的大小写不一样的话，hibernate生成的sql就会出现我B情况下的一个大写的GZZH,一个小写的gzzh? 二个字段，从而出现了这个隐含的现象。</font></strong><br/>
在本地已经测试过了，看到console出现的sql我终于可以控制住这个多出来的小尾巴了。happy...<br/>
<br/>
<br/>
我的hibernate映射文件一般都是由工具生成的，而数据库的字段不管大小写都不会影响操作,所以我从不曾注意到这种细节，的确让我想象不到问题原因如此的简单。<br/>
<br/>
Thanks.<br/>
</span>