javax.servlet.ServletException: unexpected token: ( near line 1, column 1 [( select emp from com.neusoft.talentbase.common.business.organization.model.assignedjob.AssignedJobPO a,com.neusoft.talentbase.common.business.staffing.common.po.EmployeePO emp where a.oid=emp.jobID and a.leaderLevel in ('0','1') and a.unitPO.oid=1738419 and a.status='1' and emp.status='1' and emp.employeeStatus in ('2','5','11','7') ) union ( select e from com.neusoft.talentbase.common.business.organization.model.assignedjob.AssignedJobPO a,com.neusoft.talentbase.common.business.staffing.common.po.EmployeePO e,com.neusoft.talentbase.staffing.model.parttime.ParttimePO p where a.oid=p.jobID and e.oid=p.employee and a.leaderLevel in ('0','1') and a.unitPO.oid= 1738419 and a.status='1' and e.status='1' and p.status='1' and e.employeeStatus in ('2','5','11','7') )] org.apache.struts.action.RequestProcessor.processException(RequestProcessor.java:516) org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:423) org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:226) com.neusoft.talentbase.framework.core.strutsextension.TbRequestProcessor.process(TbRequestProcessor.java:80) org.apache.struts.action.ActionServlet.process(ActionServlet.java:1164) org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:397) javax.servlet.http.HttpServlet.service(HttpServlet.java:689) javax.servlet.http.HttpServlet.service(HttpServlet.java:802) com.neusoft.report.sample.security.LoginUserFilter.doFilter(LoginUserFilter.java:49) com.neusoft.talentbase.framework.core.usercontext.UserContextFilter.doFilter(UserContextFilter.java:82) com.neusoft.talentbase.framework.core.filter.CharsetEncodingFilter.doFilter(CharsetEncodingFilter.java:55)
HQL语句:
( select emp from AssignedJobPO a,EmployeePO emp where a.oid=emp.jobID and a.leaderLevel in ('0','1') and a.unitPO.oid=1738419 and a.status='1' and emp.status='1' and emp.employeeStatus in ('2','5','11','7') )
union
( select e from AssignedJobPO a,EmployeePO e,ParttimePO p where a.oid=p.jobID and e.oid=p.employee and a.leaderLevel in ('0','1') and a.unitPO.oid= 1738419 and a.status='1' and e.status='1' and p.status='1' and e.employeeStatus in ('2','5','11','7') )
原来HQL不支持UNION操作,因此会报错。
推荐 1,用视图,2,用两个简单的查询。
下面是stackoverflow上面的一个人的留言:
You could use id in (select id from ...) or id in (select id from ...)
e.g. instead of non-working
from Person p where p.name="Joe"
union
from Person p join p.children c where c.name="Joe"
you could do
from Person p
where p.id in (select p1.id from Person p1 where p1.name="Joe")
or p.id in (select p2.id from Person p2 join p2.children c where c.name="Joe");
At least using MySQL, you will run into performance problems with the later though. It's sometimes easier to do a poor man's join on two queries instead:
// use set for uniqueness
Set<Person> people = new HashSet<Person>((List<Person>) query1.list());
people.addAll((List<Person>) query2.list());
return new ArrayList<Person>(people);
It's often better to do two simple queries than one complex one.
EDIT:
to give an example, here is the EXPLAIN output of the resulting MySQL query from the subselect solution:
mysql> explain
select p.* from PERSON p
where p.id in (select p1.id from PERSON p1 where p1.name = "Joe")
or p.id in (select p2.id from PERSON p2
join CHILDREN c on p2.id = c.parent where c.name="Joe") \G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 247554
Extra: Using where
*************************** 2. row ***************************
id: 3
select_type: DEPENDENT SUBQUERY
table: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra: Impossible WHERE noticed after reading const tables
*************************** 3. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: a1
type: unique_subquery
possible_keys: PRIMARY,name,sortname
key: PRIMARY
key_len: 4
ref: func
rows: 1
Extra: Using where
3 rows in set (0.00 sec)
Most importantly, 1. row doesn't use any index and considers 200k+ rows. Bad! Execution of this query took 0.7s wheres both subqueries are in the milliseconds.