Hibernate 6原生查询实现类似Join Fetch关联实体加载问题

问题背景 #

在Hibernate 5中，执行以下原生查询可同时获取Instrument及其关联的Exchange实体：

@Query(nativeQuery = true, value = """
select i.*, e.* from instrument i
   join exchange e on e.id = i.exchange_id
""")
List<Instrument> findAllInstrumentsHibernate5();

迁移至Hibernate 6后，执行相同查询抛出异常：

org.springframework.orm.jpa.JpaSystemException: Encountered a duplicated sql alias [id] during auto-discovery of a native-sql query

核心原因是Hibernate 6调整了原生查询的结果映射逻辑，不仅会检测重复列别名（两个实体均含id列），且默认不再按列名匹配映射。尝试指定具体列后仍无法加载关联Exchange，以下是无需切换JPQL的解决办法。

实体类定义：

@Entity
public class Instrument {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
    @GenericGenerator(name = "native")
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY, optional = false)
    @JoinColumn(name = "EXCHANGE_ID", referencedColumnName = "ID",
        foreignKey = @ForeignKey(name = "fk__instrument__exchange_id__exchange"))
    private Exchange exchange;
}

@Entity
public class Exchange {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "native")
    @GenericGenerator(name = "native")
    private Long id;

    private String name;
}

解决办法 #

1. 自定义结果集映射（@SqlResultSetMapping） #

为重复列指定唯一别名，通过@SqlResultSetMapping明确实体与列的映射关系，彻底避免别名冲突。

首先在Instrument实体上添加映射定义：

@Entity
@SqlResultSetMapping(
    name = "InstrumentWithExchangeMapping",
    entities = {
        @EntityResult(
            entityClass = Instrument.class,
            fields = {
                @FieldResult(name = "id", column = "i_id"),
                @FieldResult(name = "exchange", column = "exchange_id")
            }
        ),
        @EntityResult(
            entityClass = Exchange.class,
            fields = {
                @FieldResult(name = "id", column = "e_id"),
                @FieldResult(name = "name", column = "e_name")
            }
        )
    }
)
public class Instrument {
    // 原有实体代码
}

修改原生查询，指定列别名并引用映射：

@Query(nativeQuery = true, value = """
select i.id as i_id, i.exchange_id,
       e.id as e_id, e.name as e_name
from instrument i
join exchange e on e.id = i.exchange_id
""", resultSetMapping = "InstrumentWithExchangeMapping")
List<Instrument> findAllInstrumentsHibernate6();

2. 结合抓取配置文件（@FetchProfile） #

定义抓取配置强制加载关联实体，同时为查询列指定不冲突的别名。

在Instrument实体上添加抓取配置：

@Entity
@FetchProfile(
    name = "instrument-with-exchange",
    fetchOverrides = {
        @FetchProfile.FetchOverride(
            entity = Instrument.class,
            association = "exchange",
            mode = FetchMode.JOIN
        )
    }
)
public class Instrument {
    // 原有实体代码
}

修改查询并激活抓取配置：

@Query(nativeQuery = true, value = """
select i.*, e.id as exchange_id, e.name as exchange_name
from instrument i
join exchange e on e.id = i.exchange_id
""")
@FetchProfile(name = "instrument-with-exchange")
List<Instrument> findAllInstrumentsHibernate6();

3. 自定义结果转换器（ResultTransformer） #

实现Hibernate的ResultTransformer接口，手动控制JDBC结果集到实体的映射逻辑，完全规避别名问题。

创建转换器类：

public class InstrumentExchangeTransformer implements ResultTransformer {

    @Override
    public Object transformTuple(Object[] tuple, String[] aliases) {
        Instrument instrument = new Instrument();
        instrument.setId((Long) tuple[0]);
        
        Exchange exchange = new Exchange();
        exchange.setId((Long) tuple[1]);
        exchange.setName((String) tuple[2]);
        
        instrument.setExchange(exchange);
        return instrument;
    }

    @Override
    public List transformList(List collection) {
        return collection;
    }
}

在查询中使用转换器（需通过EntityManager获取Hibernate Query对象）：

// 在Repository实现类中编写
public List<Instrument> findAllInstrumentsHibernate6() {
    String sql = """
        select i.id, e.id, e.name
        from instrument i
        join exchange e on e.id = i.exchange_id
        """;
    return entityManager.createNativeQuery(sql)
        .unwrap(org.hibernate.query.Query.class)
        .setResultTransformer(new InstrumentExchangeTransformer())
        .getResultList();
}

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