尽管您获得了有效的结果,但 SQL 查询会提取所有数据,并且效率不如预期。
因此,您有两种选择。
修复了两个 SQL 查询的问题,这两个查询可以在读写模式下获取实体
解决此问题的最简单方法是执行两个查询:
.第一个查询将提取与提供的筛选条件匹配的根实体标识符。.第二个查询将使用以前提取的根实体标识符来获取父实体和子实体。
这种方法非常容易实现,如下所示:
List<Long> postIds = entityManager
.createQuery(
"select p.id " +
"from Post p " +
"where p.title like :titlePattern " +
"order by p.createdOn", Long.class)
.setParameter(
"titlePattern",
"High-Performance Java Persistence %"
)
.setMaxResults(5)
.getResultList();
List<Post> posts = entityManager
.createQuery(
"select distinct p " +
"from Post p " +
"left join fetch p.comments " +
"where p.id in (:postIds) " +
"order by p.createdOn", Post.class)
.setParameter("postIds", postIds)
.setHint(
"hibernate.query.passDistinctThrough",
false
)
.getResultList();
修复了一个只能在只读模式下提取实体的 SQL 查询的问题
第二种方法是对与我们的筛选条件匹配的父实体和子实体的结果集使用SDENSE_RANK,并仅限制前 N 个帖子条目的输出。
SQL 查询可以如下所示:
@NamedNativeQuery(
name = "PostWithCommentByRank",
query =
"SELECT * " +
"FROM ( " +
" SELECT *, dense_rank() OVER (ORDER BY \"p.created_on\", \"p.id\") rank " +
" FROM ( " +
" SELECT p.id AS \"p.id\", " +
" p.created_on AS \"p.created_on\", " +
" p.title AS \"p.title\", " +
" pc.id as \"pc.id\", " +
" pc.created_on AS \"pc.created_on\", " +
" pc.review AS \"pc.review\", " +
" pc.post_id AS \"pc.post_id\" " +
" FROM post p " +
" LEFT JOIN post_comment pc ON p.id = pc.post_id " +
" WHERE p.title LIKE :titlePattern " +
" ORDER BY p.created_on " +
" ) p_pc " +
") p_pc_r " +
"WHERE p_pc_r.rank <= :rank ",
resultSetMapping = "PostWithCommentByRankMapping"
)
@SqlResultSetMapping(
name = "PostWithCommentByRankMapping",
entities = {
@EntityResult(
entityClass = Post.class,
fields = {
@FieldResult(name = "id", column = "p.id"),
@FieldResult(name = "createdOn", column = "p.created_on"),
@FieldResult(name = "title", column = "p.title"),
}
),
@EntityResult(
entityClass = PostComment.class,
fields = {
@FieldResult(name = "id", column = "pc.id"),
@FieldResult(name = "createdOn", column = "pc.created_on"),
@FieldResult(name = "review", column = "pc.review"),
@FieldResult(name = "post", column = "pc.post_id"),
}
)
}
)
将提取与提供的标题匹配的所有 Post 实体及其关联的子实体。窗口函数用于为每个帖子和联接记录分配排名,以便我们以后可以仅筛选我们感兴趣的帖子记录的数量。@NamedNativeQuery
PostComment
DENSE_RANK
PostComment
提供 SQL 级列别名和需要填充的 JPA 实体属性之间的映射。SqlResultSetMapping
现在,我们可以像这样执行:PostWithCommentByRank
@NamedNativeQuery
List<Post> posts = entityManager
.createNamedQuery("PostWithCommentByRank")
.setParameter(
"titlePattern",
"High-Performance Java Persistence %"
)
.setParameter(
"rank",
5
)
.unwrap(NativeQuery.class)
.setResultTransformer(
new DistinctPostResultTransformer(entityManager)
)
.getResultList();
现在,默认情况下,像这样的本机 SQL 查询将在同一 JDBC 行中获取 Post 和 ,因此我们最终会得到一个包含这两个实体的查询。PostWithCommentByRank
PostComment
Object[]
但是,我们希望将表格数组转换为父子实体的树,因此,我们需要使用 Hibernate ResultTransformer
。Object[]
外观如下:DistinctPostResultTransformer
public class DistinctPostResultTransformer
extends BasicTransformerAdapter {
private final EntityManager entityManager;
public DistinctPostResultTransformer(
EntityManager entityManager) {
this.entityManager = entityManager;
}
@Override
public List transformList(
List list) {
Map<Serializable, Identifiable> identifiableMap =
new LinkedHashMap<>(list.size());
for (Object entityArray : list) {
if (Object[].class.isAssignableFrom(entityArray.getClass())) {
Post post = null;
PostComment comment = null;
Object[] tuples = (Object[]) entityArray;
for (Object tuple : tuples) {
if(tuple instanceof Identifiable) {
entityManager.detach(tuple);
if (tuple instanceof Post) {
post = (Post) tuple;
}
else if (tuple instanceof PostComment) {
comment = (PostComment) tuple;
}
else {
throw new UnsupportedOperationException(
"Tuple " + tuple.getClass() + " is not supported!"
);
}
}
}
if (post != null) {
if (!identifiableMap.containsKey(post.getId())) {
identifiableMap.put(post.getId(), post);
post.setComments(new ArrayList<>());
}
if (comment != null) {
post.addComment(comment);
}
}
}
}
return new ArrayList<>(identifiableMap.values());
}
}
DistinctPostResultTransformer
必须分离要获取的实体,因为我们正在覆盖子集合,并且我们不希望将其作为实体状态转换进行传播:
post.setComments(new ArrayList<>());