java - 如何将SQL查询转换为Spring JPA查询

我有这样的SQL查询：
"Select UIProfileID from UserTable where UPPER(UserID) = UPPER('?1')"。

我想将其转换为Spring JPA。
我想写getUIProfileId()并返回Integer。但是我不知道如何实现。因为User表没有UIProfileId列，所以它是从UIProfileTable表联接的。请帮我解决。
目前，我有表格：

User.java

@Entity
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@Builder
@Table(name = "UserTable")
public class User {

  @Column(name = "UserID", length = 32, nullable = false)
  @Id
  private String name;

  @ManyToOne
  @JoinColumn(name = "DomainID", nullable = false)
  private Domain domain;

  @Column(name = "Password", length = 32, nullable = false)
  private String password;

  @ManyToOne
  @JoinColumn(name = "UIProfileID", nullable = false)
  private UIProfile uiProfile;

  @Column(name = "ResPerpage", nullable = false)
  private Integer resperpage;

  @Column(name = "DefaultTab")
  private Integer defaulttab;

  @ManyToOne
  @JoinColumn(name = "AdminProfile")
  private AdminProfiles adminProfile;

  @Column(name = "LanguageId")
  private Integer languageId;
}

UIProfile.java

@Entity
@Getter
@Setter
@Table(name = "UIProfileTable")
public class UIProfile implements Serializable {

  @Id
  @Column(name = "UIProfileID", length = 11, nullable = false)
  private Integer id;

  @Column(name = "UIProfileName", length = 32, nullable = false)
  private String name;

  @OneToMany(mappedBy = "id.uiProfile")
  private List<UIProfileTopLevel> topLevels;
}

UserRepository.java

public interface UserRepository extends Repository<User, String> {

  Optional<User> findOne(String name);

  @Query("Select UIProfileID from User where UPPER(UserID) = UPPER('admin')")
  Integer getUIProfileId();

}

最佳答案

您可以尝试以下方法：

@Query("SELECT u.uiProfile.id from User u where UPPER(u.name)=UPPER('admin')")
Integer getUIProfileId();

这里User是域名类名称，而u是User的引用。使用u，我们将访问用户字段而不是使用@Column or @JoinColumn Ex：@JoinColumn(name = "UIProfileID", nullable = false)指定的列名。