Spring Data JPA 表连接创建操作符不存在错误

Question

我有两张桌子，Projects和TransitionAction。双方Projects并TransitionAction有一列request_no，其用于执行它们之间的连接。实体类如下：-

Project.java

@Entity

public class Project implements Serializable {

private static final long serialVersionUID = 1L;

@Id 

@SequenceGenerator(schema = "public", name="project_id_seq_gen",sequenceName="project_id_seq",initialValue=1,allocationSize=1)

@GeneratedValue(strategy= GenerationType.SEQUENCE,generator="project_id_seq_gen")   

private Integer id;

@Column(name = "request_no")

private String request_no;

@Column(name = "title")

private String title;

@Column(name = "department")

private String department;

@OneToMany(cascade = CascadeType.ALL, mappedBy = "requestNo")

private Set<TransitionAction> tacts;

@ManyToOne

@JoinColumn(name = "status_id")

private Status status;

@ManyToOne

@JoinColumn(name = "level_id")

private ProjectLevel level;

TransitionAction.java

@Entity

@Table(name = "transitionaction")

public class TransitionAction implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id

    @GeneratedValue(strategy = GenerationType.AUTO)

    @Column(name = "request_no")

    private String request_no;

    @Column(name = "actionDate")

    private Date actionDate;

用于检索项目列表的代码如下：-

public interface UserInfoRepository extends JpaRepository<UserInfo, Long> {

UserInfo findByUserName(String userName);

@Query("SELECT project FROM Project project Join project.tacts pta where project.request_no= pta.request_no and project.status.id=1")

List<Project> getAllUserProjects();

}

我得到无法提取 ResultSet 错误。当我检查控制台时，我发现正在生成以下查询：

select

        distinct project0_.id as id1_1_,

        project0_.department as departme2_1_,

        project0_.level_id as level_id6_1_,

        project0_.user_nodal_officer as user_nod3_1_,

        project0_.request_no as request_4_1_,

        project0_.status_id as status_i7_1_,

        project0_.title as title5_1_ 

我不明白为什么项目。id 与 tact.request_no 结合并创建错误

运算符不存在：整数 = 字符变化

Answer 1

因此，您希望通过 requestNo 将所有 TransitionAction 链接到 Project您可以通过像这样添加@ManyToOneMapping来实现这TransactionAction一点@ManyToOne(fetch = FetchType.LAZY)private Project project;现在您需要像这样修改您的查询@Query("SELECT project FROM Project project where project.status.id=1")List<Project> getAllUserProjects();获取TransactionAction给定项目的所有信息Set<TransitionAction> allTatcts =&nbsp; project.getTacts();您不需要在查询中添加连接。一旦您通过实体从 Project 中提取 TransactionAction ，Hibernate 就会处理这个问题。编辑1：但是为什么我的查询失败了？为什么项目的主键加入了 TransitionAction 的 request_no ？您的查询失败，因为在执行@OneTOMany关系时 yodidn't 定义@JoinColumn or @JoinTable哪个是单向映射。在这种情况下，Hibernate 将使用主键列进行映射。并且由于主键和列的类型不同，因此错误。没有描述任何物理映射（没有@JoinColumn 或@JoinTable），使用单向一对多连接表。表名是所有者表名、_ 和另一侧表名的串联。引用所有者表的外键名称是所有者表、_ 和所有者主键列名称的串联。引用另一侧的外键名称是所有者属性名称 _ 和另一侧主键列名称的串联。将唯一约束添加到引用另一侧表的外键以反映一对多。更多细节请参考官方文档@OneToMany@JoinTable(&nbsp; &nbsp; &nbsp; &nbsp; name="table_name",&nbsp; &nbsp; &nbsp; &nbsp; joinColumns = @JoinColumn( name="columnname"))private Set<TransitionAction> tacts;

Answer 2

这是因为您在 TransactionAction 类中的 request_no 上有 @Id 并且当您加入两个类时，它们会加入主键。您可以改用交叉连接。SELECT&nbsp;project&nbsp;FROM&nbsp;Project&nbsp;project,&nbsp;TransactionAction&nbsp;pta&nbsp;where&nbsp;project.request_no=&nbsp;pta.request_no&nbsp;and&nbsp;project.status.id=1或者你可以有双向映射。在您的 TransactionAction 实体类中添加以下内容。@ManyToOne private&nbsp;Project&nbsp;Project;然后您的查询将如下所示。select&nbsp;pta.project&nbsp;from&nbsp;TransactionAction&nbsp;pta&nbsp;where&nbsp;pta.request_no=pta.project.request_no&nbsp;and&nbsp;pta.project.status.id=1