无法将 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.id

where 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) 时，它会抛出异常。（未能进行延迟初始化和加入所需的路径）。

这是我的模式设计

@Entity

public 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;

}

@Entity

public 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;

}

@Entity

public class Roles {

    // id omitted, authority

}

@Entity

public class Hrt02EmployeeName {

    // id, firstname, lastname omitted

}

Answer 1

由于您想将其转换为User，因此您必须构建它，而不是u.username, r2.authority. 其次，您需要获取您正在使用的内容以避免LazyInitializationException：@Query("select distinct u from Users u " +&nbsp; &nbsp; &nbsp; &nbsp; " left join fetch u.groups g "&nbsp; &nbsp; &nbsp; &nbsp; " left join fetch g.roles r " +&nbsp; &nbsp; &nbsp; &nbsp; "&nbsp; ... "&nbsp; &nbsp; &nbsp; &nbsp; " where u.username = :username")public Users findRoleByUsername(@Param("username") String username);这是开始，因为您的实体Role没有映射。您将不得不以与点所在的其他实体相同的方式编写。