Selection of multiple columns unsing spring data JPA and JPQL

Question

I am trying to select two columns which are in two separate tables. One table is users and other one is privillages. I need to fetch username from users and pname from privillages. My model classes are like follows,

@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 my Privillages class is,

@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 Users repository is,

public interface UsersRepository extends CrudRepository<Users, Integer> 
{
    @Query("select u from Users ug join ug.priviJoin u")
    List<Users> findByUsername();
}

And My Privillage repository is:

public interface PrivillagesRepository extends CrudRepository<Privillages, 
Integer> {

 }

My controller file is:

@RequestMapping(value = "/joinResult", method = RequestMethod.GET)
public ModelAndView joinResultShow(Model model)
{
List<Privillages> privillages = (List<Privillages>)privillagesRepo.findAll();
model.addAttribute("joinData",privillages);
ModelAndView viewObj = new ModelAndView("fleethome");
return viewObj;
}

And displaying like:

<table>
    <tr th:each="message : ${joinData}">
        <td th:text="${message.pname}"></td>
        <td th:text="${message.pid.username}"></td>
    </tr>
</table>

And getting error like:

There was an unexpected error (type=Internal Server Error, status=500). No message available

Stacktrace is:

java.lang.NullPointerException: null
at 
com.central.controller.WebController.joinResultShow(WebController.java:58) ~
[classes/:na]
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~
[na:1.8.0_141]
at sun.reflect.NativeMethodAccessorImpl.invoke
(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_141]
at sun.reflect.DelegatingMethodAccessorImpl.
invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_141]
at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_141]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke
(InvocableHandlerMethod.java:205) ~[spring-web-
4.3.11.RELEASE.jar:4.3.11.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.
invokeForRequest(InvocableHandlerMethod.java:133) ~[spring-web-
4.3.11.RELEASE.jar:4.3.11.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.
ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.
java:97) ~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.
RequestMappingHandlerAdapter.invokeHandlerMethod
(RequestMappingHandlerAdapter  .java:827) 
~[spring-webmvc-4.3.11.RELEASE.jar:4.3.11.RELEASE]
at org.springframework.web.servlet.mvc.method.
annotation.RequestMappingHandlerAdapter.handleInternal
(RequestMappingHandlerAdapter.java:738) ~[spring-webmvc-
4.3.11.RELEASE.jar:4.3.11.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.
handle(AbstractHandlerMethodAdapter.java:85) ~[spring-webmvc-
4.3.11.RELEASE.jar:4.3.11.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.
doDispatch(DispatcherServlet.java:967) ~[spring-webmvc-
4.3.11.RELEASE.jar:4.3.11.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService
(DispatcherServlet.java:901) ~[spring-webmvc-
4.3.11.RELEASE.jar:4.3.11.RELEASE]

I need to retrieve username from users and pname column from privillages. How should I change my code?

please, add the code of your controller – Mykola Yashchenko Oct 10 '17 at 11:36 — Mykola Yashchenko, Oct 10 '17 at 11:36

score 0 · Answer 1 · answered Oct 10 '17 at 12:25

0

First of all, you should create privillages repository:

public interface PrivillagesRepository extends CrudRepository<Privillages, Integer> {
}

Then you can do find all privillages in your controller:

List<Privillages> privillages = privillages.findAll();
model.addAttribute("joinData",privillages);

After that, change your template:

<table>
    <tr th:each="message : ${joinData}">
        <td th:text="${message.pname}"></td>
        <td th:text="${message.pid.username}"></td>
    </tr>
</table>

As you can see, to print username, you need to use nested field "pid" (Users class)

answered Oct 10 '17 at 12:25

Mykola Yashchenko

5,103
3
39
48

I tried on this.But still not getting. I edited my question as per change. Can you look on that? – Mr.DevEng Oct 10 '17 at 12:56
you've added my code, but you should also remove `model.addAttribute("joinData",use)` – Mykola Yashchenko Oct 10 '17 at 12:58
anyway, if after removing you still get an error, please, post your error here – Mykola Yashchenko Oct 10 '17 at 12:59
Yes,still same error. ie - "There was an unexpected error (type=Internal Server Error, status=500). No message available". You can look my edited question. – Mr.DevEng Oct 10 '17 at 13:04
Ok, if you have such error, please, add the stacktrace – Mykola Yashchenko Oct 10 '17 at 13:05
stacktrace means that the file records and results that you are expecting for clarification? I am not clear. – Mr.DevEng Oct 10 '17 at 13:07
https://stackoverflow.com/questions/3988788/what-is-a-stack-trace-and-how-can-i-use-it-to-debug-my-application-errors – Mykola Yashchenko Oct 10 '17 at 13:09
WebController.java:58 what is the 58 line? – Mykola Yashchenko Oct 10 '17 at 13:27
line is "List privillages = (List) privillagesRepo.findAll();" – Mr.DevEng Oct 10 '17 at 13:29
Oh, I see. Please add the full code of your controller. I see that privillagesRepo == null, but I can't guess why. Did you add @EnableJpaRepositories annotation to your config? – Mykola Yashchenko Oct 10 '17 at 13:33
I have only this action in my controller. There is no pblm related to repository,I already accessed datas without association.Now I am learning spring data JPA,I am new to spring. I have no cconfiguration file.I am using springboot,spring data JPA. – Mr.DevEng Oct 10 '17 at 13:37
But I see that repository reference is null. So your config is incorrect, because Spring doesn't autowire this properly. You should have java class configuration. Please, push your code to github if it's possible – Mykola Yashchenko Oct 10 '17 at 13:40
I am using @Autowired, PrivillagesRepository privillagesRepo; in my controller. The same way i am propery accessing my other non association example data accessing.I just follow the same. Can you look into my UserRepository JPQL query ?. Is it needed ,then is it properly defined ? – Mr.DevEng Oct 10 '17 at 13:43
Try to put annotation @EnableJpaRepositories to your main class – Mykola Yashchenko Oct 10 '17 at 13:44
I got the solution. There is only use the previous user Model class with the same JPQL query. Along with the modification that you gave.I am updating answer below. And I am thankful for your responses and guiding me to troubleshoot the proper solution ,even if I am new to spring learning. – Mr.DevEng Oct 10 '17 at 14:18

score 0 · Accepted Answer · answered Oct 10 '17 at 14:22

I just explored the context that, Use the same repository as the Userrepository along with the JPQL query. And modify the view file according to that for displaying username with using the pid from Privillages Model class. I am adding the code need to modify with certain changes.

My controller file is,

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

And the repository ,

public interface UsersRepository extends CrudRepository<Users, Integer> 
{
    Users findByUsernameAndPassword(String username,String password);   

    @Query("select u from Users ug join ug.priviJoin u")
    List<Users> findByUsername();

}

And My Privillage repository,

public interface PrivillagesRepository extends CrudRepository<Privillages, 
Integer> {

}

And modify the View file with,

<table>
        <th> Username </th>
        <th> Privillage Name </th>
            <tr th:each="message : ${joinData}">
                <td th:text="${message.pid.username}"></td>
                <td th:text="${message.pname}"></td>
            </tr>
    </table>

Selection of multiple columns unsing spring data JPA and JPQL

2 Answers2