使用本机Query = true，在JPA股票中执行@Query的Resultset错误

Question

我正在使用：

在Web应用程序上工作

• Spring Boot
• PostgreSQL
• JPA和Hibernate。

我有一个名为role的表和另一个page。其中有一个带有ID的many-to-many表。我要获得的是与角色相对应的页面列表，因此我试图从角色的ID中检索页面列表，并执行查询以带上列表。问题是我在ResultSet中有错误，因为它告诉我它找不到带有名称page_id的列。

示例：我分别执行了查询，这使我正确的结果。

select p.url from role_page rp, page p where rp.role_id = 6 and rp.page_id = p.page_id;

输出：查询的输出

然后，我打电话给该功能以获取列表并进行检查以确保我获得结果。

public void rolesAndPages(){
    List<Page> pages = pageRepository.findPagePerRole(6);
    for (Page page: pages
         ) {
        System.out.println(page.getUrl());
    }
}

抛出上述错误：

org.postgresql.util.PSQLException: The column name page_id was not found in this ResultSet.

我的存储库：

@Repository("pageRepository")
public interface PageRepository extends JpaRepository<Page, Long> {
    @Query(value = "select p.url from role_page rp, page p where rp.role_id = ?1 and rp.page_id = p.page_id", nativeQuery = true)
    List<Page> findPagePerRole(Integer id);
} 

角色java

@Entity
@Table(name = "app_role")
public class Role {
@Id
@SequenceGenerator(name="pk_sequence",sequenceName="messagesounds_id_seq", allocationSize=1)
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="pk_sequence")
@Column(name="role_id")
private int id;
@Column(name="authority")
@NotEmpty(message = "*Please provide a name")
private String authority;
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "role_page", joinColumns = @JoinColumn(name = "role_id"), inverseJoinColumns = @JoinColumn(name = "page_id"))
private Set<Page> pages;
public void setPages(Set<Page> pages) {
    this.pages = pages;
}
public Set<Page> getPages() {
    return pages;
}
public Role() {}
public Role(String authority) {
    this.authority = authority;
}
public Role(String authority, Set<Page> pages){
    this.authority = authority;
    this.pages = pages;
}
public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}
public String getAuthority() {
    return authority;
}
public void setAuthority(String authority) {
    this.authority = authority;
}
}

page.java

@Entity
@Table(name = "page")
public class Page {
@Id
@SequenceGenerator(name = "pk_sequence", sequenceName = "messagesounds_id_seq", allocationSize = 1)
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "pk_sequence")
@Column(name = "page_id")
private int id;
@Column(name = "name_page")
@NotEmpty(message = "*Please provide a name")
private String name;
@Column(name = "url")
@NotEmpty(message = "*Please provide an url")
private String url;
@Column(name = "description")
@NotEmpty(message = "*Please provide a description")
private String description;
@ManyToMany(mappedBy = "pages")
private Set<Role> roles;

public Page() {
}
public Page(String name_page) {
    this.name = name_page;
}
public Page(String name_page, Set<Role> roles) {
    this.name = name_page;
    this.roles = roles;
}
public int getId() {
    return id;
}
public void setId(int id) {
    this.id = id;
}
public String getName() {
    return name;
}
public void setName(String name) {
    this.name = name;
}
public String getUrl() {
    return url;
}
public void setUrl(String url) {
    this.url = url;
}
public String getDescription() {
    return description;
}
public void setDescription(String description) {
    this.description = description;
}
public Set<Role> getRoles() {
    return roles;
}
public void setRoles(Set<Role> roles) {
    this.roles = roles;
}
}

Answer 1

错误非常明确，您需要选择page_id列才能使其正常工作，而不仅仅是p.url。

尝试以下页面的每一列：

@Query(value = "select p.* from role_page rp, page p where rp.role_id = ?1 and rp.page_id = p.page_id", nativeQuery = true)

或，这是为了检索表的每一列页面和cole_page：

@Query(value = "from role_page rp, page p where rp.role_id = ?1 and rp.page_id = p.page_id", nativeQuery = true)

两个查询都应起作用。

Answer 2

您是否尝试过：

@Query(value = "select p.url from role_page rp, page p where rp.id = ?1 and rp.id = p.id")