Spring Boot中H2可用的INSERT关联查询在代码中执行报错

一、搞定sJOIN语法错误 #

• 先检查你写在@Query里的原生SQL，大概率是拼写时漏了空格，导致某个字段/字符和JOIN连在了一起，比如写成...sJOIN...这种无效语法。H2控制台里手动输入时可能补了空格，但代码里没写对。
• 举个错误例子，比如你可能写成这样：

INSERT INTO Track_Singers (track_id, singer_id) SELECT t.id, s.id FROM Track t, Singer s WHERE t.name = :trackName AND s.name = :singerNameANDJOIN...

• 修正的时候务必保证所有关键字前后都有空格，比如：

INSERT INTO Track_Singers (track_id, singer_id) 
SELECT t.id, s.id 
FROM Track t 
JOIN Singer s ON t.some_column = s.some_column
WHERE t.name = :trackName AND s.name = :singerName

• 另外别忘了给@Query加上nativeQuery = true，不然JPA会把它当JPQL解析，也可能出语法错：

@Query(value = "INSERT INTO Track_Singers (track_id, singer_id) SELECT t.id, s.id FROM Track t JOIN Singer s ON t.id = s.track_id WHERE t.name = :trackName AND s.name = :singerName", nativeQuery = true)
void associateTrackAndSinger(@Param("trackName") String trackName, @Param("singerName") String singerName);

二、删掉重复操作，让JPA自动维护多对多关联 #

其实多对多关系根本不需要你手动写原生SQL插中间表，JPA本身就能搞定，还能避免重复操作：

1. 先把实体类的多对多映射配对：
   • Track实体：

   @Entity
   public class Track {
       @Id
       @GeneratedValue(strategy = GenerationType.IDENTITY)
       private Long id;
       private String name;
       
       @ManyToMany
       @JoinTable(
           name = "Track_Singers", // 对应自动生成的中间表名
           joinColumns = @JoinColumn(name = "track_id"),
           inverseJoinColumns = @JoinColumn(name = "singer_id")
       )
       private Set<Singer> singers = new HashSet<>();
       
       // 加个工具方法维护双向关联
       public void addSinger(Singer singer) {
           this.singers.add(singer);
           singer.getTracks().add(this);
       }
       
       // getter、setter自行补全
   }
   

   • Singer实体：

   @Entity
   public class Singer {
       @Id
       @GeneratedValue(strategy = GenerationType.IDENTITY)
       private Long id;
       private String name;
       
       @ManyToMany(mappedBy = "singers") // 关联Track里的singers字段，无需再配JoinTable
       private Set<Track> tracks = new HashSet<>();
       
       // getter、setter
       public Set<Track> getTracks() {
           return tracks;
       }
   }
   

2. 服务层直接用JPA的save方法就行，不用再调用原生SQL：

   @Service
   public class TrackService {
       private final TrackRepository trackRepo;
       private final SingerRepository singerRepo;
       
       // 用构造注入代替@Autowired更规范
       public TrackService(TrackRepository trackRepo, SingerRepository singerRepo) {
           this.trackRepo = trackRepo;
           this.singerRepo = singerRepo;
       }
       
       public void linkTrackAndSinger(String trackName, String singerName) {
           Track track = trackRepo.findByName(trackName);
           Singer singer = singerRepo.findByName(singerName);
           
           if (track != null && singer != null) {
               track.addSinger(singer);
               trackRepo.save(track); // 这一步JPA会自动往中间表插记录
           }
       }
   }
   

3. 把之前写的自定义原生SQL方法删掉，既省代码又不会再出现语法问题，还解决了重复操作的毛病。

内容的提问来源于stack exchange，提问作者Yiğit Altay