实体:
我想要立即加载前10个不同的分数(通过user_id)加入到用户
原生查询可以工作,但hibernate无法将用户映射到分数(再次延迟加载),
我试图编写JPQL查询,但它生成了错误的查询
工作本机查询:
select *
from scores
join users
on scores.user_id = users.id and
user_id in (
select distinct user_id
from scores order by value desc
)
order by value desc limit 10
发布于 2018-06-09 18:50:37
您的查询选择分数,并且只联接一些用户,但并不真正联接获取Score.users
或user.score
关联。如果你想立即加载Score和关联的用户,你需要这样做:
您可以使用从MySQL 8开始支持的Window Functions
并运行以下SQL查询:
List<Score> scores = entityManager.createNativeQuery(
"select u_s_r.* " +
"from ( " +
" select *, dense_rank() OVER (ORDER BY value DESC) rank " +
" from ( " +
" select s.*, u.* " +
" from scores s " +
" join users u s.user_id = u.id " +
" order by u.id " +
" ) u_s " +
") u_s_r " +
"where u_s_r.rank <= :rank", Score.class)
.setParameter("rank", 10)
.unwrap( NativeQuery.class )
.addEntity( "s", Score.class )
.addEntity( "u", User.class )
.setResultTransformer( DistinctScoreResultTransformer.INSTANCE )
.getResultList();
DistinctScoreResultTransformer
可能如下所示:
public class DistinctScoreResultTransformer
extends BasicTransformerAdapter {
private static final DistinctScoreResultTransformer INSTANCE =
new DistinctScoreResultTransformer();
@Override
public List transformList(List list) {
Map<Serializable, Identifiable> identifiableMap =
new LinkedHashMap<>( list.size() );
for ( Object entityArray : list ) {
if ( Object[].class.isAssignableFrom(
entityArray.getClass() ) ) {
Score score = null;
User user = null;
Object[] tuples = (Object[]) entityArray;
for ( Object tuple : tuples ) {
if(tuple instanceof Score) {
score = (Score) tuple;
}
else if(tuple instanceof User) {
user = (User) tuple;
}
else {
throw new UnsupportedOperationException(
"Tuple " + tuple.getClass() + " is not supported!"
);
}
}
Objects.requireNonNull(score);
Objects.requireNonNull(user);
if ( !identifiableMap.containsKey( score.getId() ) ) {
identifiableMap.put( score.getId(), score );
score.setUsers( new ArrayList<>() );
}
score.addUser( user );
}
}
return new ArrayList<>( identifiableMap.values() );
}
}
如果您想知道这是否可以通过JPQL完成,那么您应该知道这是不可能的。但是,您不必通过JPQL运行每个查询。SQL可以为您提供帮助。
https://stackoverflow.com/questions/50773089
复制相似问题