spring jpa 连表重复 join 如何解决?

UserEntity
@Data
@Accessors(chain = true)
@Entity
@Table(name = "user")
public class UserEntity {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @OneToOne(mappedBy = "user", fetch = FetchType.LAZY)
    @JsonManagedReference
    private UserArchiveEntity userArchive;
}
UserArchiveEntity
@Data
@Accessors(chain = true)
@Entity
@Table(name = "user_archive")
public class UserArchiveEntity {
    @Id
    @Column(name = "user_id")
    private Long userId = 0L;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "user_id", insertable = false, updatable = false)
    // 从属关系:从;一定要加这个,避免json序列化循环引用导致报错
    @JsonBackReference
    private UserEntity user;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "nation_id", insertable = false, updatable = false)
    private NationEntity nation;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "industry_id", insertable = false, updatable = false)
    private IndustryEntity industry;
}
UserRepository
@Repository
public interface UserRepository extends JpaRepository<UserEntity, Long>, JpaSpecificationExecutor<UserEntity> {
    @EntityGraph(attributePaths = {"userArchive", "userArchive.nation", "userArchive.industry"})
    @NotNull
    @Override
    Page<UserEntity> findAll(@NotNull Specification<UserEntity> specification, @NotNull Pageable pageable);

我的查询如下:

public List<UserEntity> recommend(int size) throws Exception {
        UserEntity userEntity = UserContext.get();
        Optional<UserArchiveEntity> optionalUserArchiveEntity = this.userArchiveRepository.findByUserId(userEntity.getId());
        if (optionalUserArchiveEntity.isEmpty()) {
            throw new Exception("用户档案记录不存在");
        }
        UserArchiveEntity userArchiveEntity = optionalUserArchiveEntity.get();
        Integer gender = userArchiveEntity.getGender();
        Integer judgeGender = gender == UserArchiveEntity.GENDER_MALE ? UserArchiveEntity.GENDER_FEMALE : UserArchiveEntity.GENDER_MALE;
        Specification<UserEntity> specification = (root, query, criteriaBuilder) -> {
            Predicate predicate = criteriaBuilder.conjunction();
            Join<UserEntity, UserArchiveEntity> userArchiveEntityJoin = root.join("userArchive");
            return predicate;
        };
        Sort sort = Sort.by(Sort.Direction.DESC, "infoCompletedRatio");
        Pageable pageable = PageRequest.of(0, size, sort);
        return this.userRepository.findAll(specification, pageable).getContent();

    }

出现的问题:

  1. 上述代码生成的sql针对 userArchiveleft join 两次!即 user left join userarchive on xxx left join userarchive on xxx left join nation on xx on left join industry on xx,该如何避免重复 join
  2. findAll 方法针对不同的业务场景关联的实体要求不一样;但是 spring jpa 没法声明方法签名一致的方法,也没法随意声明不同名称的方法(需要符合 spring jpa 的规范名称才会被正确解析),且由于要支持动态条件查询,必须要有 specification 这种 criterial 标准api 。该如何针对不同业务场景实现不同的模型关联?

阅读 601
2 个回答
  • 第一个问题join userArchive重复,是因为实体关联 @OneToOne 是左连接,在Specification 中手动 join 用的是内连接,所以被判定为不同的连接方式导致。在specification 中指定 root.join("userArchive", JoinType.LEFT) 指明为左连接,就不会出现重复连接问题。
  • 第二个问题,目前没想到好的方法,等待高手回答。
推荐问题
宣传栏