Spring Boot中Inner Join查询报错，MySQL Workbench可正常执行

问题情况 #

在Spring Boot中执行Inner Join查询时抛出InvalidDataAccessResourceUsageException异常，但逻辑等价的查询在MySQL Workbench中可正常执行。

MySQL Workbench中执行成功的查询语句 #

select gstem.orders.id,gstem.orders.order_no,gstem.orders.date, gstem.orders.quantity,gstem.orders.weight,
gstem.orders.eta_date, gstem.statuses_types.name as status, gstem.customers.company_name from gstem.orders inner join gstem.statuses_types on gstem.orders.status_id = gstem.statuses_types.id inner join gstem.customers on gstem.orders.customer_id = customers.id where gstem.statuses_types.category = "order_status" and statuses_types.name = "Received" and stage = "order" order by order_no;

Spring Boot中执行失败的查询语句 #

Select `orders`.`id`, `order_no`, `date`, `quantity`, `weight`, `amount`, `eta_date`, `statuses_types`.`name` as `status`, `customers`.`company_name` from `orders` inner join `statuses_types` on `orders`.`status_id` = `statuses_types`.`id` inner join `customers` on `orders`.`customer_id` = `customers`.`id` where     `statuses_types`.`category` = :category and `stage` = :stage order by `order_no`

错误信息 #

Failed to complete request: org.springframework.dao.InvalidDataAccessResourceUsageException: Unable to find column position by name: `amount` [Column 'amount' not found.] [n/a]; SQL [n/a]
2023-03-10T13:58:58.783+05:30 ERROR 23388 --- [nio-8080-exec-1] o.a.c.c.C.[.[.[/].[dispatcherServlet] : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: Unable to find column position by name: `amount` [Column 'amount' not found.] [n/a]; SQL [n/a]] with root cause

原因分析 #

错误信息明确提示Column 'amount' not found，对比两段查询语句可知：Spring Boot中的查询额外选取了amount字段，但该字段并不存在于orders表中（MySQL Workbench的成功查询未包含该字段），导致数据库无法找到对应列，触发异常。

解决方案 #

1. 移除无效字段：若orders表确实没有amount列，直接从Spring Boot的查询语句中删除amount字段，与MySQL Workbench中成功的查询保持一致。
2. 添加所需字段：若业务逻辑需要amount字段，先在数据库的orders表中添加该列，同时确保Spring Boot对应的实体类中完成该字段的映射配置。

内容的提问来源于stack exchange，提问作者Shelina Heerji