使用@Query注解执行PostgreSQL原生SQL仅首条生效其余失败

为获取所需数据，编写了一组针对PostgreSQL的关联视图SQL语句，在数据库控制台按顺序执行完全正常，但在项目的Repository层通过配置nativeQuery = true的@Query注解调用同类语句时出现异常，最终无结果返回。查看Hibernate SQL调试日志发现仅第一条创建camera_layout视图的SQL成功执行，后续语句全部中断。

原始SQL语句 #

-- 1
CREATE or replace view camera_layout AS
select layout_id, unnest(layout.camera_ids) as camera_id from layout;

-- 2
CREATE or replace view camera_region AS
select c.camera_id as camera_id ,object.region_id
FROM object LEFT JOIN camera c on object.object_id = c.object_id WHERE object.region_id = ?1;

--3
CREATE or replace view region_layout AS
select distinct cl.layout_id from camera_layout cl,
camera_region cr where cl.camera_id in (select cr.camera_id from camera_region cr);

--4
SELECT l from layout l where l.layout_id in (select rl.layout_id from region_layout rl);

代码实现 #

Repository层代码 #

@Repository
public interface LayoutRepository extends JpaRepository<Layout,Integer> {

 @Query(value = "create or replace view camera_layout AS\n" +
         "select layout_id, unnest(layout.camera_ids) as camera_id from layout" ,
          nativeQuery = true)
    void createViewLayoutCamera();

 @Query(value = "CREATE or replace view camera_region AS\n" +
            "select c.camera_id as camera_id ,object.region_id\n" +
            "FROM object LEFT JOIN camera c on object.object_id = c.object_id WHERE object.region_id = ?1 " +
            "",nativeQuery = true)
    void createViewCameraRegion(Integer region);


    @Query(value = "create or replace view region_layout AS\n" +
            " select distinct cl.layout_id from camera_layout cl,\n" +
            "camera_region cr where cl.camera_id in (select cr.camera_id from camera_region cr)",nativeQuery = true)
    void createViewRegionLayout();

   @Query( value = "select l from layout l where l.layout_id in (select rl.layout_id from region_layout rl)",nativeQuery = true)
    List <Layout> filterRegion();
}

Service层代码 #

@Override
public List<LayoutDTO> filterRegion(Integer region_id) {
    ArrayList<LayoutDTO> convert_objects = new ArrayList<>();
    LayoutDTO conv_object;
    layoutRepository.createViewLayoutCamera();
    layoutRepository.createViewCameraRegion(region_id);
    layoutRepository.createViewRegionLayout();
    List <Layout> objects = layoutRepository.filterRegion();
    // 省略转换逻辑
    return convert_objects;
}

1. 缺少@Modifying注解：Spring Data JPA的@Query注解默认将语句识别为查询操作，执行CREATE VIEW这类DDL写操作时，必须标注@Modifying注解，否则JPA会尝试读取返回结果集，找不到结果集就会抛出异常，中断后续代码执行。
2. 事务配置缺失：DDL操作需要在事务内执行，没有标注@Transactional的话，执行完第一条语句后事务状态异常，后续操作无法正常提交执行。
3. 原生查询映射错误：最后一条查询语句写的是select l from layout l，PostgreSQL中该语法返回的是行复合类型，不是字段集合，JPA无法将其映射为Layout实体对象，即使前面视图创建成功，最终查询也会返回异常或空结果。
4. 视图创建的并发风险（隐含问题）：每次查询都重复创建全局视图，多请求并发调用时会出现视图覆盖问题，导致查询结果混乱。

1. 给DDL操作方法加注解：所有执行CREATE VIEW的Repository方法都加上@Modifying和@Transactional注解，也可以将@Transactional标注在Service的filterRegion方法上统一管理事务。
   修改后的Repository方法示例：

@Modifying
@Transactional
@Query(value = "create or replace view camera_layout AS\n" +
         "select layout_id, unnest(layout.camera_ids) as camera_id from layout" ,
          nativeQuery = true)
void createViewLayoutCamera();
// 其余两个CREATE VIEW方法也加上相同的@Modifying和@Transactional注解

2. 修正查询语句：将最后一条原生查询的SQL修改为select l.* from layout l，返回所有字段用于实体映射。
3. 方案优化（推荐）：不需要每次查询都创建全局视图，可以将多个视图的逻辑合并为单条关联查询，避免DDL操作的开销和并发冲突，示例合并后SQL：

select distinct l.* from layout l
join unnest(l.camera_ids) cid on exists (
    select 1 from object o 
    left join camera c on o.object_id = c.object_id 
    where o.region_id = ?1 and c.camera_id = cid
)

直接使用该SQL作为filterRegion的查询语句，不需要额外创建三个视图，性能和稳定性更高。

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