Spring Data JPA联表查询排除指定字段的最佳实践

针对你遇到的问题，这里有几个靠谱的解决方案，既能避免查询大字段rawContent，又不影响正常的插入操作：

方案1：懒加载+实体图/自定义查询 #

给AttachmentEntity的rawContent字段标记@Basic(fetch = FetchType.LAZY)，告诉JPA默认不加载这个字段：

@Entity
@Table(name = "attachment")
public class AttachmentEntity {
    // 其他字段...
    @Basic(fetch = FetchType.LAZY)
    @Column(name = "raw_content")
    private byte[] rawContent;
    // getter、setter...
}

但光靠懒加载可能不够，有些JPA实现（比如Hibernate）在关联查询时还是会触发加载，这时候配合实体图来明确指定要加载的字段：

public interface EmailEntityRepository extends JpaRepository<EmailEntity, Long> {
    @Query("SELECT e FROM EmailEntity e JOIN FETCH e.attachments a WHERE e.emailId = :emailId")
    @EntityGraph(attributePaths = {"attachments.attachmentId", "attachments.emailId", "attachments.attachmentName", "attachments.attachmentType"})
    EmailEntity findByEmailIdWithAttachmentsWithoutRawContent(@Param("emailId") Long emailId);
}

或者直接写原生SQL，彻底控制查询字段：

@Query(value = "SELECT e.email_id, e.subject, e.body, a.attachment_id, a.email_id, a.attachment_name, a.attachment_type " +
               "FROM email e JOIN attachment a ON e.email_id = a.email_id " +
               "WHERE e.email_id = :emailId", nativeQuery = true)
List<Object[]> findEmailAndAttachmentsWithoutRawContent(@Param("emailId") Long emailId);

这种方式下，插入操作完全不受影响，调用save时JPA会正常处理所有字段，包括rawContent。

方案2：用DTO投影（最稳妥） #

定义只包含需要字段的DTO类，专门用于查询场景：

// Email对应的DTO
public class EmailWithAttachmentsDTO {
    private Long emailId;
    private String subject;
    private String body;
    private List<AttachmentDTO> attachments;

    // 带参数的构造函数，对应查询结果
    public EmailWithAttachmentsDTO(Long emailId, String subject, String body) {
        this.emailId = emailId;
        this.subject = subject;
        this.body = body;
        this.attachments = new ArrayList<>();
    }
    // getter、setter...
}

// Attachment对应的DTO
public class AttachmentDTO {
    private Long attachmentId;
    private String attachmentName;
    private String attachmentType;

    public AttachmentDTO(Long attachmentId, String attachmentName, String attachmentType) {
        this.attachmentId = attachmentId;
        this.attachmentName = attachmentName;
        this.attachmentType = attachmentType;
    }
    // getter、setter...
}

然后在Repository里用构造函数查询：

public interface EmailEntityRepository extends JpaRepository<EmailEntity, Long> {
    @Query("SELECT new com.yourpackage.dto.EmailWithAttachmentsDTO(e.emailId, e.subject, e.body) " +
           "FROM EmailEntity e WHERE e.emailId = :emailId")
    EmailWithAttachmentsDTO findEmailDtoByEmailId(@Param("emailId") Long emailId);

    @Query("SELECT new com.yourpackage.dto.AttachmentDTO(a.attachmentId, a.attachmentName, a.attachmentType) " +
           "FROM AttachmentEntity a WHERE a.emailId = :emailId")
    List<AttachmentDTO> findAttachmentDtosByEmailId(@Param("emailId") Long emailId);
}

如果要一次查询关联数据，也可以用JPQL的构造函数嵌套（注意有些JPA版本支持程度不同），或者分开查询后组装DTO。这种方式完全不会查询rawContent，插入操作还是用原实体类的save方法，两者完全隔离，不会有冲突。

方案3：拆分实体（解决你之前的插入异常） #

你之前尝试拆分到子类出现插入异常，大概率是因为用了错误的继承策略。正确的做法是将rawContent放到单独的实体类，和AttachmentEntity一对一关联：

// 主Attachment实体，不含rawContent
@Entity
@Table(name = "attachment")
public class AttachmentEntity {
    @Id
    @Column(name = "attachment_id")
    private Long attachmentId;
    @Column(name = "email_id")
    private Long emailId;
    @Column(name = "attachment_name")
    private String attachmentName;
    @Column(name = "attachment_type")
    private String attachmentType;

    @OneToOne(mappedBy = "attachment", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private AttachmentContentEntity content;
    // getter、setter...
}

// 单独存储rawContent的实体
@Entity
@Table(name = "attachment") // 和主表同表
public class AttachmentContentEntity {
    @Id
    @Column(name = "attachment_id")
    private Long attachmentId;
    @Column(name = "raw_content")
    private byte[] rawContent;

    @OneToOne
    @MapsId
    @JoinColumn(name = "attachment_id")
    private AttachmentEntity attachment;
    // getter、setter...
}

查询时只加载AttachmentEntity，插入时通过cascade = CascadeType.ALL自动保存AttachmentContentEntity，这样就不会出现插入异常了。不过这个方案比前两个复杂，适合需要频繁单独操作rawContent的场景。

内容的提问来源于stack exchange，提问作者JerryC