JPA原生SQL查询如何同时获取EmailSubscription与关联Subscriber

我有一个用于获取EmailSubscription的Repository方法：

@Query("SELECT es FROM EmailSubscription es "
    + "JOIN FETCH es.subscriber s "
    + "WHERE es.id > :batchStartId "
    + "AND es.subscriptionTypeId = :typeId "
    + "AND es.active = :active "
    + "ORDER BY es.id ASC")
List<EmailSubscription> findByIdGreaterThanAndSubscriptionTypeIdAndActive(
    @Param("batchStartId") long batchStartId,
    @Param("typeId") Integer typeId,
    @Param("active") Boolean active,
    Pageable pageable);

EmailSubscription实体定义如下：

public class EmailSubscription {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(updatable = false, nullable = false)
    private Long id;

    @Column(name = "type_id")
    private Integer subscriptionTypeId;
    private Boolean active;
    @Pattern(regexp = LOCALE_VALIDATION_REGEX, message = "invalid code")
    private String locale;
    private LocalDateTime lastSentTimestamp;
    private Integer lastSentSequence;
    private Long lastUpdatePersonId;
    private LocalDateTime lastUpdateTimestamp;
    private LocalDateTime creationTimestamp;

    @ManyToOne(targetEntity = Subscriber.class, fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "subscriber_id", referencedColumnName = "id")
    private Subscriber subscriber;
}

该JPQL查询在批量获取10k条记录时性能极差（耗时10秒以上），于是我编写了一条原生SQL查询，获取10k条记录耗时不到1秒：

@Query(value = "SELECT es.* FROM subscriptions.email_subscriptions es "
    + "IGNORE INDEX (PRIMARY) "
    + "JOIN subscriptions.subscribers s ON es.subscriber_id = s.id "
    + "WHERE es.type_id = :typeId "
    + "AND es.active = :active "
    + "AND es.id > :batchStartId "
    + "ORDER BY es.id ", nativeQuery = true)

但这条原生SQL仅能获取EmailSubscription数据，无法同时加载关联的Subscriber实体，请问如何在原生SQL查询中同时获取EmailSubscription及其关联的Subscriber？

方法1：通过@SqlResultSetMapping映射关联实体 #

利用JPA的@SqlResultSetMapping和@EntityResult定义原生查询的结果映射，让JPA自动将查询结果装配到关联实体中。

步骤1：定义结果映射 #

在EmailSubscription实体类上添加映射配置：

@Entity
@SqlResultSetMapping(
    name = "EmailSubscriptionWithSubscriberMapping",
    entities = {
        @EntityResult(
            entityClass = EmailSubscription.class,
            fields = {
                @FieldResult(name = "id", column = "es_id"),
                @FieldResult(name = "subscriptionTypeId", column = "es_type_id"),
                @FieldResult(name = "active", column = "es_active"),
                @FieldResult(name = "locale", column = "es_locale"),
                @FieldResult(name = "lastSentTimestamp", column = "es_last_sent_timestamp"),
                @FieldResult(name = "lastSentSequence", column = "es_last_sent_sequence"),
                @FieldResult(name = "lastUpdatePersonId", column = "es_last_update_person_id"),
                @FieldResult(name = "lastUpdateTimestamp", column = "es_last_update_timestamp"),
                @FieldResult(name = "creationTimestamp", column = "es_creation_timestamp")
            }
        ),
        @EntityResult(
            entityClass = Subscriber.class,
            fields = {
                @FieldResult(name = "id", column = "s_id"),
                // 根据你的Subscriber实体补充字段映射
                @FieldResult(name = "email", column = "s_email"),
                @FieldResult(name = "name", column = "s_name")
            }
        )
    }
)
public class EmailSubscription {
    // 原实体代码不变
}

步骤2：修改原生SQL查询 #

调整SQL，明确查询两个表的所有字段并添加前缀避免冲突，指定使用上述结果映射：

@Query(
    value = "SELECT "
        + "es.id AS es_id, es.type_id AS es_type_id, es.active AS es_active, "
        + "es.locale AS es_locale, es.last_sent_timestamp AS es_last_sent_timestamp, "
        + "es.last_sent_sequence AS es_last_sent_sequence, es.last_update_person_id AS es_last_update_person_id, "
        + "es.last_update_timestamp AS es_last_update_timestamp, es.creation_timestamp AS es_creation_timestamp, "
        + "s.id AS s_id, s.email AS s_email, s.name AS s_name "
        + "FROM subscriptions.email_subscriptions es "
        + "IGNORE INDEX (PRIMARY) "
        + "JOIN subscriptions.subscribers s ON es.subscriber_id = s.id "
        + "WHERE es.type_id = :typeId "
        + "AND es.active = :active "
        + "AND es.id > :batchStartId "
        + "ORDER BY es.id ",
    nativeQuery = true,
    resultSetMapping = "EmailSubscriptionWithSubscriberMapping"
)
List<Object[]> findEmailSubscriptionsWithSubscriber(
    @Param("batchStartId") long batchStartId,
    @Param("typeId") Integer typeId,
    @Param("active") Boolean active,
    Pageable pageable);

步骤3：处理查询结果 #

查询返回Object[]数组，第一个元素是EmailSubscription实例，第二个是Subscriber实例，手动关联即可：

List<Object[]> results = repository.findEmailSubscriptionsWithSubscriber(...);
List<EmailSubscription> subscriptions = results.stream()
    .map(arr -> {
        EmailSubscription es = (EmailSubscription) arr[0];
        Subscriber s = (Subscriber) arr[1];
        es.setSubscriber(s);
        return es;
    })
    .collect(Collectors.toList());

