java - 获取列名以及 JSON 响应

Question

我有三个实体类，我编写了包含两个表连接的查询。

表:费用类别

@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;
    }
}

表:交易

@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;
    }
}

表:用户详细信息

@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 + "]";
    }

我在 transaction 实体中编写了这样的查询。

    @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();

我的 Json 响应如下:

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

但我也想要带有列名的 json 响应:

[
    [
       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"
    ]
]

请帮帮我..

Answer 1

您需要将结果直接映射到 POJO 类并添加一些 json 配置:

1)定义pojo

public ResultClass implements Serializable{

   @JsonProperty("expense_categories_id")
   private Integer expenseCategoriesId;

   ...

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

2)定义映射:

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

3)定义原生查询

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

4) 在 CrudRepository 中定义此方法，不使用 @Query 注释:

public List<ResultClass> myQuery();

Teh @SqlResultSetMapping 和 @NamedNativeQuery 需要在您的映射实体之一上定义。