How to select particular column from different Table using Spring Data JPA Join

Question

I am trying to access particular column from different table using Spring Data JPA Inner Join.

I created four model. AccountModel is main model. i used manyToOne mapping in AccountModel to the other three model(DepartmentModel,AccountCPCMappingModel,InvestigatorModel). I am trying to access particular column from four table using native Query in AccountRepository

This is my application scenario.

1.AccountModel.java

 package com.demo.model;        
    import java.io.Serializable;
    import java.sql.Timestamp;
    import javax.persistence.Entity;
    import javax.persistence.Id;
    import javax.persistence.JoinColumn;
    import javax.persistence.ManyToOne;
    import javax.persistence.Table;

    @Entity
    @Table(name="account")
    public class AccountModel implements Serializable {

        private static final long serialVersionUID = 1L;
        @Id
        public Integer naccountid;
        public String namount;
        public String sacctdesc;
        public Integer naccountcpcmappingid;
        public Integer nindirectcostrate;
        public Integer nagencyid ;
        public Integer ndeptid ;
        public String sgrantnum;
        public Timestamp dstartdate;
        public Timestamp denddate;  
        public String slocation;
        public String sclientacctid;
        public Integer ninvestigatorid;
        public Integer ninstid;
        public Integer ntempaccountid;

        @ManyToOne(optional = true)
        @JoinColumn(name="ndeptid",insertable =  false, updatable = false)
        public DepartmentModel department;

        @ManyToOne(optional = true)
        @JoinColumn(name="ninvestigatorid",insertable =  false, updatable = false)
        public InvestigatorModel investigator;

        @ManyToOne(optional = false)
        @JoinColumn(name="naccountcpcmappingid",insertable =  false, updatable = false)
        public AccountCPCMappingModel accountCPC;

       //...Getter and Setter              

    }

2 DepartmentModel

package com.demo.model;

import java.io.Serializable;

import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "department")
public class DepartmentModel implements Serializable {  


    private static final long serialVersionUID = 1L;
     @Id     
    public  Integer ndeptid;    
    public String sdeptname ;
    public  Integer ninstid  ;
    public Boolean bislocked;
    public String sclientdeptid;
    public Integer nsurveymethodid;
    public Boolean bisjointuse;
    public Integer ntempdeptid;
    public  Boolean balternatejointusepercentage;
    public Integer ndivid;

  //...Getter and Setter

3.InvestigatorModel

package com.demo.model;

import java.io.Serializable;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="investigator")
public class InvestigatorModel implements Serializable{

        private static final long serialVersionUID = 1L;

        @Id
        public Integer ninvestigatorid;
        public String sinvestigatorname;
        public Integer ninstid ;
        public String stitle;
        public Integer ntempinvestigatorid;
        public Integer nempid;  

AccountRepository

package com.demo.repository;
    import java.util.List;

    import org.springframework.data.jpa.repository.JpaRepository;
    import org.springframework.data.jpa.repository.Query;
    import org.springframework.stereotype.Repository;

    import com.demo.model.AccountModel;
    @Repository
    public interface AccountRepository extends JpaRepository<AccountModel, Integer>{    


@Query(value="select acct.sclientacctid,acct.sacctdesc,acct.slocation,invest.sinvestigatorname, \r\n" + 
            "dept.sclientdeptid,dept.sdeptname,acp.sccpcode \r\n" + 
            "from Account acct join Department dept on acct.nDeptID=dept.nDeptID \r\n" + 
            "join Investigator invest on acct.nInvestigatorID=invest.nInvestigatorID \r\n" + 
            "join AccountCPCMapping acp on acct.nAccountCPCMappingID=acp.nAccountCPCMappingID \r\n" + 
            "where acct.nInstID=60 \r\n" + 
            "order by acct.sclientacctid Asc",nativeQuery=true)

         List<Object[]> findByNaccountid();

    }

AccountService

package com.demo.services;

import java.util.List;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.demo.model.AccountModel;
import com.demo.repository.AccountRepository;

   @Service
    public class AccountService {
    @Autowired
    AccountRepository accRepo; 

    public List<AccountModel> findLocation()
    {
        return accRepo.findBySlocation();    
    }   

    public Set<Object[]> gridLoad()
    {

    Set<Object[]>   gridObj=accRepo.findByNaccountid();
    return gridObj;      


    }
}

AccountController

    package com.demo.controller;

    import java.util.List;

    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.CrossOrigin;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;

    import com.demo.model.AccountModel;
    import com.demo.services.AccountService;

    @RestController
    @RequestMapping("/SpaceStudy/SpaceAdmin")
    public class AccountController
    {

    @Autowired
    AccountService accService;

    @CrossOrigin(origins="*")
    @GetMapping("/AccountMaintenance/LoadLocation")
    public List<AccountModel> findLocation()
    {
        return accService.findLocation();
    }

    @CrossOrigin(origins="*")
    @PostMapping("/AccountMaintenance/LoadGrid")
    public Set<Object[]> GridLoad()
    {
        return accService.gridLoad();

    }
}

I am getting output in this format

But I want output in JSON Format like this (key,value pairs)

sample output

[
  {
    "userId": 1,
    "id": 1
}
]

Can any one help me what i need to change in my code for json data.

Answer 1

Your query should return List<Object[]> and not List<AccountModel>.

JPA equivalent query will be something like this.

Query("select acct.sclientacctid,acct.sacctdesc,acct.slocation,invest.sinvestigatorname, dept.sclientdeptid,dept.sdeptname,acp.sccpcode
       from AccountModel acct join acct.department dept join acct.investigator invest join acct.accountCPC acp
       where acct.nInstID= :instId
       order by acct.sclientacctid")
List<Object[]> findByInstId (@Param("instId") Integer instId);

This will return you a List of Object array and the array will have the columns in order it appears in the select. Give it a try.