无法将 MS Sql 查询转换为 Hibernate @Query

Question

我有以下完美运行的 MS SQL 查询。select u.id, u.username, r2.authority, em.hrt02_first_name, em.hrt02_last_name from users as u    inner join group_members gm        on u.id = gm.user_id    inner join groups g        on gm.group_id = g.id    inner join group_authorities ga        on ga.group_id = g.id    inner join roles r2        on ga.role_id = r2.id    inner join hrt02_employee_name em        on em.id = u.idwhere u.username = 'john'输出如下+----+----------+------------+------------------+-----------------+| id | username | authority  | hrt02_first_name | hrt02_last_name |+----+----------+------------+------------------+-----------------+| 1  | john     | ROLE_ADMIN | fname            | lname           |+----+----------+------------+------------------+-----------------+| 1  | john     | ROLE_USER  | fname            | lname           |+----+----------+------------+------------------+-----------------+但是当我试图将它转换为 Hibernate Query 或 `@Query(..., nativeQuery=true) 时，它会抛出异常。（未能进行延迟初始化和加入所需的路径）。这是我的模式设计@Entitypublic class Users {    // id, username omitted    @ManyToMany      @JoinTable(name="group_members", joinColumns=@JoinColumn(name="user_id", referencedColumnName="id"), inverseJoinColumns=@JoinColumn(name="group_id", referencedColumnName="id"))    private List<Groups> groups;}@Entitypublic class Groups {    // id omitted    @ManyToMany      @JoinTable(name="group_authorities", joinColumns=@JoinColumn(name="group_id", referencedColumnName="id"), inverseJoinColumns=@JoinColumn(name="role_id", referencedColumnName="id"))    private List<Roles> roles;}@Entitypublic class Roles {    // id omitted, authority}@Entitypublic class Hrt02EmployeeName {    // id, firstname, lastname omitted}

Answer 1

由于您想将其转换为User，因此您必须构建它，而不是u.username, r2.authority. 其次，您需要获取您正在使用的内容以避免LazyInitializationException：

@Query("select distinct u from Users u " +

        " left join fetch u.groups g "

        " left join fetch g.roles r " +

        "  ... "

        " where u.username = :username")

public Users findRoleByUsername(@Param("username") String username);

这是开始，因为您的实体Role没有映射。您将不得不以与点所在的其他实体相同的方式编写。