JPAで関連テーブルのカラムの取得

JPAで多対多を扱う場合は```@ManyToMany```で関連テーブルを透過的にアクセスできる。この場合、関連テーブルのカラムにはアクセスできない。とはいえ、関連テーブルに追加でカラム持つことはあまり無いので、現実的にはそこまで困らないのだが。今回やる必要が出たのでやり方をメモしておく。

やり方は http://www.codejava.net/frameworks/hibernate/hibernate-many-to-many-association-with-extra-columns-in-join-table-example を参考にした。

サンプルのテーブルはリンク先を丸パクリしてます。それぞれのやり方のER図はリンク先参照。

# 準備
## pom.xml
```xml:pom.xml
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.0.1.RELEASE</version>
	</parent>
<dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>com.h2database</groupId>
            <artifactId>h2</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
</dependencies>
```

# 関連テーブルに代替キーを持つ

```java
@Data
@Entity
@Table(name = "USERS")
public class User {
	@Id
	private Long userId;
	private String username;
	private String password;
	private String email;
	
	@OneToMany(mappedBy = "user")
	private Set<UserGroup> userGroups;
}
```

```java
@Data
@Entity
@Table(name = "USERS_GROUPS")
@EqualsAndHashCode(exclude= {"user", "group"})
public class UserGroup {
	@Id
    private Long id;
	@ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "USER_ID")
    private User user;
	@ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "GROUP_ID")
    private Group group;
	
    private boolean activated;
    private Date registeredDate;
}
```

```java
@Data
@Entity
@Table(name = "GROUPS")
public class Group {
	@Id
	private Long groupId;
    private String name;
    
    @OneToMany(mappedBy = "group")
    private Set<UserGroup> userGroups;
}

```

関連テーブル、ここでは```USERS_GROUPS```に、代替キーのカラム```USERS_GROUPS.ID```を持たせている。あとは、それぞれの一対多の関係を、```@OneToMany```, ```@ManyToOne```で定義する。

以下動作確認用の適当なrepositoryとmainのコード。

```java
public interface UserRepository extends CrudRepository<User, Long> {
}
```

```java
@SpringBootApplication
public class ManyToManyApplication implements CommandLineRunner {
	public static void main(String[] args) throws InterruptedException {
		SpringApplication.run(ManyToManyApplication.class, args).close();
	}

	@Autowired
	UserRepository userRepository;

	@Transactional
	@Override
	public void run(String... args) throws Exception {
		Optional<User> user = userRepository.findById(1L);
		Set<UserGroup> userGroups = user.get().getUserGroups();
		for (UserGroup userGroup : userGroups) {
			System.out.println(userGroup.getRegisteredDate() + " " + userGroup.isActivated());
			System.out.println(userGroup.getGroup().getName());
		}
	}

}
```
テストデータ。

```sql:src/main/resources/data.sql
insert into users(user_id, username, password, email) values (1, 'username', 'pass', 'hoge@hoge.com');

insert into groups(group_id, name) values (10, 'groupname001');
insert into groups(group_id, name) values (20, 'groupname002');

insert into users_groups(id, user_id, group_id, activated, registered_date) values (100, 1, 10, true, '2018-04-26 12:34:56');
insert into users_groups(id, user_id, group_id, activated, registered_date) values (200, 1, 20, true, '2018-04-25 12:34:56');
```

なおlombokの```@EqualsAndHashCode(exclude= {"user", "group"})```はhashcodeがループするのでその対策につけている。



# 複合主キー
こちらは代替キーを作らず、複合主キーで対応するやり方。

```java
@Data
@Entity
@Table(name = "USERS")
public class User {
	@Id
	private Long userId;
	private String username;
	private String password;
	private String email;

	@OneToMany(mappedBy = "userGroupId.user", cascade = CascadeType.ALL)
	private Set<UserGroup> userGroups;
}
```

```java
@Data
@Entity
@Table(name = "USERS_GROUPS")
@AssociationOverrides({
	@AssociationOverride(name="userGroupId.user", joinColumns=@JoinColumn(name="user_id")),
	@AssociationOverride(name="userGroupId.group", joinColumns=@JoinColumn(name="group_id"))
})
public class UserGroup {
	@Id
	private UserGroupId userGroupId;
	
    private boolean activated;
    private Date registeredDate;
}
```

```java
@Data
@Embeddable
@EqualsAndHashCode(exclude= {"user", "group"})
public class UserGroupId implements Serializable  {
	private static final long serialVersionUID = 1L;
	
	@ManyToOne(cascade = CascadeType.ALL)
    private User user;
	@ManyToOne(cascade = CascadeType.ALL)
    private Group group;
}
```

```java
@Data
@Entity
@Table(name = "GROUPS")
public class Group {
	@Id
	private Long groupId;
	private String name;

	@OneToMany(mappedBy = "userGroupId.group", cascade = CascadeType.ALL)
	private Set<UserGroup> userGroups;
}
```

オマケのlazy fetchではなくJOIN FETCHするクエリ。

```java
@Repository
public interface UserRepository extends CrudRepository<User, Long> {
	
	@Query("select u from User u JOIN FETCH u.userGroups ug JOIN FETCH ug.userGroupId.group g")
	Optional<User> find(@Param("id")Long id);
}
```

テストデータ。

```sql:src/main/resources/data.sql
insert into users(user_id, username, password, email) values (1, 'username', 'pass', 'hoge@hoge.com');

insert into groups(group_id, name) values (10, 'groupname001');
insert into groups(group_id, name) values (20, 'groupname002');

insert into users_groups(user_id, group_id, activated, registered_date) values (1, 10, true, '2018-04-26 12:34:56');
insert into users_groups(user_id, group_id, activated, registered_date) values (1, 20, true, '2018-04-25 12:34:56');
```