有两张表Student表和Room表,这两张表没有主外键关系,独立的两张表
Student表
mysql> select * from student;
+----+-----------+---------+
| id | name | room_id |
+----+-----------+---------+
| 1 | 掌声那 | 100 |
| 2 | 战三 | 300 |
| 3 | 李四 | 500 |
+----+-----------+---------+
3 rows in set (0.00 sec)Room表
mysql> select * from room;
+----+---------+---------+
| id | name | room_id |
+----+---------+---------+
| 1 | 房间1 | 100 |
| 2 | 房间2 | 200 |
| 3 | 房间3 | 300 |
+----+---------+---------+
3 rows in set (0.00 sec)在mysql里直接敲命令能做连接查询
mysql> select s.name,r.name from student s join room r on s.room_id=r.room_id;
+-----------+---------+
| name | name |
+-----------+---------+
| 掌声那 | 房间1 |
| 战三 | 房间3 |
+-----------+---------+
2 rows in set (0.00 sec)
mysql> select s.name,r.name from student s left join room r on s.room_id=r.room_id;
+-----------+---------+
| name | name |
+-----------+---------+
| 掌声那 | 房间1 |
| 战三 | 房间3 |
| 李四 | NULL |
+-----------+---------+
3 rows in set (0.00 sec)为什么在hibernate里就不能做连接查询呢,
首先证明环境搭建是没问题的public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext("beans.xml");
SessionFactory factory = (SessionFactory) ctx.getBean("sessionFactory");
Session s = factory.openSession();
s.beginTransaction(); String hql="select s.name,s.room_id,s.id from Student s";
Query query=s.createQuery(hql);
List<Object[]> rs=query.list();
for(Object[] obj:rs){
for(Object o:obj){
System.out.print(o+",");
}
System.out.println();
}这个没问题,输出如下
掌声那,100,1,
战三,300,2,
李四,500,3,
把hql改一下,做连接查询,就报错了public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext("beans.xml");
SessionFactory factory = (SessionFactory) ctx.getBean("sessionFactory");
Session s = factory.openSession();
s.beginTransaction(); String hql = "select s.name,r.name from Student s join Room r on s.room_id=r.room_id";
Query query=s.createQuery(hql);
List<Object[]> rs=query.list();
for(Object[] obj:rs){
for(Object o:obj){
System.out.print(o+",");
}
System.out.println();
}报了这个错Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 61 [select s.name,r.name from cn.lsk.bean.Student s join Room r on s.room_id=r.room_id]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651)
at junit.Hello.main(Hello.java:67)这是为什么啊!!是我hql写错了?!!
Student表
mysql> select * from student;
+----+-----------+---------+
| id | name | room_id |
+----+-----------+---------+
| 1 | 掌声那 | 100 |
| 2 | 战三 | 300 |
| 3 | 李四 | 500 |
+----+-----------+---------+
3 rows in set (0.00 sec)Room表
mysql> select * from room;
+----+---------+---------+
| id | name | room_id |
+----+---------+---------+
| 1 | 房间1 | 100 |
| 2 | 房间2 | 200 |
| 3 | 房间3 | 300 |
+----+---------+---------+
3 rows in set (0.00 sec)在mysql里直接敲命令能做连接查询
mysql> select s.name,r.name from student s join room r on s.room_id=r.room_id;
+-----------+---------+
| name | name |
+-----------+---------+
| 掌声那 | 房间1 |
| 战三 | 房间3 |
+-----------+---------+
2 rows in set (0.00 sec)
mysql> select s.name,r.name from student s left join room r on s.room_id=r.room_id;
+-----------+---------+
| name | name |
+-----------+---------+
| 掌声那 | 房间1 |
| 战三 | 房间3 |
| 李四 | NULL |
+-----------+---------+
3 rows in set (0.00 sec)为什么在hibernate里就不能做连接查询呢,
首先证明环境搭建是没问题的public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext("beans.xml");
SessionFactory factory = (SessionFactory) ctx.getBean("sessionFactory");
Session s = factory.openSession();
s.beginTransaction(); String hql="select s.name,s.room_id,s.id from Student s";
Query query=s.createQuery(hql);
List<Object[]> rs=query.list();
for(Object[] obj:rs){
for(Object o:obj){
System.out.print(o+",");
}
System.out.println();
}这个没问题,输出如下
掌声那,100,1,
战三,300,2,
李四,500,3,
把hql改一下,做连接查询,就报错了public static void main(String[] args) {
ApplicationContext ctx = new ClassPathXmlApplicationContext("beans.xml");
SessionFactory factory = (SessionFactory) ctx.getBean("sessionFactory");
Session s = factory.openSession();
s.beginTransaction(); String hql = "select s.name,r.name from Student s join Room r on s.room_id=r.room_id";
Query query=s.createQuery(hql);
List<Object[]> rs=query.list();
for(Object[] obj:rs){
for(Object o:obj){
System.out.print(o+",");
}
System.out.println();
}报了这个错Exception in thread "main" org.hibernate.hql.ast.QuerySyntaxException: unexpected token: on near line 1, column 61 [select s.name,r.name from cn.lsk.bean.Student s join Room r on s.room_id=r.room_id]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:54)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:47)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:82)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:284)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:182)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:94)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:156)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:135)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1651)
at junit.Hello.main(Hello.java:67)这是为什么啊!!是我hql写错了?!!
也就是必须映射你上面2个实体的关系。
在hibernate中,createQuery创建的是HQL查询,如果是sql查询应该用createSQLQuery创建
没有 on关键字 在Hibernate中。