The native sql query to Postgresql via the @query annotation is not executed-CodePudding

To get the necessary data, I form an SQL query to PostgreSQL, which is a related set of View tables :

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)

When I execute these queries from the console in the database sequentially, one after the other, everything works.

However, if you run similar queries in the repository via the @query annotation ("... " , NativeQuery = true), a number of errors occur and the queries break down in the end without returning anything. How is this possible and what should I do?

My service it looks like this:

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();

When trying to execute .It returns "the query did not return results" , the Hibernate SQL debugger says that only query number 1 worked. "Hibernate: create or replace view camera_layout AS select layout_id, unnest(layout.camera_ids) as camera_id from layout" the rest break down, although they should work help me, I am very grateful in advance

CodePudding user response：

As stated here DDL statements need to be annotated with @Modifying annotation, so your repository methods should look like this:

@Modifying
@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();