How to create LEFT JOIN with JpaRepository?

Question

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.

Have a look at [this question](https://stackoverflow.com/questions/9954590/hibernate-error-querysyntaxexception-users-is-not-mapped-from-users). Does this solve your problem? — D. D., Jun 06 '21 at 01:51
Sloved. Adding to entity User @OneToMany(fetch = FetchType.LAZY) private List freeLUsers; and change query from UserRepository @Query(value = "SELECT new kz.nixwins.model.User (l.id, l.name, l.age) FROM User AS l LEFT JOIN Post AS p ON l.id=p.userId WHERE p.userId IS NULL") — natus vincer, Jun 06 '21 at 04:10
That might work but it's not how you should use Spring Data JPA + Hibernate ORM. Check my answer. — Davide D'Alto, Jun 06 '21 at 08:02

Davide D'Alto · Answer 1 · 2021-06-07T06:52:16.620

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.

How to create LEFT JOIN with JpaRepository?

1 Answers1