Hibernate通过关联表实现多对多映射

Introduction简介

Recently I was working on a small project where I need to use many to many mapping between two entity. There are many examples online. Most of them did not explain some of the gotchas that I have to work out. This compels me to write this tutorial -- letting readers know some of the issues that I have to deal with. The truth is that once I hashed out all the issues, this is a great way to represent two entity types in many-to-many relationship. This tutorial will show you why and how this is great

最近我做了一个小项目,我需要使用两个实体之间的多对多映射。网上有很多这样的例子。他们中的大多数没有解释我的疑问。这促使我写这篇教程,让读者知道的一些我需要处理的问题。事实是,一旦我完成了所有的问题,这是一个伟大的方式来表示两个实体类型的多对多关系。本教程将向您展示为什么和如何这是伟大的

So what is a many to many relationship? Imagine this, you have a blog, and you want to manage your photos uploaded to your blog. You categorize the photos via galleries. There can be five images (image-1 to image-5). and there can two galleries (gal-1, gal-2). The images image-1, image-3, and image-4 are in gal-1; image-2, image-3, image-4, and image-5 are in gal-2. As you can see, the images image-3 and image-4 are in both galleries. These associations can be identified as many-to-many. I used to avoid such a complex relationship, and only deal with two entities that has direct mapping between them, use one-to-one or one-to-many. For this many-to-many relationship, it is do-able with three tables and use some type of one-to-many mapping (one-to-many between gallery and the join table, and one-to-many between image and join table). But now I have realized that such complex mapping might not be a good idea. I am particularly concerned about the number of explicit SQL calls I had to make to the back end DB. With the proper many-to-many mapping, I think Hibernate can help me simplify the operations I am interested.

多对多的关系是什么?想象一下,你有一个博客,你需要管理你的照片上传到你的博客。你对照片进行分类。可以有五个图像(图1到图5)。和有两个画廊(gal-1 gal-2)。图像图1、图3和图4 gal-1;图2,图3,图4,图5是gal-2。正如您可以看到的,在画廊图片图3和图4。这些联系可以确定是多对多的。我用来避免这样一个复杂的关系,只有处理两个实体之间的直接映射,使用一对一或一对多。多对多关系,它与三个表可行和使用某种类型的一对多映射(画廊和连接表之间一对多,形象和连接表之间的一对多)。但是现在我已经意识到这种复杂的映射可能不是一个好主意。我特别关心的SQL调用的数量我到后端数据库。Hibernate使用适当的多对多映射,我认为可以帮助我简化操作我感兴趣。

So what type of operations am I interested? Well, here are a few ones:

所以我什么类型的操作感兴趣?嗯,这里有一些:

• I like to create galleries, and upload images, then associate images with a gallery. 我喜欢创建画廊,和上传图像,然后把图像与一个画廊。
• I like to delete galleries or images. Do these, I don't have to explicitly remove the association before deleting. 我想删除画廊或图像。做这些,我不需要显式地删除删除前的协会。
• I like to find all the images in a gallery and do pagination. 我想找到所有的图像在一个画廊,做分页。
• I like to add or remove the association between gallery and images, yet not delete the gallery or the images 我想添加或删除画廊和图像之间的关系,但不删除画廊或图像

Background背景

Sounds simple. How do we do these with plain SQL script? Well, I can insert a row in gallery table, and insert another row in image table. Finally I add a row into imagetogallery (this is the join-table) for these two. Now if I delete either the gallery or the image row, there is a way for SQL DB to automatically delete the row in the join-table. I can also delete the row in the join table and severe the relationship between image and gallery. If I want to find all images in a gallery, I do it with one query of two inner joins.

听起来很简单。我们如何用纯SQL脚本做这些?好吧,我可以在画廊表插入一行,和图像表中插入一行。最后我添加一行到imagetogallery(这是连接表)这两个。如果我删除画廊或图像行,有一种方法为SQL数据库自动删除连接表中的行。我也可以删除连接表中的行和严重的图像之间的关系和画廊。如果我想找到所有图像画廊,我做一个查询的两个内在连接。

To illustrate my operations, here is the test table I will be creating (I use MySQL, by the way):

为了说明我的操作,这是我将创建的测试表(顺便说一下,我使用MySQL):

DROP TABLE IF EXISTS imagetogallery;  
DROP TABLE IF EXISTS gallery;  DROP TABLE IF EXISTS image;    
CREATE TABLE image (     
id int NOT NULL PRIMARY KEY,     
filepath VARCHAR(256) NULL  );    
CREATE TABLE gallery (     
id int NOT NULL PRIMARY KEY,     
name VARCHAR(128) NULL  );    
CREATE TABLE imagetogallery (     
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,     
imageid int NOT NULL,     
galleryid int NOT NULL,       
FOREIGN KEY (galleryid) REFERENCES gallery(id)        
ON DELETE CASCADE        
ON UPDATE CASCADE,     
FOREIGN KEY (imageid) REFERENCES image(id)        
ON DELETE CASCADE        
ON UPDATE CASCADE  );

