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

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

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

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

準備

pom.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>

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

@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;
}

@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;
}

@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のコード。

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

@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());
		}
	}

}

テストデータ。

src/main/resources/data.sql

insert into users(user_id, username, password, email) values (1, 'username', 'pass', '[e-mail]');

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がループするのでその対策につけている。

複合主キー

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

@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;
}

@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;
}

@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;
}

@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するクエリ。

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

テストデータ。

src/main/resources/data.sql

insert into users(user_id, username, password, email) values (1, 'username', 'pass', '[e-mail]');

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