我有两个表,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_
from
project project0_
inner join
transitionaction tacts1_
on project0_.id=tacts1_.request_no
我不明白为什么要这么做。 id与tact.request_no结合并创建错误
运算符不存在:整数=字符变化
最佳答案
所以你想通过requestNo链接到Project的所有TransitionAction
您可以通过实现
像这样在@ManyToOne
中添加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 = project.getTacts();
您不需要在查询中添加联接。一旦您由实体从Project中拉出TransactionAction,Hibernate就会解决这一问题。
编辑1:
但是为什么我的查询失败了?为什么项目的主键与
TransitionAction的request_no吗?
您的查询失败,因为在进行
@OneTOMany
关系时,您未定义@JoinColumn or @JoinTable
这是单向映射。在这种情况下,Hibernate将使用primarykey列进行映射。
并且由于主键和列的类型不同,因此会出现错误。
没有描述任何物理映射(没有@JoinColumn或
@JoinTable),使用单向一对多连接表。的
表名是所有者表名_和
对方桌名称。引用所有者的外键名称
table是所有者表_和所有者的串联
主键列名称。引用该外键的外键名称
另一端是所有者属性名称_和
另一侧的主键列名称。唯一约束添加到
引用另一边表的外键将其反映到
许多。
有关更多详细信息,请参考Official doc
@OneToMany
@JoinTable(
name="table_name",
joinColumns = @JoinColumn( name="columnname")
)
private Set<TransitionAction> tacts;