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.
Your query should return
List<Object[]>
and notList<AccountModel>
.JPA equivalent query will be something like this.
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.