如何使用JParePository创建左联接？

Question

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "name")
private String name;

@Column(name = "age")
private int age;


public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public int getAge() {
    return age;
}

public void setAge(int age) {
    this.age = age;
}

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@Column(name = "name")
private String name;

@Column(name = "user_id")
private  int userId;

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public int getUserId() {
    return userId;
}

public void setUserId(int userId) {
    this.userId = userId;
}

我用@query创建了UsersRepository

public interface UsersRepository extends JpaRepository<User, Long> {
            @Query("SELECT u.id, u.name, u.age FROM users AS u LEFT JOIN post AS p ON u.id=p.user_id WHERE p.user_id IS NULL")
            List<User> findByStatus();
        }

我得到错误QuerySyntaxException:users没有映射，我可以在没有@query的情况下这样做吗？我可以用@onetomany做这个吗？如何在Jpa OneTomany上实现select？我想得到没有帖子的所有用户。

Answer 1

您没有映射关联，而是使用本机SQL查询，而不是HQL查询。

在不更改用户实体的情况下，必须使用右联接，并且应该将关联映射为单向多对一：

@Entity
class User {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

  @Column(name = "name")
  private String name;

  @Column(name = "age")
  private int age;

  ... 
}

@Entity
class Post {
  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private Long id;

  @Column(name = "name")
  private String name;  

  @ManyToOne
  @JoinColumn(name = "user_id")
  private User user;
  ... 
}

现在可以运行以下HQL查询：

@Query(value = "SELECT u FROM Post p RIGHT JOIN p.user u WHERE p IS NULL");