hibernate多表查询sql语句求救！！

qq_14908849 2015-04-15 05:07:03

SELECT p,t1.Locs,t2.totalWarnings,t3.passed FROM Projects p LEFT OUTER JOIN (SELECT SUM(loc) Locs ,job_id from
LocCounter GROUP BY job_id) t1
on p.id=(SELECT project_id FROM Jobs WHERE id=t1.job_id) LEFT OUTER JOIN
(SELECT AVG(totalWarnings) totalWarnings ,job_id from SaIssuesIummary GROUP BY job_id) t2
on p.id=(SELECT project_id FROM Jobs WHERE id=t2.job_id)
LEFT OUTER JOIN (SELECT AVG(passed/total) passed,job_id from TestngOverview GROUP BY job_id) t3
on p.id=(SELECT project_id FROM Jobs WHERE id=t3.job_id)

Projects SaIssuesIummary TestngOverview LocCounter Jobs 四张表都已经建好映射关系我的这个sql语句那你出问题啦一直运行报错

...全文

313 14 打赏收藏转发到动态举报

写回复

用AI写文章

14 条回复

切换为时间正序

请发表友善的回复…

发表回复

言月秋 2015-04-20

打赏
举报

for(User u : userList) { System.out.println(u.getId() + " --- " + u.getTruename()); } 针对于这样的语句不是很了解，for循环可以这么用吗，希望懂的大神说明下？

guoweiqaz 2015-04-18

打赏
举报

hql语句不需要on语句去除笛卡尔集，因为你映射关系上有，它翻译成sql语句时候会自动加上

ahhmdwg 2015-04-18

打赏
举报

Hibernate left join一直是困扰我的一个问题，因为对hibernate的关联映射不怎么了解，写的Hql也全部是从sql翻译过来，虽然也是大同小异，因此左连接一直不会用。其实很简单： 1、先把关联映射配好 User.hbm.xml <hibernate-mapping package="com.xieqing.crm.user"> <class name="User" table="tbl_user"> <id name="id" column="id"> <generator class="native"/> </id> <property name="truename" not-null="true" column="truename" length="32"/> <property name="sex" column="sex" length="1"/> <property name="phone" column="phone"/> <property name="roomId" column="room_id" not-null="false"/> <many-to-one name="room" class="com.xieqing.crm.room.Room" column="room_id" insert="false" update="false"/> </class> </hibernate-mapping> Room.hbm.xml <hibernate-mapping package="com.xieqing.crm.room"> <class name="Room" table="tbl_room"> <id name="id" column="room_id"> <generator class="native"></generator> </id> <property name="roomName" not-null="true" column="room_name" length="32"/> <set name="users"> <key column="room_id"></key> <one-to-many class="com.xieqing.crm.user.User" not-found="ignore"/> </set> </class> </hibernate-mapping> User.java public class User{ private Integer id; private String truename; private String phone; private int sex; private Integer roomId; private Room room; // 省略Getter setter方法 } Room.java public class Room { private Integer id; private String roomName; private Set<User> users; // 省略Getter setter方法 } tbl_user 表 Hibernate left join(左连接) tbl_room 表 Hibernate left join(左连接) // 注意：这里连接的就是User.java里的room属性了, 改了这里就OK啦。如果后面要加条件就用with （SQL是用的on） String hql = "select u from User u left join u.room"; List<User> userList = this.getHibernateTemplate().find(hql).list(); System.out.println("size----"+userList.size()); for(User u : userList) { System.out.println(u.getId() + " --- " + u.getTruename()); } 执行该查询将把tbl_user表中的4个人都查出来。

ahhmdwg 2015-04-18

打赏
举报

在hibernate里表已经配好的关系在sql是不用on和where的直接A left join b 就已经是连接了一对多或者多对一的条件已经在xml中体现出来了。

HelloBarreat 2015-04-15

打赏
举报

不行试着用子查询写一遍？

qq_14908849 2015-04-15

打赏
举报

SELECT project_name,id,t1.Locs,t2.total_warnings,t3.passed FROM projects LEFT OUTER JOIN (SELECT SUM(loc) AS Locs ,job_id from loc_counter GROUP BY job_id) t1 on projects.id=(SELECT project_id FROM jobs WHERE id=t1.job_id) LEFT OUTER JOIN (SELECT AVG(total_warnings) AS total_warnings ,job_id from sa_issues_summary GROUP BY job_id) t2 on projects.id=(SELECT project_id FROM jobs WHERE id=t2.job_id) LEFT OUTER JOIN (SELECT AVG(passed/total) AS passed,job_id from testng_overview GROUP BY job_id) t3 on projects.id=(SELECT project_id FROM jobs WHERE id=t3.job_id) 在数据库里面sql语句执行正常的但是改成hql就出问题啦我郁闷

qq_14908849 2015-04-15

打赏
举报

javax.servlet.ServletException: org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ( near line 1, column 86 [SELECT p,t1.Locs,t2.totalWarnings,t3.passed FROM domain.Projects p LEFT OUTER JOIN (SELECT SUM(loc) Locs ,job_id from domain.LocCounter GROUP BY job_id) t1 on p.id=(SELECT project_id FROM domain.Jobs WHERE id=t1.job_id) LEFT OUTER JOIN (SELECT AVG(totalWarnings) totalWarnings ,job_id from domain.SaIssuesIummary GROUP BY job_id) t2 on p.id=(SELECT project_id FROM domain.Jobs WHERE id=t2.job_id) LEFT OUTER JOIN (SELECT AVG(passed/total) passed,job_id from domain.TestngOverview GROUP BY job_id) t3 on p.id=(SELECT project_id FROM domain.Jobs WHERE id=t3.job_id) WHERE p.parentProject ='parentlink'] org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:515) org.apache.struts2.dispatcher.FilterDispatcher.doFilter(FilterDispatcher.java:422) root cause org.hibernate.hql.ast.QuerySyntaxError: unexpected token: ( near line 1, column 86 [SELECT p,t1.Locs,t2.totalWarnings,t3.passed FROM domain.Projects p LEFT OUTER JOIN (SELECT SUM(loc) Locs ,job_id from domain.LocCounter GROUP BY job_id) t1 on p.id=(SELECT project_id FROM domain.Jobs WHERE id=t1.job_id) LEFT OUTER JOIN (SELECT AVG(totalWarnings) totalWarnings ,job_id from domain.SaIssuesIummary GROUP BY job_id) t2 on p.id=(SELECT project_id FROM domain.Jobs WHERE id=t2.job_id) LEFT OUTER JOIN (SELECT AVG(passed/total) passed,job_id from domain.TestngOverview GROUP BY job_id) t3 on p.id=(SELECT project_id FROM domain.Jobs WHERE id=t3.job_id) WHERE p.parentProject ='parentlink'] org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:63) org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:215) org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127) org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83) org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:427) org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:884) org.hibernate.impl.SessionImpl.list(SessionImpl.java:834) org.hibernate.impl.QueryImpl.list(QueryImpl.java:74) dao.impl.BaseDaoImpl.findByHql(BaseDaoImpl.java:110) dao.impl.SaIssuesIummaryDaoImp.findByIp(SaIssuesIummaryDaoImp.java:48) services.impl.SaIssuesIummaryServiceImpl.findPp(SaIssuesIummaryServiceImpl.java:25) action.SaissViewAction.jobs(SaissViewAction.java:31) sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

HelloBarreat 2015-04-15