HQL实现多对多关联查询（无关系表的映射）

HQL实现多对多关联查询（无关系表的映射）

目录：

1. 应用场景
2. 演示

[一]、应用场景

在用注解的方式（@ManyToMany @JoinTable）实现多对多映射时，并没有具体的多对多关系表的实体类，那么我们如何利用HQL实现关联查询呢？比如：学生和课程表之间的多对多关系。

[二]、演示

1.实体类

IdEntity.java

1	package com.micmiu.hibernate.anno.entity;

2

3	import javax.persistence.Column;

4	import javax.persistence.GeneratedValue;

5	import javax.persistence.Id;

6	import javax.persistence.MappedSuperclass;

7

8

/**

9	* 统一定义id的entity基类.

10

*

11	* 基类统一定义id的属性名称、数据类型、列名映射及生成策略. <br>

12	* 子类可重载getId()函数重定义id的列名映射和生成策略.

13

*

14	* @author <a href="http://www.micmiu.com">Michael Sun</a>

15

*/

16	@MappedSuperclass

17	public abstract class IdEntity {

18

19	protected Long id;

20

21

@Id

22	@Column(name = "ID")

23	@GeneratedValue

24	// @GeneratedValue(strategy = GenerationType.SEQUENCE)

25	// @GeneratedValue(generator = "system-uuid")

26	// @GenericGenerator(name = "system-uuid", strategy = "uuid")

27	public Long getId() {

28	return id;

29

}

30

31	public void setId(Long id) {

32	this.id = id;

33

}

34

}

学生实体类：Student.java

1	package com.micmiu.hibernate.anno.entity;

2

3	import java.util.Collection;

4	import java.util.Date;

5

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;

13

14	import org.hibernate.annotations.Fetch;

15	import org.hibernate.annotations.FetchMode;

16

17

/**

18

*

19	* Description:

20

*

21	* @author <a href="http://www.micmiu.com">Michael</a>

22	* @see <a href="http://www.micmiu.com">http://www.micmiu.com</a>

23	* @time Create on 2013-6-6 下午2:40:08

24	* @version 1.0

25

*/

26

@Entity

27	@Table(name = "DEMO_T_STUDENT")

28	public class Student extends IdEntity {

29

30	@Column(name = "NAME")

31	private String name;

32

33	@Column(name = "BIRTHDAY")

34	private Date birthday;

35

36	private Collection<Course> courses;

37

38	public String getName() {

39	return name;

40

}

41

42	public void setName(String name) {

43	this.name = name;

44

}

45

46	public Date getBirthday() {

47	return birthday;

48

}

49

50	public void setBirthday(Date birthday) {

51	this.birthday = birthday;

52

}

53

54	@ManyToMany

55	@JoinTable(name = "DEMO_R_S2C", joinColumns = { @JoinColumn(name = "SID") }, inverseJoinColumns = { @JoinColumn(name = "CID") })

56	@Fetch(FetchMode.SUBSELECT)

57	@OrderBy("id")

58	public Collection<Course> getCourses() {

59	return courses;

60

}

61

62	public void setCourses(Collection<Course> courses) {

63	this.courses = courses;

64

}

65

66

@Override

67	public String toString() {

68	return "Student [name=" + name + ", birthday=" + birthday + "]";

69

}

70

71

}

课程实体类：Course.java

1	package com.micmiu.hibernate.anno.entity;

2

3	import javax.persistence.Column;

4	import javax.persistence.Entity;

5	import javax.persistence.Table;

6

7

/**

8

*

9	* Description:

10

*

11	* @author <a href="http://www.micmiu.com">Michael</a>

12	* @see <a href="http://www.micmiu.com">http://www.micmiu.com</a>

13	* @time Create on 2013-6-6 下午2:52:31

14	* @version 1.0

15

*/

16

@Entity

17	@Table(name = "DEMO_T_COURSE")

18	public class Course extends IdEntity {

19

20	@Column(name = "NAME")

21	private String name;

22

23	public String getName() {

24	return name;

25

}

26

27	public void setName(String name) {

28	this.name = name;

29

}

30

31

@Override

32	public String toString() {

33	return "Course [name=" + name + "]";

34

}

35

36

}

2.初始化数据：

利用dbunit初始化演示数据：

1	<?xml version='1.0' encoding="utf-8"?>

2

<dataset>

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" />

6

7	<demo_t_course id="901" name="chinese" />

8	<demo_t_course id="902" name="english" />

9	<demo_t_course id="903" name="math" />

10

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" />

17

18	</dataset>

3.演示关联查询

在Student中配置了多对多关系，在Course没有配置多对多关系，下面将分别演示关联查询这两个实体的方法：

测试基础类 HibernateBaseTest.java

1	package com.micmiu.hibernate;

2

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;

9	import org.junit.Test;

10

11

/**

12

*

13	* Description:

14

*

15	* @author <a href="http://www.micmiu.com">Michael</a>

16	* @see <a href="http://www.micmiu.com">http://www.micmiu.com</a>

17	* @time Create on 2013-6-6 下午7:31:02

18	* @version 1.0

19

*/

20	public abstract class HibernateBaseTest {

21

22	protected static SessionFactory sessionFactory;

23

24	@BeforeClass

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);

31

32

}

33

34	@AfterClass

35	public static void afterClass() {

36	sessionFactory.close();

37

}

38

39

@Test

40	public abstract void testMethod();

41

42

}

测试HQL查询：ManyToManyTest.java

1	package com.micmiu.hibernate;

2

3	import java.util.List;

4

5	import org.hibernate.Query;

6	import org.hibernate.Session;

7	import org.junit.Assert;

8	import org.junit.Test;

9

10	import com.micmiu.hibernate.anno.entity.Course;

11	import com.micmiu.hibernate.anno.entity.Student;

12

13

/**

14

*

15	* Description:测试 注解配置多对多关系的表之间的联合查询

16

*

17	* @author <a href="http://www.micmiu.com">Michael</a>

18	* @see <a href="http://www.micmiu.com">http://www.micmiu.com</a>

19	* @time Create on 2013-6-7 上午11:38:49

20	* @version 1.0

21

*/

22	public class ManyToManyTest extends HibernateBaseTest {

23

24

@Override

25

@Test

26	public void testMethod() {

27	// 测试查询有配置多对多关系的对象

28

test1();

29	// 测试查询无配置多对多关系的对象

30

test2();

31

32

}

33

34

/**

35	* 测试查询有配置多对多关系的对象

36

*/

37	@SuppressWarnings("unchecked")

38	public void test1() {

39	Session session = sessionFactory.openSession();

40

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);

45

46	List<Student> list = query.list();

47	System.out.println("----------- size:" + list.size());

48	Assert.assertEquals(1, list.size());

49

50	for (Student s : list) {

51	Assert.assertEquals("micmiu.com", s.getName());

52	System.out.println(s);

53

}

54

55	session.getTransaction().commit();

56	session.close();

57

}

58

59

/**

60	* 测试查询无配置多对多关系的对象

61

*/

62	@SuppressWarnings("unchecked")

63	public void test2() {

64	Session session = sessionFactory.openSession();

65

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'";

68

69	Query query = session.createQuery(hql);

70

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);

77

}

78

79	session.getTransaction().commit();

80	session.close();

81

}

82

}

执行结果：单元测试通过，运行日志如下：

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]