How to use OneToMany Association in more than one table

Question

I am trying to join three tables with my model class.Here is my model classes.

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 Set<Privillages> priviJoin;

@OneToMany(cascade = CascadeType.ALL, mappedBy = "actid")
private Set<Actions> actionJoin;



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 Set<Privillages> getPriviJoin() {
    return priviJoin;
}

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

public Set<Actions> getActionJoin() {
    return actionJoin;
}

public void setActionJoin(Set<Actions> actionJoin) {
    this.actionJoin = actionJoin;
}

public Users() {
}
}

And Privillages.java,

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

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

@Column(name = "pname")
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;
}


public String getPname() {
    return pname;
}
public void setPname(String pname) {
    this.pname = pname;
}

public Users getPid() {
    return pid;
}
public void setPid(Users pid) {
    this.pid = pid;
}

public Privillages(){
} 
}

And Actions.java

@Entity
@Table(name = "actions")
public class Actions implements Serializable {

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


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

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



public Integer getId() {
    return id;
}

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

public String getActname() {
    return actname;
}

public void setActname(String actname) {
    this.actname = actname;
}

public Users getActid() {
    return actid;
}

public void setActid(Users actid) {
    this.actid = actid;
}
public  Actions(){
}
}

My repository containing following code,

@Query(value = "SELECT u.*,p.*,a.* FROM users u "
            + "INNER JOIN privillages p ON u.privid = p.pid "
            + "INNER JOIN  actions a ON u.privid = a.actid", 
 nativeQuery=true)
Set<Users> findByUsername();

My controller action is,

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

And my view fleethome is like,

<table>
        <th> Username </th>
        <th> Privillage </th>
        <th> Action </th>
            <tr th:each="message : ${joinData}">

              <td th:text="${message.username}"></td>
              <td><span th:each="privi : ${message.priviJoin}"
                 th:text="${privi.pname}"></span></td>
              <td><span th:each="action : ${message.actionJoin}"
                 th:text="${action.actname}"></span></td>
            </tr>
    </table>

I am trying to join Privillages and Actions with my main model Users. Users-Privillages have one to many. And also Users - Actions also have one to many. When I joined Users with Privillages it working good. I successfully joined two table.

Now I also need to join Actions class with Users. I am trying to displaying one column from each Model classes. When I implemented the procedure that I follow previously for joining Users-Privillages is not working here, when I added one more table.

I am getting the error like,

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

How can I join the additional one table with my previous join?

Answer 1

You probably can't do that without model entity changes. If i got you right, you want to get your entity class with multiple related collections initialized from db. But this can't work as is in your case because MultipleBagFetchException: cannot simultaneously fetch multiple bags. It is basically the same problem of multiple collections with fetch = FetchType.EAGER. Easy fix would be to change Collection<Privillages> to Set<Privillages> or same for Actions if you can. More info

---

As for Exception evaluating SpringEL expression: "message.pid.username" the actual reason is that you are trying to work with joinData as if it is some database table record, but instead you should work with it like you would with java classes. Because you already got Set<User> joinData from hibernate. Can try something like

<tr th:each="message : ${joinData}">

    <td th:text="${message.username}"></td>
    <td><span th:each="privi : ${message.priviJoin}"
              th:text="${privi.pname}"></span></td>
    <td><span th:each="action : ${message.actionJoin}"
              th:text="${action.actname}"></span></td>

</tr>

If you want same output like in the image you provided, you can try:

<div  th:remove="tag" th:each="message : ${joinData}">
    <div th:remove="tag" th:each="privi : ${message.priviJoin}">
        <div th:remove="tag" th:each="action : ${message.actionJoin}">
            <tr>
                <td th:text="${message.username}"></td>
                <td th:text="${privi.pname}"></td>
                <td th:text="${action.actname}"></td>
            </tr>
        </div>
    </div>
</div>