The first three lines basically removes the tables if they've already existed. The table gallery and image each has two columns, the first is the primary key "id". "id" has type integer and cannot be NULL. For me to test easily, I will explicitly set the "id" values in my SQL test and my Hibernate enabled Java program. The last table imagetogallery is more complicate. It has a primary key "id". And its value is set to auto increment. Providing a new id value of this join table automatically whenever a row is inserted is very important when I start using Hibernate. I will explain this when I get to it. The join table also have two foreign keys one to the gallery table and one to the image table. Those two foreign keys have the cascade on update and delete. This again is important, for running SQL statement or using Hibernate. Again, when I get there, I will explain why.

前三行基本上消除了表是否已经存在。表美术馆和图像各有两列,第一个是主键“id”。“id”类型的整数,不能为空。我测试很容易,我要显式地设置“id”值测试SQL和Hibernate使Java程序。最后一个表imagetogallery更加复杂化。它有一个主键“id”。和它的值设置为自动增量。提供一个新的id值时自动连接表插入一行是非常重要的,当我开始使用Hibernate。我将解释这当我得到它。连接表也有两个外键一个画廊表和一个图像表。这两个外键有级联更新和删除。这是很重要的,为运行SQL语句或使用Hibernate。再一次,当我到达那里时,我将解释为什么。

Once I created these tables, I thought it would be a good idea to run some simulations against the setup using plain SQL statements. First thing I do is:

一旦我创建这些表,我认为这将是一个好主意对设置运行一些模拟使用纯SQL语句。我做的第一件事是:

INSERT INTO gallery (id, name) VALUES (1, 'My Gallery');    
INSERT INTO image (id, filepath) VALUES (2, 'c://images//testimg1.jpg');    
INSERT INTO image (id, filepath) VALUES (3, 'c://images//testimg2.jpg');    
INSERT INTO imagetogallery (imageid, galleryid) VALUES (2, 1);    
INSERT INTO imagetogallery (imageid, galleryid) VALUES (3, 1);  

The above code snippet will create a row in gallery table, two rows in the image table, then associate the two image rows with the gallery row. Next, I want to make sure I can do a query to find all the images belong to the gallery with id equals 1. Here is my query:

上面的代码片段将创建一个画廊表中的行,两个图像表中的行,然后将两个图像行与画廊行。接下来,我想确保我可以做一个查询以找到所有图片属于画廊与id = 1。这是我的查询:

SELECT image.* FROM image  INNER JOIN imagetogallery ON image.id = imagetogallery.imageid  WHERE imagetogallery.galleryid = 1  

The query should succeed and produce the following output:

查询成功,应该产生以下输出:

id	filepath
2	c://images//testimg1.jpg
3	c://images//testimg2.jpg

Next, I would experiment with deleting a row in the image table, say the id equals 2. This is done by the following SQL statement:

接下来,我将尝试删除图像表中的一行,说id = 2。这是通过以下SQL语句:

DELETE FROM image WHERE image.id = 2;

I use the same query that finds the images with gallery id equals 1. The query returns:

我使用相同的查询,发现图像画廊id = 1。查询返回:

id	filepath
3	c://images//testimg2.jpg

What happened? Well I did mention that when I get to the CASCADE delete and update, I will explain what these are for and why they are important. Here it is. When I create the join table, I don't have to declare the CASCADE delete or update. Then if I do the delete on the image table, I would get an error back indicating the operation will fail because of foreign key constraint violation. The reason is that the join table has a row that reference the image I was about to delete. In order to correct this error, I have to first delete the row in the join table that has the reference to the image, then I can delete the image. This is rather awkward. Now with the CASCADE delete on the foreign keys, I can just delete a row in image or in gallery tables and the rows in the join table that references either the gallery or the image will automatically be deleted. Wow! That is revolutionary! So what does CASCADE update do? Imagine this, assuming I have to update the id value for a gallery or a image. That is an update of the primary key (dangerous!) and it could fail with error because one or more rows in the join table might have reference to this image. But cit can happen and can be done. With the CASCADE update declared, I can update the id of that image (as long as the id I chose is not used already in the image table). And DB engine would automatically update the imageid in the join table. In fact, if I want to do this to the gallery, I can do it without the manual update to the join table as well. Magically! I love it!

发生了什么事?我提到,当我得到了级联删除和更新,我将解释这些是什么,为什么他们很重要。在这儿。当我创建连接表,我没有要申报的级联删除或更新。如果我做图片上的删除表,我将得到一个错误指示，操作将失败,因为违反外键约束。原因是连接表中的一行参考图像，这个图像正是我要删除的。为了纠正这个错误,我必须首先删除连接表中的行参考图像,然后我可以删除图片。这是相当尴尬的。现在有了基于外键的级联删除,现在我可以在画廊表和图片删除一行，则连接表中的对应行会自动被删除。哇!这是革命!级联更新做什么?想象一下,假设我有更新画廊或形象的id值。这是一个更新的主键(危险!),它可能失败和错误,因为一个或多个连接表中的行可能会参考这张图片。但cit可能发生和可以做到的。与级联更新宣布,我可以更新这一形象的id(只要我选择不使用id已经在图像表)。和DB引擎将自动更新imageid连接表。事实上,如果我想做这个画廊,我能做到没有手动更新连接表。神奇的!我爱它!

With all these tests, I am satisfied with the table design. Now I want to move all these to a Hibernate application. It is nothing fancy, just a plain old Java console application.

通过所有这些测试,我满意表设计。现在我想将所有这些移动到Hibernate应用程序。没有什么特别的,只是一个普通的Java控制台应用程序。