我正在使用运行MySql DB的Spring Data JPA进行Spring MVC项目,其中我有四个实体对象:旅行,费用,货币和基金 .
这是我的数据库架构的直观表示:
在ExpenseRepository接口中,我扩展了JpaRepository接口 .
现在我正在尝试运行本机SQL查询,我将传递expenseId,我将从Expense表中获取费用和金额,并从货币表中获取currency_name . (你可以看到我必须做两个内连接来获取currency_name . )
最后,我创建了另一个接口ExpenseOutput,将三列合并为一个单独的非实体接口(根据Spring Data JPA文档中提到的基于接口的投影映射) .
以下是代码:
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查询有问题吗?皮斯帮助!!
1 回答
Econ尝试在任何sql客户端(如mysql workbench)中运行此查询,例如在将其嵌入代码之前 .
我重写上面的查询,如下所示,我发现了一些语法错误和非逻辑的东西 .