我正试图从表SubContactModel中获取多个列的不同值,在该表中我将使用a值进行搜索,在本例中就是电子邮件。
虽然我能够获得不同的值,但我现在试图获得的是获取使用@ManyToOne映射的字段。到目前为止,我只能获得外键(FK(,但我想从关系中获取另一个值。例如,我想从CountryPhoneCodeModel中获得值countryphonecode。我已经包含了我的存储库层,以展示我现在是如何获得不同的值的。
子联系人模型:
@Data
@Entity
public class SubContactModel implements Serializable {
@Id
@Column(name = "SUBCONTACTID", updatable = false, unique = true, nullable = false)
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long subcontactID;
@ManyToOne()
@JoinColumn(name="PRSNTITLEID", nullable=true, updatable=true)
private PersonTitleModel personTitleID; // Person Title
@ManyToOne()
@JoinColumn(name = "countryPhonecode", nullable = true, updatable = true)
private CountryPhoneCodeModel countryPhoneCodeModel; // Country Code
@Column(name = "name", nullable = true, length = 50)
private String name;
@Column(name = "designation", nullable = true, length = 50)
private String designation;
@Column(name = "telnumber", nullable = true, length = 50)
private String telnumber;
@Column(name = "email", nullable = true, length = 50)
private String email;
@ManyToOne()
@JoinColumn(name="PERSONID", nullable=true, updatable=true)
private PersonModel personID;
}
CountryPhoneCodeModel:
public class CountryPhoneCodeModel implements Serializable {
@Id
@Column(name="CPHONECODEID",updatable = false,unique = true,nullable = false )
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long countryPhoneCodeID;
@Column(name="CPHONECODE",updatable=true,nullable=false,length=50)
private String countryPhoneCode;
@Column(name="COUNTRYNAME",updatable=true,nullable=false,length=50)
private String countryName;
@Column(name="DIALCODE",updatable=true,nullable=false,length=50)
private String dialCode;
}
存储库层:
@Repository
public interface SubContactRepo extends JpaRepository<SubContactModel, Long> {
@Query(value="SELECT DISTINCT u.designation,u.name,u.telnumber,u.countryphonecode,u.prsntitleid FROM subcontactmodel u WHERE u.email = :email", nativeQuery = true)
List <Object> findDistinctResult (String email);
}
我找到了一个解决方案:
我删除了nativeQuery=true,并更改了@Query中的字段名称,以匹配实体中的名称,而不是数据库中的表列名。
@Query(value="SELECT DISTINCT u.designation,u.name,u.telnumber, u.countryPhoneCodeModel.countryPhoneCode ,u.personTitleID.personTitleName FROM SubContactModel u WHERE u.email= :email")
List <Object> findDistinctResult (String email);
现在它按照我想要的方式工作!