spring data jpa tables joining by One to one Association

Question

I am trying to join two tables and display its result by using spring data JPA one to one association. Below I am Adding my Model and repository classes, My first model class Users is ,

@Entity
@Table(name = "users")

public class Users implements Serializable{

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

@Column(name = "username")
public String username;

@Column(name = "password")
public String password;

@Column(name = "privid")
public Integer privid;

@OneToOne()
@JoinColumn(name="join_privillage")
private Privillages priviJoin;


public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public String getUsername() {
    return username;
}

public void setUsername(String username) {
    this.username = username;
}

public String getPassword() {
    return password;
}

public void setPassword(String password) {
    this.password = password;
}

public Integer getPrivid() {
    return privid;
}

public void setPrivid(Integer privid) {
    this.privid = privid;
}

public Privillages getPriviJoin() {
    return priviJoin;
}

public void setPriviJoin(Privillages priviJoin) {
    this.priviJoin = priviJoin;
}



protected Users() {
}

public Users(String username, String password, Integer privid) {
    this.username = username;
    this.password = password;
    this.privid = privid;
}
@Override
public String toString() {
    return String.format("Users[id=%d, username='%s', password='%s']", id, 
username, password);
}
}

And my next Model class that I need to join is:

@Entity
@Table(name = "privillages")
public class Privillages implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
public  Integer id;

@Column(name = "privid")
public Integer privid;

@Column(name = "privi_name")
public String privi_name;

@OneToOne(fetch=FetchType.LAZY, mappedBy="priviJoin")
public Users user;


public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public Integer getPrivid() {
    return privid;
}

public void setPrivid(Integer privid) {
    this.privid = privid;
}

public String getPrivi_name() {
    return privi_name;
}

public void setPrivi_name(String privi_name) {
    this.privi_name = privi_name;
}

public Users getUser() {
    return user;
}

public void setUser(Users user) {
    this.user = user;
}

public Privillages() {
}

public Privillages(Integer privid, String privi_name ) {
    this.privid = privid;
    this.privi_name = privi_name;

}
}

And my repository is:

public interface UsersRepository extends CrudRepository<Users, Integer> {

@Query("SELECT u.username FROM Users u inner join p.privi_name FROM 
Privillages p")
List<Users> findByUsername();
}

And when I am running application , its hows the error like

"unexpected token: FROM near line 1, column 74 [SELECT u.username FROM com.central.model.Users u inner join p.privi_name FROM com.central.model.Privillages p]".

And

" Validation failed for query for method public abstract java.util.List com.central.repository.UsersRepository.findByUsername()!".

Maciej Kowalski · Accepted Answer · 2017-10-04T13:33:14.707

2

You are using a projection in the query but expect a whole entity as a result.
Also you are joining in a wrong way and using database column names instead of mapped field names.
You are using FROM twice..

If you want to have the whole entity as a result and the Privilege already fetched along with User, then try:

@Query("SELECT u FROM Users u inner join fetch u.priviJoin")

Update

if you want to use projection then you have to change the result type:

@Query("SELECT u.username,p.privi_name FROM Users u inner join u.priviJoin p")
List<Object[]> findByUsername();

you also do not need fetch in that case.

edited Oct 04 '17 at 13:33

answered Oct 04 '17 at 11:55

Maciej Kowalski

25,605
12
54
63

I am using this query with id as condition. With displaying username and priv_name. I changed the query like this now ."@Query("SELECT u.username,p.privi_name FROM Users u inner join Privillages p ON u.id=p.id")" .But still showing error "Path expected for join! ". – Mr.DevEng Oct 04 '17 at 13:16
Also my foreign key constraint is laying on privid on both table. – Mr.DevEng Oct 04 '17 at 13:25
I need to fetch records from both table by the condition of privid.So what steps i need to follow to get those mapping using one to one association? – Mr.DevEng Oct 04 '17 at 13:33
you just have to add where statement to any of the above. Either will work depending on your final needs – Maciej Kowalski Oct 04 '17 at 13:53
Ok, thank you for your response sir.I will try those. – Mr.DevEng Oct 04 '17 at 13:57

spring data jpa tables joining by One to one Association

1 Answers1

Linked