mysql - Spring Data JPA 中的内连接查询和基于接口的投影
问题描述
我正在使用运行 MySql DB 的 Spring Data JPA 开发一个 Spring MVC 项目,其中我有四个实体对象:Travel、Expense、Currency 和 Fund。
这是我的数据库架构的可视化表示:
在 ExpenseRepository 接口中,我扩展了 JpaRepository 接口。
现在我正在尝试运行一个本地 SQL 查询,我将在其中传递费用 ID,我将从 Expense 表中获取费用和金额,并从货币表中获取 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查询有问题?帮助!
解决方案
Econ 尝试在任何 sql 客户端(例如 mysql 工作台)中运行此查询,然后再将其嵌入代码中。
我重写了上面的查询,如下所示,我发现了一些语法错误和不合逻辑的东西。
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>
推荐阅读
- api - Spotify API 认证
- azure - Azure 服务编辑器配置更改未应用
- ocaml - 绝对值函数 Ocaml
- swift - List SwiftUI 中的置顶页脚
- spring-boot - 如何将额外的 linux 依赖项添加到 spring-boot buildpack 映像中?
- android - React-Native 计步器值问题(Android)
- tkinter - 在 tkinter 中切换帧时如何更新/刷新小部件?
- python - 查找子目录的大小
- java - 无法在 Selenium 的 textarea 中找到元素
- python-3.x - 从张量 Pytorch 批量采样数据