由hibernate更改的本机查询

Question

我正在尝试在我的存储库中执行此方法:

@Query(value = "SELECT a.ID as 'ID', a.STATUS as 'STATUS', a.SOURCE as 'SOURCE', a.EXTERNAL_REFERENCE_ID as 'EXTERNAL_REFERENCE_ID', s.ID as 'STREET_ID', l.ID as 'LOCALITY_ID', c.ID as 'COUNTRY_ID', p.ID as 'POSTCODE_ID', a.INSERTED_TIMESTAMP as 'INSERTED_TIMESTAMP', a.UPDATED_TIMESTAMP as 'UPDATED_TIMESTAMP', a.DELETED_TIMESTAMP as 'DELETED_TIMESTAMP'n" +
"FROM address_management_svc.subaddress san" +
"INNER JOIN address_management_svc.address an" +
"ON (sa.ADDRESS_ID = a.ID)n" +
"INNER JOIN street sn" +
"ON (s.id = a.STREET_ID)n" +
"INNER JOIN locality ln" +
"ON (l.id = a.LOCALITY_ID)n" +
"INNER JOIN country cn" +
"ON (c.id = a.COUNTRY_ID)n" +
"INNER JOIN post_code pn" +
"ON (p.id = a.POSTCODE_ID)", nativeQuery = true)
List<AddressEntity> getAll();

我打开了jpa日志记录，由于某种原因，我得到了所有这些(下面是一个示例，因为实际上我得到了这样的所有记录，这是数十万)

select
streetenti0_.ID as id1_4_0_,
streetenti0_.DELETED_TIMESTAMP as deleted_2_4_0_,
streetenti0_.EXTERNAL_REFERENCE_ID as external3_4_0_,
streetenti0_.INSERTED_TIMESTAMP as inserted4_4_0_,
streetenti0_.LOCALITY_ID as locality9_4_0_,
streetenti0_.NAME as name5_4_0_,
streetenti0_.SOURCE as source6_4_0_,
streetenti0_.STATUS as status7_4_0_,
streetenti0_.UPDATED_TIMESTAMP as updated_8_4_0_,
localityen1_.ID as id1_2_1_,
localityen1_.COUNTRY_ID as country_9_2_1_,
localityen1_.DELETED_TIMESTAMP as deleted_2_2_1_,
localityen1_.EXTERNAL_REFERENCE_ID as external3_2_1_,
localityen1_.INSERTED_TIMESTAMP as inserted4_2_1_,
localityen1_.NAME as name5_2_1_,
localityen1_.SOURCE as source6_2_1_,
localityen1_.STATUS as status7_2_1_,
localityen1_.UPDATED_TIMESTAMP as updated_8_2_1_,
countryent2_.ID as id1_1_2_,
countryent2_.DELETED_TIMESTAMP as deleted_2_1_2_,
countryent2_.INSERTED_TIMESTAMP as inserted3_1_2_,
countryent2_.NAME as name4_1_2_,
countryent2_.UPDATED_TIMESTAMP as updated_5_1_2_ 
from
STREET streetenti0_ 
left outer join
LOCALITY localityen1_ 
on streetenti0_.LOCALITY_ID=localityen1_.ID 
left outer join
COUNTRY countryent2_ 
on localityen1_.COUNTRY_ID=countryent2_.ID 
where
streetenti0_.ID=?
2021-09-06 17:06:19.012 address-management-service TRACE natty-lappy-work [http-nio-8080-exec-1] org.hibernate.type.descriptor.sql.BasicBinder - binding parameter [1] as [INTEGER] - [179779]
2021-09-06 17:06:19.031 address-management-service DEBUG natty-lappy-work [http-nio-8080-exec-1] org.hibernate.engine.jdbc.spi.SqlStatementLogger - 
select
localityen0_.ID as id1_2_0_,
localityen0_.COUNTRY_ID as country_9_2_0_,
localityen0_.DELETED_TIMESTAMP as deleted_2_2_0_,
localityen0_.EXTERNAL_REFERENCE_ID as external3_2_0_,
localityen0_.INSERTED_TIMESTAMP as inserted4_2_0_,
localityen0_.NAME as name5_2_0_,
localityen0_.SOURCE as source6_2_0_,
localityen0_.STATUS as status7_2_0_,
localityen0_.UPDATED_TIMESTAMP as updated_8_2_0_,
countryent1_.ID as id1_1_1_,
countryent1_.DELETED_TIMESTAMP as deleted_2_1_1_,
countryent1_.INSERTED_TIMESTAMP as inserted3_1_1_,
countryent1_.NAME as name4_1_1_,
countryent1_.UPDATED_TIMESTAMP as updated_5_1_1_ 
from
LOCALITY localityen0_ 
left outer join
COUNTRY countryent1_ 
on localityen0_.COUNTRY_ID=countryent1_.ID 
where
localityen0_.ID=?

我想让它执行我给它的语句，不翻译任何东西。我也把它设置为nativeQuery = true，但这似乎没有帮助。知道我能做什么吗?

Answer 1

我认为这是Blaze-Persistence实体视图的一个完美用例。

我创建的库允许JPA模型和自定义接口或抽象类定义模型之间的轻松映射，类似于类固醇上的Spring Data projection。其思想是，您以您喜欢的方式定义您的目标结构(域模型)，并通过JPQL表达式将属性(getter)映射到实体模型。

您的用例的DTO模型可以像下面这样使用Blaze-Persistence Entity-Views:

@EntityView(AddressEntity.class)
public interface AddressDto {
@IdMapping
Long getId();
String getStatus();
String getSource();
String getExternalReferenceId();
@Mapping("street.id")
Long getStreetId();
@Mapping("locality.id")
Long getLocalityId();
@Mapping("country.id")
Long getCountryId();
@Mapping("postCode.id")
Long getPostcodeId();
Instant getInsertedTimestamp();
Instant getUpdatedTimestamp();
Instant getDeletedTimestamp();
}

查询是将实体视图应用于查询的问题，最简单的就是通过id进行查询。

AddressDto a = entityViewManager.find(entityManager, AddressDto.class, id);

Spring Data集成允许您几乎像Spring Data projection一样使用它:https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<AddressDto> findAll(Pageable pageable);

最好的部分是，它只会获取实际需要的状态!

在你的情况下，它甚至会避免所有的连接，只选择外键，因为它们在address表中。