Get related table columns with JPA

When dealing with many-to-many in JPA, you can transparently access related tables with `` `@ ManyToMany```. In this case, you cannot access the columns of the related table. However, I don't often have additional columns in the related table, so in reality it's not that much of a problem. I need to do it this time, so make a note of how to do it.

For the method, I referred to http://www.codejava.net/frameworks/hibernate/hibernate-many-to-many-association-with-extra-columns-in-join-table-example.

The sample table has a rounded link. See the link for the ER diagram of each method.

Preparation

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>

Have an alternate key in the related table

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

The related table, here USERS_GROUPS, has an alternate key column USERS_GROUPS.ID. After that, each one-to-many relationship is defined by `@ OneToMany```, `@ ManyToOne``.

Below is the appropriate repository and main code for checking the operation.

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

}

test data.

src/main/resources/data.sql

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

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

Note that lombok's ``` @ EqualsAndHashCode (exclude = {"user "," group "})` `` is attached to the countermeasure because hashcode loops.

Composite primary key

This is a method that does not create an alternate key, but uses a composite primary key.

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

A query that JOIN FETCH instead of the bonus lazy 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);
}

test data.

src/main/resources/data.sql

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

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