Relation Mapping
EJB 3.0 コース
第9回
1
ここでの目標

複数のテーブルを組み合わせてデータにア
クセスする「Relation Mapping」について理解
する
2
@OneToOne Annotation
3
一対一の関係の例：自宅は一つ
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
309
3
丸山
不二夫
829
冨岡
稚内
ADDR_ID
北海道
MANAGER_ID
829
123
097-0012
日本
P_CODE
COUNTRY
EJB_ADDRESS 住所テーブル
ADDRESS_ID
STREET
CITY PROVINCE
4
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
ADDR_ID
@Entity
@Table(name="EJB_EMPLOYEE")
public class Employee implements Serializable {……
@Id @Column(name="EMP_ID", primaryKey=true)
public Integer getId() { return id; } ……
@Version @Column(name="VERSION")
public int getVersion() { return version; } ……
@Column(name="L_NAME", length=80)
public String getLastName() { return lastName; } ……
@Column(name=“F_NAME", length=80)
public String geFirstName() { return firstName; } ……
@OneToOne(cascade=ALL, fetch=LAZY)
@JoinColumn(name="ADDR_ID")
public Address getAddress() { return address; } ……
}
5
EJB_ADDRESS 住所テーブル
ADDRESS_ID
STREET
CITY PROVINCE
P_CODE
COUNTRY
@Entity
@Table(name="EJB_ADDRESS")
public class Address implements Serializable {…..
@Id
@Column(name="ADDRESS_ID", primaryKey=true)
public Integer getId() { return id; } ….
public void setId(Integer id) { this.id = id; }
public String getStreet() { return street; }
public void setStreet(String street) { this.street = street; }
Default名
public String getCity() { return city; }
public void setCity(String city) { this.city = city; }
public String getProvince() { return province; }
public void setProvince(String province) { this.province = province; }
@Column(name="P_CODE")
public String getPostalCode() { return postalCode; }….
public String getCountry() { return country; }
public void setCountry(String country) { this.country = country; }
}
6
社員のアドレスの取得
public Address getAddressForEmployee(Integer employeeId) {
Employee employee = (Employee)em
.find("Employee", employeeId);
return employee.getAddress();
}
public void setAddressForEmployee
(Address address, Integer employeeId) {
em.persist(address);
Employee employee =
(Employee)em.find("Employee", employeeId);
employee.setAddress(address);
}
public void updateAddress(Address addressExample) {
em.find("Address", addressExample.getId());
em.merge(addressExample);
}
7
@ManyToOne Annotation
8
多対一の関係の例：複数の社員が一人の上司を持つ
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
ADDR_ID
MANAGER_ID
401
3
王子
東
6605
309
402
3
山田
和夫
6606
309
309
3
丸山
不二夫
829
123
F_NAME
ADDR_ID
MANAGER_ID
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
9
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
ADDR_ID
MANAGER_ID
@Entity
@Table(name="EJB_EMPLOYEE")
public class Employee implements Serializable {……
@Id @Column(name="EMP_ID", primaryKey=true)
public Integer getId() { return id; } ……
@Version @Column(name="VERSION")
public int getVersion() { return version; } ……
@Column(name="L_NAME", length=80)
public String getLastName() { return lastName; } ……
@Column(name=“F_NAME", length=80)
public String geFirstName() { return firstName; } ……
@OneToOne
@JoinColumn(name="ADDR_ID")
public Address getAddress() { return address; } ……
@ManyToOne(cascade=PERSIST, fetch=LAZY)
@JoinColumn(name="MANAGER_ID",referencedColumnName="EMP_ID")
public Employee getManager() { return manager; } ……
}
10
社員の上司の取得
public Employee getManagerForEmployee(Integer employeeId) {
Employee employee =
(Employee)em.find("Employee", employeeId);
Employee manager = employee.getManager();
if (manager != null) {
manager.getAddress();
manager.getManager();
}
return manager;
}
11
@OneToMany Annotation
12
一対多の関係の例：一人が複数の部下を持つ
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
309
3
丸山
不二夫
ADDR_ID
829
MANAGER_ID
123
401
3
王子
東
6605
309
402
3
山田
和夫
6606
309
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
ADDR_ID
MANAGER_ID
13
@OneToMany
@JoinColumn(name="MANAGER_ID", referencedColumnName="EMP_ID")
public Collection<Employee> getManagedEmployees() {
return managedEmployees;
}
社員の部下（複数）の取得
public Collection
getManagedEmployeesForEmployee( Integer employeeId) {
Employee employee =
(Employee)em.find("Employee", employeeId);
Collection employees = employee.getManagedEmployees();
Iterator employeeIterator = employees.iterator();
while (employeeIterator.hasNext()) {
Employee mEmployee =
(Employee)employeeIterator.next();
mEmployee.getAddress();
mEmployee.getManager();
}
return employees;
14
}
一対多の関係の例：一人が複数の電話を持つ
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
309
3
丸山
不二夫
１
2
3
43xx
7511
81xx
86xx
32
33
携帯
ADDR_ID
MANAGER_ID
829
職場
123
0162
自宅
309
0162
090
309
309
EJB_PHONENUMBER 電話番号テーブル
ID NUMB CITY TYPE AREA_CODE
OWNER_ID
15
EJB_PHONENUMBER 電話番号テーブル
ID NUMB CITY TYPE
AREA_CODE
OWNER_ID
@Entity
@Table(name="EJB_PHONENUMBER")
public class PhoneNumber implements Serializable {
@Id
@Column(name="ID", primaryKey=true)
public Integer getId() { return id; }
public void setId(Integer id) { this.id = id; }
@Column(name="NUMB")
public String getNumber() { return number; }
public void setNumber(String number) { this.number = numbe}
public String getType() { return type; }
public void setType(String type) { this.type = type; }
@Column(name="AREA_CODE")
public String getAreaCode() { return areaCode; }
public void setAreaCode(String areaCode) { this.areaCode = areaCode; }
@ManyToOne(fetch=LAZY)
@JoinColumn(name="OWNER_ID", referencedColumnName="EMP_ID")
public Employee getOwner() { return owner; }
public void setOwner(Employee owner) { this.owner = owner; }
}
16
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
ADDR_ID
MANAGER_ID
@Entity
@Table(name="EJB_EMPLOYEE")
public class Employee implements Serializable {……
@Id @Column(name="EMP_ID", primaryKey=true)
public Integer getId() { return id; } ……
@Version @Column(name="VERSION")
public int getVersion() { return version; } ……
}
……
@OneToOne
@JoinColumn(name="ADDR_ID")
public Address getAddress() { return address; } ……
@ManyToOne
@JoinColumn(name="MANAGER_ID",referencedColumnName="EMP_ID")
public Employee getManager() { return manager; }
@OneToMany(cascade=ALL)
@JoinColumn(name="OWNER_ID", referencedColumnName="EMP_ID")
public Collection<PhoneNumber> getPhoneNumbers() {
return phoneNumbers; }
}
17
社員の電話番号（複数）の取得
public Collection
getPhoneNumbersForEmployee(Integer employeeId) {
Employee employee =
(Employee)em.find("Employee", employeeId);
Collection phoneNumbers = employee.getPhoneNumbers();
Iterator pIterator = phoneNumbers.iterator();
while (pIterator.hasNext()) {
PhoneNumber pNumber =
(PhoneNumber)pIterator.next();
pNumber.getOwner();
}
return phoneNumbers;
}
18
@ManyToMany Annotation
19
EJB_PROJECT プロジェクトテーブル
PROJ_ID
VERSION
PROJ_NAME
DESCRIP
LEADER_ID
@Entity
@Table(name="EJB_PROJECT")
public class Project implements Serializable {
@Id @Column(name="PROJ_ID", primaryKey=true)
public Integer getId() { return projId; }
public void setId(Integer id) { this.projId = id; }
@Version @Column(name="VERSION")
public int getVersion() { return version; }
protected void setVersion(int version) { this.version = version; }
@Column(name="PROJ_NAME")
public String getName() { return name; }
public void setName(String name) { this.name = name; }
@Column(name="DESCRIP")
public String getDescription() { return description; }
public void setDescription(String description) { this.description = description; }
@OneToOne(fetch=LAZY) @JoinColumn(name="LEADER_ID")
public Employee getTeamLeader() { return teamLeader; }
public void setTeamLeader(Employee teamLeader) {…. }
}
20
多対多の関係の例：社員のプロジェクトへの所属
EJB_PROJ_EMP
Association Table
EMP_ID
PROJ_ID
401 王子
101 EJBプロジェクト
401 王子
102 Webプロジェクト
402 山田
102 Webプロジェクト
402 山田
103 UMLプロジェクト
403 川添
103 UMLプロジェクト
403 川添
101 EJBプロジェクト
309 丸山
101 EJBプロジェクト
309 丸山
102 Webプロジェクト
309 丸山
103 UMLプロジェクト
21
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
ADDR_ID
MANAGER_ID
401
3
王子
東
6605
309
402
3
山田
和夫
6606
309
403
3
川添
真知子
6607
309
EJB_PROJECT プロジェクトテーブル
PROJ_ID
VERSION
PROJ_NAME
DESCRIP
LEADER_ID
101
3
EJBプロジェクト
EJB….
401
102
3
Webプロジェクト
Web….
402
103
3
UMLプロジェクト
UML….
403
22
多対多の関係の表現： Association Table
EJB_EMPLOYEE 社員テーブル
EMP_ID
VERSION
L_NAME
F_NAME
ADDR_ID
MANAGER_ID
joinColumns=@JoinColumn(name="EMP_ID",
referencedColumnName="EMP_ID"),
Association Table
EJB_PROJ_EMP
EMP_ID
PROJ_ID
inverseJoinColumns=@JoinColumn(name="PROJ_ID",
referencedColumnName="PROJ_ID")
EJB_PROJECT プロジェクトテーブル
PROJ_ID
VERSION
PROJ_NAME
DESCRIP
LEADER_ID
23
@ManyToMany(cascade=PERSIST)
@AssociationTable(table=@Table(name="EJB_PROJ_EMP"),
joinColumns=@JoinColumn(name="EMP_ID",
referencedColumnName="EMP_ID"),
inverseJoinColumns=@JoinColumn(name="PROJ_ID",
referencedColumnName="PROJ_ID")
)
public Collection<Project> getProjects() { return projects; }
public void setProjects(Collection<Project> projects) {
this.projects = projects; }
プロジェクトの生成
public void createProject(String name, String description) {
Project project= new Project();
project.setName(name);
project.setDescription(description);
em.persist(project);
}
24
プロジェクトへの社員の追加
public void addEmployeeToProject(
Integer employeeId, Integer projectId) {
Employee employee =
(Employee)em.find("Employee", employeeId);
Project project = (Project)em.find("Project", projectId);
project.addTeamMember(employee);
employee.addProject(project);
}
25
プロジェクト所属の社員の獲得
public Collection getEmployeesForProject(Integer projectId){
Project project =
(Project)em.find(“Project”, projectId);
Iterator employeeIterator =
project.getTeamMembers().iterator();
}
while (employeeIterator.hasNext()) {
Employee employee =
(Employee)employeeIterator.next();
employee.getAddress();
employee.getManager();
}
return project.getTeamMembers();
26
社員登録の削除
public void removeEmployee(Integer employeeId) {
Employee employee = (Employee)
em.find("Employee", employeeId);
Collection<Project> projects = employee.getProjects();
for (Project project : projects) {
project.removeTeamMember(employee);
}
Collection<Employee> employees =
employee.getManagedEmployees();
for (Employee emp : employees) {
emp.setManager(null);
}
Employee manager = employee.getManager();
if (manager != null) {
manager.removeManagedEmployee(employee);
}
}
em.remove(employee);
27

IDCard

Printable Catalog - Hollister Incorporated

Lab 3 - University of Washington

KBME TV 3 (PBS) http://www.prairiepublic.org/ KCCD FM 90.3 (NPR