Get Column name along with JSON response

Question

I have three entity classes, I have written the query which includes join of two tables.

Table: ExpensesCategories

@Entity
@Table(name = "ExpensesCategories")
public class ExpensesCategories {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "category_id", unique = true)
    private int categoryId;

    @NotNull
    private String categoryName;

    @NotNull
    private String categoryCodeInBankStats;

    public int getCategoryId() {
        return categoryId;
    }

    public void setCategoryId(int categoryId) {
        this.categoryId = categoryId;
    }

    public String getCategoryName() {
        return categoryName;
    }

    public void setCategoryName(String categoryName) {
        this.categoryName = categoryName;
    }

    public String getCategoryCodeInBankStats() {
        return categoryCodeInBankStats;
    }

    public void setCategoryCodeInBankStats(String categoryCodeInBankStats) {
        this.categoryCodeInBankStats = categoryCodeInBankStats;
    }
}

Table: Transactions

@Entity
@Table(name = "TransactionHistory")
public class TransactionHistory {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private int id;

    @Temporal(TemporalType.DATE)
    private Date dateOfTransaction;

    private String transactionType;

    private String refNo;

    private Date valueDate;

    private double withdrawalAmount;

    private double depositAmount;

    private double closingBalance;

    @ManyToOne
    @JoinColumn(name="userDetailsId", referencedColumnName="user_id")
    private UserDetails userDetails;

    @ManyToOne
    @JoinColumn(name="expenseCategoriesId", referencedColumnName="category_id")
    private ExpensesCategories expenseCategories;

    public TransactionHistory(int userId, Date dateOfTransaction, String transactionType, String refNo, Date valueDate,
            double withdrawalAmount, double depositAmount, double closingBalance) {
        this.dateOfTransaction = dateOfTransaction;
        this.transactionType = transactionType;
        this.refNo = refNo;
        this.valueDate = valueDate;
        this.withdrawalAmount = withdrawalAmount;
        this.depositAmount = depositAmount;
        this.closingBalance = closingBalance;
    }

    public TransactionHistory() {
    }

    public Date getDateOfTransaction() {
        return dateOfTransaction;
    }

    public void setDateOfTransaction(Date date) {
        this.dateOfTransaction = date;
    }

    public String getTransactionType() {
        return transactionType;
    }

    public void setTransactionType(String transactionType) {
        this.transactionType = transactionType;
    }

    public String getRefNo() {
        return refNo;
    }

    public void setRefNo(String refNo) {
        this.refNo = refNo;
    }

    public Date getValueDate() {
        return valueDate;
    }

    public void setValueDate(Date valueDate) {
        this.valueDate = valueDate;
    }

    public double getWithdrawalAmount() {
        return withdrawalAmount;
    }

    public void setWithdrawalAmount(double withdrawalAmount) {
        this.withdrawalAmount = withdrawalAmount;
    }

    public double getDepositAmount() {
        return depositAmount;
    }

    public void setDepositAmount(double depositAmount) {
        this.depositAmount = depositAmount;
    }

    public double getClosingBalance() {
        return closingBalance;
    }

    public void setClosingBalance(double closingBalance) {
        this.closingBalance = closingBalance;
    }

    public UserDetails getUserDetails() {
        return userDetails;
    }

    public void setUserDetails(UserDetails userDetails) {
        this.userDetails = userDetails;
    }

    public int getId() {
        return id;
    }

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

    public ExpensesCategories getExpenseCategories() {
        return expenseCategories;
    }

    public void setExpenseCategories(ExpensesCategories expenseCategories) {
        this.expenseCategories = expenseCategories;
    }
}

Table: User Details

@Entity
@Table(name = "Employee")
public class UserDetails {

    @Id
    @Column(name = "user_id", unique = true)
    private int id;
    @NotNull
    private String firstname;
    @NotNull
    private String lastname;
    @Column(unique = true)
    @NotNull
    private String emailaddress;
    @NotNull
    private String role;



