Hibernate 对象查询生成的SQL的疑问[已解决]

  惟一外键关键的情况(many-to-one):我用了二个这样的例子进行测试

环境:Oracle8i,Hibernate3.1.3

2张表都是三个字段，然后进行对象查询

2种情况的映射文件都是相似的，只是属性具体名称稍有不同

  

xml 代码

        <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"/>

	加黑加粗的是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,

评论

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'>&lt;</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'>&gt;</span><span>    </span></span></li>
    <li class=''><span>             <span class='tag'>&lt;</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'>/&gt;</span><span>    </span></span></li>
    <li class='alt'><span> <span class='tag'><!----><span class='tag-name'>property</span><span class='tag'>&gt;</span><span>   </span></span></span></li>
    <li class=''><span> <span class='tag'>&lt;</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'>/&gt;</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'>&lt;</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'>/&gt;</span><span> <br/>
改为<br/>
</span></span><span><span class='tag'>&lt;</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'>/&gt;</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>

3 楼 janh 2007-04-17  

你可真行，难道没发现sqr是SQR的小写吗？

肯定是你两个地方写的不一样，产生这样的现象就不奇怪了。

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的查询多出来了一个字段

1 楼 yb31 2007-04-17  

没看明白
你还是二种分开写吧.把代码贴完整点