CODEWITHSUNDEEP
spring-boothibernatespring-data-jpa
natus vincer
natus vincer

Reputation: 125

How to create LEFT JOIN with JpaRepository?

I have two tables Users and Post.

Entity User looks like this:

@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;
}

Entity Post looks like this:

@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;
}

I created UsersRepository with @Query

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

I get error QuerySyntaxException: users is not mapped Can I do this without @Query? Can i do this with @OneToMany? How can I implement select on Jpa OneToMany? I want to get all users who don't have posts.

Upvotes: 0

Views: 9014

Answers (1)

Davide D&#39;Alto
Davide D&#39;Alto

Reputation: 8236

You are not mapping the association and you are using a native SQL query, instead of a HQL one.

Without changing your User entity, you have to use a right join and you should map the association as a uni-directional many-to-one:

@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;
  ... 
}

Now you can run the following HQL query:

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

Check the Hibernate ORM documentation for examples of mapping associations and HQL/JPQL queries.

Upvotes: 2

Related Questions