方法2：使用构造函数直接装配完整对象 #

定义包含关联实体参数的构造函数，让JPA直接通过构造函数装配对象。

步骤1：添加带参数的构造函数 #

在EmailSubscription和Subscriber中分别添加对应构造函数（必须保留无参构造函数供JPA使用）：

// EmailSubscription类
public class EmailSubscription {
    // 原字段定义

    public EmailSubscription() {}

    public EmailSubscription(Long id, Integer subscriptionTypeId, Boolean active, String locale,
                             LocalDateTime lastSentTimestamp, Integer lastSentSequence,
                             Long lastUpdatePersonId, LocalDateTime lastUpdateTimestamp,
                             LocalDateTime creationTimestamp, Subscriber subscriber) {
        this.id = id;
        this.subscriptionTypeId = subscriptionTypeId;
        this.active = active;
        this.locale = locale;
        this.lastSentTimestamp = lastSentTimestamp;
        this.lastSentSequence = lastSentSequence;
        this.lastUpdatePersonId = lastUpdatePersonId;
        this.lastUpdateTimestamp = lastUpdateTimestamp;
        this.creationTimestamp = creationTimestamp;
        this.subscriber = subscriber;
    }
}

// Subscriber类
public class Subscriber {
    // 原字段定义

    public Subscriber() {}

    public Subscriber(Long id, String email, String name) {
        this.id = id;
        this.email = email;
        this.name = name;
    }
}

步骤2：定义构造函数结果映射并修改查询 #

在EmailSubscription实体上添加构造函数映射：

@Entity
@SqlResultSetMapping(
    name = "EmailSubscriptionWithSubscriberConstructorMapping",
    classes = @ConstructorResult(
        targetClass = EmailSubscription.class,
        columns = {
            @ColumnResult(name = "es_id", type = Long.class),
            @ColumnResult(name = "es_type_id", type = Integer.class),
            @ColumnResult(name = "es_active", type = Boolean.class),
            @ColumnResult(name = "es_locale", type = String.class),
            @ColumnResult(name = "es_last_sent_timestamp", type = LocalDateTime.class),
            @ColumnResult(name = "es_last_sent_sequence", type = Integer.class),
            @ColumnResult(name = "es_last_update_person_id", type = Long.class),
            @ColumnResult(name = "es_last_update_timestamp", type = LocalDateTime.class),
            @ColumnResult(name = "es_creation_timestamp", type = LocalDateTime.class),
            @ColumnResult(name = "s_id", type = Long.class),
            @ColumnResult(name = "s_email", type = String.class),
            @ColumnResult(name = "s_name", type = String.class)
        }
    )
)
public class EmailSubscription {
    // 原代码不变
}

修改Repository方法使用该映射：

@Query(
    value = "SELECT "
        + "es.id AS es_id, es.type_id AS es_type_id, es.active AS es_active, "
        + "es.locale AS es_locale, es.last_sent_timestamp AS es_last_sent_timestamp, "
        + "es.last_sent_sequence AS es_last_sent_sequence, es.last_update_person_id AS es_last_update_person_id, "
        + "es.last_update_timestamp AS es_last_update_timestamp, es.creation_timestamp AS es_creation_timestamp, "
        + "s.id AS s_id, s.email AS s_email, s.name AS s_name "
        + "FROM subscriptions.email_subscriptions es "
        + "IGNORE INDEX (PRIMARY) "
        + "JOIN subscriptions.subscribers s ON es.subscriber_id = s.id "
        + "WHERE es.type_id = :typeId "
        + "AND es.active = :active "
        + "AND es.id > :batchStartId "
        + "ORDER BY es.id ",
    nativeQuery = true,
    resultSetMapping = "EmailSubscriptionWithSubscriberConstructorMapping"
)
List<EmailSubscription> findEmailSubscriptionsWithSubscriber(
    @Param("batchStartId") long batchStartId,
    @Param("typeId") Integer typeId,
    @Param("active") Boolean active,
    Pageable pageable);

查询会直接返回已装配好Subscriber的EmailSubscription列表，无需手动处理。

方法3：分两步批量查询（简化方案） #

如果映射配置过于繁琐，可以分两次查询：

1. 用高效的原生SQL获取EmailSubscription列表
2. 提取所有subscriber_id批量查询Subscriber，再手动关联

示例代码：

// 1. 获取EmailSubscription列表
List<EmailSubscription> subscriptions = repository.findEmailSubscriptions(...);

// 2. 提取所有关联的subscriberId
List<Long> subscriberIds = subscriptions.stream()
    .map(es -> es.getSubscriber().getId()) // 或添加getSubscriberId()方法直接获取字段值
    .collect(Collectors.toList());

// 3. 批量查询Subscriber
List<Subscriber> subscribers = subscriberRepository.findAllById(subscriberIds);

// 4. 建立映射并关联
Map<Long, Subscriber> subscriberMap = subscribers.stream()
    .collect(Collectors.toMap(Subscriber::getId, s -> s));

subscriptions.forEach(es -> es.setSubscriber(subscriberMap.get(es.getSubscriber().getId())));

这种方法避免了复杂的映射配置，两次查询总耗时仍远低于原JPQL查询，适合字段较多的场景。

额外优化建议 #

原JPQL性能差可能是索引问题，可尝试：

• 给email_subscriptions表的type_id、active、id字段建立联合索引
• 去掉原生SQL中的IGNORE INDEX (PRIMARY)，验证是否仍能保持高效，再尝试调整JPQL的查询计划

内容的提问来源于stack exchange，提问作者accursed medal 36