多對多

在資料庫表格上要進行多對多對應，可以藉由一個中介表格來完成，也就是藉由多對一、一對多來完成多對多關聯。

多對多由於使用了中介表格，在查詢效率不彰，且在程式的物件模式上，多對多會使得物件與物件之間彼此依賴，並不是一個很好的設計方式，在設計上應避免使用多對多關係。

如果一定要使用多對多關係的話，在表格上先如下建立：

CREATE TABLE user (

id INT(11) NOT NULL auto_increment PRIMARY KEY,

name VARCHAR(100) NOT NULL default ''

);

CREATE TABLE user_server (

user_id INT(11),

server_id INT(11)

);

CREATE TABLE server (

id INT(11) NOT NULL auto_increment PRIMARY KEY,

address VARCHAR(100) NOT NULL default ''

);

先設計User類別如下：

User.java

package onlyfun.caterpillar;

import java.util.Set;

public class User {

private Integer id;

private String name;

private Set servers;

public User() {

}

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public Set getServers() {

return servers;

}

public void setServers(Set servers) {

this.servers = servers;

}

再來設計Server類別如下：

Server.java

package onlyfun.caterpillar;

import java.util.Set;

public class Server {

private Integer id;

private String address;

private Set users;

public String getAddress() {

return address;

}

public void setAddress(String address) {

this.address = address;

}

public Integer getId() {

return id;

}

public void setId(Integer id) {

this.id = id;

}

public Set getUsers() {

return users;

}

public void setUsers(Set users) {

this.users = users;

}

在映射文件上，使用<many-to-many>標籤來完成映射關係：

User.hbm.xml

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

<!DOCTYPE hibernate-mapping

PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

</id>

<set name="servers"

table="user_server"

cascade="save-update">

<many-to-many class="onlyfun.caterpillar.Server"

column="server_id"/>

</set>

</class>

</hibernate-mapping>

注意到cascade是設定為save-update，因為在多對多的關係中，很少因為刪除其中之一，而所關聯的實體都要一併刪除的，所以設定save-update，表示在save或update時，一併對關聯的物件進行對應的save或update。

Server.hbm.xml的定義如下：

Server.hbm.xml

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

<!DOCTYPE hibernate-mapping

PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

</id>

<set name="users"

table="user_server"

inverse="true"

cascade="save-update">

<many-to-many class="onlyfun.caterpillar.User"

column="user_id"/>

</set>

</class>

</hibernate-mapping>

一個儲存時的例子如下：

Server server1 = new Server();

server1.setAddress("PC-219");

server1.setUsers(new HashSet());

Server server2 = new Server();

server2.setAddress("PC-220");

server2.setUsers(new HashSet());

Server server3 = new Server();

server3.setAddress("PC-221");

server3.setUsers(new HashSet());

User user1 = new User();

user1.setName("caterpillar");

user1.setServers(new HashSet());

User user2 = new User();

user2.setName("momor");

user2.setServers(new HashSet());

// 多對多，互相參考

user1.getServers().add(server1);

user1.getServers().add(server2);

user1.getServers().add(server3);

server1.getUsers().add(user1);

server2.getUsers().add(user1);

server3.getUsers().add(user1);

user2.getServers().add(server1);

user2.getServers().add(server3);

server1.getUsers().add(user2);

server3.getUsers().add(user2);

Session session = sessionFactory.openSession();

Transaction tx= session.beginTransaction();

session.save(user1);

session.save(user2);

tx.commit();

session.close();

執行後資料庫的內容如下：

mysql> select * from user;

+----+--------------+

| id | name |

+----+--------------+

| 1 | caterpillar |

| 2 | momor |

+----+--------------+

2 rows in set (0.00 sec)

mysql> select * from user_serv

+----------+-------------+

| user_id | server_id |

+----------+-------------+

| 1 | 1 |

| 1 | 2 |

| 1 | 3 |

| 2 | 1 |

| 2 | 2 |

+----------+-------------+

5 rows in set (0.00 sec)

mysql> select * from server;

+----+-----------+

| id | address |

+----+-----------+

| 1 | PC-219 |

| 2 | PC-221 |

| 3 | PC-220 |

+----+-----------+

3 rows in set (0.00 sec)