Hi, i have the following problem.
Having this hql select:
Code:
public Integer getClientBuyerUsersCount() {
...
String query = "select count(u) from UserVO u where ${quickSearchCondition} and u.client.id = :clientId and u.buyer = :buyer and u.status.id is null";
Query q = createQuery(query,listForm);
q.setLong("clientId", clientId);
q.setParameter("buyer", Boolean.TRUE, Hibernate.YES_NO);
return (Integer) q.uniqueResult();
}
where
Code:
createQuery(query,listForm)
is a method that is replacing
${quickSearchCondition} with some "OR" conditions.
The resulted HQL after parsing is looking like:
Code:
select count(u)
from UserVO u
where (
upper(u.firstName||' '||u.lastName) like upper(:name1)
or upper(u.companyName) like upper(:company1)
or upper(u.email) like upper(:email1)
or upper(u.phone) like upper(:phone1)
or upper(u.updUser.title||' '||u.updUser.firstName||' '||u.updUser.lastName) like upper(:fullUserName1)
)
and u.client.id = :clientId and u.buyer = :buyer and u.status.id is null
The resulted SQL that i'm getting from log is:
Code:
select count(uservo0_."ID") as x0_0_
from "USER" uservo0_, "USER" uservo1_
where
(
(upper(uservo0_."FIRSTNAME"||' '||uservo0_."LASTNAME") like upper(?))
or (upper(uservo0_."COMPANYNAME") like upper(?))
or (upper(uservo0_."EMAIL") like upper(?))
or (upper(uservo0_."PHONE") like upper(?))
or (upper(uservo1_."TITLE"||' '||uservo1_."FIRSTNAME"||' '||uservo1_."LASTNAME")like upper(?)
and uservo0_."UPDUSERID"=uservo1_."ID"
and uservo0_."UPDUSERID"=uservo1_."ID"
and uservo0_."UPDUSERID"=uservo1_."ID"
)
)
and(uservo0_."CLIENTID"=? )and(uservo0_."BUYER"=? )and(uservo0_."STATUSID" is null )
A join is performed by Hibernate because of u.updUser.title, u.updUser.firstName and u.updUser.lastName. The problem is that the join condition (which is duplicated 3 times -
and uservo0_."UPDUSERID"=uservo1_."ID") is placed inside the parenthesis for OR conditions and this way the result of the query is not accurate.
User.hbm.xml mapping file is:
Code:
<hibernate-mapping>
<class name="common.backend.vo.UserVO" table="`USER`" lazy="true">
<id name="id" type="long" unsaved-value="null">
<column name="`ID`" not-null="true"/>
<generator class="native">
<param name="sequence">"SEQ_USER"</param>
</generator>
</id>
<property name="userName" column="`USERNAME`" type="string" unique="true" not-null="true"/>
<property name="codeword" column="`CODEWORD`" type="string" not-null="true"/>
<property name="email" column="`EMAIL`" type="string" not-null="false"/>
<property name="title" column="`TITLE`" type="string" not-null="false"/>
<property name="firstName" column="`FIRSTNAME`" type="string" not-null="false"/>
<property name="lastName" column="`LASTNAME`" type="string" not-null="false"/>
<property name="buyer" column="`BUYER`" type="yes_no" not-null="true"/>
<property name="admin" column="`ADMIN`" type="yes_no" not-null="true"/>
<many-to-one name="insUser" column="`INSUSERID`" class="common.backend.vo.UserVO" not-null="true"/>
<many-to-one name="updUser" column="`UPDUSERID`" class="common.backend.vo.UserVO" not-null="true"/>
...
thanks for any help or ideeas.
Login
FAQ
Search