CODEWITHSUNDEEP
javaspring-mvcspring-data-jpamodel-associations
Mr.DevEng
Mr.DevEng

Reputation: 2431

Error in Spring data JPA Join - One to many association

I am trying to implement an example of one to many examples using spring data JPA. I am new to data JPA. My Model are,1-Users.java

@Entity
@Table(name = "users")
public class Users implements Serializable{

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
public String username;
public String password;
public Integer privid;

@OneToMany(cascade = CascadeType.ALL, mappedBy = "pid")
private Collection<Privillages> priviJoin;

public Integer getId() {
    return id;
}
public void setId(Integer id) {
    this.id = id;
}
@Column(name = "username")
public String getUsername() {
    return username;
}
public void setUsername(String username) {
    this.username = username;
}
@Column(name = "password")
public String getPassword() {
    return password;
}
public void setPassword(String password) {
    this.password = password;
}

@Column(name = "privid")
public Integer getPrivid() {
    return privid;
}
public void setPrivid(Integer privid) {
    this.privid = privid;
}

public Collection<Privillages> getPriviJoin() {
    return priviJoin;
}
public void setPriviJoin(Privillages priviJoin) {
    this.priviJoin = (Collection<Privillages>) priviJoin;
}

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

and, Privillages.java

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

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

public String pName;


@ManyToOne(optional = false)
@JoinColumn(name = "pid", referencedColumnName = "privid")
public Users pid;

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

@Column(name = "pname")
public String getPname() {
    return pname;
}
public void setPname(String pname) {
    this.pname = pname;
}

@Column(name = "pid")
public Users getPid() {
    return pid;
}
public void setPid(Users pid) {
    this.pid = pid;
}

public Privillages(){
} 
}

And my repository is,

public interface UsersRepository extends CrudRepository<Users, Integer> 
{   
    @Query(value ="SELECT u.*,p.*  FROM users u INNER JOIN privillages p ON 
    u.privid=p.pid", nativeQuery=true)
    List<Users> findByUsername();
}

My controller functionality is,

@RequestMapping(value = "/joinResult", method = RequestMethod.GET)
public ModelAndView joinResultShow(Model model)
        {
            List<Users> use = new ArrayList<Users>(); 
            use = (List<Users>) userRepo.findByUsername();
            model.addAttribute("joinData",use);
            ModelAndView viewObj = new ModelAndView("fleethome");
            return viewObj;
        }

And displaying like ,

 <div th:each= "user: ${joinData}">
    <span th:text="${user.username}">
    </span>
    <span th:text="${user.pname}">
    </span>
</div>

And getting an error like,

There was an unexpected error (type=Internal Server Error, status=500). Exception evaluating SpringEL expression: "user.pname" (fleethome:50)

And Also I tried like non native query like,

@Query("SELECT u.username,p.pname  FROM Users u INNER JOIN Privillages p ON 
u.privid=p.pid")

But that also showing "Path expected for join! [SELECT u.username,p.pname FROM com.central.model.Users u INNER JOIN Privillages p ON u.privid=p.pid]"

Upvotes: 0

Views: 1892

Answers (1)

Maciej Kowalski
Maciej Kowalski

Reputation: 26572

User can have many privileges.. you cant seem expect to get only one all the time.

Stick to the jpa query and write it as following:

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

Then in your view you have to add another loop:

<div th:each= "user: ${joinData}">
    <span th:text="${user.username}">
    </span>
    <div th:each="priv: ${user.privJoin}">
      <span th:text="${priv.pname}">
      </span>
    </div>
</div>

Upvotes: 1

Related Questions