JPA @Query编写多表关联SQL触发MariaDB语法错误该如何解决

报错信息：You have an error in your SQL syntax check the manual that corresponds to your MariaDB server version for the right syntax to use

错误原因 #

• 保留关键字用作别名：你给Address表设置的别名add是MariaDB官方保留关键字，属于语法违规。
• 别名前后不一致：Address表的定义别名为add，但后续WHERE条件中全部使用了不存在的别名ad调用字段，无法被SQL解析器识别。
• 原生SQL写法错误：你开启了nativeQuery = true使用原生SQL逻辑，原生SQL不支持SELECT mg这种JPQL专属的整表别名查询写法，查询全字段需要明确写为SELECT mg.*。

修正后的代码 #

原生SQL版本 #

@Query(value = "SELECT mg.* FROM Management AS mg INNER JOIN Information AS info ON info.id = mg.id INNER JOIN Address AS ad ON info.id = ad.id WHERE ad.divition =:divition and ad.district =:district and ad.thana =:thana and ad.postOffice =:postOffice", nativeQuery = true)

优化JPQL版本（无需原生查询） #

如果你的三个实体已经通过@OneToOne注解配置了关联映射，可直接使用JPQL查询，写法更简洁：

@Query(value = "SELECT mg FROM Management mg JOIN mg.information info JOIN info.address ad WHERE ad.divition =:divition and ad.district =:district and ad.thana =:thana and ad.postOffice =:postOffice")

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