JPQL和JPA中是否有本地查询中'GROUP_CONCAT'的替代方法？

Question

我正在尝试使用JPA的JPQL获取一个个人资料的资料菜单。我的“Profile”和“ProfileMenus”实体有多对一的关系。

我已经尝试查看这些答案，但没有找到任何可行的解决方案。 如何在Spring Boot应用程序中添加非标准化的SQL函数？ 使用JPA和Hibernate注册SQL函数

https://vladmihalcea.com/hibernate-sql-function-jpql-criteria-api-query/

我也通过这个链接了解了一下，似乎遇到了和我一样的问题， 如何在Spring Boot应用程序中手动注册非标准化SQL函数？ 当使用本地查询时，我可以使用以下查询获取我的数据：

 SELECT
 GROUP_CONCAT(pm.user_menu_id SEPARATOR ',')
 AS profile_menu_ids,
 p.description
 FROM profile p
 LEFT JOIN profile_menu pm ON p.id = pm.profile_id
 WHERE
 p.id =:profileId
 AND
 pm.status = 'Y'
 GROUP BY p.id

上述查询提供给我以下数据：

profile_menu_ids	description
4,5	管理员资料

是否有任何方法或JPA中的jpql替代方案可以获得上述结果？

- Sabu Shakya

---

“GROUP_CONCAT”是MySQL（和SQLite）特定的函数，因此我怀疑JPQL会支持它。也许你需要在Java端处理它，可以使用流(Stream)方式实现。 - Tim Biegeleisen

https://dev59.com/1FrUa4cB1Zd3GeqPghqj - Tirex

我实际上想使用JPQL来处理它。在JPQL中没有替代方法吗？@TimBiegeleisen - Sabu Shakya

不，我认为没有，主要是因为每个数据库供应商都有不同的处理组合的方式（可能有一种ANSI SQL的方法可以做到，但我猜想很少/没有供应商实际支持它）。 - Tim Biegeleisen

Answer 1

您可以考虑使用FluentJPA，它支持任何自定义函数：

public ProfileMenuGroup getMenuIdsByProfile(int profileId) {
    FluentQuery query = FluentJPA.SQL((Profile p,
                                       ProfileMenu pm) -> {
        String menuIds = alias(GROUP_CONCAT(pm.getUserMenuId(), ","),
                                            ProfileMenuGroup::getProfileMenuIds);
        String description = alias(p.getDescription(), ProfileMenuGroup::getDescription);

        SELECT(menuIds, description);
        FROM(p).LEFT_JOIN(pm).ON(p == pm.getProfile());
        WHERE(p.getId() == profileId && pm.getStatus() == "Y");
        GROUP(BY(p.getId()));
    });
    return query.createQuery(em, ProfileMenuGroup.class).getSingleResult();
}

查询生成以下 SQL（profileId 自动绑定）：

SELECT GROUP_CONCAT(t1.user_menu_id SEPARATOR ',') AS profile_menu_ids,
                                    t0.description AS description 
FROM profile t0  LEFT JOIN profile_menu t1  ON (t0.id = t1.profile_id) 
WHERE ((t0.id = ?1) AND (t1.status = 'Y')) 
GROUP BY  t0.id

假设有以下类型声明：

@Entity
@Data // lombok
@Table(name = "profile")
public class Profile {
    @Id
    private int id;

    private String description;
}

@Entity
@Data // lombok
@Table(name = "profile_menu")
public class ProfileMenu {
    @Id
    private int id;

    @ManyToOne
    @JoinColumn(name = "profile_id")
    private Profile profile;

    private int userMenuId;

    private String status;
}

@Tuple
@Data // lombok
public class ProfileMenuGroup {

    private String profileMenuIds;

    private String description;
}

- Konstantin Triger