代码之家 › 专栏 › 技术社区 › Arzhang Mosaffa

选择前n行,每组的第一个最大行由一列分隔(spring、hibernate、jpql)

jpql jpa hibernate spring mysql

Arzhang Mosaffa · 技术社区 · 6 年前

实体:

分数[ID,用户ID,值]
用户[id,name]

我想急切地加载前10名的分数(按用户ID)加入到用户中

本机查询可以工作,但hibernate无法将用户映射到score(再次延迟加载)。

我试图编写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

1 回复 | 直到 6 年前

Vlad Mihalcea 6 年前

轻松的生活!

现在,查询选择分数,只加入一些用户,但不真正加入获取 Score.users 或 user.score 协会。如果希望同时加载score和关联用户,则需要执行以下操作:

正如我在 this article 可以使用窗口函数。 MySQL 8 supports Windows 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运行每个查询。 Native SQL is the Magic Wand 在数据访问层工具箱中。