时间戳

首页 > 解决方案 > spring data jpa update of multiple rows doesn't work

postgresql - spring data jpa update of multiple rows doesn't work

问题描述

Time ago my query worked fine for a single row update. Now I have to modify this query to update multiple row. The query is native and use postgresql and postgis.

The old query:

@Modifying
@Transactional
@Query(value = "WITH tmp AS (SELECT ST_Difference( (SELECT ST_Buffer(ST_Union(ST_Buffer(a.area\\:\\:geometry, 0.002)), -0.002) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city), \n" + 
        "                     (ST_Difference( ST_GeomFromGeoJSON(?2)\\:\\:geometry, (SELECT ST_Union(a.area\\:\\:geometry) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city and c.full_area=true)                          \n" + 
        "                       ))                                      \n" + 
        "                   ) AS final_area)\n" + 
        "UPDATE mydb.dis_area SET new_area=(SELECT final_area FROM tmp), " +
        "id_type=3 " +
        "WHERE id_dis=?1 ",
        nativeQuery = true)
Integer insertShape(Integer id, String shapeGeoJson);

In the new query I added some parameter in @Modifying as stated here:

@Modifying(flushAutomatically = true, clearAutomatically = true)
@Transactional
@Query(value = "WITH tmp AS (SELECT ST_Difference( (SELECT ST_Buffer(ST_Union(ST_Buffer(a.area\\:\\:geometry, 0.002)), -0.002) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city), \n" + 
        "                     (ST_Difference( ST_GeomFromGeoJSON(?2)\\:\\:geometry, (SELECT ST_Union(a.area\\:\\:geometry) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city and c.full_area=true)                          \n" + 
        "                       ))                                      \n" + 
        "                   ) AS final_area)\n" + 
        "UPDATE mydb.dis_area SET new_area=(SELECT final_area FROM tmp), " +
        "id_type=3 " +
        "WHERE id_dis_aree=(select id_dis_aree from dis_area where id_dis=?1) ",
        nativeQuery = true)
Integer insertShape(Integer id, String shapeGeoJson);

But sadly this changed doesn't have any effect. (If I lunch the query from postgresql it run perfectly). How can I solve?

Edit: I added the query, but it works on postgresql. The only difference is that the old version: WHERE id_dis=?1 target a single row, and the new WHERE id_dis_aree=(select id_dis_aree from dis_area where id_dis=?1) target multiple rows. The couple id_dis_aree and id_dis is a primary key. Two or more records could have same id_dis_aree and different id_dis. So with the second query, I fetch id_dis_aree from id_dis, to affect more rows.

Edit2: I did 2 tests:

  1. substituting the last subselect directly with a fixed wired id value: WHERE id_dis_aree=123456 In this way it works. This could be a solution workaround, fetching id_dis_aree and after calling the query.

  2. substituting the last subselect with this: WHERE id_dis_aree IN (select id_dis_aree from dis_area where id_dis=?1) Doesn't work. (Memo: the subselect return always a single value).

标签： postgresqlspring-bootspring-data-jpa

解决方案

I didn't find a true solution, but just a workaround:

I fetched the value from the subquery: (select id_dis_aree from dis_area where id_dis=?1) in the @Service by calling disAreeRepository.findByIdIdDis(idDis).getId().getIdDisAree()

@Transactional
public Integer insertDis(Integer idDis, String shapeGeoJson) {
    return disAreeRepository.
            insertShape(
                    idDis,
                    shapeGeoJson,
                    disAreeRepository.findByIdIdDis(idDis).getId().getIdDisAree() 
                    );
}

And then passed it to @Repository native query as third parameter:

@Modifying(flushAutomatically = true, clearAutomatically = true)
@Query(value = "WITH tmp AS (SELECT ST_Difference( (SELECT ST_Buffer(ST_Union(ST_Buffer(a.area\\:\\:geometry, 0.002)), -0.002) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city), \n" + 
        "                     (ST_Difference( ST_GeomFromGeoJSON(?2)\\:\\:geometry, (SELECT ST_Union(a.area\\:\\:geometry) \n" + 
        "   FROM mydb.city_area a, mydb.dis_city d, mydb.city c \n" + 
        "   where c.id_city=d.id_city and d.id_dis=?1  \n" + 
        "   and c.cod_city=a.cod_city and c.full_area=true)                          \n" + 
        "                       ))                                      \n" + 
        "                   ) AS final_area)\n" + 
        "UPDATE mydb.dis_area SET new_area=(SELECT final_area FROM tmp), " +
        "id_type=3 " +
        "WHERE id_dis_aree=?3 ",
        nativeQuery = true)
Integer insertShape(Integer id, String shapeGeoJson, Integer idDisAree);

推荐阅读