spring数据jpa中mysql内部连接查询及基于接口的投影

n53p2ov0  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(280)

我正在从事一个springmvc项目,springdatajpa运行mysql db,其中我有四个实体对象:travel、expense、currency和fund。
以下是我的db模式的可视化表示:

在expenserepository接口中,我扩展了jparepository接口。
现在我正在尝试运行一个本地sql查询,在这里我将传递expenseid,并从expense表中获取expense和amount,从currency表中获取currency\u name(你可以看到我必须做两个内部连接来获得货币名称。)
最后,我创建了另一个接口expenseoutput,将这三列合并到一个单独的非实体接口中(根据SpringDataJPA文档中提到的基于接口的投影Map)。
代码如下:

package com.binarycraftbd.ksktravelbackend.Repo

import com.binarycraftbd.ksktravelbackend.JoinQueries.ExpenseData
import com.binarycraftbd.ksktravelbackend.Model.Expense
import org.springframework.data.jpa.repository.JpaRepository
import org.springframework.data.jpa.repository.Query

interface ExpenseRepo : JpaRepository<Expense, Int> {

    @Query("select expense, amount from expense, currencyName from currency inner join fund on expense.fund_id=fund.id inner join currency on fund.currency_id=currency.id where expense.id=?1", nativeQuery = true)
    fun getCurrencyByExpId(expId:Int): ExpenseOutput

    interface ExpenseOutput{
        fun getExpense():String
        fun getAmount(): String
        fun getCurrencyname(): String
    }
}

但是,当我通过restcontroller函数运行代码时,出现以下错误:

This application has no explicit mapping for /error, so you are seeing this as a fallback.

Sat Sep 22 20:51:25 BDT 2018
There was an unexpected error (type=Internal Server Error, status=500).
could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet

我还在这里给出实体类:
旅行舱位

@Entity
@Table(name = "travel")
class Travel(
        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE)
        val id: Int=0,
        val travelName: String="",

        @OneToMany(mappedBy = "travel")
        @JsonIgnore
        val funds: List<Fund> ?= null,

        @OneToMany(mappedBy = "travel")
        @JsonIgnore
        val expenses: List<Expense>?=null

)

货币类别

@Entity
@Table(name = "currency")
class Currency(

        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE)
        val id: Int=0,
        val currencyName: String="",

        @OneToMany(mappedBy = "currency")
        @JsonIgnore
        val funds: List<Fund>?=null
)

基金类别

@Entity
class Fund(
        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE)
        val id:Int=0,
        val fundName:String="",

        @OneToMany(mappedBy = "fund")
        @JsonIgnore
        val expenses: List<Expense>?= null,

        @ManyToOne
        val travel: Travel?=null,

        @ManyToOne
        val currency:Currency?=null
)

费用类别

@Entity
class Expense(

        @Id
        @GeneratedValue(strategy = GenerationType.SEQUENCE)
        val id:Int=0,
        val date:String="",
        val time:String="",
        val expense:String="",
        val amount:String="",
        val category:String="",

        @ManyToOne
        val travel: Travel?=null,

        @ManyToOne
        val fund: Fund?=null
)

如何解决这个问题?在expenserepository中写入查询代码是否不正确?或者sql查询有什么问题吗?救命!!

sc4hvdpw

sc4hvdpw1#

econ尝试在任何sql客户机(例如mysql workbench)中运行此查询,然后再将其嵌入到代码中。
我像下面一样重新编写上面的查询,我发现了一些语法错误和非逻辑的东西。

select expense.expense, expense.amount, currency.currency_name  
from expense inner join fund on (expense.fund_id=fund.id)
inner join currency on (fund.currency_id=currency.id)
where expense.id=<replace this segment with a valid expenseId>

相关问题