HQL实现多对多关联查询(无关系表的映射)
目录:
- 应用场景
- 演示
[一]、应用场景
在用注解的方式(@ManyToMany @JoinTable)实现多对多映射时,并没有具体的多对多关系表的实体类,那么我们如何利用HQL实现关联查询呢?比如:学生和课程表之间的多对多关系。
[二]、演示
1.实体类
IdEntity.java
1 | package com.micmiu.hibernate.anno.entity; |
3 | import javax.persistence.Column; |
4 | import javax.persistence.GeneratedValue; |
5 | import javax.persistence.Id; |
6 | import javax.persistence.MappedSuperclass; |
11 | * 基类统一定义id的属性名称、数据类型、列名映射及生成策略. <br> |
12 | * 子类可重载getId()函数重定义id的列名映射和生成策略. |
17 | public abstract class IdEntity { |
31 | public void setId(Long id) { |
学生实体类:Student.java
1 | package com.micmiu.hibernate.anno.entity; |
3 | import java.util.Collection; |
6 | import javax.persistence.Column; |
7 | import javax.persistence.Entity; |
8 | import javax.persistence.JoinColumn; |
9 | import javax.persistence.JoinTable; |
10 | import javax.persistence.ManyToMany; |
11 | import javax.persistence.OrderBy; |
12 | import javax.persistence.Table; |
14 | import org.hibernate.annotations.Fetch; |
15 | import org.hibernate.annotations.FetchMode; |
23 | * @time Create on 2013-6-6 下午2:40:08 |
27 | @Table (name = "DEMO_T_STUDENT" ) |
28 | public class Student extends IdEntity { |
30 | @Column (name = "NAME" ) |
33 | @Column (name = "BIRTHDAY" ) |
34 | private Date birthday; |
36 | private Collection<Course> courses; |
38 | public String getName() { |
42 | public void setName(String name) { |
46 | public Date getBirthday() { |
50 | public void setBirthday(Date birthday) { |
51 | this .birthday = birthday; |
55 | @JoinTable (name = "DEMO_R_S2C" , joinColumns = { @JoinColumn (name = "SID" ) }, inverseJoinColumns = { @JoinColumn (name = "CID" ) }) |
56 | @Fetch (FetchMode.SUBSELECT) |
58 | public Collection<Course> getCourses() { |
62 | public void setCourses(Collection<Course> courses) { |
63 | this .courses = courses; |
67 | public String toString() { |
68 | return "Student [name=" + name + ", birthday=" + birthday + "]" ; |
课程实体类:Course.java
1 | package com.micmiu.hibernate.anno.entity; |
3 | import javax.persistence.Column; |
4 | import javax.persistence.Entity; |
5 | import javax.persistence.Table; |
13 | * @time Create on 2013-6-6 下午2:52:31 |
17 | @Table (name = "DEMO_T_COURSE" ) |
18 | public class Course extends IdEntity { |
20 | @Column (name = "NAME" ) |
23 | public String getName() { |
27 | public void setName(String name) { |
32 | public String toString() { |
33 | return "Course [name=" + name + "]" ; |
2.初始化数据:
利用dbunit初始化演示数据:
1 | <? xml version = '1.0' encoding = "utf-8" ?> |
3 | < demo_t_student id = "101" name = "micmiu.com" birthday = "2012-12-12" /> |
4 | < demo_t_student id = "102" name = "sjsky007@gmail.com" birthday = "2008-08-08" /> |
5 | < demo_t_student id = "103" name = "ctosun.com" birthday = "2013-01-04" /> |
7 | < demo_t_course id = "901" name = "chinese" /> |
8 | < demo_t_course id = "902" name = "english" /> |
9 | < demo_t_course id = "903" name = "math" /> |
11 | < demo_r_s2c sid = "101" cid = "901" /> |
12 | < demo_r_s2c sid = "101" cid = "902" /> |
13 | < demo_r_s2c sid = "101" cid = "903" /> |
14 | < demo_r_s2c sid = "102" cid = "902" /> |
15 | < demo_r_s2c sid = "102" cid = "903" /> |
16 | < demo_r_s2c sid = "103" cid = "903" /> |
3.演示关联查询
在Student中配置了多对多关系,在Course没有配置多对多关系,下面将分别演示关联查询这两个实体的方法:
测试基础类 HibernateBaseTest.java
1 | package com.micmiu.hibernate; |
3 | import org.hibernate.SessionFactory; |
4 | import org.hibernate.cfg.Configuration; |
5 | import org.hibernate.service.ServiceRegistry; |
6 | import org.hibernate.service.ServiceRegistryBuilder; |
7 | import org.junit.AfterClass; |
8 | import org.junit.BeforeClass; |
17 | * @time Create on 2013-6-6 下午7:31:02 |
20 | public abstract class HibernateBaseTest { |
22 | protected static SessionFactory sessionFactory; |
25 | public static void beforeClass() { |
26 | Configuration configuration = new Configuration().configure(); |
27 | ServiceRegistry serviceRegistry = new ServiceRegistryBuilder() |
28 | .applySettings(configuration.getProperties()) |
29 | .buildServiceRegistry(); |
30 | sessionFactory = configuration.buildSessionFactory(serviceRegistry); |
35 | public static void afterClass() { |
36 | sessionFactory.close(); |
40 | public abstract void testMethod(); |
测试HQL查询:ManyToManyTest.java
1 | package com.micmiu.hibernate; |
5 | import org.hibernate.Query; |
6 | import org.hibernate.Session; |
7 | import org.junit.Assert; |
10 | import com.micmiu.hibernate.anno.entity.Course; |
11 | import com.micmiu.hibernate.anno.entity.Student; |
15 | * Description:测试 注解配置多对多关系的表之间的联合查询 |
19 | * @time Create on 2013-6-7 上午11:38:49 |
22 | public class ManyToManyTest extends HibernateBaseTest { |
26 | public void testMethod() { |
37 | @SuppressWarnings ( "unchecked" ) |
39 | Session session = sessionFactory.openSession(); |
41 | session.beginTransaction(); |
42 | String hql = "select s from Student s join s.courses c where s.name like '%micmiu.com%' and c.name ='math'" ; |
43 | String hql2 = "select s from Student s,Course c where c.id in elements (s.courses) and s.name like '%micmiu.com%' and c.name ='math'" ; |
44 | Query query = session.createQuery(hql); |
46 | List<Student> list = query.list(); |
47 | System.out.println( "----------- size:" + list.size()); |
48 | Assert.assertEquals( 1 , list.size()); |
50 | for (Student s : list) { |
51 | Assert.assertEquals( "micmiu.com" , s.getName()); |
52 | System.out.println(s); |
55 | session.getTransaction().commit(); |
62 | @SuppressWarnings ( "unchecked" ) |
64 | Session session = sessionFactory.openSession(); |
66 | session.beginTransaction(); |
67 | String hql = "select distinct c from Student s,Course c where c.id in elements (s.courses) and s.name like '%micmiu.com%' and c.name ='math'" ; |
69 | Query query = session.createQuery(hql); |
71 | List<Course> list = query.list(); |
72 | System.out.println( "----------- size:" + list.size()); |
73 | Assert.assertEquals( 1 , list.size()); |
74 | for (Course c : list) { |
75 | Assert.assertEquals( "math" , c.getName()); |
76 | System.out.println(c); |
79 | session.getTransaction().commit(); |
执行结果:单元测试通过,运行日志如下:
Hibernate:
select
student0_.ID as ID1_,
student0_.birthday as birthday1_,
student0_.name as name1_
from
DEMO_T_STUDENT student0_
inner join
DEMO_R_S2C courses1_
on student0_.ID=courses1_.SID
inner join
DEMO_T_COURSE course2_
on courses1_.CID=course2_.ID
where
(
student0_.name like ‘%micmiu.com%’
)
and course2_.name=’math’
———– size:1
Student [name=micmiu.com, birthday=2012-12-12 00:00:00.0]
Hibernate:
select
distinct course1_.ID as ID0_,
course1_.name as name0_
from
DEMO_T_STUDENT student0_ cross
join
DEMO_T_COURSE course1_
where
(
course1_.ID in (
select
courses2_.CID
from
DEMO_R_S2C courses2_
where
student0_.ID=courses2_.SID
)
)
and (
student0_.name like ‘%micmiu.com%’
)
and course1_.name=’math’
———– size:1
Course [name=math]