How to convert a SQL query to Spring JPA query

Question

I have a SQL query like this: "Select UIProfileID from UserTable where UPPER(UserID) = UPPER('?1')".

I want to convert it to Spring JPA. I want to write getUIProfileId() and return Integer. But I don't know how to implement. Because User table doesn't have UIProfileId column that it was joined from UIProfileTable table. Please help me solve it. Currently, I have tables:

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();

}

Ajit Soman · Accepted Answer · 2017-08-12 12:33:40Z

1

You can try this:

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

Here User is the domain class name and u is the reference of User. with u we will access User's field NOT the column name which are specified with @Column or @JoinColumn Ex : @JoinColumn(name = "UIProfileID", nullable = false).

edited Aug 12, 2017 at 12:33

answered Aug 12, 2017 at 11:32

Ajit Soman

4,0693 gold badges26 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Thuong Tran Over a year ago

Hi, Ajit Soman. It will be returning UIProfile object cannot be cast to java.lang.Integer

Thuong Tran Over a year ago

@ Ajit Soman. I want to get Id of UIProfileTable @Column(name = "UIProfileID", length = 11, nullable = false) private Integer id;.

Thuong Tran Over a year ago

@ Ajit Soman I think it will return a UIProfile why it arises List here.

Ajit Soman Over a year ago

Ok i have updated my answer . i have'nt noticed @ManyToOne Mapping

Thuong Tran Over a year ago

Hi, Ajit Soman UIProfile is the entity class. You were SELECT u.uiProfile it will return UIProfile. My problem is how to we get Id of UIProfileID column of UIProfileTable table from User entity(UserTable table)

|

Collectives™ on Stack Overflow

How to convert a SQL query to Spring JPA query

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related