    public UserDetails(String firstname, String lastname, String emailaddress, String role) {
        this.firstname = firstname;
        this.lastname = lastname;
        this.emailaddress = emailaddress;
        this.role = role;
    }

    public int getId() {
        return id;
    }

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

    public UserDetails() {
    }

    public String getFirstname() {
        return firstname;
    }

    public void setFirstname(String firstname) {
        this.firstname = firstname;
    }

    public String getLastname() {
        return lastname;
    }

    public void setLastname(String lastname) {
        this.lastname = lastname;
    }

    public String getEmailaddress() {
        return emailaddress;
    }

    public void setEmailaddress(String emailaddress) {
        this.emailaddress = emailaddress;
    }

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    @Override
    public String toString() {
        return "Employee [id=" + id + ", firstname=" + firstname + ", lastname=" + lastname + ", emailaddress="
                + emailaddress + ", role=" + role + "]";
    }

I have written query like this in transaction entity.

    @Query( nativeQuery=true, value="SELECT a.expense_categories_id, a.Total_withdrawal_Amount, b.category_code_in_bank_stats, b.category_name FROM (SELECT expense_categories_id , SUM(withdrawal_amount) AS Total_withdrawal_Amount FROM transaction_history GROUP BY expense_categories_id) a join expenses_categories b on a.expense_categories_id = b.category_id

")
        List<Object[]> getCategorizedExpenses();

My Json Response is like:

[
    [
        1,
        21,
        "UPI",
        "UPI Payments"
    ],
    [
        2,
        3733.59,
        "POS",
        "Shopping"
    ]
]

But i want json response with column names as well:

[
    [
       expense_categories_id: 1,
       Total_withdrawal_Amount: 21,
       category_code_in_bank_stats: "UPI",
       category_name: "UPI Payments"
    ],
    [
        expense_categories_id: 2,
        Total_withdrawal_Amount: 3733.59,
        category_code_in_bank_stats: "POS",
        category_name: "Shopping"
    ]
]

Please help me out..

Answer 1

You would need to map the results directly to a POJO class and ad some json config:

1) Define the pojo

public ResultClass implements Serializable{

   @JsonProperty("expense_categories_id")
   private Integer expenseCategoriesId;

   ...

   public ResultClass(Integer expenseCategoriesId ... // rest params){
      this.expenseCategoriesId = expenseCategoriesId;
      ...
   }  
}

2) Define the mapping:

@SqlResultSetMapping(
    name="myMapping",
    classes={
        @ConstructorResult(
            targetClass=ResultClass.class,
            columns={
                @ColumnResult(name="expenseCategoriesId"),
                @ColumnResult(name="totalWithdrawalAmount")
                // further mappings ...
            }
        )
    }
)

3) Define a native query

@NamedNativeQuery(name="TransactionHistory.myQuery"
    , query="SELECT new mypackage.ResultClass(a.expense_categories_id as expeneCategoriesId ... ) from ...")

4) Define this method in the CrudRepository without the @Query annotation:

public List<ResultClass> myQuery();

Teh @SqlResultSetMapping and @NamedNativeQuery would need to be defined on one of your mapped entities.

Answer 2

Your native query will give you an object[][] as an result. So, it actually a mxn rows. So, I think you should create a class names Response

public class Response{

   private Long expense_categories_id;
   private Double Total_withdrawal_Amount;
   private String category_code_in_bank_stats;
   private String category_name;


   //getters and setters for all attributes

}



List<Response> fillCategorizedExpenses(){
   List<Response> response_List = new ArrayList<>();
   Response response = null; 
   Object[][] // fill each object with by accessing their index from 
              //this array.
   for() //iterate the object array. {
       response = new Response();
       response.setExpense_categories_id(value); // set all attributes.
       ....
       .... 
       ....
       response_List.add(response);
    }
     return response_List; //this will print as you need in your project.

}

Thank You :) Hope this might help you out.